Thursday, May 17, 2018

Multi-tenant backup and recover part 2

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.


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.


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