This blog is a followup on both backing up/recovering a multi-tenant and restore/recovery prioritization.
In my last blog I went through the trace files on the recovery of a database. I showed where it will ALWAYS go through any incremental backups before going to the archive logs.
WELL --- I found an exception to this with PDB's on 12.2
Here's the scenario..
When perform a Point in Time recovery of a PDB database, it will not use the incremental backups.
In my test I created a PDB and performed a full backup along with 2 incremental backups.
After the second backup, I created a restore point.
Here is where the restore point is.
In my last blog I went through the trace files on the recovery of a database. I showed where it will ALWAYS go through any incremental backups before going to the archive logs.
WELL --- I found an exception to this with PDB's on 12.2
Here's the scenario..
When perform a Point in Time recovery of a PDB database, it will not use the incremental backups.
In my test I created a PDB and performed a full backup along with 2 incremental backups.
After the second backup, I created a restore point.
Here is where the restore point is.
QL> select a.name,b.name,scn from v$restore_point a,v$pdbs b where a.con_id=b.con_id;
RESTORE POINT NAME PDB NAME SCN
----------------------------------- ---------------- ------------
TEST PDB1 1543086
Here is what I have for RMAN backups
starting full resync of recovery catalog
full resync complete
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
237 Incr 0 480.62M DISK 00:00:23 17-MAY-18
BP Key: 245 Status: AVAILABLE Compressed: NO Tag: TAG20180517T210354
Piece Name: D:\APP\BGRENN\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\EA727D0952C947CE8B2F124498996396\BACKUPSET\2018_05_17\O1_MF_NNND0_TAG20180517T210354_FHW9R000_.BKP
List of Datafiles in backup set 237
Container ID: 3, PDB Name: PDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
8 0 Incr 1541202 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\SYSTEM01.DBF
9 0 Incr 1541202 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\SYSAUX01.DBF
10 0 Incr 1541202 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\UNDOTBS01.DBF
11 0 Incr 1541202 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\USERS01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
336 Incr 1 1.89M DISK 00:00:03 17-MAY-18
BP Key: 346 Status: AVAILABLE Compressed: NO Tag: TAG20180517T211010
Piece Name: D:\APP\BGRENN\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\EA727D0952C947CE8B2F124498996396\BACKUPSET\2018_05_17\O1_MF_NNND1_TAG20180517T211010_FHW9ZWXY_.BKP
List of Datafiles in backup set 336
Container ID: 3, PDB Name: PDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
8 1 Incr 1542930 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\SYSTEM01.DBF
9 1 Incr 1542930 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\SYSAUX01.DBF
10 1 Incr 1542930 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\UNDOTBS01.DBF
11 1 Incr 1542930 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\USERS01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
458 Incr 1 104.00K DISK 00:00:02 17-MAY-18
BP Key: 474 Status: AVAILABLE Compressed: NO Tag: TAG20180517T211152
Piece Name: D:\APP\BGRENN\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\EA727D0952C947CE8B2F124498996396\BACKUPSET\2018_05_17\O1_MF_NNND1_TAG20180517T211152_FHWB32PX_.BKP
List of Datafiles in backup set 458
Container ID: 3, PDB Name: PDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
8 1 Incr 1543195 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\SYSTEM01.DBF
9 1 Incr 1543195 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\SYSAUX01.DBF
10 1 Incr 1543195 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\UNDOTBS01.DBF
11 1 Incr 1543195 17-MAY-18 NO D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\USERS01.DBF
You will note that I have 3 backupsets
- Level 0 -- Ckp SCN 1541202
- Level 1 -- Ckp SCN 1542930
- Level 1 -- Ckp SCN 1542195
The restore point (SCN 1543086) falls directly between the first level 1 and the second level 1
BUT. here is the recovery log
it doesn't use the incrementals and goes straight to the archive logs.
it doesn't use the incrementals and goes straight to the archive logs.
Recovery Manager: Release 12.2.0.1.0 - Production on Thu May 17 21:22:58 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: ORCL (DBID=1504172276)
RMAN-06008: connected to recovery catalog database
RMAN>
RMAN-03090: Starting recover at 17-MAY-18
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=273 device type=DISK
RMAN-08054: starting media recovery
RMAN-06050: archived log for thread 1 with sequence 4 is already on disk as file D:\APP\BGRENN\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2018_05_17\O1_MF_1_4_FHW9XZK1_.ARC
RMAN-06050: archived log for thread 1 with sequence 5 is already on disk as file D:\APP\BGRENN\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2018_05_17\O1_MF_1_5_FHW9Y1NM_.ARC
RMAN-06050: archived log for thread 1 with sequence 6 is already on disk as file D:\APP\BGRENN\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2018_05_17\O1_MF_1_6_FHW9Z7YO_.ARC
RMAN-06050: archived log for thread 1 with sequence 7 is already on disk as file D:\APP\BGRENN\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2018_05_17\O1_MF_1_7_FHWB07F4_.ARC
RMAN-06050: archived log for thread 1 with sequence 8 is already on disk as file D:\APP\BGRENN\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2018_05_17\O1_MF_1_8_FHWB0RQG_.ARC
RMAN-06050: archived log for thread 1 with sequence 9 is already on disk as file D:\APP\BGRENN\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2018_05_17\O1_MF_1_9_FHWB0T56_.ARC
RMAN-06050: archived log for thread 1 with sequence 10 is already on disk as file D:\APP\BGRENN\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2018_05_17\O1_MF_1_10_FHWB24PH_.ARC
RMAN-08181: media recovery complete, elapsed time: 00:00:15
RMAN-03091: Finished recover at 17-MAY-18
RMAN>
Recovery Manager complete.
When going through the trace file here is the clue that it can't use the incremental backup for a PITR.
I see this message for all the datafiles
"Stop scn (1543359) is in the future of until scn (0) with no catalog, do not trust stopSCN"
Then I see this message "sys.dbms_backup_restore.recoverCancel"
DBGMISC: ENTERED krmkgdf [21:25:08.011]
DBGMISC: Retrieved file 8, created: 1527866, stopscn: 1543359, blocks: 32000 (rfno: 1, ts: SYSTEM [0]): [21:25:08.510] (krmkgdf)
DBGMISC: Name: D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\SYSTEM01.DBF (krmkgdf)
DBGMISC: Auxname: (krmkgdf)
DBGMISC: Creation Thread: 1; Creation Size : 32000 [21:25:08.591] (krmkgdf)
DBGMISC: Stop scn (1543359) is in the future of until scn (0) with no catalog, do not trust stopSCN [21:25:08.615] (krmkgdf)
DBGMISC: File 8 is online [21:25:08.641] (krmkgdf)
DBGMISC: Retrieved file 9, created: 1527869, stopscn: 1543359, blocks: 44800 (rfno: 4, ts: SYSAUX [1]): [21:25:08.665] (krmkgdf)
DBGMISC: Name: D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\SYSAUX01.DBF (krmkgdf)
DBGMISC: Auxname: (krmkgdf)
DBGMISC: Creation Thread: 1; Creation Size : 42240 [21:25:08.750] (krmkgdf)
DBGMISC: Stop scn (1543359) is in the future of until scn (0) with no catalog, do not trust stopSCN [21:25:08.782] (krmkgdf)
DBGMISC: File 9 is online [21:25:08.857] (krmkgdf)
DBGMISC: Retrieved file 10, created: 1527871, stopscn: 1543359, blocks: 12800 (rfno: 9, ts: UNDOTBS1 [2]): [21:25:08.883] (krmkgdf)
DBGMISC: Name: D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\UNDOTBS01.DBF (krmkgdf)
DBGMISC: Auxname: (krmkgdf)
DBGMISC: Creation Thread: 1; Creation Size : 12800 [21:25:09.082] (krmkgdf)
DBGMISC: Stop scn (1543359) is in the future of until scn (0) with no catalog, do not trust stopSCN [21:25:09.106] (krmkgdf)
DBGMISC: File 10 is online [21:25:09.140] (krmkgdf)
DBGMISC: Retrieved file 11, created: 1528354, stopscn: 1543359, blocks: 640 (rfno: 11, ts: USERS [5]): [21:25:09.165] (krmkgdf)
DBGMISC: Name: D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\USERS01.DBF (krmkgdf)
DBGMISC: Auxname: (krmkgdf)
DBGMISC: Creation Thread: 1; Creation Size : 640 [21:25:09.248] (krmkgdf)
DBGMISC: Stop scn (1543359) is in the future of until scn (0) with no catalog, do not trust stopSCN [21:25:09.272] (krmkgdf)
DBGMISC: File 11 is online [21:25:09.306] (krmkgdf)
DBGMISC: -- No more datafiles -- [21:25:09.331] (krmkgdf)
DBGMISC: ENTERED krmkgbh [21:25:09.356]
DBGMISC: EXITED krmkgbh with status No backup history required - no flags set [21:25:09.379] elapsed time [00:00:00:00.023]
DBGMISC: ENTERED krmkLinkPrePluginDf [21:25:09.405]
DBGMISC: Not qualified for PrePlugin translation [21:25:09.430] (krmkLinkPrePluginDf)
DBGMISC: EXITED krmkLinkPrePluginDf with status Done [21:25:09.503] elapsed time [00:00:00:00.098]
DBGMISC: EXITED krmkgdf [21:25:09.604] elapsed time [00:00:00:01.593]
DBGMISC: EXITED krmkdbtr with address 57850840 [21:25:09.629] elapsed time [00:00:00:02.094]
DBGMISC: krmknmtr: the parse tree after name translation is: [21:25:09.653] (krmknmtr)
DBGMISC: 1 DFILE
DBGMISC: 1 DF fno=8 pplfno=0 pdbid=3 pdbname=PDB1 crescn=1527866
DBGMISC: blksize=8192 blocks=32000 rfno=1 pdbForeignDbid=2445639199
DBGMISC: fn=D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\SYSTEM01.DBF
DBGMISC: ts=PDB1:SYSTEM, flags=KRMKDF_DATABASE, flags=KRMKDF_INBACKUP
DBGMISC: fedata: sta=0x80 crescn=1527866
DBGMISC: 2 DF fno=9 pplfno=0 pdbid=3 pdbname=PDB1 crescn=1527869
DBGMISC: blksize=8192 blocks=44800 rfno=4 pdbForeignDbid=2445639199
DBGMISC: fn=D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\SYSAUX01.DBF
DBGMISC: ts=PDB1:SYSAUX, flags=KRMKDF_DATABASE, flags=KRMKDF_INBACKUP
DBGMISC: fedata: sta=0x80 crescn=1527869
DBGMISC: 3 DF fno=10 pplfno=0 pdbid=3 pdbname=PDB1 crescn=1527871
DBGMISC: blksize=8192 blocks=12800 rfno=9 pdbForeignDbid=2445639199
DBGMISC: fn=D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\UNDOTBS01.DBF
DBGMISC: ts=PDB1:UNDOTBS1, flags=KRMKDF_DATABASE, flags=KRMKDF_INBACKUP
DBGMISC: fedata: sta=0x80 crescn=1527871
DBGMISC: 4 DF fno=11 pplfno=0 pdbid=3 pdbname=PDB1 crescn=1528354
DBGMISC: blksize=8192 blocks=640 rfno=11 pdbForeignDbid=0
DBGMISC: fn=D:\APP\BGRENN\VIRTUAL\ORADATA\ORCL\PDB1\USERS01.DBF
DBGMISC: ts=PDB1:USERS, flags=KRMKDF_DATABASE, flags=KRMKDF_INBACKUP
DBGMISC: fedata: sta=0x80 crescn=1528354
DBGMISC: EXITED krmknmtr with status DFILE [21:25:10.459] elapsed time [00:00:00:14.039]
DBGSQL: CHANNEL> begin begin sys.dbms_backup_restore.recoverCancel; exception when others then null; end; sys.dbms_backup_restore.recoverStart( opcode => sys.dbms_backup_restore.PDBPITR_CONVERSATION ,pitrSCN => :untscn:untscn_i ,pitrTime => :untiltime:untiltime_i ,scnBased => :scnbased ,toBefore => :to_before ,resetSCN => :resetSCN:resetSCN_i ,resetTime => :resetTime:resetTime_i ,cleanResPt => :cleanResPt ,localUndo => :localUndo ,rspName => :rspName); end;
DBGSQL: sqlcode = 0
DBGSQL: B :untscn = 1543087
DBGSQL: B :untiltime = NULL
DBGSQL: B :scnbased = 1
DBGSQL: B :to_before = 1
DBGSQL: B :resetSCN = 1490582
DBGSQL: B :resetTime = "17-MAY-18"
DBGSQL: B :cleanResPt = 0
DBGSQL: B :localUndo = 1
DBGSQL: B :rspName = TEST
I believe that PDB's are special when it comes to PITR.
The PDB has information that is contained in the CDB, Performing a incremntal backup of the CDB will affect information for all PDB's. The only choice recovery has is to use the archive logs for more granularity.
No comments:
Post a Comment