In Oracle Database 12.1 flashback database operations were limited to the root container, and therefore affected all pluggable databases (PDBs) associated with the root container. Oracle Database 12.2 now supports flashback of a pluggable database.
My environment details-
Primary - SRORCL
Standby - TRORCL
At primary-
C:\Users\Mahesh>set ORACLE_SID=SRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 6 12:02:16 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
LOCAL_UNDO_ENABLED
TRUE
true if local undo is enabled
SQL> select name ,db_unique_name,open_mode,database_role from v$database;
NAME DB_UNIQUE_NA OPEN_MODE DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL SRORCL READ WRITE PRIMARY
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ WRITE NO
4 SRPDB_DUP READ WRITE NO
SQL> select flashback_on from v$database ;
FLASHBACK_ON
------------------
YES
SQL>
At standby-
SQL> select name ,db_unique_name,open_mode,database_role from v$database;
NAME DB_UNIQUE_NA OPEN_MODE DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL TRORCL READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ ONLY NO
4 SRPDB_DUP READ ONLY NO
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 107 1 380
ARCH CLOSING 1 105 51200 32
ARCH CLOSING 1 106 20480 1341
RFS IDLE 0 0 0 0
RFS IDLE 1 108 23870 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 108 23870 409600
11 rows selected.
SQL> select flashback_on from v$database ;
FLASHBACK_ON
------------------
YES
SQL>
At Primary database truncate one table-
SQL> alter session set container=SRPDB;
Session altered.
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
06-JUN-20 11.55.39.355000 AM +05:30
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SRPDB READ WRITE NO
SQL>
SQL> select count(1) from hr.job_history;
COUNT(1)
----------
10
SQL> truncate table hr.job_history;
Table truncated.
SQL>
SQL> select count(1) from hr.job_history;
COUNT(1)
----------
0
SQL>
Now flashback the primary database to get the table content back.
SQL> alter session set container=SRPDB;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SRPDB READ WRITE NO
SQL> shut immediate ;
Pluggable Database closed.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SRPDB MOUNTED
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
C:\Users\Mahesh>set ORACLE_SID=SRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 6 12:05:36 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> FLASHBACK PLUGGABLE DATABASE SRPDB TO TIMESTAMP TO_TIMESTAMP('2020-06-06 11:55:39', 'YYYY-MM-DD HH24:MI:SS');
Flashback complete.
SQL>
Below is the alert log entry-
Flashback Restore Start
2020-06-06 12:13:57.668000 +05:30
Restore Flashback Pluggable Database SRPDB (3) until change 4401023
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
2020-06-06 12:13:59.012000 +05:30
Recovery of Online Redo Log: Thread 1 Group 2 Seq 106 Reading mem 0
Mem# 0: F:\ORADATA\SRORCL\ONLINELOG\O1_MF_2_H5544RWG_.LOG
Mem# 1: F:\ORADATA\ORAFRA\SRORCL\ONLINELOG\O1_MF_2_H5545FBT_.LOG
2020-06-06 12:14:05.213000 +05:30
Recovery of Online Redo Log: Thread 1 Group 3 Seq 107 Reading mem 0
Mem# 0: F:\ORADATA\SRORCL\ONLINELOG\O1_MF_3_H5544RXG_.LOG
Mem# 1: F:\ORADATA\ORAFRA\SRORCL\ONLINELOG\O1_MF_3_H5545FQ0_.LOG
Recovery of Online Redo Log: Thread 1 Group 1 Seq 108 Reading mem 0
Mem# 0: F:\ORADATA\SRORCL\ONLINELOG\O1_MF_1_H5544RTJ_.LOG
Mem# 1: F:\ORADATA\ORAFRA\SRORCL\ONLINELOG\O1_MF_1_H5545F40_.LOG
2020-06-06 12:14:06.551000 +05:30
Incomplete Recovery applied until change 4404404 time 06/06/2020 11:55:40
Flashback Media Recovery Complete
Flashback Pluggable Database SRPDB (3) recovered until change 4404404, at 06/06/2020 11:55:40
Completed: FLASHBACK PLUGGABLE DATABASE SRPDB TO TIMESTAMP TO_TIMESTAMP('2020-06-06 11:55:39', 'YYYY-MM-DD HH24:MI:SS')
SQL> alter pluggable database SRPDB open resetlogs;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ WRITE NO
4 SRPDB_DUP READ WRITE NO
SQL> alter session set container=SRPDB;
Session altered.
SQL> select count(1) from hr.job_history;
COUNT(1)
----------
10
SQL>
We recovered our table back, but if you check the standby alert logfile, could see MRP has been crashed. Below is the alertlog entry from standby database.
Recovery of pluggable database SRPDB aborted due to pluggable database open resetlog marker.
To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 4404404, or timestamp before 06/06/2020 11:55:40, and restart recovery
MRP0: Background Media Recovery terminated with error 39874
Errors in file F:\APP\MAHESH\diag\rdbms\trorcl\trorcl\trace\trorcl_pr00_4336.trc:
ORA-39874: Pluggable Database SRPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4404404.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
2020-06-06 12:15:43.266000 +05:30
Recovered data files to a consistent state at change 4407458
Errors in file F:\APP\MAHESH\diag\rdbms\trorcl\trorcl\trace\trorcl_pr00_4336.trc:
ORA-39874: Pluggable Database SRPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4404404.
C:\Users\Mahesh>set ORACLE_SID=TRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 6 12:18:41 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 107 1 380
ARCH CLOSING 1 105 51200 32
ARCH CLOSING 1 106 20480 1341
RFS IDLE 0 0 0 0
RFS IDLE 1 108 26760 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
10 rows selected.
SQL>
Check the current PDB incarnation from v$pdb_incarnation and we can confirm that same SCN has been recorded in standby alert logfile as well.
SQL> alter session set container=SRPDB;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SRPDB READ WRITE NO
SQL> select status,INCARNATION_SCN from v$pdb_incarnation;
STATUS INCARNATION_SCN
------- ---------------
CURRENT 4404404
PARENT 1490582
SQL>
Let flashback the standby PDB to a SCN lower than 4404404, let say to SCN 4404402.
C:\Users\Mahesh>set ORACLE_SID=TRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 6 12:26:53 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ ONLY NO
4 SRPDB_DUP READ ONLY NO
SQL> alter pluggable database SRPDB close immediate ;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB MOUNTED
4 SRPDB_DUP READ ONLY NO
SQL>
Let's try to do the flashback now.
SQL> flashback pluggable database SRPDB to scn 4404402;
flashback pluggable database SRPDB to scn 4404402
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance
SQL>
So it looks like it's not possible to flashback a PDB just by mounting only the affected PDB. We need to start the standby CDB in mount mode.
SQL> shut immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 8923240 bytes
Variable Size 348129176 bytes
Database Buffers 369098752 bytes
Redo Buffers 7852032 bytes
Database mounted.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 SRPDB MOUNTED
4 SRPDB_DUP MOUNTED
SQL> flashback pluggable database SRPDB to scn 4404402;
Flashback complete.
SQL>
Alertlog entry-
flashback pluggable database SRPDB to scn 4404402
2020-06-06 12:36:53.275000 +05:30
Flashback Restore Start
Restore Flashback Pluggable Database SRPDB (3) until change 4400347
2020-06-06 12:36:54.305000 +05:30
Flashback Restore Complete
Completed: flashback pluggable database SRPDB to scn 4404402
Lets enable MRP now
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 108 36864 1656
ARCH CONNECTED 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 109 360 1
8 rows selected.
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 108 36864 1656
ARCH CONNECTED 0 0 0 0
RFS IDLE 0 0 0 0
RFS RECEIVING 1 109 380 1
MRP0 APPLYING_LOG 1 108 38518 38519
9 rows selected.
SQL>
I did some log switch on the primary and verified standby got catch up.
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”,
2 (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG
3 WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
5 FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 110 110 0
SQL>
Let's Open the PDB in read only and verify the table content
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 SRPDB MOUNTED
4 SRPDB_DUP MOUNTED
SQL> alter database open ;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB MOUNTED
4 SRPDB_DUP MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ ONLY NO
4 SRPDB_DUP READ ONLY NO
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 110 1 18
ARCH CLOSING 1 108 36864 1656
ARCH CLOSING 1 109 1 430
RFS IDLE 0 0 0 0
RFS IDLE 1 111 403 1
MRP0 APPLYING_LOG 1 111 403 409600
9 rows selected.
SQL> alter session set container=SRPDB;
Session altered.
SQL> select count(1) from hr.job_history;
COUNT(1)
----------
10
SQL>
No comments:
Post a Comment