DBA Hub

📋Steps in this guide1/5

How to flashback a Pluggable database ( PDB) in oracle 12.2 - DBACLASS DBACLASS

From Oracle 12.2 onwards we can flashback a Pluggable database(PDB). And flashback is very easy and simple if LOCAL UNDO is enabled(which is also a new feature in Oracle 12.2). Let’s start the DEMO. DEMO: 1. Check whether local undo is enabled or not. SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPE DESCRIPTION ----------------------- […]

oracle clusteringintermediate
by OracleDba
11 views
1

Overview

From Oracle 12.2 onwards we can flashback a Pluggable database(PDB). And flashback is very easy and simple if LOCAL UNDO is enabled(which is also a new feature in Oracle 12.2). Let’s start the DEMO. 1. Check whether local undo is enabled or not. > For more info – Local undo and shared undo in oracle 12.2 For more info – Local undo and shared undo in oracle 12.2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME           PROPE DESCRIPTION
----------------------- ----- ---------------------------------------------

LOCAL_UNDO_ENABLED      TRUE  true if local undo is enabled
2

Section 2

For more info – Local undo and shared undo in oracle 12.2 2. Check whether flashback is enabled or not. In a multitenant database, flashback can be enabled at container level only. 2. Create a restore point in the pluggable database.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /export/home/oracle/product/12
                                                 c/recovery_area
db_recovery_file_dest_size           big integer 12690M


SQL> Database log mode         Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

SQL>
SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
NO


SQL> alter database flashback on;

Database altered.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
YES
3

Section 3

3. Do some DML changes on the PDB. 4. Now do the flashback to the restore point(we created in the previous stage).

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SDCP1                          READ WRITE NO
SQL> alter session set container=SDCP1;

Session altered.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SDCP1                          READ WRITE NO

--- row_count at time of restore point:

SQL> select count(*) from PDBCLASS.TABLE1;

  COUNT(*)
----------
     72633


SQL>  create restore point STAGE1 guarantee flashback database;

Restore point created.


SQL> SELECT NAME,CON_ID ,TIME FROM V$RESTORE_POINT;

NAME             CON_ID TIME
------------ ---------- -----------------------------------------------------------------------
STAGE1                3 27-NOV-18 10.18.25.000000000 AM

SQL> insert into PDBCLASS.TABLE1 select * from PDBCLASS.TABLE1;
72633 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from PDBCLASS.TABLE1;

COUNT(*)
----------
145266
4

Section 4

5. Check data again(whether we are seeing the old data or not): What if LOCAL_UNDO is not enabled?

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SDCP1 READ WRITE NO
SQL> alter pluggable database SDCP1 CLOSE;

Pluggable database altered.

SQL> flashback pluggable database SDCP1 to restore point STAGE1;

Flashback complete.

SQL> alter pluggable database SDCP1 open;
alter pluggable database SDCP1 open
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '/export/home/oracle/product/12c/oradata/ORA12CR2/SDCP1/encryp_ts1.dbf'

SQL> alter pluggable database SDCP1 open resetlogs;

Pluggable database altered.

SQL> select count(*) from PDBCLASS.TABLE1;

  COUNT(*)
----------
     72633



SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SDCP1                          READ WRITE NO
5

Section 5

In oracle 12.2 if local_undo is not enabled, then we can use AUXILIARY DESTINATION parameter , which will create an auxiliary instance with container database’s system,sysaux and undo tablespace, and then restore the PDB using rollback transactions. However this flahback will take a long time and will consume additional space on the auxiliary destination. steps FOR SHARED UNDO:

Code/Command (click line numbers to comment):

1
2
3
SQL> alter pluggable database SDCP1 close;
SQL>flashback pluggable database SDCP1 to restore point STAGE1 auxiliary destination '/oradata/aux_inst';
SQL> alter pluggable database SDCP1 open resetlogs;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!