Introduction
In this tutorial, we walk through backing up and point in time pluggable database.
Prerequisite
Before starting, you should:
Ensure that you have enough space to back-up your database, including all PDBs.
Install Oracle Database 19c.
Create one CDB with more than one PDBs in it.
By default, the CDB is set to No Archive mode.
Archive Log are managed at the container and not at the PDB level.
ARCHIVE LOG LIST;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 76
Current log sequence 78
SHOW PARAMETER DB_RECOVERY_FILE_DEST;
NAME TYPE VALUE
------------------------------------ ------ ------------------------------
db_recovery_file_dest string D:Oracleapporaclebaserecov
ery_area
db_recovery_file_dest_size big in 12732M
teger
Using an SP file by default
Note: You need to be in your CDB and get all your PDB in an OPEN state
SQL> shutdown immediate;
Pluggable Database closed.
SQL> startup mount;
SP2-0714: invalid combination of STARTUP options
SQL> STARTUP MOUNT;
SP2-0714: invalid combination of STARTUP options
SQL> startup
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
Note: ALTER PLUGGABLE needs to be executed in the CDB
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
Pluggable database altered.
You can either use a trigger or SAVE STATE of your PDB or PDB’s to keep status (CLOSE or OPEN) .
Note: missing LOCATION
SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;
Pluggable database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
Always good to have your Archive log in a different disk or drive.
SQL> alter system set log_archive_dest_1='E:OracleArchiveLog';
alter system set log_archive_dest_1='E:OracleArchiveLog'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed
SQL> alter system set log_archive_dest_1='LOCATION=E:OracleArchiveLog' scope=both;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:OracleArchiveLog
Oldest online log sequence 77
Next log sequence to archive 79
Current log sequence 79
SQL> alter system switch logfile;
System altered.
I will create the second PDB
SQL> CREATE PLUGGABLE DATABASE PDBDEV ADMIN USER Svcpdbdev IDENTIFIED BY pbpdev$dv8N
2 FILE_NAME_CONVERT = ('D:ORACLEAPPORACLEBASEORADATAORCL19PDBSEED', 'D:ORACLEAPPORACLEBASEORADATAORCL19PDBDEV');
Pluggable database created.
The newly created pluggable database is in mount state
SQL> SET LINESIZE 110
SQL> COLUMN name FORMAT A10
SQL> COLUMN OPEN_MODE FORMAT A10
SQL> select name,OPEN_MODE FROM v$containers order by con_id;
NAME OPEN_MODE
---------- ----------
CDB$ROOT READ WRITE
PDB$SEED READ ONLY
ORCLPDB READ WRITE
PDBTST READ WRITE
PDBDEV MOUNTED
Change the status of the PDBDEV to open and save the state, to avoid the status changing back.
SQL> alter pluggable database PDBDEV open;
Pluggable database altered.
SQL> alter pluggable database PDBDEV save state;
Pluggable database altered.
Create a TABLESPACE and Table.
SQL> alter session set container=PDBDEV;
Session altered.
SQL> CREATE TABLESPACE tbsPDBDEV DATAFILE 'D:OracleapporaclebaseoradataORCL19PDBDEVdata01.dbf' SIZE 100M
2 AUTOEXTEND ON NEXT 10M;
Tablespace created.
SQL> create user pdbdev identified by pdbdev default tablespace tbsPDBDEV;
User created.
SQL> create table pdbdev.tablebeforebackup(id NUMBER GENERATED BY DEFAULT AS IDENTITY,status varchar2(100)) tablespace tbsPDBDEV;
Table created.
The INSERT statement due to no privileges on the Tablespace.
SQL> INSERT INTO pdbdev.tablebeforebackup (status) VALUES ('Data before backup');
INSERT INTO pdbdev.tablebeforebackup (status) VALUES ('Data before backup')
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TBSPDBDEV'
SQL> ALTER USER pdbdev quota unlimited on tbsPDBDEV;
User altered.
SQL> INSERT INTO pdbdev.tablebeforebackup (status) VALUES ('Data before backup');
1 row created.
Check and save the current timestamp.
SQL> select timestamp_to_scn(sysdate) from v$database;
TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
8284077
Do exactly the same to PDBTST
alter session set container=PDBTST;
SQL> create user pdbtst identified by pdbtst default tablespace tbsPDBTST;
create user pdbtst identified by pdbtst default tablespace tbsPDBTST
*
ERROR at line 1:
ORA-65023: active transaction exists in container PDBDEV
Trying to create a new transaction while an active transaction is in PBDDEV.
SQL> alter session set container=PDBDEV;
Session altered.
SQL> commit;
Commit complete.
After switching and committing the active transaction.
SQL> alter session set container=PDBTST;
Session altered.
SQL> create user pdbtst identified by pdbtst default tablespace tbsPDBTST;
User created.
SQL> create table pdbtst.tablebeforebackup(id NUMBER GENERATED BY DEFAULT AS IDENTITY,status varchar2(100)) tablespace tbsPDBTST;
Table created.
SQL> grant create session, connect, unlimited tablespace to pdbtst;
Grant succeeded.
SQL> INSERT INTO pdbtst.tablebeforebackup (status) VALUES ('Data before backuptst');
1 row created.
Check and save the current timestamp.
SQL> select timestamp_to_scn(sysdate) from v$database;
TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
8413352
Connect to RMAN
C:Usersfajwe>rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 17 23:54:18 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL19 (DBID=375939688)
Backup the database .It backups the entire database including PDB’s.
SPOOL LOG to 'E:OracleRMANLogRMANbackup.sql'
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:OracleBackupControlMonCumautobackup_control_file_%F';
run {
CROSSCHECK BACKUP;
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE FORMAT 'E:OracleBackupDatafileMonCumDB_%d_%u_%s_%T' ARCHIVELOG ALL FORMAT 'E:OracleBackupArchiveMonCumarchivelogs_%d_%u_%s_%T';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
}
EXIT;
RMAN>
Recovery Manager complete.
Create a TABLE pdbtst.tableafterbackup in TABLESPACE in tbsPDBTST.
SQL> SHOW CON_NAME;
CON_NAME
------------------------------
PDBTST
SQL> INSERT INTO pdbtst.tablebeforebackup (status) VALUES ('Data after backuptst');
1 row created.
SQL> commit;
Commit complete.
SQL> create table pdbtst.tableafterbackup(id NUMBER GENERATED BY DEFAULT AS IDENTITY,status varchar2(100)) tablespace tbsPDBTST;
Table created.
SQL> commit;
Commit complete.
Check and save the current timestamp.
SQL> select timestamp_to_scn(sysdate) from v$database;
TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
8433614
Create a TABLE pdbdev.tableafterbackup in TABLESPACE tbsPDBDEV.
SQL> alter session set container=PDBDEV;
Session altered.
SQL> create table pdbdev.tableafterbackup(id NUMBER GENERATED BY DEFAULT AS IDENTITY,status varchar2(100)) tablespace tbsPDBDEV;
Table created.
SQL> commit;
Commit complete.
Check and save the current timestamp.
SQL> select timestamp_to_scn(sysdate) from v$database;
TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
8435079
Second Incremental Backup. Backs up all changes made after the first Full Backup.
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:OracleBackupControlDailDIFFautobackup_control_file_%F';
RMAN> run {
2> CROSSCHECK BACKUP;
3> sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
4> BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE FORMAT 'E:OracleBackupDatafileDIFFDB_%d_%u_%s_%T' ARCHIVELOG ALL not backed up 1 times FORMAT 'E:OracleBackupArchiveDiffarchivelogs_%d_%u_%s_%T';
5> CROSSCHECK BACKUP;
6> CROSSCHECK ARCHIVELOG ALL;
7> DELETE NOPROMPT OBSOLETE;
8> DELETE NOPROMPT EXPIRED BACKUP;
9> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
10> }
RMAN>
RMAN> EXIT;
RMAN>
Recovery Manager complete.
Restore PDBDEV table to before the backup.
Check the existing tables
SQL> alter session set container=PDBDEV;
Session altered.
COLUMN TABLE_NAME FORMAT A20
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN OWNER FORMAT A10
select TABLE_NAME,TABLESPACE_NAME,OWNER from all_tables where TABLESPACE_NAME='TBSPDBDEV';
TABLE_NAME TABLESPACE OWNER
-------------------- ---------- ----------
TABLEBEFOREBACKUP TBSPDBDEV PDBDEV
TABLEAFTERBACKUP TBSPDBDEV PDBDEV
Close the Pluggable PDBDEV.
alter pluggable database pdbDEV close;
exit
Table pdbdev.tableafterbackup in PDBDEV is a complete disaster so we want to restore to a point when pdbdev.tableafterbackup does not exist.
Run the point-in-time recovery
RMAN> run {
2> set until SCN = 8284077;
3> restore pluggable database pdbdev;
4> recover pluggable database pdbdev auxiliary destination=' D:OracleapporaclebaseoradataORCL19PDBDEV ';
5> alter pluggable database pdbdev open resetlogs;
6> }
executing command: SET until clause
Starting restore at 2021/08/14 21:06:05
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=402 device type=DISK
creating datafile file number=23 name=D:ORACLEAPPORACLEBASEORADATAORCL19PDBDEVDATA01.DBF
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 00020 to D:ORACLEAPPORACLEBASEORADATAORCL19PDBDEVSYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00021 to D:ORACLEAPPORACLEBASEORADATAORCL19PDBDEVSYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00022 to D:ORACLEAPPORACLEBASEORADATAORCL19PDBDEVUNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece E:ORACLEBACKUPDATAFILEDIFFDB_ORCL19_0U06CDUA_30_20210813
channel ORA_DISK_1: piece handle=E:ORACLEBACKUPDATAFILEDIFFDB_ORCL19_0U06CDUA_30_20210813 tag=TAG20210813T022451
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 2021/08/14 21:06:32
Starting recover at 2021/08/14 21:06:32
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=101
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=102
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=103
channel ORA_DISK_1: reading from backup piece E:ORACLEBACKUPARCHIVEDIFFARCHIVELOGS_ORCL19_1206GESH_34_20210814
channel ORA_DISK_1: piece handle=E:ORACLEBACKUPARCHIVEDIFFARCHIVELOGS_ORCL19_1206GESH_34_20210814 tag=TAG20210814T150657
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
media recovery complete, elapsed time: 00:00:03
Finished recover at 2021/08/14 21:07:11
Statement processed
Verify the table in TBSPDBDEV, now only have Tablebeforebackup.
SQL> alter session set container=PDBDEV;
Session altered.
SQL> COLUMN TABLE_NAME FORMAT A20
SQL> COLUMN TABLESPACE_NAME FORMAT A10
SQL> COLUMN OWNER FORMAT A10
SQL> select TABLE_NAME,TABLESPACE_NAME,OWNER from all_tables where TABLESPACE_NAME='TBSPDBDEV';
TABLE_NAME TABLESPACE OWNER
-------------------- ---------- ----------
TABLEBEFOREBACKUP TBSPDBDEV PDBDEV
Let’s do the same for PDBTST
The state of the table before the restore
COLUMN TABLE_NAME FORMAT A20
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN OWNER FORMAT A10
select TABLE_NAME,TABLESPACE_NAME,OWNER from all_tables where TABLESPACE_NAME='TBSPDBTST';
TABLE_NAME TABLESPACE OWNER
-------------------- ---------- ----------
TABLEBEFOREBACKUP TBSPDBTST PDBTST
TABLEAFTERBACKUP TBSPDBTST PDBTST
Close the Pluggable PDBDEV.
alter pluggable database pdbtst close;
exit
Table pdbdev.tableafterbackup in PDBDEV is a complete disaster so we want to restore to a point when pdbdev.tableafterbackup does not exist.
Run the point-in-time recovery
RMAN> run {
2> set until SCN = 8413352;
3> restore pluggable database pdbtst;
4> recover pluggable database pdbtst auxiliary destination=' D:OracleapporaclebaseoradataORCL19PDBTST';
5> alter pluggable database pdbtst open resetlogs;
6> }
executing command: SET until clause
Starting restore at 2021/08/15 01:17:53
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=265 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 00016 to D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTSYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00017 to D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTSYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00018 to D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTUNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00019 to D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTDATA01.DBF
channel ORA_DISK_1: reading from backup piece E:ORACLEBACKUPDATAFILEMONCUMDB_ORCL19_0305QFGF_3_20210806
channel ORA_DISK_1: piece handle=E:ORACLEBACKUPDATAFILEMONCUMDB_ORCL19_0305QFGF_3_20210806 tag=TAG20210806T070120
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 2021/08/15 01:18:19
Starting recover at 2021/08/15 01:18:19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00016: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTSYSTEM01.DBF
destination for restore of datafile 00017: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTSYSAUX01.DBF
destination for restore of datafile 00018: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTUNDOTBS01.DBF
destination for restore of datafile 00019: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTDATA01.DBF
channel ORA_DISK_1: reading from backup piece E:ORACLEBACKUPDATAFILEDIFFDB_ORCL19_0805QGVF_8_20210806
channel ORA_DISK_1: piece handle=E:ORACLEBACKUPDATAFILEDIFFDB_ORCL19_0805QGVF_8_20210806 tag=TAG20210806T072728
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00016: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTSYSTEM01.DBF
destination for restore of datafile 00017: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTSYSAUX01.DBF
destination for restore of datafile 00018: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTUNDOTBS01.DBF
destination for restore of datafile 00019: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTDATA01.DBF
channel ORA_DISK_1: reading from backup piece E:ORACLEBACKUPDATAFILEDIFFDB_ORCL19_0E05RGFJ_14_20210806
channel ORA_DISK_1: piece handle=E:ORACLEBACKUPDATAFILEDIFFDB_ORCL19_0E05RGFJ_14_20210806 tag=TAG20210806T162508
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00016: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTSYSTEM01.DBF
destination for restore of datafile 00017: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTSYSAUX01.DBF
destination for restore of datafile 00018: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTUNDOTBS01.DBF
destination for restore of datafile 00019: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTDATA01.DBF
channel ORA_DISK_1: reading from backup piece E:ORACLEBACKUPDATAFILEDIFFDB_ORCL19_0K05RI4C_20_20210806
channel ORA_DISK_1: piece handle=E:ORACLEBACKUPDATAFILEDIFFDB_ORCL19_0K05RI4C_20_20210806 tag=TAG20210806T165328
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00016: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTSYSTEM01.DBF
destination for restore of datafile 00017: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTSYSAUX01.DBF
destination for restore of datafile 00018: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTUNDOTBS01.DBF
destination for restore of datafile 00019: D:ORACLEAPPORACLEBASEORADATAORCL19PBPTSTDATA01.DBF
channel ORA_DISK_1: reading from backup piece E:ORACLEBACKUPDATAFILEDIFFDB_ORCL19_0S06CDU4_28_20210813
channel ORA_DISK_1: piece handle=E:ORACLEBACKUPDATAFILEDIFFDB_ORCL19_0S06CDU4_28_20210813 tag=TAG20210813T022451
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
archived log for thread 1 with sequence 101 is already on disk as file E:ORACLEARCHIVELOGARC_101_1_1074611244.ARCH
archived log for thread 1 with sequence 102 is already on disk as file E:ORACLEARCHIVELOGARC_102_1_1074611244.ARCH
archived log for thread 1 with sequence 103 is already on disk as file E:ORACLEARCHIVELOGARC_103_1_1074611244.ARCH
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=104
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=105
channel ORA_DISK_1: reading from backup piece E:ORACLEBACKUPARCHIVEDIFFARCHIVELOGS_ORCL19_1206GESH_34_20210814
channel ORA_DISK_1: piece handle=E:ORACLEBACKUPARCHIVEDIFFARCHIVELOGS_ORCL19_1206GESH_34_20210814 tag=TAG20210814T150657
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
media recovery complete, elapsed time: 00:00:01
Finished recover at 2021/08/15 01:19:09
Statement processed
Quick verify shows the table TABLEBEFOREBACKUP is the only existing table. The Point-in-time restore completed successfully.
COLUMN TABLE_NAME FORMAT A20
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN OWNER FORMAT A10
select TABLE_NAME, TABLESPACE_NAME, OWNER from all_tables where TABLESPACE_NAME='TBSPDBTST';
TABLE_NAME TABLESPACE OWNER
-------------------- ---------- ----------
TABLEBEFOREBACKUP TBSPDBTST PDBTST
Leave a Reply