Dataguard - STANDBY_FILE_MANAGEMENT

In this blog, i am planning to discuss the impact of the DG  parameter STANDBY_FILE_MANAGEMENT.  From Oracle document the description of this parameter is as follows:

"STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database. STANDBY_FILE_MANAGEMENT is only applicable to physical standby databases."

Now lets see the behavior for various values of this parameter.

1. STANDBY_FILE_MANAGEMENT=AUTO

Now am adding a tablespace in primary:
SQL> create tablespace test1 datafile '/u02/app/oracle/oradata/dbdg/test1.dbf' size 30M;

File added in Primary:

Alert log:

Thu Jul 31 05:17:39 2014
Archived Log entry 36 added for thread 1 sequence 26 ID 0xb4d94b9e dest 1:
Thu Jul 31 09:50:00 2014
create tablespace test1 datafile '/u02/app/oracle/oradata/dbdg/test1.dbf' size 30M
Completed: create tablespace test1 datafile '/u02/app/oracle/oradata/dbdg/test1.dbf' size 30M
 

File System:

SQL> !ls /u02/app/oracle/oradata/dbdg/
archivelog     redo01.log  sysaux01.dbf  test1.dbf
control01.ctl  redo02.log  system01.dbf  undotbs01.dbf
example01.dbf  redo03.log  temp01.dbf    users01.dbf

Checking the Standby:

Alert log:

Thu Jul 31 05:17:41 2014
Media Recovery Log /u02/app1/oracle/flash_recovery_area/DBDG_STBY/archivelog/2014_07_31/o1_mf_1_26_9xm10tnm_.arc
Media Recovery Waiting for thread 1 sequence 27 (in transit)

File System
SQL> !ls /u02/app1/oracle/oradata/dbdg
archivelog     stbyredo1.log  stbyredo4.log  temp01.dbf
control01.ctl  stbyredo2.log  sysaux01.dbf   undotbs01.dbf
example01.dbf  stbyredo3.log  system01.dbf   users01.dbf

Nothing happened. Now executing switch log file in primary:


SQL> alter system switch logfile;
System altered. 
Primary Alert log:
Thu Jul 31 09:59:15 2014
Thread 1 advanced to log sequence 28 (LGWR switch)
  Current log# 1 seq# 28 mem# 0: /u02/app/oracle/oradata/dbdg/redo01.log
Thu Jul 31 09:59:15 2014
LNS: Standby redo logfile selected for thread 1 sequence 28 for destination LOG_ARCHIVE_DEST_2
Thu Jul 31 09:59:15 2014
Archived Log entry 38 added for thread 1 sequence 27 ID 0xb4d94b9e dest 1:
 
Standby alertlog:
Thu Jul 31 09:59:15 2014
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Thu Jul 31 09:59:15 2014
RFS[2]: Selected log 4 for thread 1 sequence 28 dbid -1260808290 branch 853848671
Archived Log entry 17 added for thread 1 sequence 27 ID 0xb4d94b9e dest 1:
Thu Jul 31 09:59:19 2014
Media Recovery Log /u02/app1/oracle/flash_recovery_area/DBDG_STBY/archivelog/2014_07_31/o1_mf_1_27_9xmkjvjr_.arc
Recovery created file /u02/app1/oracle/oradata/dbdg/test1.dbf
Successfully added datafile 6 to media recovery
Datafile #6: '/u02/app1/oracle/oradata/dbdg/test1.dbf'
Media Recovery Waiting for thread 1 sequence 28 (in transit)
 
So the tablespace with the datafile is added in standby. And it got added only after a alter system switch logfile. The above case is in maximum performance mode now lets test the same in maximum protection mode.

In Max-Protection Mode:

 There is no difference in Max-Protection & Max-Performance modes. Even in Max-Protection mode the datafile got created only after switch log file.

With Real-time apply:

Stopping the recovery process and starting the real-time apply.

Standby:
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
 
alert log:
MRP0: Background Media Recovery process shutdown (dbdg)
Managed Standby Recovery Canceled (dbdg)
Completed:  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Jul 31 10:34:15 2014
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
Media Recovery Start: Managed Standby Recovery (dbdg)
 started logmerger process
Thu Jul 31 10:34:15 2014
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Media Recovery Waiting for thread 1 sequence 31 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 31 Reading mem 0
  Mem# 0: /u02/app1/oracle/oradata/dbdg/stbyredo2.log
 
Now am dropping & creating a tablespace in primary:
Primary:
SQL> drop tablespace test1 including contents and datafiles;
Tablespace dropped.

SQL>  create tablespace test1 datafile '/u02/app/oracle/oradata/dbdg/test1.dbg' size 10M;
Tablespace created.
 
alert log:
Thu Jul 31 10:35:12 2014
drop tablespace test1 including contents and datafiles
Deleted file /u02/app/oracle/oradata/dbdg/test1.dbg
Completed: drop tablespace test1 including contents and datafiles
Thu Jul 31 10:35:27 2014
 create tablespace test1 datafile '/u02/app/oracle/oradata/dbdg/test1.dbg' size 10M
Completed:  create tablespace test1 datafile '/u02/app/oracle/oradata/dbdg/test1.dbg' size 10M
 
Standby alert log:
Thu Jul 31 10:35:12 2014
Recovery deleting file #6:'/u02/app1/oracle/oradata/dbdg/test1.dbg' from controlfile.
Deleted file /u02/app1/oracle/oradata/dbdg/test1.dbg
Recovery dropped tablespace 'TEST1'
Thu Jul 31 10:35:27 2014
Recovery created file /u02/app1/oracle/oradata/dbdg/test1.dbg
Successfully added datafile 6 to media recovery
Datafile #6: '/u02/app1/oracle/oradata/dbdg/test1.dbg'

So with real time appply the changes are moving to standby the same moment. No switch logfile is necessary. Now lets test and see what is behavior when the parameter is set to Manual.

2. STANDBY_FILE_MANAGEMENT=MANUAL

Standby:
SQL> alter system set standby_file_management=manual;
System altered.

Primary:
SQL> create tablespace test1 datafile '/u02/app/oracle/oradata/dbdg/test1.dbg' size 10M;
Tablespace created.

alert log:
Thu Jul 31 10:42:19 2014
create tablespace test1 datafile '/u02/app/oracle/oradata/dbdg/test1.dbg' size 10M
Completed: create tablespace test1 datafile '/u02/app/oracle/oradata/dbdg/test1.dbg' size 10M

Standby:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01274: cannot add datafile '/u02/app/oracle/oradata/dbdg/test1.dbg' - file
could not be created
 
Alert log:
Thu Jul 31 10:42:01 2014
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;
Thu Jul 31 10:42:20 2014
File #6 added to control file as 'UNNAMED00006' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1281916
Media Recovery failed with error 1274
Errors in file /u02/app1/oracle/diag/rdbms/dbdg_stby/dbdg/trace/dbdg_pr00_7279.trc:
ORA-00283: recovery session canceled due to errors
ORA-01274: cannot add datafile '/u02/app/oracle/oradata/dbdg/test1.dbg' - file could not be created
Slave exiting with ORA-283 exception
Errors in file /u02/app1/oracle/diag/rdbms/dbdg_stby/dbdg/trace/dbdg_pr00_7279.trc:
ORA-00283: recovery session canceled due to errors
ORA-01274: cannot add datafile '/u02/app/oracle/oradata/dbdg/test1.dbg' - file could not be created
Thu Jul 31 10:42:20 2014
ORA-283 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE...
 
Since the file could not be created the real time apply got interrupted and aborted. The same will  happen with normal recovery also. If you notice the previous snippet of the alert log file. A new datafile is recorded with control file.

File #6 added to control file as 'UNNAMED00006' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!

That is a dummy file and not created on the file system. Now in order to restart the apply process we have create the file manually and start the MRP. For creating the file the following command can be used @ the standby.

ALTER DATABASE CREATE DATAFILE '/u02/app1/oracle/product/db_2/dbs/UNNAMED00006' as '/u02/app1/oracle/oradata/dbdg/test1.dbg';

Once the file is created the recovery could be started again.

Hope its useful!!

 

11.2 Dataguard Physical Standby

Environment:

Test Machine: OEL 5.5(2 servers) with 6GB RAM

Primary Instance : dbdg
ORACLE_BASE=/u02/app/oracle
ORACLE_HOME=/u02/app/oracle/product/db_2
ORACLE_SID=dbdg

Standby Instance: dbdg_stby
ORACLE_BASE=/u02/app1/oracle
ORACLE_HOME=/u02/app1/oracle/product/db_2
ORACLE_SID=dbdg

This configuration comes with a different directory structure between primary and standby. so few specific steps are added which is not the case with a regular setup.

Configuration (Primary Server):

Enable Archiving:

Update the archive log location to a local destination and enable archiving

SQL> startup mount pfile="/u02/app/oracle/product/db_2/dbs/pfile_dbdg.ora"
ORACLE instance started.

Total System Global Area 2488635392 bytes
Fixed Size                  2215904 bytes
Variable Size            1392508960 bytes
Database Buffers         1073741824 bytes
Redo Buffers               20168704 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database force logging;
Database altered.

SQL> sho parameter name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      dbdg
db_unique_name                       string      dbdg
global_names                         boolean     FALSE
instance_name                        string      dbdg
lock_name_space                      string
log_file_name_convert                string
service_names                        string      dbdg.localdomain
log_archive_dest_1                   string      LOCATION=/u02/app/oracle/orada
                                                 ta/dbdg/archivelog

Parameter Changes:

LOG_ARCHIVE_CONFIG:

"LOG_ARCHIVE_CONFIG enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs, and specifies the unique database names (DB_UNIQUE_NAME) for each database in the Data Guard configuration"

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbdg,dbdg_stby)';

Archiving to Remote Destination(Log Shipping):

Set the second archival destination to point to the service of the standby so that logs can be shipped.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=dbdg_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbdg_stby';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

FAL_SERVER:

"FAL_SERVER specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server."

alter system set fal_server=dbdg_stby;

STANDBY_FILE_MANAGEMENT:

"STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database. STANDBY_FILE_MANAGEMENT is only applicable to physical standby databases."

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;  Click Here to know More!!

TNS Setup:

Create the TNS names.
dbdg =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.164.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbdg.localdomain)  <<<<<This should reflect the service name in lsnrctl status>>>>
    )
  )
dbdg_stby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.164.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbdg_stby.localdomain)
    )
  )

To note,
1. The service name should point to the service name from your DB
2. The Alias for the TNS(dbdg_stby) is what is being referred by primary for the remote archival.

Backup the Primary DB:

Backup the primary database(for manual method)
$ rman target=/
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Create Standby controlfile:

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/dbdg_stby.ctl';

Create standby pfile:

CREATE PFILE='/tmp/initdbdg_stby.ora' FROM SPFILE;

Amend the following params in the standby pfile,

*.db_unique_name='dbdg_stby'
*.fal_server='dbdg'
*.log_archive_dest_2='SERVICE=dbdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbdg'

Log_Archive_dest_2 is set in case of a role reversal. Not necessary otherwise.

Configuration (Standby Server):

Create necessary folders in standby server

mkdir -p /u02/app1/oracle/flash_recovery_area/DBDG
mkdir -p /u02/app1/oracle/flash_recovery_area/dbdg
mkdir -p /u02/app1/oracle/oradata/dbdg
mkdir -p /u02/app1/oracle/admin/dbdg/adump

Update the parameter in standby pfile to suit the location in standby server. Because here we have a difference in path between primary and standby. So control_file, flash_recovery_area, diag_dest and such parameters have to be updated accordingly for standby.

Move files from primary to standby:

The following files to be moved from primary to standby.

1. Standby control file -- Move to the path as mentioned in initora of standby

2. DB Backupsets -- Move to FRA as mentioned in standby controlfile.

3. Password file -- Default patch $ORACLE_HOME/dbs

4. Standby initora file -- Default patch $ORACLE_HOME/dbs

Start Standby Instance:

1. Create spfile from the standby pfile  from primary.

2. Startup the standby instance in mount state.

sqlplus sys as sysdba

SQL>startup mount

Restore Database:

Since the location of the files has changed[See flash recovery area & oradata] the backup references from control file can no more be used. Hence cataloging the backup piece with the standby control file.

bash-3.2$ pwd
/u02/app1/oracle/flash_recovery_area/DBDG/backupset/2014_07_29
bash-3.2$ ls -lrt
total 1211524
-rw-r----- 1 oracle oinstall    9830400 Jul 29 15:42 o1_mf_ncsnf_TAG20140729T145324_9xgt24kn_.bkp
-rw-r----- 1 oracle oinstall 1199472640 Jul 29 15:42 o1_mf_nnndf_TAG20140729T145324_9xgt0dn1_.bkp
-rw-r----- 1 oracle oinstall     509952 Jul 29 15:42 o1_mf_annnn_TAG20140729T145421_9xgt25w9_.bkp
-rw-r----- 1 oracle oinstall   29550080 Jul 29 15:42 o1_mf_annnn_TAG20140729T145322_9xgt0bso_.bkp
bash-3.2$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 29 17:46:33 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DBDG (DBID=3034159006, not open)
RMAN> catalog backuppiece '/u02/app1/oracle/flash_recovery_area/DBDG/backupset/2014_07_29/o1_mf_ncsnf_TAG20140729T145324_9xgt24kn_.bkp';
using target database control file instead of recovery catalog
cataloged backup piece
backup piece handle=/u02/app1/oracle/flash_recovery_area/DBDG/backupset/2014_07_29/o1_mf_ncsnf_TAG20140729T145324_9xgt24kn_.bkp RECID=5 STAMP=854214423
RMAN> catalog backuppiece '/u02/app1/oracle/flash_recovery_area/DBDG/backupset/2014_07_29/o1_mf_nnndf_TAG20140729T145324_9xgt0dn1_.bkp';
cataloged backup piece
backup piece handle=/u02/app1/oracle/flash_recovery_area/DBDG/backupset/2014_07_29/o1_mf_nnndf_TAG20140729T145324_9xgt0dn1_.bkp RECID=6 STAMP=854214440
RMAN> catalog backuppiece '/u02/app1/oracle/flash_recovery_area/DBDG/backupset/2014_07_29/o1_mf_annnn_TAG20140729T145421_9xgt25w9_.bkp';
cataloged backup piece
backup piece handle=/u02/app1/oracle/flash_recovery_area/DBDG/backupset/2014_07_29/o1_mf_annnn_TAG20140729T145421_9xgt25w9_.bkp RECID=7 STAMP=854214460
RMAN>  catalog backuppiece '/u02/app1/oracle/flash_recovery_area/DBDG/backupset/2014_07_29/o1_mf_annnn_TAG20140729T145322_9xgt0bso_.bkp';
cataloged backup piece
backup piece handle=/u02/app1/oracle/flash_recovery_area/DBDG/backupset/2014_07_29/o1_mf_annnn_TAG20140729T145322_9xgt0bso_.bkp RECID=8 STAMP=854214481

After cataloging the backups. The backups have to be restored in the new place.

Control file has the path reference to /u01/app, but the files have to restored to /u01/app1 hence using newname to restore the files in the new path. Then using the switch command to update the new location in the standby control file.

run
{
set newname FOR DATAFILE '/u02/app/oracle/oradata/dbdg/system01.dbf' TO '/u02/app1/oracle/oradata/dbdg/system01.dbf';
#restore tablespace system;
set newname FOR DATAFILE '/u02/app/oracle/oradata/dbdg/sysaux01.dbf'TO '/u02/app1/oracle/oradata/dbdg/sysaux01.dbf';
#restore tablespace sysaux;
set newname FOR DATAFILE '/u02/app/oracle/oradata/dbdg/undotbs01.dbf' TO '/u02/app1/oracle/oradata/dbdg/undotbs01.dbf';
#restore tablespace UNDOTBS1;
set newname FOR DATAFILE '/u02/app/oracle/oradata/dbdg/users01.dbf' TO '/u02/app1/oracle/oradata/dbdg/users01.dbf';
#restore tablespace users;
set newname FOR DATAFILE '/u02/app/oracle/oradata/dbdg/example01.dbf' TO '/u02/app1/oracle/oradata/dbdg/example01.dbf';
#restore tablespace example;
restore database;
switch datafile all;
}

Create standby redo logs:

ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app1/oracle/oradata/dbdg/stbyredo1.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app1/oracle/oradata/dbdg/stbyredo2.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app1/oracle/oradata/dbdg/stbyredo3.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app1/oracle/oradata/dbdg/stbyredo4.log') SIZE 50M;

Please note there are four standby redo logs created. Usually you should have n+1 standby redo log where n is the number of online redo logs from primary. {}

Online redo logs:

Create online redologs (optional for role reversal) But this cannot be done when the standby_file_management is set to AUTO. So we have to change this to MANUAL and then create the online log files.

ALTER DATABASE ADD LOGFILE ('/u02/app1/oracle/oradata/dbdg/onlineredo1.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u02/app1/oracle/oradata/dbdg/onlineredo2.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u02/app1/oracle/oradata/dbdg/onlineredo3.log') SIZE 50M;

Start recovery:

alter database recover managed standby database disconnect from session;

Now MRP process in the standby would start and the changes would be applied to the standby.

Verification:

One usual issue with the DG setup is the log shipping may not happen due to incorrect service configuration. We can check the same with the following Query.

select dest_id, status, error from v$archive_dest;

In this setup I had issue with log shipping and the above query clearly denotes that.

select dest_id,status,error from v$archive_dest;

   DEST_ID STATUS
---------- ---------
ERROR
-----------------------------------------------------------------
         1 VALID

         2 ERROR
ORA-12154: TNS:could not resolve the connect identifier specified

So from the error from primary it is clear that the second archive log destination is not valid because of the TNS issue. Further checking with we could see that the issue was with the TNS names. The service_name in TNS was incorrect as it was missing the domain. After changing that MRP started applying and destination 2 became valid.

You may further check if logs are shipped and applied by
SQL>archive log list

From both the primary and standby.