RMAN Working scenarios

 Loss of All Control Files and All Data Files

(we have one level 0 and Level 1 backup)

step 1: take level 0 backup

RMAN> backup incremental level 0 database plus archivelog;

step 2: create some tables and insert the some data's

step 3: take level 1 backup

backup incremental level 1 database plus archivelog;

list backup;

list backup of database summary;

step 4: find the location and delete the datafiles and control file in OS Level

select name from v$controlfile;

select name from v$datafile;

rm -rf /dbfilelocation/*.ctl & *.dbf

step 5: kill or down the DB

shut abort

step 6: start the DB with nomount stage and recover the controlfiles,datafiles

startup nomount;

RMAN>

restore controlfile from 'take lastest control file backup piece';

sql 'alter database mount';

restore database from tag='level 0 backup tag';

recover database from tag='level 1 backup tag';

sql 'alter database open resetlogs';

step 7: connect DB and verify the files and locations.

Loss of All Datafiles Only:

step 1: take full backup or Level 0 backup

RMAN>

backup database plus archivelog;

(OR)

backup incremental level 0 database plus archivelog;

step 2: create new users tables and insert the some datas

step 3: switch the log files

alter system switch logfile;

step 4: delete the all datafiles OS level

rm -rf /u01/app/oracle/*.dbf

step 5: down the DB or Kill the pmon process

kill -9 –get pid— ps -ef|grep pmon

(OR)

shut immediate

step 6: start the db with mount stage because we have a spfile and control-file

startup mount;

step 7: restore from the latest backup

RMAN>

restore database from tag;

recover database;

step 8: open the database and verify the objects

alter database open;

Loss of users tablespace data files only:

step 1: take full backup or Level 0 backup

RMAN>
backup database plus archivelog;
(OR)
backup incremental level 0 database plus archivelog;

step 2: delete the all datafiles OS level

rm -rf /u01/app/oracle/users*.dbf
(OR)
other tablespace datafiles by users created

step 3: users tablespace make offline

SQL>
alter tablespace tbs_name offline immediate;

step 4: restore and recover the users tablespace

RMAN>
restore tablespace tbs_name;
recover tablespace tbs_name;

step 5: users tablespace make online and verify

SQL>
alter tablespace tbs_name online;
select file_id,file_name from dba_data_files;

PITR with Incremental Backups:

step 1: take incremental backup level 0

RMAN>
backup incremental level 0 database plus archivelog;

step 2: check timestampe for PITR

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

step 3: create new users, tables and insert some datas

step 4: take incremental backup level 1

RMAN>
backup incremental level 1 database plus archivelog;

step 5: delete all data files from OS Level

rm -rf *.dbf

step 6: kill or down the databse

kill -9 –get pid— ps -ef|grep pmon
(OR)
shut immediate

step 7:start the db with mount stage becasue we have a spfile and controlfile

startup mount;

step 8: perform the PITR

RMAN>
run
{
set until time "to_date('2024-10-15 12:20:30′,'YYYY-MM-DD HH24:MI:SS')";
restore database from tag ;
recover database from tag ;
sql 'alter database open resetlogs';
}

step 9: connect the database and verify the objects

only for recover the db until before new users creation.

PITR in Users tablespace level:

step 1: create new tablespace,user and assign new tablespace to new user, create some tables use new user

step 2: switch the log files

alter system switch logfile;

step 3: take full backup

RMAN>
backup database plus archivelog;

step 4: drop the tablespace manually use SQLPlUS Commands

SQL>
drop tablespace tbs_name including contents and datafiles;

step 5: open the alert log file and check the time when the tablespace was dropped

step 6: connect RMAN and start the tablespace PITR

RMAN>
run
{
recover tablespace tbs_name until time "to_date('2024-10-15 12:20:30′,'YYYY-MM-DD HH24:MI:SS')"
auxiliary destination '/u02/auxdest';
}

step 7: make tablespace to online and check the status

SQL>
alter tablespace tbs_name online;
select status,tablespace_name from dba_tablespaces;

PITR in Table Level: (not recommended use flashback recovery)

step 1: create new tablespace,user and assign new tablespace to new user

create some tables use new user and add some datas

step 2: check current SCN or timestampe

SQL>
select dbms_flashback.get_system_change_number from dual;
(OR)
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

step 3: insert more datas in same tables

step 4: connect with RMAN and start PITR

recover Using SCN:

RMAN>
recover table muthu.emp until scn
auxiliary destination '/u02/auxdest'
remap table 'MUTHU'.'EMP':'EMP_PRVE';

recover Using SCN:

RMAN>
recover table muthu.emp until time "to_date('2024-10-15 12:20:30′,'YYYY-MM-DD HH24:MI:SS')"
auxiliary destination '/u02/auxdest'
remap table 'MUTHU'.'EMP':'EMP_PRVE';

Table Recover to Dump file:

recover table muthu.emp until time "to_date('2024-10-15 12:20:30′,'YYYY-MM-DD HH24:MI:SS')"
auxiliary destination '/u02/auxdest'
datapump destination '/backup'
dump file 'emp.dmp'
notableimport;

Recover the table Remap Different tablespace:

recover table muthu.emp until time "to_date('2024-10-15 12:20:30′,'YYYY-MM-DD HH24:MI:SS')"
auxiliary destination '/u02/auxdest'
remap table 'MUTHU'.'EMP':'EMP_PRVE'
remap tablespace 'USERS":'TBS_NAME';

Recover the table remao different User:

recover table muthu.emp until time "to_date('2024-10-15 12:20:30′,'YYYY-MM-DD HH24:MI:SS')"
auxiliary destination '/u02/auxdest'
remap table 'MUTHU'.'EMP':'SIVA'.'EMP_PRVE';

How to connect CDB and PDB in RMAN

Connect to the ROOT CDB
rman target=/

Connect to PDB
rman taget=sys@pdb_name

Complete Recovery in Container Database:

step 1: down the database

SQL>
shutdown immediate;
(OR)
shut abort;

Step 2: start the DB with mount stage

startup mount

step 3: start the recovery use RMAN with root container

rman target=/
RMAN>
run
{
restore database;
recover database;
alter database open;
}

Complete Recovery in ROOT Container:

step 1: down the database

SQL>
shutdown immediate;
(OR)
shut abort;

Step 2: start the DB with mount stage

startup mount

step 3: start the recovery use RMAN with root container

rman target=/
RMAN>
run
{
restore database ROOT;
recover database ROOT;

#needed the PDB recover for before OPEN the Database

alter database open;
}

Complete Recovery in PDB:

step 1: Close the pluggable databases

SQL>
alter pluggable database pdb1_name,pdb2_name close;

step 2: start the recovery use RMAN with root container

RMAN>
run
{
restore pluggable database pdb1_name,pdb2_name;
recover pluggable database pdb1_name,pdb2_name;
}

step 3: open the Pluggable databases

SQL>
alter pluggable database pdb1_name,pdb2_name open;

Complete Recovery in PDB with direct PDB:

step 1: connect to RMAN

rman target=admin_user@pdb_name
RMAN>
shut immediate;
restore database;
recover database;
startup;

Complete Recovery in tablespace, connecting to the PDB:

rman target=sys@pdb_name
RMAN>
run
{
alter tablespace tbs_name offline;
restore tablespace tbs_name;
recover tablespace tbs_name;
alter tablespace tbs_name online;
}

Complete Recovery in Data Files, connecting to the PDB or Root:

rman target=/
(OR)
rman target=sys@pdb_name
RMAN>
run{
alter database datafile 22 offline;
restore datafile 22;
recover datafile 22;
alter database datafile 22 online;
}

Point In Time Recovery in Container Database :

step 1: down the database and start with mount stage

SQL>
shut immediate; # (OR) abort the database
startup mount;

step 2: connect with RMAN and start the recovery

$ rman target=/
RMAN>
run {
set until time "to_date('13-Feb-2024 09:50:35′,'DD-MON-YYYY HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
}

Point In Time Recovery in PDB

step 1: close the pluggable database

alter pluggable database pdb_name close;

step 2: connect with RMAN and start the recovery

$ rman target=/
RMAN>
run {
set until time "to_date('13-Feb-2024 09:50:35′,'DD-MON-YYYY HH24:MI:SS')";
restore pluggable database pdb_name;
recover pluggable database pdb_name;
alter pluggable database pdb_name open resetlogs;
}

Recover the table In Pluggable Database:

step 1: connect with RMAN and start the recovery

rman target=/
RMAN>
recover table muthu.emp of pluggable database pdb_name
until time "to_date('2024-10-15 12:20:30′,'YYYY-MM-DD HH24:MI:SS')"
auxiliary destination '/u02/auxdest'
remap table 'MUTHU'.'EMP':'EMP_PRVE';

Oracle Database Clone Via RMAN:

Pre-Request: Need a Separate Linux Machine and Installed the Oracle 19c Software Only(No need DB Creation)

192.168.31.201 – source – db name is muthu
192.168.31.202 – target – db name is siva

Activity steps:

  1. Take source db Backup
  2. Move spfile, control-files and backup pieces to Target database server
  3. Start the target database with mount stage
  4. Restore the target database from backup pieces.
  5. Open the target Database as Source DB SID name.
  6. Rename the target database.

Step 1: Take Full backup on source use RMAN

RMAN>
backup database plus archivelog delete input;

step 2: Copy the ControlFile use RMAN restore command

restore controlfile to '/backup/ctrl_file.ctl';

step 3: copy the pfile from source to target

scp initmuthu.ora oracle@192.168.31.202:/oracle_home/dbs

step 4: edit the pfile in target, change SID except for DB_Name parameter and keep it as source

vi initmuthu.ora > %s/muthu/siva/g
keep Only db_name=muthu

step 5: create the new directories in target server as per new pfile locations

like control file,audit_file_dest and db_recover_file_dest locations use mkdir in OS Level

step 6: sent the control file from source to target

cd /backup
scp ctrl_file.ctl oracle@192.168.31.202:/target_controlfile_location/ctrl_file_1.ctl
scp ctrl_file.ctl oracle@192.168.31.202:/target_controlfile_location/ctrl_file_1.ctl

step 7: copy the backup pieces from source to target in same backup locations

check backup summary and get the backup pieces location
RMAN>
list backup of database summary;
list backup tag ;

OS Level:create same backup pieces location in taget server
scp /backup/pieces/location* oracle@192.168.31.202:/backup/pieces/

step 8: export the environment variable in target and keep source dbname and start with mount:

startup mount;

step 9: get the latest SCN available in the archivelog

target rman>
list backup of archivelog all;

#noted the nextscn

step 10: Rename the DB redologfiles so, they can be created in new location

check source side redolog location:
select member from v$logfile;

connect target DB:
select instance_name,status from v$indtance; ##muthu,mounted is the result
alter database rename file '/u01/muthu/redo01.log' to '/u02/siva/redo01'; ## follow the all log locations

step 11: restore the datafiles to new locations in target database

check source DB: select file#,name from v$datafile; #set lines 999
check target DB:RMAN> list backupof database summary;

check datafile number and change the datafile locations in target

RMAN>
run
{
set newname for datafile 1 to '/u02/siva/system01.dbf';
set newname for datafile 2 to '/u02/siva/sysaux01.dbf';
set newname for datafile 3 to '/u02/siva/undo01.dbf';
set newname for datafile 4 to '/u02/siva/users01.dbf';
set newname for datafile 5 to '/u02/siva/temp01.dbf';
restore database from tag ;
switch datafile all;
recover database until scn ; # check step 9 and take SCN
alter database open resetlogs;
}

step 12: check target database status

select name open_mode from v$database;

Renaming the database after clone in target:

step 13:connect target db and take backup on controlfile

SQL>
alter database backup controlfile to trace as '/tmp/create_control.sql';

step 14: down the target database

SQL>
shut immediate;

step 15: check pfile name in target and change db_name parameter is 'siva'

mv initmuthu.ora initsiva.ora
vi initsiva.ora > change the db_name parameter value to 'siva'

step 16: start the target DB with nomount stage

export ORACLE_SID=siva
sqlplus / a sysdba
startup nomount;

step 17: edit the controlfile in trace location with new SID

cat /tmp/create_control.sql #copy only the resetlogs content and change 'REUSE' to 'SET',change DB name is siva

step 18: remove controlfile

delete control file before check pfile

step 19: create the controlfile for new instance

sqlplus / as sysdba
select instance_name,status from v$instance;
copy and paste the edited control file content and created

step 20:open the database

SQL>
alter database open resetlogs;

step 21: verify the locations in target:

select name, open_mode from v$databaase;
select name from v$controlfile;
select member from v$logfile;

Comments

Post a Comment