Point-in-Time Recovery of CDB in Oracle12c using RMAN.pdf ...

Viewer
Transcript

Point-in-Time Recovery of CDBs in Oracle12c using RMAN

SQL> show con_name CON_NAME -----------------------------CDB$ROOT SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- --------db_name string cdbhen

SQL> select DBID, name, CDB, CON_ID, CON_DBID from v$database; DBID NAME CDB CON_ID CON_DBID ---------- --------- --- ---------- ---------1757560214 CDBHEN YES 0 1757560214 SQL> show pdbs CON_ID ---------2 3 4

CON_NAME -----------------------------PDB$SEED PDB1 COGNOSPDB

OPEN MODE ---------READ ONLY READ WRITE READ WRITE

RESTRICTED ---------NO NO NO

Save PDBs state across database bounces SQL> alter pluggable database PDB1,COGNOSPDB save state; Pluggable database altered.

SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 76 Current log sequence 78 Change to Archive Mode SQL> SQL> SQL> SQL>

shutdown immediate startup mount alter database archivelog; alter database open;

SQL> archive log list Database log mode Automatic archival

Archive Mode Enabled

http://www.hendrydatabase.blogspot.com.au/

1

Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 76 Next log sequence to archive 78 Current log sequence 78 Create a backup of Container database plus archivelog [oracle@devcogdb01 2015_02_19]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Thu Feb 19 14:12:43 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. reserved.

All rights

connected to target database: CDBHEN (DBID=1757560214) RMAN> list backup summary; using target database control file instead of recovery catalog specification does not match any backup in the repository RMAN> backup database plus archivelog; Starting backup at 19-FEB-15 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=78 RECID=1 STAMP=872076947 input archived log thread=1 sequence=79 RECID=2 STAMP=872076974 input archived log thread=1 sequence=80 RECID=3 STAMP=872077577 input archived log thread=1 sequence=81 RECID=4 STAMP=872077846 input archived log thread=1 sequence=82 RECID=5 STAMP=872078000 input archived log thread=1 sequence=83 RECID=6 STAMP=872079181 input archived log thread=1 sequence=84 RECID=7 STAMP=872085639 channel ORA_DISK_1: starting piece 1 at 19-FEB-15 channel ORA_DISK_1: finished piece 1 at 19-FEB-15 piece handle=/u04/fast_recovery_area/CDBHEN/backupset/2015_02_19/o1_mf_annnn_TAG2 0150219T140039_bgbz07r9_.bkp tag=TAG20150219T140039 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 19-FEB-15 Starting backup at 19-FEB-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 name=/u02/oradata/CDBHEN/cognospdb/cognos01.dbf input datafile file name=/u02/oradata/CDBHEN/cognospdb/sysaux01.dbf input datafile file name=/u02/oradata/CDBHEN/cognospdb/audit01.dbf input datafile file name=/u02/oradata/CDBHEN/cognospdb/system01.dbf input datafile file name=/u02/oradata/CDBHEN/cognospdb/users01.dbf

http://www.hendrydatabase.blogspot.com.au/

2

number=00014 number=00012 number=00015 number=00011 number=00013

channel ORA_DISK_1: starting piece 1 at 19-FEB-15 channel ORA_DISK_1: finished piece 1 at 19-FEB-15 piece handle=/u04/fast_recovery_area/CDBHEN/0F3EF27421B26B61E0530CA0010AFD81/back upset/2015_02_19/o1_mf_nnndf_TAG20150219T140042_bgbz0cpb_.bkp tag=TAG20150219T140042 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:05:46 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=/u02/oradata/CDBHEN/datafile/o1_mf_undotbs1_bg2jhr9y_.dbf input datafile file number=00001 name=/u02/oradata/CDBHEN/datafile/o1_mf_system_bg2jfow4_.dbf input datafile file number=00003 name=/u02/oradata/CDBHEN/datafile/o1_mf_sysaux_bg2jcyjn_.dbf input datafile file number=00006 name=/u02/oradata/CDBHEN/datafile/o1_mf_users_bg2jhq71_.dbf channel ORA_DISK_1: starting piece 1 at 19-FEB-15 channel ORA_DISK_1: finished piece 1 at 19-FEB-15 piece handle=/u04/fast_recovery_area/CDBHEN/backupset/2015_02_19/o1_mf_nnndf_TAG2 0150219T140042_bgbzc5yv_.bkp tag=TAG20150219T140042 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26 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=/u02/oradata/CDBHEN/0F2B4FDD8966154CE0530CA0010AD594/datafile/o1_mf_sy saux_bg2k4dgv_.dbf input datafile file number=00008 name=/u02/oradata/CDBHEN/0F2B4FDD8966154CE0530CA0010AD594/datafile/o1_mf_sy stem_bg2k4dg9_.dbf input datafile file number=00010 name=/u02/oradata/CDBHEN/0F2B4FDD8966154CE0530CA0010AD594/datafile/o1_mf_us ers_bg2k54od_.dbf channel ORA_DISK_1: starting piece 1 at 19-FEB-15 channel ORA_DISK_1: finished piece 1 at 19-FEB-15 piece handle=/u04/fast_recovery_area/CDBHEN/0F2B4FDD8966154CE0530CA0010AD594/back upset/2015_02_19/o1_mf_nnndf_TAG20150219T140042_bgbzczqq_.bkp tag=TAG20150219T140042 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u02/oradata/CDBHEN/datafile/o1_mf_sysaux_bg2jk8bl_.dbf input datafile file number=00005 name=/u02/oradata/CDBHEN/datafile/o1_mf_system_bg2jk8c1_.dbf channel ORA_DISK_1: starting piece 1 at 19-FEB-15 channel ORA_DISK_1: finished piece 1 at 19-FEB-15 piece handle=/u04/fast_recovery_area/CDBHEN/0F2B2E0BEC3411A5E0530CA0010AF112/back upset/2015_02_19/o1_mf_nnndf_TAG20150219T140042_bgbzdgwf_.bkp tag=TAG20150219T140042 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16 Finished backup at 19-FEB-15 Starting backup at 19-FEB-15 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

http://www.hendrydatabase.blogspot.com.au/

3

input archived log thread=1 sequence=85 RECID=8 STAMP=872086046 channel ORA_DISK_1: starting piece 1 at 19-FEB-15 channel ORA_DISK_1: finished piece 1 at 19-FEB-15 piece handle=/u04/fast_recovery_area/CDBHEN/backupset/2015_02_19/o1_mf_annnn_TAG2 0150219T140726_bgbzdz4h_.bkp tag=TAG20150219T140726 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 19-FEB-15 Starting Control File and SPFILE Autobackup at 19-FEB-15 piece handle=/u04/fast_recovery_area/CDBHEN/autobackup/2015_02_19/o1_mf_s_8720860 48_bgbzf0sq_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 19-FEB-15 RMAN> [oracle@devcogdb01 2015_02_19]$ pwd /u04/fast_recovery_area/CDBHEN/backupset/2015_02_19 [oracle@devcogdb01 2015_02_19]$ ls -lrt total 1392256 -rw-r-----. 1 oracle oinstall 49969664 Feb o1_mf_annnn_TAG20150219T140039_bgbz07r9_.bkp -rw-r-----. 1 oracle oinstall 1370554368 Feb o1_mf_nnndf_TAG20150219T140042_bgbzc5yv_.bkp -rw-r-----. 1 oracle oinstall 3742720 Feb o1_mf_annnn_TAG20150219T140726_bgbzdz4h_.bkp [oracle@devcogdb01 2015_02_19]$

19 19 19

14:00 14:06 14:07

Create tables and populate rows in CDB [oracle@devcogdb01 ~]$ echo $ORACLE_SID cdbhen [oracle@devcogdb01 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 20 16:37:03 2015 Copyright (c) 1982, 2014, Oracle.

All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ----------------------------db_name string cdbhen SQL> show con_name CON_NAME -----------------------------CDB$ROOT

http://www.hendrydatabase.blogspot.com.au/

4

SQL> show pdbs CON_ID ---------2 3 4 SQL> set 16:39:00 16:39:08 16:39:14

CON_NAME -----------------------------PDB$SEED PDB1 COGNOSPDB

OPEN MODE ---------READ ONLY READ WRITE READ WRITE

RESTRICTED ---------NO NO NO

time on SQL> SQL> SQL> create table hendry_CDB_users as select * from dba_users;

Table created. 16:39:15 SQL> select count(*) from hendry_CDB_users; COUNT(*) ---------35 16:39:25 SQL> alter system archive log current; System altered. 16:39:38 HH24:MI:SS';

SQL>

alter

session

set

nls_date_format='DD-MON-YYYY

Session altered. 16:39:46 SQL> select sysdate from dual; SYSDATE -------------------20-FEB-2015 16:39:55

Drop the Table 16:41:17 SQL> select sysdate from dual; SYSDATE -------------------20-FEB-2015 16:41:19 16:41:19 SQL> drop table hendry_CDB_users; Table dropped. 16:41:28 SQL> select count(*) from hendry_CDB_users; select count(*) from hendry_CDB_users * ERROR at line 1: ORA-00942: table or view does not exist 16:41:40 SQL> create table missing_in_CDB as select * from dba_users; Table created.

http://www.hendrydatabase.blogspot.com.au/

5

16:42:00 SQL> select count(*) from missing_in_CDB; COUNT(*) ---------35

Container database (CDB) Point in Time Recovery SQL> conn / as sysdba Connected. 16:42:31 SQL> alter system archive log current; System altered. 16:43:38 SQL> archive log list Database log mode Automatic archival Archive destination Oldest online log sequence Next log sequence to archive Current log sequence

Archive Mode Enabled USE_DB_RECOVERY_FILE_DEST 94 96 96

$ rman target=/ RUN { SHUTDOWN IMMEDIATE; STARTUP MOUNT; SET UNTIL TIME "TO_DATE('20-FEB-2015 HH24:MI:SS')"; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS; }

16:39:55','DD-MON-YYYY

[oracle@devcogdb01 ~]$ rman target=/ Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 20 16:48:50 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. reserved.

All rights

connected to target database: CDBHEN (DBID=1757560214) RMAN> RUN { SHUTDOWN IMMEDIATE; STARTUP MOUNT; SET UNTIL TIME "TO_DATE('20-FEB-2015 16:39:55','DD-MON-YYYY HH24:MI:SS')"; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS; } 2> 3> 4> 5> 6> 7> 8> using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down

http://www.hendrydatabase.blogspot.com.au/

6

connected to target database (not started) Oracle instance started database mounted Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers

1610612736 bytes 2924928 956305024 637534208 13848576

bytes bytes bytes bytes

executing command: SET until clause Starting restore at 20-FEB-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK skipping datafile 5; already restored to file /u02/oradata/CDBHEN/datafile/o1_mf_system_bg2jk8c1_.dbf skipping datafile 7; already restored to file /u02/oradata/CDBHEN/datafile/o1_mf_sysaux_bg2jk8bl_.dbf 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 00011 to /u02/oradata/CDBHEN/cognospdb/system01.dbf channel ORA_DISK_1: restoring datafile 00012 to /u02/oradata/CDBHEN/cognospdb/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00013 to /u02/oradata/CDBHEN/cognospdb/users01.dbf channel ORA_DISK_1: restoring datafile 00014 to /u02/oradata/CDBHEN/cognospdb/cognos01.dbf channel ORA_DISK_1: restoring datafile 00015 to /u02/oradata/CDBHEN/cognospdb/audit01.dbf channel ORA_DISK_1: reading from backup piece /u04/fast_recovery_area/CDBHEN/0F3EF27421B26B61E0530CA0010AFD81/backupset/2 015_02_19/o1_mf_nnndf_TAG20150219T140042_bgbz0cpb_.bkp channel ORA_DISK_1: piece handle=/u04/fast_recovery_area/CDBHEN/0F3EF27421B26B61E0530CA0010AFD81/back upset/2015_02_19/o1_mf_nnndf_TAG20150219T140042_bgbz0cpb_.bkp tag=TAG20150219T140042 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:03:26 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 00001 to /u02/oradata/CDBHEN/datafile/o1_mf_system_bg2jfow4_.dbf channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/CDBHEN/datafile/o1_mf_sysaux_bg2jcyjn_.dbf channel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/CDBHEN/datafile/o1_mf_undotbs1_bg2jhr9y_.dbf channel ORA_DISK_1: restoring datafile 00006 to /u02/oradata/CDBHEN/datafile/o1_mf_users_bg2jhq71_.dbf channel ORA_DISK_1: reading from backup piece /u04/fast_recovery_area/CDBHEN/backupset/2015_02_19/o1_mf_nnndf_TAG20150219 T140042_bgbzc5yv_.bkp channel ORA_DISK_1: piece handle=/u04/fast_recovery_area/CDBHEN/backupset/2015_02_19/o1_mf_nnndf_TAG2 0150219T140042_bgbzc5yv_.bkp tag=TAG20150219T140042 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:36

http://www.hendrydatabase.blogspot.com.au/

7

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 00008 to /u02/oradata/CDBHEN/0F2B4FDD8966154CE0530CA0010AD594/datafile/o1_mf_system_ bg2k4dg9_.dbf channel ORA_DISK_1: restoring datafile 00009 to /u02/oradata/CDBHEN/0F2B4FDD8966154CE0530CA0010AD594/datafile/o1_mf_sysaux_ bg2k4dgv_.dbf channel ORA_DISK_1: restoring datafile 00010 to /u02/oradata/CDBHEN/0F2B4FDD8966154CE0530CA0010AD594/datafile/o1_mf_users_b g2k54od_.dbf channel ORA_DISK_1: reading from backup piece /u04/fast_recovery_area/CDBHEN/0F2B4FDD8966154CE0530CA0010AD594/backupset/2 015_02_19/o1_mf_nnndf_TAG20150219T140042_bgbzczqq_.bkp channel ORA_DISK_1: piece handle=/u04/fast_recovery_area/CDBHEN/0F2B4FDD8966154CE0530CA0010AD594/back upset/2015_02_19/o1_mf_nnndf_TAG20150219T140042_bgbzczqq_.bkp tag=TAG20150219T140042 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 Finished restore at 20-FEB-15 Starting recover at 20-FEB-15 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 85 is already on disk as file /u04/fast_recovery_area/CDBHEN/archivelog/2015_02_19/o1_mf_1_85_bgbzdyks_.a rc archived log for thread 1 with sequence 86 is already on disk as file /u04/fast_recovery_area/CDBHEN/archivelog/2015_02_19/o1_mf_1_86_bgcv8fv5_.a rc archived log for thread 1 with sequence 87 is already on disk as file /u04/fast_recovery_area/CDBHEN/archivelog/2015_02_19/o1_mf_1_87_bgcvndmg_.a rc archived log for thread 1 with sequence 88 is already on disk as file /u04/fast_recovery_area/CDBHEN/archivelog/2015_02_19/o1_mf_1_88_bgcz4m55_.a rc archived log for thread 1 with sequence 89 is already on disk as file /u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_89_bgd95vxd_.a rc archived log for thread 1 with sequence 90 is already on disk as file /u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_90_bgdvmb9v_.a rc archived log for thread 1 with sequence 91 is already on disk as file /u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_91_bgfpwpx0_.a rc archived log for thread 1 with sequence 92 is already on disk as file /u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_92_bgfwn3g7_.a rc archived log for thread 1 with sequence 93 is already on disk as file /u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_93_bgfwo0mp_.a rc archived log for thread 1 with sequence 94 is already on disk as file /u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_94_bgfwpbqq_.a rc archived log for thread 1 with sequence 95 is already on disk as file /u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_95_bgfwxtw1_.a rc

http://www.hendrydatabase.blogspot.com.au/

8

archived log file name=/u04/fast_recovery_area/CDBHEN/archivelog/2015_02_19/o1_mf_1_85_bgbzdy ks_.arc thread=1 sequence=85 archived log file name=/u04/fast_recovery_area/CDBHEN/archivelog/2015_02_19/o1_mf_1_86_bgcv8f v5_.arc thread=1 sequence=86 archived log file name=/u04/fast_recovery_area/CDBHEN/archivelog/2015_02_19/o1_mf_1_87_bgcvnd mg_.arc thread=1 sequence=87 archived log file name=/u04/fast_recovery_area/CDBHEN/archivelog/2015_02_19/o1_mf_1_88_bgcz4m 55_.arc thread=1 sequence=88 archived log file name=/u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_89_bgd95v xd_.arc thread=1 sequence=89 archived log file name=/u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_90_bgdvmb 9v_.arc thread=1 sequence=90 archived log file name=/u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_91_bgfpwp x0_.arc thread=1 sequence=91 archived log file name=/u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_92_bgfwn3 g7_.arc thread=1 sequence=92 archived log file name=/u04/fast_recovery_area/CDBHEN/archivelog/2015_02_20/o1_mf_1_93_bgfwo0 mp_.arc thread=1 sequence=93 media recovery complete, elapsed time: 00:00:27 Finished recover at 20-FEB-15 Statement processed RMAN>

Check if Point-in-Time Recovery worked [oracle@devcogdb01 2015_02_19]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 20 07:37:46 2015 Copyright (c) 1982, 2014, Oracle.

All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show pdbs CON_ID ---------2 3 4

CON_NAME -----------------------------PDB$SEED PDB1 COGNOSPDB

http://www.hendrydatabase.blogspot.com.au/

9

OPEN MODE ---------READ ONLY READ WRITE READ WRITE

RESTRICTED ---------NO NO NO

SQL> select count(*) from hendry_CDB_users; COUNT(*) ---------38 The CDB was restored/recovered to the point-in-time 16:39:55'just before the table hendry_CDB_users was dropped

'20-FEB-2015

SQL> select count(*) from missing_in_CDB; select count(*) from missing * ERROR at line 1: ORA-00942: table or view does not exist This Table “missing_in_CDB” was created after the restore point-in-time 20-FEB-2015 16:41:19, hence missing.

Shalom

http://www.hendrydatabase.blogspot.com.au/

10