Incremental Database recovery -RMAN

 

SOURCE (SourceDB - HOST - DBHOST01)

TARGET (TargetDB - HOST - DBHOST02)



STEP 1

ON SOURCE

Take a full backup of source database

Rman target /


run
{
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=DBHOST01,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)' FORMAT './%d/daily/%T_%d_Full_Daily_%s_%U';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO './%d/daily/%T_%d_Control_%F';
BACKUP KEEP UNTIL TIME 'SYSDATE+8' RESTORE POINT 'MANUAL_FULL_03142023' FORCE NOEXCLUDE AS BACKUPSET TAG 'MANUAL_FULL_03142023' DATABASE;
BACKUP AS BACKUPSET TAG '%TAG' ARCHIVELOG ALL NOT BACKED UP 2 TIMES FORMAT './%d/daily/%T_%d_arch_%s_%U';
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUPSET;
DELETE NOPROMPT OBSOLETE DEVICE TYPE 'SBT_TAPE';
DELETE NOPROMPT BACKUP OF ARCHIVELOG UNTIL TIME 'SYSDATE-8';
}

Take backup of pfile & copied to /u03/app/oracle/utils/temp/*.ora on target server
create pfile='/tmp/initSource.ora' from spfile;


STEP 2

On Target

Create the target database using BUI before proceeding with below steps.

Remove below files and directories

 rm -rf /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_38/dbs/*
 rm -rf /u02/app/oracle/oradata/SourceDB/dbs/spfileSourceDB.ora
 rm -rf /u04/app/oracle/redo/SourceDB/SourceDB/controlfile/cntrlSourceDB.ctl
 rm -rf /u02/app/oracle/oradata/SourceDB/SourceDB/datafile/*
 rm -rf /u03/app/oracle/fast_recovery_area/SourceDB/*
 rm -rf/u04/app/oracle/redo/SourceDB/SourceDB/onlinelog/*



ls -lrt /u02/app/oracle/oradata/SourceDB/dbs/spfileSourceDB.ora
ls -lrt /u04/app/oracle/redo/SourceDB/SourceDB/controlfile/cntrlSourceDB.ctl
ls -lrt /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_4/dbs/
ls -lrt /u02/app/oracle/oradata/SourceDB/SourceDB/datafile/
ls -lrt /u03/app/oracle/fast_recovery_area/SourceDB/
ls -lrt /u04/app/oracle/redo/SourceDB/SourceDB/onlinelog/

 mkdir -p /u03/app/oracle/fast_recovery_area/SourceDB/onlinelog/
 mkdir -p /u03/app/oracle/fast_recovery_area/SourceDB/controlfile/



Identify the latest controlfile autobackup and use that controlfile autobackup to restore the latest full backup.






STEP 3

ON TARGET


shutdown abort the  TargetDB database

. oraenv 

TargetDB

Sqlplus / as sysdba

Shutdown abort


STEP 4

ON TARGET

. oraenv
TargetDB



Make sure the dbid and parameter file is correct.


rman target /



run
{
set dbid=***;
startup nomount pfile='/u03/app/oracle/utils/temp/initTargetDB.ora';
allocate channel sbt type 'SBT_TAPE' parms 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=DBHOST01,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)';
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO './%d/daily/%T_%d_Control_%F';
restore spfile from autobackup;
shutdown immediate;
startup nomount;
alter system set listener_networks='' scope=both;
restore controlfile from autobackup;
alter database mount;
restore database;
recover database;
}

Ignore errors 

archived log thread=1 sequence=15991
released channel: sbt
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/22/2023 13:47:22
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 15991 and starting SCN of 22372526


STEP 5

ON SOURCE

Take manual archivelog backup

rman target /

run
{
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=DBHOST02,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)' FORMAT './%d/daily/%T_%d_Full_Daily_%s_%U';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO './%d/daily/%T_%d_Control_%F';
BACKUP DEVICE TYPE 'SBT_TAPE' TAG '%TAG' ARCHIVELOG ALL NOT BACKED UP 2 TIMES FORMAT './%d/daily/%T_%d_arch_%s_%U';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO 'SBT_TAPE';
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-3' BACKED UP 2 TIMES TO DEVICE TYPE 'SBT_TAPE';
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
}

STEP 6

On target 

indentify the archivelog backup piece and catalog and perform the 2nd recovery

run
{
allocate channel sbt type 'SBT_TAPE' parms 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=ivndatadom01.ad.skynet,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)';
CATALOG BACKUPPIECE './SourceDB/daily/20230804_SourceDB_arch_22474_ua22vc9b_22474_1_1';
recover database;
}


STEP 7

DURING CUTOVER.

ON source database

For source database, Stop all rman backup jobs and put the database in blackout mode .

Make sure no application sessions are connected to the source database.

select username, status, count(*) from v$session where username is not null and username not in ('SYS','SYSTEM','DBSNMP','SYSRAC') GROUP BY USERNAME, STATUS;


create user test identified by test default tablespace users;

grant create session,create  table,select_catalog_role  to test;

alter user test quota unlimited on users;


connect test/test

create table test as select * from dba_users;
insert into test select * from dba_users;
commit;

conn / as sysdba

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system checkpoint;

shutdown immediate;

startup mount;


take final archivelog backup when database is in mount state.

rman target /

run
{
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=DBHOST02,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)' FORMAT './%d/daily/%T_%d_Full_Daily_%s_%U';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO './%d/daily/%T_%d_Control_%F';
BACKUP DEVICE TYPE 'SBT_TAPE' TAG '%TAG' ARCHIVELOG ALL NOT BACKED UP 2 TIMES FORMAT './%d/daily/%T_%d_arch_%s_%U';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO 'SBT_TAPE';
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-3' BACKED UP 2 TIMES TO DEVICE TYPE 'SBT_TAPE';
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
}



STEP 8

Indentify the remaining backup pieces to catalog and perform final recover.


rman target /

run
{
allocate channel sbt type 'SBT_TAPE' parms 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=wobdatadom01.ad.skynet,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)';
CATALOG BACKUPPIECE './TargetDB/daily/20230314_TargetDB_arch_7846_l61n18qt_7846_1_1';
recover database;
}


STEP 9

Open the database with resetlogs


RMAN> alter database open resetlogs;


++++++++++++++incase if we receive below error+++++++++++++++

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/09/2023 07:12:11
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '/u04/app/oracle/redo/IVLABDEV/IVLABDEV/onlinelog/log_g2m1.log'
ORA-00312: online log 2 thread 1: '/u03/app/oracle/fast_recovery_area/IVLABDEV/onlinelog/log_g2m2.log'



Solution;

SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------
         1          1 CLEARING
         4          1 CLEARING
         3          1 CLEARING
         2          1 CLEARING_C
                      URRENT


SQL> alter database clear unarchived logfile group 2;

Database altered.


RMAN>alter database open resetlogs;

Statement processed



SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 UNUSED
         3          1 UNUSED
         4          1 UNUSED
 
 
COL DF# FORMAT 999
COL DF_NAME FORMAT A35
COL TBSP_NAME FORMAT A7
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL CHANGE# FORMAT 99999999
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, 
       d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#
;


SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 UNUSED
         3          1 UNUSED
         4          1 UNUSED

SQL> set lines 1000;
col MEMBER for a80;
select * from v$logfile;
SQL> SQL>

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_     CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
         3         ONLINE  /u04/app/oracle/redo/TargetDB/TargetDB/onlinelog/log_g3m1.log                    NO           0
         2         ONLINE  /u04/app/oracle/redo/TargetDB/TargetDB/onlinelog/log_g2m1.log                    NO           0
         1         ONLINE  /u04/app/oracle/redo/TargetDB/TargetDB/onlinelog/log_g1m1.log                    NO           0
         2         ONLINE  /u03/app/oracle/fast_recovery_area/TargetDB/onlinelog/log_g2m2.log               NO           0
         3         ONLINE  /u03/app/oracle/fast_recovery_area/TargetDB/onlinelog/log_g3m2.log               NO           0
         1         ONLINE  /u03/app/oracle/fast_recovery_area/TargetDB/onlinelog/log_g1m2.log               NO           0
         4         ONLINE  /u04/app/oracle/redo/TargetDB/TargetDB/onlinelog/log_g4m1.log                    NO           0
         4         ONLINE  /u03/app/oracle/fast_recovery_area/TargetDB/onlinelog/log_g4m2.log               NO           0

8 rows selected.



SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2


SQL> alter system switch logfile;

SQL> alter system switch logfile;

SQL> alter system switch logfile;

SQL> alter system checkpoint;

System altered.

SQL>

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/TargetDB/TargetDB/datafile/o1_mf_temp_l0gjoopm_.tmp

SQL> !ls -lrt /u02/app/oracle/oradata/TargetDB/TargetDB/datafile/o1_mf_temp_l0gjoopm_.tmp
-rw-r----- 1 oracle asmadmin 29368320 Mar  7 08:57 /u02/app/oracle/oradata/TargetDB/TargetDB/datafile/o1_mf_temp_l0gjoopm_.tmp

SQL> show user
USER is "TEST"
SQL> select count(*) from test;

  COUNT(*)
----------
        92

SQL>

SQL>select file#, status, fuzzy, error, checkpoint_change#,
to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss') as checkpoint_time
from v$datafile_header;



[oracle@wobodaprd01n0 ~]$ srvctl stop database -d WBPLL019
[oracle@wobodaprd01n0 ~]$ srvctl start database -d WBPLL019

SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
2883099068 TargetDB

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TargetDB 2883099068       PARENT  1          10-NOV-21
2       2       TargetDB 2883099068       PARENT  2499432    04-APR-22
3       3       TargetDB 2883099068       CURRENT 42147169733 14-MAR-23



STEP 10 (POST MIGRATION STEPS)





Comments

Post a Comment

Dear User,

Thank you very much for your kind response

Popular posts from this blog

Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c

Image
Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c This document is prepared for monitoring windows server where sql server installed.Mostly used OEM for monitoring databases. In this document we will cover agent installation with target addition into OEM 12c. We will also cover prerequisite like enabling port for communication from agent to oms server Agent Installation on windows server  As a part of installation, we require to fulfil prerequisite like connectivity between OEM server and Window server. For Port opening and communication between two servers Ports required to open : 4903 , 7799 , 3872 Following steps require doing for manually installation of Agent in Windows server. Verifying if the agent exists on OMS. To begin, open your Cloud Control and go to "Setup -> Extensibility -> Self Update". Open the "Agent Software" folder and check if the Agent Software of Windows x64 is with statu
Read more

Oracle 10g/11g Linux:SMS the alert logs ORA- errors generated in timestamp

Image
SMS Alert logs ORA- errors This is something new in database administration part.Suppose DBA was out of office and got call from company manager to come to office.There is some problem in database.Generally DBA connect database and check connection possible or not then check any alert log entry.It is better if DBA and their company people got messages whenever any ORA- errors came in certain period of time. For this,I found some solution to read alert log and send sms to respective people. It is mandatory if we put database in archive log and where frequent archives generated.This is require because of the timestamp of archives used to get the start and end time of alertlogs entry. This is all done through SHELL script and on linux machine. Here it is the magical script can help to get the info of SMS by mail ans SMS. This shell designed to read alerts of last 30 minutes. #!/bin/bash #Go to bdump location cd /oracle1/oradata/pindb/bdump/ #SMS d
Read more

Oracle 11g: Install Instant Client 11.2.0.3.0 on Linux x86_64 Server.

Oracle 11g: Install Instant Client 11.2.0.3.0 on Linux x86_64 Server. ***What is Instant Client &benefits? As per Oracle docs, Oracle Database Instant Client Instant Client allows you to run your applications without installing the standard Oracle client or having an ORACLE_HOME. OCI, OCCI, Pro*C, ODBC, and JDBC applications work without modification, while using significantly less disk space than before. Even SQL*Plus can be used with Instant Client. No recompile, no hassle. ISVs and Partners   benefit greatly from packaging Instant Client along with their applications, saving customers the extra step of installing and configuring an Oracle client. Free. The same fully-featured, high-performance applications that Oracle ISVs and partners have always delivered will continue to work, untouched. Customers can try new packaged applications and Oracle client features quickly without worrying about other installations. Larger enterprises can au
Read more