Friday, May 22, 2015

Backing up and restoring a 12C Pluggable Database, plus new RMAN stuff

Having just installed V12C again, I’m going back to the basics – backup and restore. I want to see how pluggable databases are handled. No sites we support are currently running 12C in Production, but if one comes along I don’t want to be caught out with something as simple as a restore.It’s just as well that I did this, because it turns out that under certain circumstances a PDB, or a tablespace in a PDB is not easily recoverable.
Bottom line – dropping a PDB or a tablespace in a PDB if you’re not using an RMAN catalogue means recovering it is hard. You need to use an auxiliary database and restore the control file. A corrupt or missing datafile is recoverable from RMAN directly.

This explains it in more detail:

This is my first test, which didn’t work, but is here for documentation purposes.

Set the database to archivelog mode if it isn’t already, and increase the size of the fra:

SQL> alter system set db_recovery_file_dest_size=25000m;
System altered.

I created a pluggable database (see my earlier post), and changed the backup redundancy in RMAN to 3 so that I have a few backups to work with

rman target /
configure retention policy to redundancy 3;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters are successfully stored

RMAN 12C has a few options – you can backup everything (using ‘backup database;’), or the root, or PDBs, or tablespaces.

This is quite handy, and each one has advantages and disadvantages. Taking a whole backup means you just run the one command and it backs up everything. The good thing about this is that you don’t have to recover everything, you can just specify one of the components.

Another new thing is the ‘SYSBACKUP’ privilege – you can now create a user with this privilege to just perform backups.

Take a backup of everything as it is now:

backup database plus archivelog delete input;

This is part of the log – notice it contains the archived logs, the CDB (ORCL), and the PDBs (PDBORCL, ANDYPDB, APEXV5). I’ve removed some of the files referenced for brevity:

Starting backup at 22-MAY-15
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=118 RECID=10 STAMP=880382596
channel ORA_DISK_1: starting piece 1 at 22-MAY-15
channel ORA_DISK_1: finished piece 1 at 22-MAY-15
piece handle=D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2015_05_22\O1_MF_ANNNN_TAG20150522T144317_BOXDZ5TV_.BKP tag=TAG20150522T144317 comment=N
ONE
Finished backup at 22-MAY-15

Starting backup at 22-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: starting piece 1 at 22-MAY-15
channel ORA_DISK_1: finished piece 1 at 22-MAY-15
piece handle=D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2015_05_22\O1_MF_NNNDF_TAG20150522T144319_BOXDZ95S_.BKP tag=TAG20150522T144319 comment=N
ONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=D:\ORADATA\ORCL\PDBORCL\EXAMPLE01.DBF
channel ORA_DISK_1: starting piece 1 at 22-MAY-15
channel ORA_DISK_1: finished piece 1 at 22-MAY-15
piece handle=D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\515E550C2C4B416BADC26762A04A9354\BACKUPSET\2015_05_22\O1_MF_NNNDF_TAG20150522T144319_BOXF1Z0M_.BKP
 tag=TAG20150522T144319 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00026 name=D:\ORADATA\APEXV5\APEX_1680921507026813.DBF
channel ORA_DISK_1: starting piece 1 at 22-MAY-15
channel ORA_DISK_1: finished piece 1 at 22-MAY-15
piece handle=D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\535D7505070D462CBDB3292603CEAF2A\BACKUPSET\2015_05_22\O1_MF_NNNDF_TAG20150522T144319_BOXF3Q05_.BKP
 tag=TAG20150522T144319 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00018 name=D:\ORADATA\ANDYPDB\ANDYTBS_01.DBF
channel ORA_DISK_1: starting piece 1 at 22-MAY-15
channel ORA_DISK_1: finished piece 1 at 22-MAY-15
piece handle=D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\A321301A4072474184C54EE928D935DD\BACKUPSET\2015_05_22\O1_MF_NNNDF_TAG20150522T144319_BOXF5GDQ_.BKP
 tag=TAG20150522T144319 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF
channel ORA_DISK_1: starting piece 1 at 22-MAY-15
channel ORA_DISK_1: finished piece 1 at 22-MAY-15
piece handle=D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\4BC4C5138BA04423BF0194C261F133B3\BACKUPSET\2015_05_22\O1_MF_NNNDF_TAG20150522T144319_BOXF67P6_.BKP
 tag=TAG20150522T144319 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 22-MAY-15

Starting Control File and SPFILE Autobackup at 22-MAY-15
piece handle=D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2015_05_22\O1_MF_S_880382862_BOXF7JM6_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-15

OK, I’ll make some changes to a pluggable database and then take another backup of just that PDB.

I’ll create a user in the pdb for performing the backups – sign into the CDB and set the container:

SQL> alter session set container=andypdb;

Session altered.

SQL> create user backupuser identified by backupuser;

User created.

SQL> grant sysbackup to backupuser;

Grant succeeded.

Now sign into the container from RMAN (note, the tnsnames.ora entry for the container needs to be present):

rman target backupuser@andypdb

Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 22 14:58:57 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: ORCL (DBID=1407389917)

Now I’ll see the tablespaces in the PDB:

RMAN> select tablespace_name from dba_tablespaces;

using target database control file instead of recovery catalog
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
ANDYTBS

See what I did there? This is one of the new RMAN 12C features – you can now issue SQL commands from within RMAN.

So, create a new tablespace – you can do that from RMAN as well:

RMAN> create tablespace rmantest datafile 'd:\oradata\andypdb\rmantest_01.dbf' size 50m;
using target database control file instead of recovery catalog
Statement processed

I’ll take a backup of just this PDB:

RMAN> backup pluggable database andypdb;

Starting backup at 22-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 05/22/2015 15:06:01
RMAN-07538: Pluggable Database qualifier not allowed when connected to a Pluggable Database

Ah. At least the error is obvious. Exit from RMAN and then connect back into the CDB:

RMAN> exit
rman target /
RMAN> backup pluggable database andypdb;
Starting backup at 22-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=369 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=D:\ORADATA\ANDYPDB\SYSAUX01.DBF
input datafile file number=00016 name=D:\ORADATA\ANDYPDB\SYSTEM01.DBF
input datafile file number=00018 name=D:\ORADATA\ANDYPDB\ANDYTBS_01.DBF
input datafile file number=00033 name=D:\ORADATA\ANDYPDB\RMANTEST_01.DBF
channel ORA_DISK_1: starting piece 1 at 22-MAY-15
channel ORA_DISK_1: finished piece 1 at 22-MAY-15
piece handle=D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\A321301A4072474184C54EE928D935DD\BACKUPSET\2015_05_22\O1_MF_NNNDF_TAG20150522T150723_BOXGDDD4_.BKP
 tag=TAG20150522T150723 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 22-MAY-15

Starting Control File and SPFILE Autobackup at 22-MAY-15
piece handle=D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2015_05_22\O1_MF_S_880384079_BOXGFKFQ_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-15

Note that you can backup multiple pluggable databases in the one line by separating them with a comma:

RMAN> backup pluggable database andypdb,apexv5;

Check the file is in the backup:

RMAN> list backup of database;

List of Backup Sets
===================

  List of Datafiles in backup set 47
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  16      Full 3953070    22-MAY-15 D:\ORADATA\ANDYPDB\SYSTEM01.DBF
  17      Full 3953070    22-MAY-15 D:\ORADATA\ANDYPDB\SYSAUX01.DBF
  18      Full 3953070    22-MAY-15 D:\ORADATA\ANDYPDB\ANDYTBS_01.DBF
  33      Full 3953070    22-MAY-15 D:\ORADATA\ANDYPDB\RMANTEST_01.DBF

I’ll connect back into the pluggable database in RMAN and drop the new tablespace:

rman target backupuser@andypdb

Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 22 15:10:04 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: ORCL (DBID=1407389917)

RMAN> select tablespace_name from dba_tablespaces;

using target database control file instead of recovery catalog
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
ANDYTBS
RMANTEST

RMAN> drop tablespace rmantest including contents and datafiles;

Statement processed


RMAN> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
ANDYTBS

Oh dear, the tablespace has gone.

I’ll restore it from the backup I just took, in this case restoring the whole PDB:

First, shutdown the database, then mount it and restore the database. You can do it from the CDB or the PDB, but the commands are different:

From the PDB it’s ‘restore database;’, from the CDB it’s ‘restore pluggable database pdb_name;’

RMAN> shutdown immediate

database closed

RMAN> restore database;

Starting restore at 22-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00016 to D:\ORADATA\ANDYPDB\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00017 to D:\ORADATA\ANDYPDB\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00018 to D:\ORADATA\ANDYPDB\ANDYTBS_01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\A321301A4072474184C54EE928D935DD\BACKUPSET\2015_05_22\O1_MF_NNNDF
_TAG20150522T163844_BOXMQNWJ_.BKP
channel ORA_DISK_1: piece handle=D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\A321301A4072474184C54EE928D935DD\BACKUPSET\2015_05_22\O1_MF_NNNDF_TAG20150522T
163844_BOXMQNWJ_.BKP tag=TAG20150522T163844
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 22-MAY-15

This is where I noticed a problem – the RMANTEST tablespace datafile is not restored.

Checking the backup:

  List of Datafiles in backup set 63
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  16      Full 3956317    22-MAY-15 D:\ORADATA\ANDYPDB\SYSTEM01.DBF
  17      Full 3956317    22-MAY-15 D:\ORADATA\ANDYPDB\SYSAUX01.DBF
  18      Full 3956317    22-MAY-15 D:\ORADATA\ANDYPDB\ANDYTBS_01.DBF
  35      Full 3956317    22-MAY-15

It turns out that dropping a PDB or a tablespace within a PDB deletes references from the controlfile. Since I’m not using an RMAN catalogue, I can’t restore it without creating an auxiliary database and restoring the controlfile first.

That’s something to bear in mind.

I did the test again, but this time deleted the datafile instead.

Backed it up:

  List of Datafiles in backup set 65
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  16      Full 3956997    22-MAY-15 D:\ORADATA\ANDYPDB\SYSTEM01.DBF
  17      Full 3956997    22-MAY-15 D:\ORADATA\ANDYPDB\SYSAUX01.DBF
  18      Full 3956997    22-MAY-15 D:\ORADATA\ANDYPDB\ANDYTBS_01.DBF
  36      Full 3956997    22-MAY-15 D:\ORADATA\ANDYPDB\RMANTEST_01.DBF

I deleted the file at the OS level, then  reopened the database, and tried to connect to the PDB:

SQL> alter session set container=andypdb;

Session altered.

SQL> select tablespace_name from dba_tablespaces;
select tablespace_name from dba_tablespaces
                            *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 36 - see DBWR trace file
ORA-01110: data file 36: 'D:\ORADATA\ANDYPDB\RMANTEST_01.DBF'

OK, let’s try a restore now:

D:\app\oracle\product\12.1.0\dbhome_1\NETWORK\ADMIN>rman target backupuser@andypdb

Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 22 16:53:07 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: ORCL (DBID=1407389917, not open)

RMAN> restore database;

Starting restore at 22-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00016 to D:\ORADATA\ANDYPDB\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00017 to D:\ORADATA\ANDYPDB\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00018 to D:\ORADATA\ANDYPDB\ANDYTBS_01.DBF
channel ORA_DISK_1: restoring datafile 00036 to D:\ORADATA\ANDYPDB\RMANTEST_01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\A321301A4072474184C54EE928D935DD\BACKUPSET\2015_05_22\O1_MF_NNNDF
_TAG20150522T164715_BOXN7N6N_.BKP
channel ORA_DISK_1: piece handle=D:\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\A321301A4072474184C54EE928D935DD\BACKUPSET\2015_05_22\O1_MF_NNNDF_TAG20150522T
164715_BOXN7N6N_.BKP tag=TAG20150522T164715
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 22-MAY-15

RMAN> recover database;

Starting recover at 22-MAY-15
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 22-MAY-15

RMAN> alter database open;

Statement processed


The tablespace is back:

RMAN> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
ANDYTBS
RMANTEST

So, I discovered something interesting – don’t drop a PDB or a tablespace in a PDB unless you really want it gone, because getting it back is hard.


Next time I’ll look at more fine grained restores.

Thursday, May 21, 2015

Importing a V4.2 APEX application into APEX V5 (and changing the theme)

I thought I’d see how easy or hard it is to import a V4.2 application into V5. Short answer - very easy.

Create a schema in the target database, probably a good idea to make it the same as the source so that the import is easy.

Export the application tables / data from the source database and import it into the target so that the APEX app will work. No data, no work.

Export the application from the V4.2 APEX environment - it gets output as an SQL file.


Create a new workspace in the V5 APEX environment.

Sign in as Admin and click on ‘Manage Workspaces /  Create Workspace’:


Give it a name, and click ‘Next’:


Select ‘Yes’ and choose the schema (or create a new one if you like):


Click ‘Next’

Enter the Admin details:

Click 'Next


If the details are OK, click ‘Create Workspace’.



Click ‘Done’.

Now, create the APEX user and assign the workspace. Click on ‘Manage Workspace / Manage Developers and Users’:


Click 'Create User'


Enter the details, I tend to allow the user to be an administrator in a Test environment because it’s me doing the development.



I also tend to set the password and not change it on first login:


Click 'Create User'


Log out of the ADMIN user and in as the new user


Click on ‘Application Builder / Import’


Click on ‘Choose File’ and browse to the exported V4.2 sql file




Click ‘Next’

If all OK, click ‘Next’ to install


I like to keep the application id, it’s up to you.



Click ‘Install Application’

Once done, click ‘Next’



Click ‘Install’


If all goes well, the application will be installed


Click on ‘Run Application’ (or edit if you like)
.
Looks the same as the V4.2 version (data obscured for obvious reasons)


So that I can make sure I don’t get confused between the live app and the one I've just imported, I decided to change the theme to distinguish them. This is also useful to know in case you want to change the style of an application later after creation.

First, you need to create a new theme for the application, based on an existing one.

Click on ‘Application nnnn’ in the edit bar:


At the top right, click on the ‘Shared Components’ icon:


In the middle of the page, click on the ‘Themes’ link


Click on the ‘Create’ button:


Select ‘From the Repository’ and click ‘Next'


Leave ‘Desktop’ and click ‘Next’


Select ‘All Themes’ and pick a theme from the drop-down:



Click ‘Next’

Click ‘Create’

The theme will now appear:


At the top-right, click ‘Switch Theme’


Select the themes from the drop-down menus


Click 'Next'


If all OK, click 'Next'


Click on 'Switch Theme'


The new theme will now be set as the current:one


Click on the ‘Application nnn’ at the top left of the page to return to the main page:


Click on ‘Run Application’ to see the new theme:



So, importing from a V4.2 application and switching the theme is pretty straight forward.

























Tuesday, May 19, 2015

Creating an APEX V5 Application – Part 2

In my earlier post I described how to create a basic app to display tablespace information in APEX V5, which is quite different from V4.

I’ll continue to play around, and put my experiences here. This time I'll hide some columns from the report, add a new region and move it around the page. Exciting!


So, open the page created in the previous post :


This has too much information, so I want to hide some of the columns.

Click on ‘Edit Page 1’ to enter edit mode:


OK, where to start?

I could just rewrite the SQL, but this is an APEX article.

In the left hand pane, expand the ‘Columns’ item:


I want to hide a few of these. So, I select one I want to hide, and then in the right-hand pane select  the ‘Hidden’ drop-down item under ‘Type’:


Save it, and then run the page using the ‘Run’ icon:


It's gone!



So, just repeat for the other columns. You can ‘ctrl-click’ or ‘shift-click’ in the left hand pane to multi-select.



And set them all to ‘Hidden’ in the right-hand pane:


The page looks tidier now.


One thing I noticed while doing this – the edit page and ‘normal’ page open in different tabs – this makes editing a lot faster as it just switches tabs.


Nice.

OK, let’s add a new region that shows the host name and database name and startup time.
Instead of drag and drop, you can use the old fashioned way. Click on the ‘Create’ icon:


At the pop-up, click on the type (I tend to chose Classic):


Give it a name and change details as required:


Click ‘Next’

If you were basing this report on a table, enter the details here:


Otherwise click on ‘SQL Query’ and enter the SQL:


Click ‘Next’

Change any settings here – for example, I know this will only be 1 row.


Click 'Next'



Click ‘Create Region’


The new region appears:


Click 'Save' and 'Run'


Note that because I set my date formats when creating the application in the earlier post, it shows the time – if not, you can change this in the column formatting.

OK, it looks good, but I want to see the database details above the tablespace table.

Go back to edit mode.


When creating regions in the previous version, you needed to enter the sequence number to determine where they appeared in the page. This new version allows you to drag and drop. So just click on the ‘Database Details’ section and drag and drop it above the ‘Tablespaces’ section:


Another nice feature.

Click 'Save' and 'Run'




So that's some more of the basic functionality in the new version. Next time I’ll look at adding links to a column item to direct to another page.