Thursday, 19 February 2015

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

While RMAN full backup, you may encountered “RMAN-06059: expected archived log not found, loss of archived log compromises recoverability”

Error log:

[oracle@NVMBD1BZY150D00 ~]$ rman target / cmdfile=bkp_inc_lev0.rcv

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 20 12:31:30 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CLONEDB (DBID=958582256)

RMAN> run
2> {
3> backup incremental level 0 database tag "level 0 "  plus archivelog delete input tag "ARCHIVE BACKUP";
4> }
5>
6>

Starting backup at 20-FEB-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=176 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 02/20/2015 12:31:31
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /data1/clonedb/archive/1_50_872092231.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Recovery Manager complete.

Cause:

This can happen for a variety of reasons; the file has been manually moved or deleted,
the archive log destination has recently been changed,
the file has been compressed, etc

Solution:

Your options are either to restore the missing file(s), or to perform a crosscheck.
To perform a crosscheck, run the following command from within RMAN:

RMAN> CROSSCHECK ARCHIVELOG ALL;

Above command will give information of expired RMAN repository after verification. (i.e. in catalog or controlfile)

Above will marked archives as Expired who are not available physically and who are not required for any kind of recovery.

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

Above command will delete all archive logs who are marked as expired while crosscheck.





I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!
Posted by at 2 comments:
Label ,

RECOVER LOSS OF ALL CONTROLFILES (NO CATALOG)

In this session will be discussing scenario of recovering database from loss of all controlfile using rman.

STEP1: CREATE A SCENARIO FOR TEST PURPOSE.

[oracle@NVMBD1BZY150D00 clonedb]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 19 15:50:38 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn oracle/oracle

Connected.

SQL>CREATE TABLE myobjects AS SELECT * FROM DBA_OBJECTS;

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
MYOBJECTS

SQL> insert into myobjects select * from myobjects;

919664 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from myobjects;

  COUNT(*)
----------
   1839328   >>>> need to check this record count after recovery

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

Note - current log sequence is 7 - not archived but contains the last committed changes that we made
Note - archive logs will not be found in $ARCV area, but in the flashback location

STEP2: STIMULATE A FAILURE

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------------------------
/u01/ORACLE/testdb/control01.ctl
/u01/ORACLE/testdb/control02.ctl
/u01/ORACLE/testdb/control03.ctl

SQL> !rm /u01/ORACLE/testdb/*.ctl


SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error.

STEP3: SHUT DONW THE DATABASE AND STARTUP IN NOMOUNT STAGE TO RECOVER CONTROL FILE.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  893386752 bytes
Fixed Size                  2076816 bytes
Variable Size             432017264 bytes
Database Buffers          452984832 bytes
Redo Buffers                6307840 bytes

Since we are not using a RMAN catalog we need to set the DBID

RMAN> set dbid=2415549446;

executing command: SET DBID


STEP4: RESTORE THE CONTROLFILE.

RMAN> run {
2> restore controlfile from autobackup;
3> }

Starting restore at 18-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/ORACLE/flash_recovery_area
database name (or database unique name) used for search: TESTDB
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/ORACLE/testdb/control01.ctl
output filename=/u01/ORACLE/testdb/control02.ctl
output filename=/u01/ORACLE/testdb/control03.ctl
Finished restore at 18-SEP-07

STEP5: MOUNT AND RECOVER THE DATABASE;

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 18-SEP-07
Starting implicit crosscheck backup at 18-SEP-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 18-SEP-07

Starting implicit crosscheck copy at 18-SEP-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-SEP-07

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp

using channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0
datafile 4 not processed because file is offline

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc
archive log thread 1 sequence 7 is already on disk as file /u01/ORACLE/testdb/redo03.log
archive log filename=/u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc thread=1 sequence=6
archive log filename=/u01/ORACLE/testdb/redo03.log thread=1 sequence=7  >>>> current redo log with committed but unarchived changes applied
media recovery complete, elapsed time: 00:00:09
Finished recover at 18-SEP-07

STEP6: OPEN DATABASE WITH RESETLOGS OPTION.

SQL> alter database open resetlogs;

Database altered.

SQL> conn ORACLE/ORACLE

Connected.

SQL> select count(*) from myobjects;

  COUNT(*)
----------
   1839328





I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!
Posted by at No comments:
Label

Monday, 16 February 2015

DATA GUARD BROKER CONFIGURATION

In this article we are going to learn how to set up Data Guard broker for managing data guard.
Primary and standby database server details:
Primary Server: ora1-1.mydomain Database: TESTDB
Standby Server: ora1-2.mydomain Database: STANDBY
Primary Database Details:
 
[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 12 16:06:52 2015
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
 
STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         TESTDB           PRIMARY          MAXIMUM PERFORMANCE
 
SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
           803
 
Standby Database Details:
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
 
STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      STANDBY          PHYSICAL STANDBY MAXIMUM PERFORMANCE
 
SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
           803
 
On both primary and standby databases, enable the dataguard broker process by setting the value of the parmater “DG_BROKER_START” to TRUE.
SQL> alter system set dg_broker_start=true;
 
System altered.
 
Add a static entry for the DGMGRL in the listener.ora file on both the primary and standby servers.
On Primary:
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTDB_DGMGRL)
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = TESTDB)
    )
On Standby:
   (SID_DESC =
      (GLOBAL_DBNAME = STANDBY_DGMGRL)
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = STANDBY)
    )
Listener.ora file contents on primary server:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.9.2)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTDB_DGMGRL )
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = TESTDB)
    )
  )
Listener.ora file contents on standby server:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.9.3)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
 
SID_LIST_LISTENER =
   (SID_DESC =
      (GLOBAL_DBNAME = STANDBY_DGMGRL )
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = STANDBY)
    )
 
  )
On the primary server, create the Dataguard Broker configuration.
 
[oracle@NVMBD1BZY150D00 ~]$ dgmgrl
 
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
 
Copyright (c) 2000, 2009, Oracle. All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
 
DGMGRL> connect sys/sys@TESTDB
 
Connected.
 
DGMGRL> create configuration 'dgtest' as primary database is 'TESTDB' connect identifier is TESTDB;
 
Configuration "dgtest" created with primary database "TESTDB"
 
DGMGRL> show configuration;
 
Configuration - dgtest
 
  Protection Mode: MaxPerformance
  Databases:
    TESTDB - Primary database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
DISABLED
 
Add the standby database “SRPSTB” to the configuration created above.
DGMGRL> add database 'STANDBY' as connect identifier is STANDBY maintained as physical;
 
Database "STANDBY" added
 
DGMGRL> show configuration;
 
Configuration - dgtest
 
  Protection Mode: MaxPerformance
  Databases:
    TESTDB  - Primary database
    STANDBY - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
DISABLED
 
The configuration added, needs to be enabled.
 
DGMGRL> enable configuration;
 
Enabled.
 
DGMGRL> show configuration
 
Configuration - dgtest
 
  Protection Mode: MaxPerformance
  Databases:
    TESTDB  - Primary database
    STANDBY - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
 
Setup is complete, you are ready to use DG Broker.
 
Few Monitorable properties to troubleshoot

DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';

Equivalent Broker Commands to 'ALTER SYSTEM'

SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'stby_dbname' set state='LOG-APPLY-OFF';

SQL> alter database recover managed standby database disconnect;
DGMGRL> edit database 'stby_dbname' set state='ONLINE';

SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;

SQL> alter system set log_archive_dest_state_2='enable' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='ON';

SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';

DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases
 
 
I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!
 
 
Posted by at No comments:
Label
Subscribe to: Posts (Atom)