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:

http://reneantunez.blogspot.mx/2014/06/rman-12c-say-goodbye-to-your-backup.html

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.

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.

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.