Tuesday 25 August 2020

Multitenant - Performing Flashback for PDB using restorepoint

  • Multitenant - Performing Flashback for PDB using restorepoint


oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 21:04:31 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> CREATE RESTORE POINT pre_change FOR PLUGGABLE DATABASE plm2 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> col name format a20
SELECT SCN, NAME, CON_ID, PDB_RESTORE_POINT, GUARANTEE_FLASHBACK_DATABASE,CLEAN_PDB_RESTORE_POINT FROM V$RESTORE_POINT;SQL>

       SCN NAME                     CON_ID PDB GUA CLE
---------- -------------------- ---------- --- --- ---
   3478897 PRE_CHANGE                    5 YES YES NO

SQL> conn infodba/infodba@plm2
Connected.
SQL> DELETE infodba.tb1;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> ALTER PLUGGABLE DATABASE plm2 close immediate;

Pluggable database altered.

SQL> FLASHBACK PLUGGABLE DATABASE plm2 TO RESTORE POINT pre_change;

Flashback complete.

SQL> ALTER PLUGGABLE DATABASE plm2 OPEN RESETLOGS;

Pluggable database altered.

SQL> conn infodba/infodba@plm2
Connected.
SQL> select count(*) from infodba.tb1;

  COUNT(*)
----------
        10

SQL> conn / as sysdba
Connected.

SQL> ALTER SESSION SET CONTAINER = plm2;

Session altered.

SQL> DROP RESTORE POINT pre_change;

Restore point dropped.


Posted by at No comments:
Labels:

Monday 24 August 2020

Multitenant - Enable Flashback on CDB and perform flashback on CDB Common User


Multitenant -  Enable Flashback on CDB and  perform flashback on CDB Common User
  • Create common user on CDB 

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 20:40:04 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> CREATE USER C##USER1 IDENTIFIED BY oracle CONTAINER=ALL;

User created.

SQL> GRANT CREATE SESSION TO C##USER1 CONTAINER=ALL;

Grant succeeded.

SQL>
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area/SMRCDB
db_recovery_file_dest_size           big integer 10398M


  • Enable flashback on CDB database

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

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

SQL> SHUTDOWN IMMEDIATE
;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size                  8793832 bytes
Variable Size             704643352 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440 SCOPE=BOTH;

System altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.


SQL> ALTER DATABASE OPEN;

Database altered.


  • Check current scn and perform flashback by drop common user 


SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    3477642

SQL> col username format A20
SELECT USERNAME, COMMON, CON_ID FROM CDB_USERS WHERE USERNAME='C##USER1';SQL>

USERNAME             COM     CON_ID
-------------------- --- ----------
C##USER1             YES          1

SQL> DROP USER C##USER1 CASCADE;

User dropped.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size                  8793832 bytes
Variable Size             704643352 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL>FLASHBACK DATABASE TO SCN 3477642;

Flashback complete.


  • Check the status of common user and then open database in resetlogs 

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;

Pluggable database altered.

SQL> SELECT USERNAME, COMMON, CON_ID FROM CDB_USERS WHERE USERNAME='C##USER1';

USERNAME             COM     CON_ID
-------------------- --- ----------
C##USER1             YES          1
C##USER1             YES          5
C##USER1             YES          6
C##USER1             YES          8

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
STARTUP MOUNORACLE instance shut down.

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size                  8793832 bytes
Variable Size             704643352 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PLM2                           MOUNTED
         6 PLM4                           MOUNTED
         8 PLM5                           MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.


Posted by at No comments:
Labels:

Multitenant - Performing Backup and Restore on Pluggable database

Enabling a PDB Administrator to Backup and Restore its PDB

Scenario Preview :

  1. User creation and sysdba privilage and perform backup on pluggable database.
  2. Restore and Recover system datafile from pluggable database.
  3. Perform SCN based recovery on pluggable database.
PART 1:


We create user infodba and grant sysdba or sysbackup privlege to user and perform backup and recovery operations.


SQL> GRANT SYSDBA TO infodba;

Grant succeeded.

  • Backup Pluggable database PLM2

oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> rman target "infodba@PLM2"

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 17 16:39:43 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: SMRCDB:PLM2 (DBID=3048296195)



RMAN> BACKUP DATABASE TAG 'PLM2PDB';

Starting backup at 17-AUG-20
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 number=00044 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf
input datafile file number=00031 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf
input datafile file number=00030 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hmnpzbd0_.dbf
input datafile file number=00032 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
channel ORA_DISK_1: starting piece 1 at 17-AUG-20
channel ORA_DISK_1: finished piece 1 at 17-AUG-20
piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_PLM2PDB_hmnx6hdd_.bkp tag=PLM2PDB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 17-AUG-20

  • Validate  backup and check size 

RMAN> LIST BACKUPSET;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
17      685.29M    DISK        00:00:17     17-AUG-20
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20200817T135457
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/backupset/2020_08_17/o1_mf_annnn_TAG20200817T135457_hmnhps92_.bkp

  List of Archived Logs in backup set 17
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    49      3079727    16-AUG-20 3115985    16-AUG-20
  1    50      3115985    16-AUG-20 3154730    16-AUG-20
  1    51      3154730    16-AUG-20 3192919    17-AUG-20
  1    52      3192919    17-AUG-20 3240222    17-AUG-20
  1    53      3240222    17-AUG-20 3249147    17-AUG-20

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21      Full    556.96M    DISK        00:00:41     17-AUG-20
        BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20200817T135526
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T135526_hmnj4sj9_.bkp
  List of Datafiles in backup set 21
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  30      Full 3249524    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hmnpzbd0_.dbf
  31      Full 3249524    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf
  32      Full 3249524    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
  44      Full 3249524    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
23      260.00K    DISK        00:00:00     17-AUG-20
        BP Key: 23   Status: AVAILABLE  Compressed: NO  Tag: TAG20200817T140343
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/backupset/2020_08_17/o1_mf_annnn_TAG20200817T140343_hmnj77s4_.bkp

  List of Archived Logs in backup set 23
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    54      3249147    17-AUG-20 3249582    17-AUG-20

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    556.96M    DISK        00:00:33     17-AUG-20
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20200817T141833
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  30      Full 3250159    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hmnpzbd0_.dbf
  31      Full 3250159    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf
  32      Full 3250159    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
  44      Full 3250159    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27      Full    1.11M      DISK        00:00:00     17-AUG-20
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20200817T142139
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T142139_hmnk8vhb_.bkp
  List of Datafiles in backup set 27
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  44      Full 3250286    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29      Full    557.00M    DISK        00:00:05     17-AUG-20
        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: PLM2PDB
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_PLM2PDB_hmnsfpnq_.bkp
  List of Datafiles in backup set 29
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  30      Full 3458218    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hmnpzbd0_.dbf
  31      Full 3458218    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf
  32      Full 3458218    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
  44      Full 3458218    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
30      Full    556.98M    DISK        00:00:03     17-AUG-20
        BP Key: 30   Status: AVAILABLE  Compressed: NO  Tag: PLM2PDB
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_PLM2PDB_hmnx6hdd_.bkp
  List of Datafiles in backup set 30
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  30      Full 3462287    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hmnpzbd0_.dbf
  31      Full 3462287    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf
  32      Full 3462287    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
  44      Full 3462287    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

PART2 : 


  • Perform backup and recovery of Pluggable database.



oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 14:18:03 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> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PLM2                           READ WRITE NO
         6 PLM4                           READ WRITE NO
         8 PLM5                           READ WRITE NO
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 17 14:18:19 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SMRCDB (DBID=3280283628)

RMAN> backup pluggable database plm2;

Starting backup at 17-AUG-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=106 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00044 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf
input datafile file number=00031 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf
input datafile file number=00030 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hlr3x0xl_.dbf
input datafile file number=00032 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
channel ORA_DISK_1: starting piece 1 at 17-AUG-20
channel ORA_DISK_1: finished piece 1 at 17-AUG-20
piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp tag=TAG20200817T141833 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 17-AUG-20

Starting Control File and SPFILE Autobackup at 17-AUG-20
piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/autobackup/2020_08_17/o1_mf_s_1048688358_hmnk4h8f_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-AUG-20


RMAN> list backup of pluggable database plm2;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21      Full    556.96M    DISK        00:00:41     17-AUG-20
        BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20200817T135526
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T135526_hmnj4sj9_.bkp
  List of Datafiles in backup set 21
  Container ID: 5, PDB Name: PLM2
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  30      Full 3249524    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hlr3x0xl_.dbf
  31      Full 3249524    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf
  32      Full 3249524    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
  44      Full 3249524    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    556.96M    DISK        00:00:33     17-AUG-20
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20200817T141833
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp
  List of Datafiles in backup set 25
  Container ID: 5, PDB Name: PLM2
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  30      Full 3250159    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hlr3x0xl_.dbf
  31      Full 3250159    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf
  32      Full 3250159    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
  44      Full 3250159    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf


RMAN> BACKUP TABLESPACE plm2:users;

Starting backup at 17-AUG-20
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 number=00044 name=/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-AUG-20
channel ORA_DISK_1: finished piece 1 at 17-AUG-20
piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T142139_hmnk8vhb_.bkp tag=TAG20200817T142139 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-AUG-20

Starting Control File and SPFILE Autobackup at 17-AUG-20
piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/autobackup/2020_08_17/o1_mf_s_1048688500_hmnk8wpm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-AUG-20


RMAN> LIST BACKUP OF TABLESPACE plm2:users;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21      Full    556.96M    DISK        00:00:41     17-AUG-20
        BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20200817T135526
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T135526_hmnj4sj9_.bkp
  List of Datafiles in backup set 21
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  44      Full 3249524    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    556.96M    DISK        00:00:33     17-AUG-20
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20200817T141833
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  44      Full 3250159    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27      Full    1.11M      DISK        00:00:00     17-AUG-20
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20200817T142139
        Piece Name: /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T142139_hmnk8vhb_.bkp
  List of Datafiles in backup set 27
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  44      Full 3250286    17-AUG-20              NO    /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf

             

oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 15:48:19 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> ALTER SESSION SET CONTAINER=PLM2;

Session altered.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/
o1_mf_system_hlr3x0xl_.dbf

  • Remove system datafile on PLM2 database  and perform recovery below:

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PLM2                           READ WRITE NO

oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 17 15:58:07 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SMRCDB (DBID=3280283628)

RMAN> shutdown abort;

using target database control file instead of recovery catalog
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1795162112 bytes

Fixed Size                     8793832 bytes
Variable Size                704643352 bytes
Database Buffers            1073741824 bytes
Redo Buffers                   7983104 bytes

RMAN> RESTORE TABLESPACE PLM2:SYSTEM;

Starting restore at 17-AUG-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

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 00030 to /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hlr3x0xl_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp tag=TAG20200817T141833
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 17-AUG-20

RMAN> RECOVER TABLESPACE PLM2:SYSTEM;

Starting recover at 17-AUG-20
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 17-AUG-20

RMAN> ALTER DATABASE OPEN;

Statement processed

PART 3:

  • Perform SCN based Pluggable database backup


RMAN> SELECT NAME, OPEN_MODE FROM V$PDBS WHERE NAME='PLM2';



NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------

PLM2
MOUNTED

SQL> alter session set container=PLM2;

Session altered.

SQL> select * from infodba.tb1;

no rows selected

SQL> BEGIN
FOR I IN 1..10 LOOP
INSERT INTO INFODBA.TB1 (ID,NOTES) VALUES (I, TO_CHAR(SYSDATE,'DD-MM-YY
HH24:MI'));
END LOOP;
COMMIT;
END;
/  2    3    4    5    6    7    8

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM INFODBA.TB1;

  COUNT(*)
----------
        10

SQL> SELECT TIMESTAMP_TO_SCN(sysdate) from DUAL;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
                  3469088

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    3469108

SQL> DELETE INFODBA.TB1;

10 rows deleted.

SQL> commit;

Commit complete.

SQL>
ALTER PLUGGABLE DATABASE plm2 CLOSE IMMEDIATE;


oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 19:26:24 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 CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    3468533

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PLM2                           MOUNTED
         6 PLM4                           READ WRITE NO
         8 PLM5                           READ WRITE NO
SQL> alter pluggable database PLM2 open;

Pluggable database altered.



oracle@srv3:~> rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Aug 17 19:30:25 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SMRCDB (DBID=3280283628)

RMAN> ALTER PLUGGABLE DATABASE plm2 CLOSE IMMEDIATE;

using target database control file instead of recovery catalog
Statement processed

RMAN> RUN
{
SET UNTIL SCN=3469108;
RESTORE PLUGGABLE DATABASE plm2;
RECOVER PLUGGABLE DATABASE plm2;
}
2> 3> 4> 5> 6>
executing command: SET until clause

Starting restore at 17-AUG-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

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 00030 to /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_system_hmnpzbd0_.dbf
channel ORA_DISK_1: restoring datafile 00031 to /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hmnrmhsn_.dbf
channel ORA_DISK_1: restoring datafile 00032 to /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_undotbs1_hlr3x0xq_.dbf
channel ORA_DISK_1: restoring datafile 00044 to /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T185138_hmo1hqpy_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T185138_hmo1hqpy_.bkp tag=TAG20200817T185138
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:52
Finished restore at 17-AUG-20

Starting recover at 17-AUG-20
current log archived
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 17-AUG-20

RMAN>
ALTER PLUGGABLE DATABASE plm2 OPEN RESETLOGS;

RMAN> ALTER PLUGGABLE DATABASE plm2 OPEN RESETLOGS;

Statement processed

RMAN>
Recovery Manager complete.
oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 17 19:34:02 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>  alter session set container=PLM2;

Session altered.

SQL> SELECT COUNT(*) FROM INFODBA.TB1;

  COUNT(*)
----------
        10

      
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)