2014546antunezppr-140412175517-phpapp01

COLLABORATE 14 – IOUG Forum Database - HA

RMAN IN 12c : THE NEXT GENERATION Gustavo René Antúnez, The Pythian Group

A BSTRACT BSTRACT Oracle Recovery Manager (Oracle RMAN) has evolved since being released in version 8i. With the newest version of Oracle 12c, RMAN has great new features that will allow you to reduce your down time in case of a disaster. In this session paper you will learn about the new features that were introduced in Oracle 12c and how can you take advantage of them from the first day you upgrade to this version.

T ARGET A UDIENCE UDIENCE This paper will wi ll be beneficial benefic ial for anyone who w ho is beginning beginni ng to use Oracle’s Orac le’s Recovery Manager and for those thos e who are already using it, but want to understand the several new features of RMAN in Oracle 12c.

EXECUTIVE SUMMARY Learner will be able to: • • • •

Understand what is RMAN and how has it evolved along versions of Oracle Obtain the knowledge of the newest features of RMAN in Oracle Database 12c and how to apply them. Provide examples of backup and recovery solutions with RMAN 12c How RMAN and EM 12c are moving hand in hand with DBaaS

RMAN IN 12C: THE NEXT GENERATION The new generation ge neration of Oracle’s Oracle ’s popular database has been designed des igned for the Cloud C loud and will enable you to make more efficient use your IT resources while continuing to improve your users’ service levels. With this, there are enhancements and new features, which will make it easier for customers to take advantage of the Cloud. In here we will discuss several of these new features, but only specific to Oracle’s backup and recovery utility RMAN. The first thing th ing that we have to do, is understand unde rstand that in Oracle O racle 12c, a new concept c oncept is introduced, in troduced, which wh ich are Pluggable Pluggabl e and Container databases.

WHAT IS A CONTAINER AND A PLUGGABLE D ATABASE ? A container database d atabase (CDB) is an Oracle databas e that includes inclu des zero, one, or many customer-created custome r-created Containers Cont ainers or Pluggable Databases. The CDB has: •

•

1|P a g e

One ROOT container (CDB$ROOT) containing SYSTEM, SYSAUX, UNDO, and TEMP tablespaces, Controlfiles and Redologs. One SEED container (PDB$SEED) containing SYSTEM, SYSAUX, TEMP, EXAMPLE tablespaces, used as a template to create new PDBs

“ R MA M A N I N 1 2 C: C : T H E N E XT X T G E NE N E R AT A T I ON ON ” W h i t e P a p e r


A pluggable Database D atabase (PDB) is a user-created user-creat ed container holding the data dat a and code for your y our specific applications. A PDB: •

Has SYSTEM, SYSAUX, and TEMP tablespaces.

•

Contains any number of other user created tablespaces.

•

•

Writes to the t he container UNDO U NDO tablespace, tablespace , controlfiles and redologs. Undo and redo is annotated with details of the PDB that they belong to.

GENERAL B ACKUP AND R ECOVERY –O VERVIEW OF RMAN ECOVERY Now we have to understand what RMAN is and how RMAN works. Recovery Manager or better known as RMAN, is an Oracle client utility that is installed with the Enterprise or Standard edition, you can also find it with the Admin option when installing install ing the Oracle Client. By being a client side utility, it allows you to use one RMAN executable version to backup current and previous versions of the Oracle Database; there are some restrictions to this though, which can be verified in MOS document RMAN Compatibility Matrix [ID 73431.1]. This RMAN executable uses a file called c alled recover.bsq, recover.bs q, this file is located in $ORACLE_HOME/rdbms/admin, $ORACLE_HOME/r dbms/admin, basically basic ally what the executable exe cutable does, does , is to interpret interpre t the commands comman ds you give it, it , direct server serve r sessions to execute those th ose commands, and record its activity in the TARGET database control file that is being backed up. There are two tw o main SYS packages pac kages that do the work of backup and an d recovery, which whi ch are DBMS_RCVMAN, DBMS_R CVMAN, this has h as the procedures which list your database incarnations, the set until time recovery window, list your backups, to name a few, and DBMS_BACKUP_RESTORE , which as you might have guessed is the one who does the backup and recovery operations, like create the control file snapshot , backup the datafiles, backup the spfile to name some. As mentioned mention ed above, the way w ay that the RMAN RM AN client directs d irects the server s erver sessions to execute the commands are through channels, a channel represents one stream of data to a device, and corresponds to one database server session. The channel reads data into PGA memory, processes it, and writes it to the output device. The work of each e ach channel, whether wh ether of type disk or System Syst em Backup Tape (SBT), is subdivided s ubdivided into int o the following follow ing distinct phases: Read Phase A channel reads read s blocks from disk di sk into input I/O buffers. The Th e allocation of these buffers depends on the t he number of datafiles being read simultaneously from disk and written to the same backup piece. One way to control the numbers of files is the backup parameter FILESPERSET

•

Copy Phase A channel copies co pies blocks from fro m input buffers buffe rs to output buffers b uffers and performs perform s additional processing proc essing on the th e blocks, like the validation of the data blocks, as it verifies that it's not backing up corrupt data blocks, it's also the phase where it does the binary compression and the backup encryption

•

Write Phase A channel writes w rites the blocks blo cks from output buffers to storage st orage media. The write phase can c an be either to t o SBT or to disk, and these are mutually exclusive, meaning you write to one or the other, not both. As you can see by the phases p hases above, and what w hat distinguishes distingui shes RMAN from fro m any other method, me thod, is that the th e backup is at the t he block level, as to the user managed backups, it brings great advantages, as it wont have to backup empty blocks. •

2|P a g e

“ R MA M A N I N 1 2 C: C : T H E N E XT X T G E NE N E R AT A T I ON ON ” W h i t e P a p e r


WHAT DO YOU NEED TO BACKUP ? When you consider on using Oracle’s RMAN utility, there are files that are critical to backup so that you can be able to restore and recover your database in case a disaster were to occur: •

Data files

•

Control files

•

Archived Redo logs (If Database running in Archive Log mode)

But let’s say a full on disaster were to happen, in which not only you lose your database, you lose your site or your server, you need some additional files are needed so that you can have your data available as soon as possible: •

Parameter File (pfile1 or spfile)

•

Block Change Tracking File1

•

ORACLE_HOME/GRID_HOME1

•

tnsnames.ora / listener.ora/ sqlnet.ora 1

These additional files are not critical in RMAN to be backed up, but are very important in case a full disaster were to occur, as if you were not to have a backup of these, you would need to rebuild your ORACLE_HOME / GRID_HOME from scratch, taking you more time to have the data available to your client. These files are not backe d up with Oracle’s RMAN util ity, you need to use a distinct me thod to back up these types of files .

1

ORACLE RMAN 12C NEWEST FEATURES !" #$#%!&'() ) +,-,./0/ In Oracle 12c the SYSBACKUP privilege was introduced, this privilege allows a user to perform backup and recovery operations RMAN. You can connect with the “as sysbackup” or if using a password, a password file needs to be created or in place. There are also special OS System Groups (OSDBA or OSBACKUPDBA), which allow you to connect to your database as a privilege user without the need of an additional password and perform your backups. One thing to keep in mind for windows environments, you need to have in your sqlnet.ora the next entry: SQLNET.AUTHENTICATION_SERVICES=(NTS).

OS System Group Name

UNIX/Linux

Windows

OSDBA

dba

for all Oracle homes ORA_DBA ( ) ORA_HOMENAME_DBA ( ) for each specific Oracle home

OSBACKUPDBA

backupdba

ORA_HOMENAME_SYSBACKUP

3|P a g e

“ R MA N I N 1 2 C: T H E N E XT G E NE R AT I ON ” W h i t e P a p e r


But what is most important about this is that it allows you to not grant the SYSDBA privilege to the user that runs and administers your backups, so that user will no longer be able to SELECT/INSERT/UPDATE/DELETE ANY TABLE. [oracle@oracleenespanol2 Desktop]$ rman target '"/ as sysbackup"' connected to target database: CDB1 (DBID=808250731) RMAN> select privilege from dba_sys_privs where grantee='SYSBACKUP'; PRIVILEGE ---------------------------------------ALTER SYSTEM AUDIT ANY SELECT ANY TRANSACTION SELECT ANY DICTIONARY RESUMABLE CREATE ANY DIRECTORY UNLIMITED TABLESPACE ALTER TABLESPACE ALTER SESSION ALTER DATABASE CREATE ANY TABLE DROP TABLESPACE CREATE ANY CLUSTER 13 rows selected

Having this privilege allows you to perform the following operations and as well enables you to connect to the database even if the database is not open. • • • • • • • • •

•

•

•

STARTUP/ SHUTDOWN ALTER DATABASE/ SYSTEM/ SESSION/ TABLESPACE CREATE CONTROLFILE/ ANY DIRECTORY/ ANY TABLE/ PFILE/ SPFILE / ANY CLUSTER/ SESSION CREATE RESTORE POINT (including GUARANTEED restore points) DROP DATABASE/ TABLESPACE/ RESTORE POINT (including GUARANTEED restore points) FLASHBACK DATABASE/ RESUMABLE UNLIMITED TABLESPACE SELECT ANY DICTIONARY/ ANY TRANSACTION SELECT o X$ tables (that is, the fixed tables) o V$ and GV$ views (that is, the dynamic performance views) o APPQOSSYS.WLM_CLASSIFIER_PLAN o SYSTEM.LOGSTDBY$PARAMETERS DELETE/INSERT o SYS.APPLY$_SOURCE_SCHEMA o SYSTEM.LOGSTDBY$PARAMETERS EXECUTE o SYS.DBMS_BACKUP_RESTORE o SYS.DBMS_RCVMAN o SYS.DBMS_DATAPUMP o SYS.DBMS_IR o SYS.DBMS_PIPE o SYS.SYS_ERROR o SYS.DBMS_TTS o SYS.DBMS_TDB o SYS.DBMS_PLUGTS o SYS.DBMS_PLUGTSP SELECT_CATALOG_ROLE

4|P a g e



%" #())1+2 31+ 4(.2,2/5!52 &152!,5/+ 6!2!%!#/# !56 ).(00!%./ 6!2!%!#/# With the RMAN utility it provides full backup and recovery support, not only for your container database, but also for one or all of your pluggable databases. With this in mind the first thing that you want to do is identify how your database is composed. You can do this with the report schema command. When you use it in a container level, you can see that your pluggable databases files are preceded with the name of it. When you use it at the pluggable level, you will only see the files that belong to that particular database. [oracle@oracleenespanol2 admin]$ rman target sys/oracle@cdb1 connected to target database: CDB1 (DBID=808250731) RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name CDB1 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- -----------------------1 790 SYSTEM *** /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c522mbz_.dbf 3 880 SYSAUX *** /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c520w6w_.dbf 4 125 UNDOTBS1 *** /u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_9c524cnr_.dbf 5 250 PDB$SEED:SYSTE M *** /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c5257ms_.dbf 6 5 USERS *** /u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_9c524bjm_.dbf 7 590 PDB$SEED:SYSAU X *** /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c5257mj_.dbf 8 260 *** PDB1:SYSTEM /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf 9 620 *** PDB1:SYSAUX /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf 10 5 *** PDB1:USERS /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------1 152 TEMP 32767 /u01/app/oracle/oradata/CDB1/datafile/o1_mf_temp_9c5254yc_.tmp 2 20 PDB$SEED:TEMP 32767 /u01/app/oracle /oradata/CDB1/da tafile/pdbseed_ temp01.dbf 3 505 32767 PDB1:TEMP /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_temp_9c52h0kf_.dbf RMAN> exit

Recovery Manager complete. [oracle@oracleenespanol2 admin]$ rman target sys/oracle@ pdb1 Recovery Manager: Release 12.1.0.1.0 - Production on Fri Jan 24 21:55:11 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=808250731) RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name CDB1 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------

5|P a g e


COLLABORATE 14 – IOUG Forum Database - HA 8 260 SYSTEM *** /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf 9 620 SYSAUX *** /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf 10 5 USERS *** /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------3 505 TEMP 32767 /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_temp_9c52h0kf_.dbf

Backing up your databases in 12c does not differ much as with previous versions as long as you understand the hierarchy that persists with this version. When backing up the container database ( CDB1 ), it will backup your root and all of your pluggable databases files. With this type of backup it will allow you to recover one or all of your pluggable databases as well as your container database. [oracle@oracleenespanol2 admin]$ rman target sys/oracle@cdb1 connected to target database: CDB1 (DBID=808250731) RMAN> backup database plus archivelog; Starting backup at 25-JAN-14 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=82 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=42 RECID=39 STAMP=837794802 channel ORA_DISK_1: starting piece 1 at 25-JAN-14 channel ORA_DISK_1: finished piece 1 at 25-JAN-14 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_01_25/o1_mf_annnn_TAG20140125T164643_9g8m7 3h2_.bkp tag=TAG20140125T164643 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 25-JAN-14 Starting backup at 25-JAN-14 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=00003 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c520w6w_.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c522mbz_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_9c524cnr_.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_9c524bjm_.dbf channel ORA_DISK_1: starting piece 1 at 25-JAN-14 channel ORA_DISK_1: finished piece 1 at 25-JAN-14 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_01_25/o1_mf_nnndf_TAG20140125T164644_9g8m7 4qz_.bkp tag=TAG20140125T164644 comment=NONE 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=00009 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf input datafile file number=00010 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf channel ORA_DISK_1: starting piece 1 at 25-JAN-14 channel ORA_DISK_1: finished piece 1 at 25-JAN-14 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_01_25/o1_ mf_nnndf_TAG20140125T164644_9g8m9svd_.bkp tag=TAG20140125T164644 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting full datafile backup set

6|P a g e


COLLABORATE 14 – IOUG Forum Database - HA channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c5257mj_.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c5257ms_.dbf channel ORA_DISK_1: starting piece 1 at 25-JAN-14 channel ORA_DISK_1: finished piece 1 at 25-JAN-14 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/EDDDB886A1191F07E043344EB2C0BE27/backupset/2014_01_25/o1_ mf_nnndf_TAG20140125T164644_9g8mbwz1_.bkp tag=TAG20140125T164644 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 Finished backup at 25-JAN-14 Starting backup at 25-JAN-14 current log archived using channel ORA_DISK_1 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=43 RECID=40 STAMP=837794960 channel ORA_DISK_1: starting piece 1 at 25-JAN-14 channel ORA_DISK_1: finished piece 1 at 25-JAN-14 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_01_25/o1_mf_annnn_TAG20140125T164920_9g8md 08p_.bkp tag=TAG20140125T164920 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 25-JAN-14 Starting Control File and SPFILE Autobackup at 25-JAN-14 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2014_01_25/o1_mf_s_837794961_9g8md1o5_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 25-JAN-14

When you only connect to your pluggable ( PDB1 ), you can also make a backup of this database, but it will only allow you to recover that particular database. One thing to keep in mind, is that if you include your archivelogs when backing up your database, it doesn’t do an archivelog switch, this will only happen at the container level, when you do it at a pluggable level, it will tell you that there are no archivelogs in the repository. [oracle@oracleenespanol2 admin]$ rman target sys/oracle@ pdb1 Recovery Manager: Release 12.1.0.1.0 - Production on Sat Jan 25 16:54:00 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=808250731) RMAN> backup database plus archivelog; Starting backup at 25-JAN-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=82 device type=DISK

specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 25-JAN-14 Starting backup at 25-JAN-14 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=00009 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf input datafile file number=00010 name=/u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf channel ORA_DISK_1: starting piece 1 at 25-JAN-14 channel ORA_DISK_1: finished piece 1 at 25-JAN-14 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_01_25/o1_ mf_nnndf_TAG20140125T165412_9g8mo4t7_.bkp tag=TAG20140125T165412 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 Finished backup at 25-JAN-14

7|P a g e


COLLABORATE 14 – IOUG Forum Database - HA Starting backup at 25-JAN-14 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 25-JAN-14 Starting Control File and SPFILE Autobackup at 25-JAN-14 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2014_01_25/o1_mf_s_837795277_9g8moy7n_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 25-JAN-14

In this version, Oracle recommends that you frequently backup your root database as it contains critical metadata for the whole CDB, while each PDB might fall into it’s own SLA’s depending on the lifecycle of it or of your business needs. The way to backup your root is connecting to the target and issue the backup database root command. [oracle@oracleenespanol2 admin]$ rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Sat Jan 25 17:12:31 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=808250731) RMAN> backup database root; Starting backup at 25-JAN-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=82 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=00003 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_9c520w6w_.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_9c522mbz_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_9c524cnr_.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_9c524bjm_.dbf channel ORA_DISK_1: starting piece 1 at 25-JAN-14 channel ORA_DISK_1: finished piece 1 at 25-JAN-14 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_01_25/o1_mf_nnndf_TAG20140125T171238_9g8nq p8h_.bkp tag=TAG20140125T171238 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 Finished backup at 25-JAN-14 Starting Control File and SPFILE Autobackup at 25-JAN-14 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2014_01_25/o1_mf_s_837796413_9g8nsfo0_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 25-JAN-14

8|P a g e



As with previous versions of RMAN, one way to know what backups you have is with the command LIST, and with the report has been added the record of Container ID, which will allow you to identify to which database your backup belongs to, keeping in mind that your container database will not have a Container ID reported, it is assumed that is the ID number 1. RMAN> list backup tag TAG20140125T164644; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------11 Full 1.34G DISK 00:01:19 25-JAN-14 BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20140125T16 4644 Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_01_25/o1_mf_nnndf_TAG20140125T164644_9g8m74qz_.bk p List of Datafiles in backup set 11 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---1 Full 2230796 25-JAN-14 /u01/app/oracle/ oradata/CDB1/da tafile/o1_mf_sys tem_9c522mbz_.db f 3 Full 2230796 25-JAN-14 /u01/app/oracle/ oradata/CDB1/da tafile/o1_mf_sys aux_9c520w6w_.db f 4 Full 2230796 25-JAN-14 /u01/app/oracle/ oradata/CDB1/dat afile/o1_mf_und otbs1_9c524cnr_. dbf 6 Full 2230796 25-JAN-14 /u01/app/oracle/ oradata/CDB1/da tafile/o1_mf_use rs_9c524bjm_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------12 Full 694.53M DISK 00:00:34 25-JAN-14 BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20140125T16 4644 Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/backupset/2014_01_25/o1_mf_nnnd f_TAG20140125T164644_9g8m9svd_.bkp List of Datafiles in backup set 12

Container ID: 3, PDB Name: PDB1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---8 Full 2230931 25-JAN-14 /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_system_9c52fqt1_.dbf 9 Full 2230931 25-JAN-14 /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_sysaux_9c52fqvt_.dbf 10 Full 2230931 25-JAN-14 /u01/app/oracle/oradata/CDB1/EDDDC5E35CF7216DE043344EB2C0AB6F/datafile/o1_mf_users_9c52hof0_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------13 Full 680.13M DISK 00:00:31 25-JAN-14 BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20140125T16 4644 Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/EDDDB886A1191F07E043344EB2C0BE27/backupset/2014_01_25/o1_mf_nnnd f_TAG20140125T164644_9g8mbwz1_.bkp List of Datafiles in backup set 13

Container ID: 2, PDB Name: PDB$SEED File LV Type Ckp SCN ---- -- ---- ---------5 Full 1732663 7 Full 1732663

9|P a g e

Ckp Time --------18-DEC-13 18-DEC-13

Name ---/u01/app/oracle/ oradata/CDB1/da tafile/o1_mf_sys tem_9c5257ms_.db f /u01/app/oracle/ oradata/CDB1/da tafile/o1_mf_sys aux_9c5257mj_.db f



&" +789:7;<=> 2?@A7B ?=C 2?@A7 )?;D
In 12c, RMAN allows you to recover one or more tables/table partitions to a specific point in time without affecting the rest of your objects in your container or pluggable databases and without the need to restore the entire containing tablespace. Before you restore your table, the following prerequisites need to be met: •

The target database must be in read-write mode.

•

The target database must be in ARCHIVELOG mode.

•

•

•

You must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want recover these objects. You need to have at least 1 Gigabyte extra in memory for the auxiliary database. You need to have the physical disk space to hold the SYSTEM/SYSAUX/UNDO/TEMP, the physical disk space that was occupied by the table as well as the space of the datapump export that is generated.

•

As of version 12.1, you need to use a SERVICE_NAME when connecting to RMAN or it will fail.

•

You can use SCN, Time or a Log Sequence number

•

Tables and table partitions on standby databases cannot be recovered.

•

Tables with named NOT NULL constraints cannot be recovered with the REMAP option.

In the example below, we will restore the table TEST.RENE, with the command RECOVER TABLE , that was logically corrupted and remap the table to TEST.RENE_RCV, we are also running this from the container database CDB1, and so we have to specify that the table belongs to the pluggable database PDB1. Several lines have been removed to help readability. [email protected] [cdb1] /u01/app/oracle/exports oracle $ rman target sys/oracle@cdb1 Recovery Manager: Release 12.1.0.1.0 - Production on Sun Jan 26 17:09:12 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=808250731)

RMAN> RECOVER TABLE 'TEST'.'RENE' OF PLUGGABLE DATABASE PDB1 2> UNTIL TIME "to_date('26-JAN-2014 17:08:20','dd-MON-yyyy hh24:mi:ss')" 3> AUXILIARY DESTINATION '/u01/app/oracle/exports' 4> REMAP TABLE 'TEST'.'RENE':'RENE_RCV'; … RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time List of tablespaces expected to have UNDO segments Tablespace SYSTEM Tablespace UNDOTBS1 Creating automatic instance, with SID='obtp' initialization parameters used for automatic instance: db_name=CDB1 db_unique_name=obtp_pitr_PDB1_CDB1 compatible=12.1.0.0.0 db_block_size=8192 db_files=200 sga_target=1G processes=80 diagnostic_dest=/u01/app/oracle db_create_file_dest=/u01/app/oracle/exports log_archive_dest_1='location=/u01/app/oracle/exports' enable_pluggable_database=true _clone_one_pdb_recovery=true #No auxiliary parameter file used starting up automatic instance CDB1 Oracle instance started … Automatic instance created

10 | P a g e


COLLABORATE 14 – IOUG Forum Database - HA … Finished recover at 26/01/2014 17:12:24 sql statement: alter database open read only contents of Memory Script: { sql clone 'alter pluggable database PDB1 open read only'; } … contents of Memory Script: { # set requested point in time set until time "to_date('26-JA N-2014 17:08:20','dd-MO N-yyyy hh24:mi:ss')"; # online the datafiles restored or switched sql clone 'PDB1' "alter database datafile 10 online"; # recover and open resetlogs recover clone database tablespace "PDB1":"USERS" , "SYSTEM", "UNDOTBS1", "SYSAUX", "PDB1":"SYSTEM", "PDB1":"SYSAUX" delete archivelog; alter clone database open resetlogs; } … starting media recovery … database opened … sql statement: alter pluggable database PDB1 open contents of Memory Script: { # create directory for datapump import sql 'PDB1' "create or replace directory TSPITR_DIROBJ_DPDIR as '' /u01/app/oracle/exports''"; … Performing export of tables... EXPDP> Starting "SYS"."TSPITR_EXP_obtp_nBpo": … EXPDP> . . exported "TEST"."RENE" 5.062 KB 1 rows EXPDP> Master table "SYS"."TSPITR_EXP_obtp_nBpo" successfully loaded/unloaded EXPDP> ****************************************************************************** EXPDP> Dump file set for SYS.TSPITR_EXP_obtp_nBpo is: EXPDP> /u01/app/oracle/exports/tspitr_obtp_55157.dmp EXPDP> Job "SYS"."TSPITR_EXP_obtp_nBpo" successfully completed at Sun Jan 26 17:13:54 2014 elapsed 0 00:00:25 Export completed contents of Memory Script: { # shutdown clone before import shutdown clone abort } executing Memory Script Oracle instance shut down Performing import of tables... IMPDP> Master table "SYS"."TSPITR_IMP_obtp_zdmu" successfully loaded/unloaded … IMPDP> . . imported "TEST"."RENE_RC V" 5.062 KB 1 rows IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER IMPDP> Job "SYS"."TSPITR_IMP_obtp_zdmu" successfully completed at Sun Jan 26 17:14:10 2014 elapsed 0 00:00:04 Import completed … Removing automatic instance Automatic instance removed … Finished recover at 26/01/2014 17:14:13

11 | P a g e



6" #J. ,=D7;E?87 ,FI;9:7F7=DB In RMAN 12c, you can now execute SQL commands and PL/SQL procedures from the RMAN command line without prefixing the command with the SQL keyword. This includes select statements as well as the describe command which offers the same functionality as the SQL*plus describe command. You no longer need to enclose the SQL command in quotes, which greatly simplifies the syntax when the SQL command itself requires quotation marks. oracle $ rman target sys/oracle@cdb1 connected to target database: CDB1 (DBID=808250731) RMAN> select name from v$database; NAME --------CDB1 RMAN> desc cdb_data_files Name Null? ----------------------------------------- -------FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS CON_ID

Type ---------------------------VARCHAR2(513) NUMBER VARCHAR2(30) NUMBER NUMBER VARCHAR2(9) NUMBER VARCHAR2(3) NUMBER NUMBER NUMBER NUMBER NUMBER VARCHAR2(7) NUMBER

RMAN> select a.con_id,a.file_id,substr(a.file_name, instr(a.file_name, '/',-1)+1) file_name from cdb_data_files a; CON_ID FILE_ID FILE_NAME ---------- ---------- -----------------------------------------------------------2 5 o1_mf_system_9c 5257ms_.dbf 2 7 o1_mf_sysaux_9c 5257mj_.dbf 3 8 o1_mf_system_9c 52fqt1_.dbf 3 9 o1_mf_sysaux_9c 52fqvt_.dbf 3 10 o1_mf_users_9c5 2hof0_.dbf 1 6 o1_mf_users_9c5 24bjm_.dbf 1 4 o1_mf_undotbs1_ 9c524cnr_.dbf 1 3 o1_mf_sysaux_9c 520w6w_.dbf 1 1 o1_mf_system_9c 522mbz_.dbf 9 rows selected.

/" !8D<:7 6?D?@?B7 6HIA<8?D<9= ,FI;9:7F7=DB In Oracle 11g, Active duplicate was introduced; it is defined as a duplicate database that is created over a network without restoring backups of the target database. This technique is an alternative to backup-based duplication. In 11g this method used a push-based method, in which from the target database would send full image copies over the network to the auxiliary database, using the target channels.

12 | P a g e



In 12c, the default method is the pull-based method; RMAN sends datafile data as backupsets to the auxiliary instance and from within that instance, restores the datafiles. RMAN will create a new backupset on the source and directly transmit (through Sql*net) to the auxiliary. There are several factors that determine whether Backup Sets or Image Copies are used: •

•

RMAN will only use image copies when no auxiliary channels are allocated or when the number of allocated channels is less than the number of channels. RMAN will use Backup Sets when the connection to the target database is established using a net service name and any one of the following conditions is satisfied: o The DUPLICATE ... FROM ACTIVE DATABASE command contains either the USING BACKUPSET, USING COMPRESSED BACKUPSET, or SECTION SIZE clause. o The number of auxiliary channels allocated is equal to or greater than the number of target channels allocated.

[email protected] [cdb2] /home/oracle oracle $ rman target sys/oracle@cdb1 auxiliary sys/oracle@cdb2 connected to target database: CDB1 (DBID=808250731) connected to auxiliary database: CDB2 (not mounted) RMAN> DUPLICATE TARGET DATABASE TO cdb2 FROM ACTIVE DATABASE

PASSWORD FILE USING

COMPRESSED

; BACKUPSET

Starting Duplicate Db at 26/01/2014 22:25:43 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK current log archived contents of Memory Script: {

backup as copy reuse targetfile '/u01/app/oracl e/product/12.1.0 /db_1/dbs/orapwc db1' auxiliary format '/u01/app/oracle/product/12.1.0/db_1/dbs/orapwcdb2' ; } … Finished backup at 26/01/2014 22:25:45 contents of Memory Script: { … shutdown clone immediate; startup clone force nomount

restore clone from service

'cdb1' using compressed backupset

primary controlfile; alter clone database mount; } … Starting restore at 26/01/2014 22:25:52 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=19 device type=DISK channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using compressed network backup set from service cdb1 channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/u01/app/oracle/oradata/CDB2/controlfile/o1_mf_9c524smo_.ctl output file name=/u01/app/oracle/fast_recovery_area/CDB2/controlfile/o1_mf_9c524sx9_.ctl Finished restore at 26/01/2014 22:25:55 database mounted contents of Memory Script: { set newname for clone datafile … set newname for clone datafile

13 | P a g e

1 to new; 10 to new;


COLLABORATE 14 – IOUG Forum Database - HA restore

from service

'cdb1'

using compressed backupset

clone database ; sql 'alter system archive log current'; } executing Memory Script … Starting restore at 26/01/2014 22:26:00 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using compressed network backup set from service cdb1 channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/CDB2/datafile/o1_mf_system_%u_.dbf … Finished restore at 26/01/2014 22:30:55 sql statement: alter system archive log current current log archived contents of Memory Script: {

restore clone force from service

'cdb1' using compressed backupset

archivelog from scn 2294627; switch clone datafile all; } executing Memory Script Starting restore at 26/01/2014 22:30:56 … contents of Memory Script: { set until scn 2295023; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 26/01/2014 22:30:59 using channel ORA_AUX_DISK_1 starting media recovery … Finished recover at 26/01/2014 22:31:00 Oracle instance started … sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CDB2" RESETLOGS ARCHIVELOG … {

Alter clone da tabase open rese tlogs; } executing Memory Script database opened contents of Memory Script: {

sql clone "alter pluggable database all open"; } executing Memory Script sql statement: alter pluggable database all open Finished Duplicate Db at 26/01/2014 22:32:00

14 | P a g e



Another enhancement that was introduced in 12c is that you can now specify that you don’t want the auxiliary database to be opened when the duplication finishes ( NOOPEN option). This is very helpful in environments, like Peoplesoft, where is critical to not open a database after its been duplicated, as it will start running scripts to the production environment and can cause errors in you application. [email protected] [cdb2] /home/oracle oracle $ rman target sys/oracle@cdb1 auxiliary sys/oracle@cdb2 connected to target database: CDB1 (DBID=808250731) connected to auxiliary database: CDB2 (not mounted) RMAN> DUPLICATE TARGET DATABASE TO cdb2 FROM ACTIVE DATABASE PASSWORD FILE USING COMPRESSED BACKUPSET NOOPEN; Starting Duplicate Db at 27/01/2014 20:24:47 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=20 device type=DISK current log archived contents of Memory Script: { backup as copy reuse targetfile '/u01/app/oracl e/product/12.1.0 /db_1/dbs/orapwc db1' auxiliary format '/u01/app/oracle/product/12.1.0/db_1/dbs/orapwcdb2' ; } executing Memory Script ... datafile 10 switched to datafile copy input datafile copy RECID=6 STAMP=837981000 file name=/u01/app/oracle/oradata/CDB2/datafile/o1_mf_users_9gg914ln_.dbf Leaving database unopened, as requested Finished Duplicate Db at 27/01/2014 20:29:58

EM12C AND RMAN 12C Oracle Enterprise Manager 12c provides you with an interface where you can manage most of RMAN’s features via a GUI interface. Using EM 12c also removes the need to continuously maintain RMAN scripts and crontab jobs. Another great advantage of using EM 12c for RMAN is for reporting purposes as you can view a more friendly report of your backups, that are not as cryptic as the LIST BACKUP command. It is imperative to know that to access the RMAN features in EM 12c you need to have a credentialed OS account, as it will be a needed requirement for you to setup your backups with this tool.

15 | P a g e



To access the RMAN features in EM 12cR3, you need to login and go to the following location, Targets ! Databases !DB_NAME! Availability !Backup&Recovery. There you fill find all the Backup And Recovery Options you have in you hand with EM12cR3

!" #8K7CHA<=> ? 3HAA %?8GHI To run a full backup within EM12c, you need to go to Availability !Backup&Recovery !Schedule Backup… Where you will have the range of backup types that you want to run, either Oracle-Suggested Backup or Customized Backup. In this document we will only explore the Customized Backup. With the Customized Backup you are given the option to select the type of objects you want to backup •

Whole Database

•

Container Database Root

•

Pluggable Databases

•

•

Tablespaces Datafiles

•

Archived Logs

•

All Recovery Files on Disk

Now you will have the Options for your backup, where you will decide if you want to run a full or incremental backup, hot or cold backup as well as backup your Archived Redo Logs

16 | P a g e



In the settings section, you can define if your backup will reside in Disk or you will use a MML, as well were you can view the RMAN settings for your database

In the third step of this process, you will set the schedule for your backups •

•

•

One Time (Immediately). - This is a one time only execution of your backup and will start as soon as you submit the job. One Time (Later). - This is a one time only execution of your backup and will start at the specified date. Repeating. – This job will run at the desired start time, will repeat through out the defined frequency and it can be indefinite or end at a specified date.

17 | P a g e



The last step in this process will allow you to revise the script that is going to be executed.

Once the job is submitted, you can view the progress of the backup run

18 | P a g e



Also by clicking on the status, you can view the details of the current step.

%" -<7L<=> %?8GHI +7I9;DB Once the backup finishes, if you want to view a report the backup job, go to Availability !Backup&Recovery ! Backup Reports!BACKUP_NAME. Were you will see the details of the backup, like the number of datafiles , to which container and tablespace they belong to , compression ratio (if any) , etc…

19 | P a g e



&" 0;9HIB %?8GHIB One of the greatest advantages of using EM12c, is the feature of setting up and schedule backups for a whole set of groups. This is not only available for 12c databases, it is available for 10.2 and higher versions in EM12cR3. Taking advantage of setting up the following only once per group: •

Backup Storage Settings

•

Recovery Catalog Settings

•

Backup Type

Frequency If you are using Administrative Groups, this will make the task even easier, as you would setup the Backup Configuration and Schedule Backup for each of your administrative group and every time you add a new target to an administrative group, it will automatically use these properties, without worrying about them. •

To use this, you will need to have a group or an administrative group created, for this example, I have an admistrative group defined by Lifecycle Status!Location, and will do the setup for the group “Prod-Mexi-Grp”

To access this group, in EM12cR3, you need to go to Targets !Groups ! View !Expand All and click on “ProdMexi-Grp”. Once you have accessed the Prod-Mexi-Grp page, you will see several dashboards that will provide you the Status, general Information, Incidents Job Activities, etc… To start the group backup setup, you need to define the backup configuration first for this group; you need to go to “Prod-Mexi-Grp” !Group !Backup Configurations

20 | P a g e



You will now click on Create to define all of the following: •

•

•

21 | P a g e

Storage o Disk Settings Parallelism " Backup Type. - Backup Set/ Image Copy/ Compressed Backup Set " " Backup Location. – If Left blank, it will use the database FRA o Tape Settings. - Important that you have the same vendor across all target groups Policy o Backup Policy o Retention.- It is important to know that you can only set by number of days. If you have retention based on redundancy for a specific target, choose “Do not override the retention policy” and setup at each of the group target level. o Compression. – Basic/Low/Medium/High o Encryption. –AES128/AES192/AES256 Recovery catalog o Control File o RMAN Recovery Catalog



Once you have setup your Backup Configurations, you need to go to “Prod-Mexi-Grp” !Group!Schedule Backup, where you will choose the Backup Scope, that can only be in EM12cR3: •

Whole Database

•

All Recovery Files on Disk. - Tape Backup As well you can define if you want to include all of the databases in the group, or just a select number of databases.

22 | P a g e



You will now need to define the Database credentials for the whole group, and as well the OS Credentials, so for this to work, you will need to have the same OS user password as well as the same DB User password across all the targets that are going to be backed up as a group.

In the options stage of this process, you will define options of your backup •

Backup Type. - Full/Incremental

•

Backup Mode. - Hot/Cold

•

Maximum Files per Backup Set

•

Section Size

23 | P a g e



In the settings section, you will choose the Media Destination and the Backup Configuration that was created previously, “MEXICO_PROD_BACKUPS”.

Before finishing, you will setup the frequency and start time of these backups.

In the last part, similar to other backup jobs, you get the opportunity to review the job , and if you are set with the Backup Procedure, you hit submit and you will see an informational banner that the job was submitted.

You can click on the link of the submitted procedure and it will take you to a detail page, where you will see the status of all your backups, as well as the logs of each of the steps that were submitted. Another way to see the jobs that have been run for this group is to go to Enterprise !Provisioning and Patching !Procedure Activity ! Advanced Search ! Type!Backup and Restore, and if needed filter by the name you gave it above.

24 | P a g e



CONCLUSION In RMAN 12c there are great new features that will help you if you are ever faced with a disaster, these new features will allow you to reduce your mean time to recover (MTTR) and as well ease your tasks in duplicating your databases. If you incorporate EM12c into your backup strategy, you can simplify your backup administration tasks, especially when you have correctly setup your administrative groups and you have several hundred databases within those groups.

R EFERENCES a) b) c) d)

e)

Oracle® Database Backup and Recovery User's Guide 12c Release 1 (12.1) From http://docs.oracle.com/cd/E16655_01/backup.121/e17630/toc.htm RMAN Recipes for Oracle Database 12c: A Problem-Solution Approach, 2nd Ed. Darl Kuhn, Sam Alapati, and Arup Nanda (2013) Apress Back Up a Thousand Databases Using Enterprise Manager Cloud Control 12c From http://www.oracle.com/technetwork/articles/oem/havewala-rman-em12c-2104270.html My Oracle Support Notes : a. 1534487.1 b. 1521005.1 c. 1521524.1 d. 1401574.1 e. 1521075.1 RMAN | Pythian - Data Experts Blog from http://www.pythian.com/blog/tag/rman/

25 | P a g e


2014546antunezppr-140412175517-phpapp01

Recommend Documents