***
Oracle Database Recovery and Backup Task Documentation
This document describes the steps performed for deleting redo log files, performing an RMAN point-in-time recovery, and backing up the database with archived logs in an Oracle 12c environment with a multitenant container database (CDB) and pluggable database (PDB).
***
Environment and Initial Setup
- Oracle Database version: 12c Release 12.2.0.1.0 (64-bit Enterprise Edition)
- Working with Container Database (CDB) and Pluggable Database (PDB1)
- OS user: oracle
- Date and time of operations: Sep 20, 2025, around 18:30 to 18:50 +04 timezone
***
Step 1: Switch Container and Create Table
1. Set container to PDB1:
SQL> alter session set container=PDB1;
Session altered.
2. Verify PDB status:SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
3. Create a simple table named `emp` with columns `id` (int) and `name` (varchar(100)):
SQL> create table emp(id int,name varchar(100));
Table created.
4. Insert one row into the `emp` table:
SQL> insert into emp values (1,'Muthu');
1 row created.
SQL> commit;
Commit complete.
5. Validate the data insertion:
SQL> select * from emp;
ID NAME
--------------------------------------------------------------------------------
1 Muthu
6. Check the current System Change Number (SCN):
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2779187
7. Confirm the current date/time from the OS shell:
SQL> !date
Sat Sep 20 18:32:15 +04 2025
***
Step 2: List and Remove Redo Log Files
1. List redo log members (files):
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/oradb/redo03.log
/u01/app/oracle/oradata/oradb/redo02.log
/u01/app/oracle/oradata/oradb/redo01.log
2. List the contents of Oracle data directory:
SQL> !ls -lrt /u01/app/oracle/oradata/oradb/
total 2498756
drwxr-x---. 2 oracle oinstall 4096 Sep 4 2020 pdbseed
drwxr-x---. 2 oracle oinstall 104 Sep 4 2020 pdb1
-rw-r-----. 1 oracle oinstall 142614528 Sep 20 18:20 temp01.dbf
-rw-r-----. 1 oracle oinstall 209715712 Sep 20 18:36 redo01.log
-rw-r-----. 1 oracle oinstall 209715712 Sep 20 18:37 redo02.log
-rw-r-----. 1 oracle oinstall 849354752 Sep 20 18:37 system01.dbf
-rw-r-----. 1 oracle oinstall 639639552 Sep 20 18:37 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 283123712 Sep 20 18:37 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Sep 20 18:37 users01.dbf
-rw-r----- 1 oracle oinstall 209715712 Sep 20 18:37 redo03.log
-rw-r-----. 1 oracle oinstall 18726912 Sep 20 18:38 control01.ctl
3. Remove all redo log files from directory:
SQL> !rm /u01/app/oracle/oradata/oradb/redo*
4. Confirm redo log files removal by listing directory again:
SQL> !ls -lrt /u01/app/oracle/oradata/oradb/
total 1884344
drwxr-x---. 2 oracle oinstall 4096 Sep 4 2020 pdbseed
drwxr-x---. 2 oracle oinstall 104 Sep 4 2020 pdb1
-rw-r-----. 1 oracle oinstall 142614528 Sep 20 18:20 temp01.dbf
-rw-r-----. 1 oracle oinstall 849354752 Sep 20 18:37 system01.dbf
-rw-r-----. 1 oracle oinstall 639639552 Sep 20 18:37 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 283123712 Sep 20 18:37 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Sep 20 18:37 users01.dbf
-rw-r-----. 1 oracle oinstall 18726912 Sep 20 18:38 control01.ctl
***
Step 3: Shutdown Database and Mount for Recovery
1. Shutdown the database immediately:
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 801112064 bytes
Fixed Size 8797872 bytes
Variable Size 423625040 bytes
Database Buffers 360710144 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
***
Step 4: RMAN Point-in-Time Recovery to a Specific Sequence
1. Start RMAN and connect to target database:
[oracle@muthu trace]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Sep 20 18:40:06 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (DBID=2803495960, not open)
2. Run recovery commands:
RMAN> run
{
set until sequence 3;
restore database;
recover database;
alter database open resetlogs;
}2> 3> 4> 5> 6> 7>
executing command: SET until clause
Starting restore at 20-SEP-2025 18:40:21
using target database control file instead of recovery catalog
flashing back control file to SCN 2780743
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
skipping datafile 5; already restored to file /u01/app/oracle/oradata/oradb/pdbseed/system01.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/oradb/pdbseed/sysaux01.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/oradb/pdbseed/undotbs01.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 00001 to /u01/app/oracle/oradata/oradb/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oradb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oradb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/oradb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fra/oradb/ORADB/backupset/2025_09_20/o1_mf_nnndf_TAG20250920T183632_ndxgzjpn_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fra/oradb/ORADB/backupset/2025_09_20/o1_mf_nnndf_TAG20250920T183632_ndxgzjpn_.bkp tag=TAG20250920T183632
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fra/oradb/ORADB/archivelog/2025_09_20/o1_mf_1_1_ndxgz7r8_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fra/oradb/ORADB/archivelog/2025_09_20/o1_mf_1_2_ndxh0h3t_.arc
archived log file name=/u01/app/oracle/fra/oradb/ORADB/archivelog/2025_09_20/o1_mf_1_1_ndxgz7r8_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fra/oradb/ORADB/archivelog/2025_09_20/o1_mf_1_2_ndxh0h3t_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:01
Finished recover at 20-SEP-2025 18:40:53
Statement processed
RMAN>
***
Step 5: Validate Recovery and Backup
[oracle@muthu trace]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 20 18:44:09 2025
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 name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORADB READ WRITE
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> alter session set container=PDB1;
Session altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2782820
SQL> !date
Sat Sep 20 18:44:28 +04 2025
SQL> select * from emp;
ID NAME
--------------------------------------------------------------------------------
1 Muthu
[oracle@muthu trace]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Sep 20 18:47:20 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB
RMAN> backup database plus archivelog;
Finished Control File and SPFILE Autobackup at 20-SEP-2025 18:48:26
No comments:
Post a Comment