RMAN BACKUP ON PHYSICAL STANDBY DATABASE

RMAN BACKUP ON PHYSICAL STANDBY DATABASE

Recovery catalog is required for taking RMAN BACKUP of Physical standby database. This is not optional.

Primary and standby database must use the same recovery catalog.

Its not necessary to register the standby database.

Recovery Script

Recovery Script

spool recovery_details.txt
set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numwidth 30
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
archive log list;
select name,db_unique_name,dbid,controlfile_type,controlfile_time,open_mode,database_role,checkpoint_change#,archive_change# from v$database;
select * from v$database_incarnation;
select INCARNATION#, RESETLOGS_TIME from V$DATABASE_INCARNATION order by RESETLOGS_TIME ;
col name for a75
select file#,name,status,enabled from v$datafile;
select * from v$recovery_file_dest ;
select * from v$recover_file ;
select * from v$recovery_log ;
select file#,name,recover,fuzzy,checkpoint_change#,creation_change#,creation_time from v$datafile_header;
col error for a15
select FILE#,STATUS,ERROR,RECOVER,FUZZY,CREATION_CHANGE#,CREATION_TIME, RESETLOGS_CHANGE#,RESETLOGS_TIME from v$datafile_header;
select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;
select status, checkpoint_change#, fuzzy,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by status, checkpoint_change#, fuzzy, checkpoint_time
order by status, checkpoint_change#, fuzzy, checkpoint_time;
select file#,substr(name,1,70) name, error, status,recover, fuzzy, resetlogs_change#, resetlogs_time,checkpoint_change#,checkpoint_time from v$datafile_header where fuzzy='YES';
select file#,ts#,substr(name, 1, 70), status,checkpoint_change# from v$datafile where status not in ('SYSTEM','ONLINE');
select checkpoint_change#,controlfile_time from v$database;
select group#,thread#,sequence#,members,archived,status,first_change# from v$log;
select group#,substr(member,1,60) from v$logfile;
select min(fhscn) from x$kcvfh;
select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;
select count(*),fhsta from x$kcvfh group by fhsta;
select count(*),fhrba_seq from x$kcvfh group by fhrba_seq;
select count(*),fhscn from x$kcvfh group by fhscn;
select count(*),fhafs from x$kcvfh group by fhafs;
select hxfil FileNo,FHSTA status from x$kcvfhall;
select fhdbn,fhdbi,hxfil,fhsta,fhscn,fhafs,fhrba_seq,fhtnm tbs_name from x$kcvfh;
spool off
exit

-+-

ORA-12528, Blocked, all appropriate instances are blocking new connections.

UR=A Connecting to Oracle Instance OPEN in Restricted Session

To connect to instance running in restricted mode, add the UR = A clause under the CONNECT_DATA parameter of the tnsnames. ora file:

(CONNECT_DATA=

(SERVICE_NAME = PRD1.de.jnb.com)
(UR = A)
)

sqlplus ‘sys/**********@prd1 as sysdba’

ORA-12528, Blocked, all appropriate instances are blocking new connections.

Oracle Database Block Corruption:

Block Corruption Symptom:

ORA-01578
1. Appear in aler logfile.
2. When you take the RMAN backup.
3. When you try to access that corupted data block.
How to Handle this:
1. Check the alert logfile.
2. analyze table emp validate structure
Identify and preserve objects that will be lost after PITR.
3. Recover data from the corrupted block.
4. Resolve any hardware issue
– Memory boards
– Disk controllers
– Disks.

Setting Parameters to Detect Corruption:

DB_BLOCK_CHECKING – Prevent memory and data corruption
DB_BLOCK_CHECKSOME – Detect I/O storage disk corruption.
Note: These parameters have a performance impact.

The V$DATABASE_BLOCK_CORRUPTION view displays blocks marked corrupt.

BLOCK RECOVERY

PREREQUISITE:

The target database must be in ARCHIVELOG MODE.
The backupas of the datafiles containing the corrupt blocks must be full orf level 0.
RMAN can use only archive redo log for the recovery.
The corrupted data block can be restored from Flashback Logs if available.

The RMAN RECOVER…BLOCK command:

1. Recover a singel block:

RECOVER DATAFILE 6 BLOCK 3;

2. Recover multiple block:

RECOVER DATAFILE 2 BLOCK 43
RECOVER DATAFILE 3 BLOCK 69;
RECOVER DATAFILE 3 BLOCK 143;

3. Recover all blocks logged in V$DATABASE_BLOCK_CORRUPTION:

RECOVER CORRUPTION LIST;