Resolve Physical Standby gaps

Resolve Physical Standby gaps

Scenario #1 - Version 10g – ASM is used as storage and primary’s backups not available on standby
Assumptions:
1. The primary’s backup location can not be mounted onto standby server i.e it the NFS mount where primary’s backup are taken is not available to standby server
2. ASM is the storage on primary and standby

To resolve the gap, we need to transfer the standby from primary to standby.

There are 2 cases:

1. The missing log is available in the primary database and has not been deleted
2. The missing log is not available in the primary database and is available in the archive log backup of primary.


First check if the log is available or not, run the following query on primary database:

select status, deleted from v$archived_log where sequence# = ;
In both the cases, the steps are almost similar. Wherever the steps are exclusive to a case, a note is provided to specify the same.

Step#1 – Restore the missing log from primary’s archived log backup.

This step is only required when the log has been deleted from the primary database and is available only in the archived log backups.
RMAN> connect catalog username/password@catalog

RMAN> connect target /

RMAN> restore archivelog sequence 157682;

==> If there is a gap of more than 1 log, we need to use the statement like:

RMAN> restore archivelog from sequence until sequence ;

Note that this will restore the archive log to the default archival destination of the database specified by the parameter log_archive_dest_1

==> You can use the following command to know the backup piece which contains the backup of that missing log

RMAN> list backup of archivelog sequence 157682;

Step#2 – Copy the archive log from ASM diskgroup to the normal OCFS file system

If the missing archive log is available in the primary database, start from this step.
Use the following RMAN command to achieve this:

RMAN> copy archivelog '+DATA/IPWP_RWC1/ARCHIVELOG/2009_11_13/thread_1_seq_157682.1341.702785107' to '/tmp/thread_1_seq_157682.1341.702785107';

Step#3 – “scp” the log from primary server to standby server’s file system

scp /tmp/thread_1_seq_157682.1341.702785107 oracle@ipw-db-sac1: /tmp/thread_1_seq_157682.1341.702785107

Step#4 – Manually recover the standby database using the just shipped archived log

To do this first cancel the managed recovery:

Now, we need to perform the manual recovery to apply the archive log which is missing and has been transferred from primary:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> recover automatic standby database; <== This is for manual recovery ORA-00279: change 34121233951 generated at 11/12/2009 07:54:10 needed for
thread 1
ORA-00289: suggestion : +DATA
ORA-00280: change 34121233951 for thread 1 is in sequence #157682
ORA-00278: log file '+DATA' no longer needed for this recovery
ORA-00308: cannot open archived log '+DATA'
ORA-17503: ksfdopn:2 Failed to open file +DATA
ORA-15045: ASM file name '+DATA' is not in reference form


Specify log: {=suggested filename AUTO CANCEL}
/tmp/thread_1_seq_157682.1341.702785107 <== At this prompt, provide the name of the log which has been copied over from primary

ORA-00279: change 34121306612 generated at 11/12/2009 07:58:19 needed for
thread 1
ORA-00289: suggestion : +DATA
ORA-00280: change 34121306612 for thread 1 is in sequence #157683
ORA-00278: log file '/tmp/thread_1_seq_157682.1341.702785107' no longer needed
for this recovery


Specify log: {=suggested filename AUTO CANCEL}
CANCEL <== At this prompt now, enter CANCEL to tell oracle to stop recovery because we have applied the one missing log
Media recovery cancelled.

Now, restart managed recovery

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Now by querying the view v$archived_log, we can see that the missing log has been bypassed and MRP process has proceeded the log application process.

Scenario # 2 - Version 10g – Primary’s backups are available on the standby
Assumptions:
1. The primary’s backup location can be mounted onto standby server i.e it the NFS mount where primary’s backup are taken is available to standby server
2. OCFS is being used as storage

If the archive logs have been deleted from the database, we need to restore them from the archive log backups as demonstrated. This scenario is in fact independent of ASM or OCFS because backup pieces are available on the standby.

STEP#1: First, know the primary’s backup piece(s) which contain the missing archive log(s), for example:

RMAN> list backup of archivelog from sequence 154472 until sequence 154474;
This will give the name of all backup pieces which contain the required logs.

STEP#2: Since the backup pieces are available on the standby server, catalog all the backup pieces on the standby. For example,

RMAN > catalog backuppiece '/location/piece_name';
You do not need to connect to recovery catalog, but connect to standby as the target. Note that “catalog” is only available in version 10g

STEP#3: Once the pieces are cataloged, stay connected to the standby database as target and restore the archive logs as follows:

RMAN> restore archivelog from sequence 154472 until sequence 154474;

RMAN will restore all the archivelogs to the correct diskgroup (archival location) on the standby

Since we have now restored the missing log(s) onto the standby, there is no need of recovery. The restored logs will now be available in standby and shold be applied by MRP automatically which has been so far waiting for the gaps to be filled.

Scenario # 3 - Version 9i – Primary’s backups are not available on standby server
Assumptions:
1. The archive logs have been deleted from the primary database
2. Primary’s backups are not available on standby server.

In this scenario, the steps are almost similar to the first scenario.

Note that, if ASM is not the storage, then we just need to restore the archive logs and move them to the standby server. Which means step#2 can be skipped when compared to the first scenario.
Important notes on resolving physical standby gaps

==> If the archive log has not been deleted from the database and ASM is not the storage, then simply copy the archive log from primary to standby’s archival location. MRP will automatically pick the archive logs and start applying them. This is true for 10g as well as 9i.

==> In all the scenarios, there are 2 fundamental ways to fill the gap on standby – Either by restoring the missing logs at standby’s archival location where MRP can pick it up automatically
OR
If the missing log(s) can not be restored at the default location, then perform an incomplete recovery of standby using the restored archived log which has been restored at the non-default location. This is basically applicable in cases where ASM is being used

Thursday, October 16, 2008

Physical Standby out of sync - Missing Datafiles Scenario

Environment:
1. Primary has 200 datafiles and standby has only 166 datafiles

2. Primary is a 3 node cluster and Standby is a 2 node cluster

3. The DB name is mydb

Problem and Symptoms:
1. When I tried to start the MRP on standby, it reported the following error in alert log:

**************************************************************
Errors in file /u01/app/oracle/admin/mydb/bdump/mydb1_mrp0_21189.trc:ORA-01111: name for data file 167 is unknown - rename to correct fileORA-01110: data file 167: '/u01/app/oracle/product/9.2.0/dbs/UNNAMED00167'ORA-01157: cannot identify/lock data file 167 - see DBWR trace fileORA-01111: name for data file 167 is unknown - rename to correct fileORA-01110: data file 167: '/u01/app/oracle/product/9.2.0/dbs/UNNAMED00167'
*************************************************************

2. On further investigation, standby’s alert log also contains following errors:

************************************************************************
Tue Sep 9 04:05:03 2008Media Recovery Log /u03/oradata/mydb/arc_backup/mydb_2_2173.arcMedia Recovery Log /u03/oradata/mydb/arc_backup/mydb_1_1896.arcWARNING: File being created with same name as in PrimaryExisting file may be overwrittenFile #167 added to control file as 'UNNAMED00167'. Originally created as:'/u07/oradata/mydb/myfile_1.dbf'Recovery was unable to create the file as:'/u07/oradata/mydb/myfile_1.dbf'MRP0: Background Media Recovery terminated with error 1274Tue Sep 9 04:05:06 2008Errors in file /u01/app/oracle/admin/mydb/bdump/mydb1_mrp0_7175.trc:ORA-01274: cannot add datafile '/u07/oradata/mydb/myfile-1.dbf' - file could not be createdORA-01119: error in creating database file '/u07/oradata/mydb/myfile_1.dbf'ORA-27054: Message 27054 not found; product=RDBMS; facility=ORALinux-x86_64 Error: 13: Permission denied
**************************************************************************

3. On checking the view v$archived_log, there were lot of log sequence# which were APPLIED=NO

4. There is no gap in the sequence#

Reason:

Parameter db_file_name_convert was not set at standby database. So as long as the files were created on /u02 and /u03 on primary, there was no problem on the standby because standby had /u02 and /u03. But when file#167 was added at /u07 on primary (on Sep 9 04:05:03 2008), it could not map to a /u07 mount point on standby because /u07 does not exists on standby and db_file_name_convert was also not set. As indicated by the alert log, the file#167 was registered in the standby’s control file as “UNANMED00167” at the default location of $ORACLE_HOME/dbs but the file was not created physically on standby database.

Action Plan:

1. At the standby:Please set the db_file_name_convert parameter at the Standby for the /u07 folder at the Primary to the corresponding folder at the Standby.
Since this parameter is a Static parameter, you need to bounce the Standby DB.

******************************************************************************
As step#1, you can do following instead of the above step:

At the standby:
Create /u07 soft link for /u02, to eliminate the bounce of standby db due to the addition of db_file_name_convert init.ora parameter

*********************************************************************************************
2. At the standby :SQL> alter system set standby_file_management=manual;

3. At the Primary for the datafile 167 :

SQL> alter tablespace <> begin backup ;
Copy the Datafile from the Primary to Standby to the correct location.
SQL> Alter tablespace end backup;

4. At the Standby:

SQL> alter database rename file '.......UNNAMED00167' to '<>';

******************************************************************************
You can skip steps#3 and #4 and instead do following step after #2:

At the Standby:
SQL> ALTER DATABASE CREATE DATAFILE '< ....UNNAMED00167>' as '<>';

******************************************************************************
5. To create the remaining datafiles at the Standby automatically:

SQL> alter system set standby_file_management=auto;

6. Start the MRP at the Standby
SQL> alter database recover managed standby database;

At standby database ensure the MRP is running as expected:
SQL>select process, status , sequence# from v$managed_standby;

When Primary and Standby are RAC databases:
1. On Standby: You can see multiple copies of some or all logs transported and applied on standby when you check the view v$archived_log.

2. On Standby: All sequence# should have APPLIED=YES in v$archived_log for all threads. This ensures that all logs from all threads were transported and applied on standby and hence keeps standby in sync with primary.

3. On Standby: In the view v$archived_log you may not see same number of multiple copies of all logs. For example, if the primary is a 3 node cluster, you may or may not have 3 copies of each log i.e. you may not have the same sequence# log on standby for all 3 threads. Of course the reason is that number of logs generated on all 3 nodes of primary will differ. The current sequence# transported from a node of primary RAC database can be seen by querying v$archived_log on standby:

SQL> select max(sequence#) from v$archived_log where thread#=1;

As explained above, the output will differ for all 3 threads.

Wednesday, August 6, 2008

Tips for Oracle DBAs

I have a blog where i stream some useful Oracle tips.
http://rohitguptaoracletips.blogspot.com/
These are very few tips out of some challenging tasks i have been doing and do have lot more to add

-Rohit

So your standby is out of sync?

When using dataguard, there are various scenarios where physical standby goes out of sync with primary. Refer http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIAADC for scenarios where standby needs to be rolled forward and the steps to follow to bring it in sync with primary.

Before doing anything we need to verify that why standby is not in sync with primary. In this paticular note, i am covering the scenrio where a log is missing from the standby and the associated problems. Verify from v$archived_log that there is a gap in Sequence number. All the logs upto that gap should have APPLIED=YES.

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

There are basically couple of steps to be performed when the standby is not in sync with Primary and is lagging in terms of redo logs. These are:
1. Take a incremental backup of primary from the SCN where standby is lagging behind and apply on the standby server
2. Then re-create the controlfile of standby database from the primary
******************************************************************************
STEP#1
1. on STANDBY database query the V$DATABASE view and record the current SCN of the standby database:
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1.3945E+10

SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;
TO_CHAR(CURRENT_SCN)
----------------------------------------
13945141914

2. Stop Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL*ERROR at line 1:ORA-16136: Managed Standby Recovery not active
If you see this above error, it means Managed Recovery is already off
You can also confirm from the view v$managed_standby to see if the MRP is running or not
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

3. Connect to the primary database as the RMAN target and create an incremental backup from the current SCN of the standby database that was recorded in step 1:
BACKUP INCREMENTAL FROM SCN 13945141914 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FOR STANDBY'

4. Do a recovery of STANDBY database using the incremental backup of primary taken above
--> On the Standby server, Without connecting to recovery catalog, catalog the backupset of the incermental backup
$ rman nocatalog target /
RMAN> CATALOG BACKUPPIECE '/dump/ipwp/inc_bkup/ForStandby_1qjm8jn2_1_1';
--> Now in the same session, start the recovery
RMAN> RECOVER DATABASE NOREDO;
you should see something like follwing at the end:
channel ORA_DISK_1: reading from backup piece /dump/ipwp/inc_bkup/ForStandby_1qjm8jn2_1_1channel ORA_DISK_1: restored backup piece 1piece handle=/dump/ipwp/inc_bkup/ForStandby_1qjm8jn2_1_1 tag=FOR STANDBYchannel ORA_DISK_1: restore complete, elapsed time: 01:53:08
Finished recover at 2008-07-25 05:20:3
--> Delete the backup set from standby
RMAN> DELETE BACKUP TAG 'FOR STANDBY';
using channel ORA_DISK_1
List of Backup PiecesBP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
17713 17713 1 1 AVAILABLE DISK /dump/ipwp/inc_bkup/ForStandby_1qjm8jn2_1_1
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piecebackup piece handle=/dump/ipwp/inc_bkup/ForStandby_1qjm8jn2_1_1 recid=17713 stamp=660972421Deleted 1 objects

5. Try to start the managed recovery.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
--> If you get an error here, you need to goto STEP#2 for bringing standby in sync
--> If no error, then using the view v$managed_standby, verify that MRP process is started.

6. After this check whether the logs are being applied on the standby or not.
SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
*******************************************************************************************
STEP #2: Since Managed recovery failed after applying the incremental backup, we need to re-create the controlfile of standby. The reason for re-creating the controlfile is that State of the database was same because the database_scn was not updated in the control file after applying the incremental backup while the scn for datafiles were updated. Due to this standby database was still looking for the old file to apply.
To recreate the standby controlfile:
--> Take the backup of controlfile from primary
rman target sys/oracle@boston catalog rman/cat@emrepbackup current controlfile for standby;
--> Copy the controlfile backup to the standby system (or if it is on the common NFS mount, no need to transfer or copy)
--> Shutdown all instances (If standby is RAC) of the standby.
sqlplus / as sysdbashutdown immediateexit
--> Startup nomount, one instance.
sqlplus / as sysdbastartup nomountexit
--> Restore the standby control file.
rman nocatalog target /restore standby controlfile from '/tmp/o1_mf_TAG20070220T151030_.bkp';exit
--> Startup the standby with the new control file.
sqlplus / as sysdbashutdown immediatestartup mountexit
--> Restart managed recovery in one instance (if standby is RAC) of the standby database:
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT

The abobe statement will succeed without errors but still MRP process is not started. The reason is that since the controlfile has been restored from the primary, it is looking for datafiles at the same location as are in primary instead of standby. For example, if the priamry datafiles are located at '+DATA/prod_db/DATAFILE' and standby datafiles are at '+DATA/standby_db/DATAFILE', the new controlfile has the datafiles location as '+DATA/prod_db/DATAFILE'. This can be verified from the query "select name from v$datafile" on the standby instance. We need to rename all the datafiles to reflect the correct location.
To rename the datafiles, there are 2 ways:
1. Without RMAN
--> Change the parameter standby_file_management=manual--> ALTER DATABASE RENAME FILE '+DATA/prod_db/datafile/users.310.620229743' TO '+DATA/standby_db/datafile/USERS.1216.648429765';
2. Using RMAN
--> rman nocatalog target /
--> Catalog the files, the string specified should refer to the diskgroup/filesystem destination of the standby data files.
RMAN> catalog start with '+diskgroup//datafile/';
e.g.:
RMAN> catalog start with '+DATA/ipwp_sac1/datafiles/';
This will give the user a list of files and ask if they should all be catalog. The user should review and say YES if all the datafiles are properly listed.
--> Once that is done, then commit the changes to the controlfile
RMAN> switch database to copy;
--> Now if you start the managed recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
and check for processes in the view V$MANAGED_STANBY, MRP process should be there. It will also start applying all the archived logs that were missing since last applied log (this might take hours)
*******************************************************************************************
After re-crreating the controfile and renaming the datafiles (before starting the managed recovery), we observed (and it is possible) that there is a datafile in production which is not present on the standby. This was verified by checking the names of datafiles from v$datafile view. It showed that there is one datafile whose location is still as of production and renaming effort also failed because the datafile is not at all present in the standby. So in such a case we need to backup that single datafile from production and restore it at standby.
--> On Production:
RMAN> run{ Allocate channel c1 type disk; Backup datafile '+DATA/ipwp_rwc1/datafile/ipw_invli_is.1643.660041401' format '/dump/ipwp/rman_backup/ipw_invli_is'; }

--> On standby:
RMAN> catalog backuppiece '/dump/db/rman_backup/ipw_invli_is;
cataloged backuppiecebackup piece handle=/dump/ipwp/rman_backup/ipw_invli_is recid=26806 stamp=661232892
RMAN> RESTORE DATAFILE '+DATA/prod_db/datafile/ipw_invli_is.1643.660041401';
Starting restore at 2008-07-28 03:58:18allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=321 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00045 to +DATA/ipwp_rwc1/datafile/ipw_invli_is.1643.660041401channel ORA_DISK_1: reading from backup piece /dump/ipwp/rman_backup/ipw_invli_ischannel ORA_DISK_1: restored backup piece 1piece handle=/dump/ipwp/rman_backup/ipw_invli_is tag=TAG20080728T010613channel ORA_DISK_1: restore complete, elapsed time: 00:01:26Finished restore at 2008-07-28 03:59:46
RMAN> delete backuppiece '/dump/ipwp/rman_backup/ipw_invli_is';
allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=310 devtype=DISK
List of Backup PiecesBP Key BS Key Pc# Cp# Status Device Type Piece Name------- ------- --- --- ----------- ----------- ----------26806 26805 1 1 AVAILABLE DISK /dump/ipwp/rman_backup/ipw_invli_is
Do you really want to delete the above objects (enter YES or NO)? YESdeleted backup piecebackup piece handle=/dump/ipwp/rman_backup/ipw_invli_is recid=26806 stamp=661232892Deleted 1 objects
--> After this re-confirm the location of all datafiles and then start the managed recovery.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
*******************************************************************************

How do I recover an Oracle database table with recyclebin?

How do I recover an Oracle database table with recyclebin?

One feature of Oracle 10g is recyclebin, which allows you to recover dropped tables. With recyclebin, any tables you drop do not actually get deleted. Instead, Oracle renames the table and its associated objects to a system-generated name that begins with BIN$.

For example, consider this simple table:

  SQL> create table testing (col varchar2(10), row_chng_dt date);

  Table created.

  SQL> insert into testing values ('Version1', sysdate);

  1 row created.

  SQL> select * from testing ;

  COL        ROW_CHNG

  ---------- --------

  Version1   16:10:03

Dropping this table will place it in recyclebin:

  SQL> drop table testing;

  Table dropped.

  SQL> select object_name, original_name, type, droptime from recyclebin;

  OBJECT_NAME                    ORIGINAL_NAME TYPE  DROPTIME

  ------------------------------ ------------- ----- ---------------

  BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TESTING       TABLE 2006-09-01:16:10:12

Dropping this table caused it to be renamed. The table data is still there, and you can query it as you would a normal table:

  SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

  COL        ROW_CHNG

  ---------- --------

  Version1   16:10:03

Because the table data is still there, it's very easy to "undrop" the table; use the FLASHBACK TABLE... TO BEFORE DROP command. This simply renames the BIN$... table to its original name:

  SQL> flashback table testing to before drop;

  Flashback complete.

  SQL> select * from testing ;

  COL        ROW_CHNG

  ---------- --------

  Version1   16:10:03

  SQL> select * from recyclebin ;

  no rows selected

Note: After a table has been dropped, it has only been renamed. It is still part of your tablespace, and this space counts against your user tablespace quota. The space will not be reclaimed until you restore the table with the flashback command, or use purge to clear the table out of recyclebin:

  SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

  Table purged.

Oracle will leave objects in recyclebin until the tablespace runs out of space, or until you reach your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation.

Adding,Deleting or renaming tablespace or database on primary what affect on standby database

8.1 Adding a Datafile or Creating a Tablespace

The initialization parameter, STANDBY_FILE_MANAGEMENT, allows you to control whether or not adding a datafile to the primary database is automatically propagated to the standby database, as follows:

• If you set the STANDBY_FILE_MANAGEMENT initialization parameter in the standby database server parameter file to AUTO, any new datafiles created on the primary database are automatically created on the standby database as well.
• If you do not specify the STANDBY_FILE_MANAGEMENT initialization parameter or if you set it to MANUAL, then you must manually copy the new datafile to the standby database when you add a datafile to the primary database.

Note that if you copy an existing datafile from another database to the primary database, then you must also copy the new datafile to the standby database and re-create the standby control file, regardless of the setting of STANDBY_FILE_MANAGEMENT initialization parameter.

The following sections provide examples of adding a datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO and MANUAL, respectively.

8.2 Adding a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to AUTO

The following example shows the steps required to add a new datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

1. Add a new tablespace to the primary database:

SQL> CREATE TABLESPACE new_ts DATAFILE 't_db2.dbf'

  2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;

2. Archive the current redo log so it will get copied to the standby database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

3. Verify that the new datafile was added to the primary database:

SQL> SELECT NAME FROM V$DATAFILE;

----------------------------------------------------------------------

/disk1/oracle/dbs/t_db1.dbf

/disk1/oracle/dbs/t_db2.dbf

4. Verify that the new datafile was added to the standby database:

SQL> SELECT NAME FROM V$DATAFILE;

----------------------------------------------------------------------

/disk1/oracle/dbs/s2t_db1.dbf

/disk1/oracle/dbs/s2t_db2.dbf

8.3 Adding a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to MANUAL

The following example shows the steps required to add a new datafile to the primary and standby database when the STANDBY_FILE_MANAGEMENT initialization parameter is set to MANUAL. You must set the STANDBY_FILE_MANAGEMENT initialization parameter to MANUAL when the standby datafiles reside on raw devices.

1.      Add a new tablespace to the primary database:

SQL> CREATE TABLESPACE new_ts DATAFILE 't_db2.dbf'

       2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;

2.      Verify that the new datafile was added to the primary database:

SQL> SELECT NAME FROM V$DATAFILE;

----------------------------------------------------------------------

/disk1/oracle/dbs/t_db1.dbf

/disk1/oracle/dbs/t_db2.dbf

3.      Perform the following steps to copy the tablespace to a remote standby location:

a.       Place the new tablespace offline:

SQL> ALTER TABLESPACE new_ts OFFLINE;

b.      Copy the new tablespace to a local temporary location using an operating system utility copy command. Copying the files to a temporary location will reduce the amount of time that the tablespace must remain offline. The following example copies the tablespace using the UNIX cp command:

% cp t_db2.dbf s2t_db2.dbf

c.       Place the new tablespace back online:

SQL> ALTER TABLESPACE new_ts ONLINE;

d.      Copy the local copy of the tablespace to a remote standby location using an operating system utility command. The following example uses the UNIX rcp command:

%rcp s2t_db2.dbf standby_location

4.      Archive the current redo log on the primary database so it will get copied to the standby database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

5.      Use the following query to make sure that managed recovery is running. If the MRP or MRP0 process is returned, managed recovery is being performed.

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

6.      Verify that the datafile was added to the standby database after the redo log was applied to the standby database.

 SQL> SELECT NAME FROM V$DATAFILE;

----------------------------------------------------------------------

/disk1/oracle/dbs/s2t_db1.dbf

/disk1/oracle/dbs/s2t_db2.dbf

8.4 Dropping a Tablespace in the Primary Database

When you delete one or more datafiles or drop one or more tablespaces in the primary database, you also need to delete the corresponding datafiles in the standby database, as follows:

1. Drop the tablespace at the primary site:

SQL> DROP TABLESPACE tbs_4;

SQL> ALTER SYSTEM SWITCH LOGFILE;

% rm tbs_4.dbf

2. Make sure that managed recovery is on (so that the change is applied to the standby database). If the following query returns the MRP or MRP0 process, managed recovery is on.

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

3. Delete the corresponding datafile on the standby site after the archived redo log was applied to the standby database. For example:

% rm tbs_4.dbf

4. On the primary database, after ensuring that the standby database has applied the redo information for the dropped tablespace, you can remove the datafile for the tablespace. For example:

% rm tbs_4.dbf

8.5 Renaming a Datafile in the Primary Database

When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

The following steps describe how to rename a datafile in the primary database and manually propagate the changes to the standby database. If you do not want the standby database to have the same physical structure as the primary database, then these steps are not required.

1. To rename the datafile in the primary database, take the tablespace offline:

SQL> ALTER TABLESPACE tbs_4 OFFLINE;

2. Exit from the SQL prompt and issue an operating system command, such as the following UNIX mv command, to rename the datafile on the primary system:

% mv tbs_4.dbf tbs_x.dbf

3. Rename the datafile in the primary database and bring the tablespace back online:

SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE 'tbs_4.dbf'

  2> TO 'tbs_x.dbf';

SQL> ALTER TABLESPACE tbs_4 ONLINE;

4. Connect to the standby database and make sure that all the logs are applied; then stop managed recovery operations:

SQL> SELECT NAME, SEQUENCE#, ARCHIVED, APPLIED

  2> FROM V$ARCHIVED_LOG;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

5. Shut down the standby database:

SQL> SHUTDOWN;

6. Rename the datafile at the standby site using an operating system command, such as the UNIX mv command:

% mv tbs_4.dbf tbs_x.dbf

7. Start and mount the standby database with the new control file:

  SQL> STARTUP NOMOUNT;

     SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

8. Rename the datafile in the standby controlfile. Note that the STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL.

SQL> ALTER DATABASE RENAME FILE 'tbs_4.dbf'

  2> TO 'tbs_x.dbf';

9. On the standby database, restart managed recovery operations:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

  2> DISCONNECT FROM SESSION;

If you do not rename the corresponding datafile at the standby site, and then try to refresh the standby database control file, the standby database will attempt to use the renamed datafile, but it will not find it. Consequently, you will see error messages similar to the following in the alert log:

ORA-00283: recovery session canceled due to errors

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/disk1/oracle/dbs/tbs_x.dbf'

Data Guard – Adding and Resizing a datafile on the Primary database

In a Data Guard environment, care should be taken while performing tasks like adding or
resizing datafiles on the primary database. Ideally, the primary and standby sites should
be identical in terms of file system structure. Also disk space allocated should be identical,
or we could be faced with a case when resizing a datafile on the the primary site fails on the
standby site as the disk space available is not adequate or maybe a file was added on a file
system on the primary and the corresponding file system was not available on the standby site
based on the parameter db_file_name_convert.

In such cases the MRP0 process will stop on the standby site and redo apply operations will cease.

Test case:

Primary machine:  itlinuxdevblade07
Standby machine: itlinuxdevblade08

We will use the USERS tablespace for the test

• Add a datafile
• Resize a datafile
• Drop tablespace

Database used for testing is dgtest9i.

Firstly the following gives an explanation of init.ora parameters used in a dataguard set-up
and whether they pertain to the primary or standby database.

log_archive_dest_1 : used both when Primary and when Standby

standby_archive_dest : used when Standby, ignored when Primary

log_archive_dest_2 :  used when Primary, ignored when Standby

db_file_name_convert : used when Standby, ignored when Primary

log_file_name_convert :  used when Standby, ignored when Primary

fal_server :   used when Standby, ignored when Primary

fal_client :  used when Standby, ignored when Primary

standby_file_management :  used when Standby, ignored when Primary

log_archive_format :  used both when Primary and when Standby

log_archive_start :  used both when Primary and when Standby

remote_archive_enable : must be TRUE on both Primary and Standby

drs_start :  must be TRUE to use Data Guard Broker

compatible :   must be identical on both Primary and Standby

db_name, db_domain :  must be identical on both Primary and Standby

Primary SITE:

. setdgtest9i

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf

cd /opt/oracle/oradata/dgtest9i> ls –l

[oracle@itlinuxdevblade07 dgtest9i]$ ls -l
total 987948
drwxr-xr-x    2 oracle   dba          4096 Feb 12 11:28 bkp
-rw-r-----    1 oracle   dba      41951232 Jan 25 12:20 temp01.dbf
-rw-r-----    1 oracle   dba      78651392 Mar  4 14:33 undotbs01.dbf
-rw-r-----    1 oracle   dba      22028288 Mar  3 14:41 users01.dbf

In STANDBY database init.ora…

*.standby_file_management='AUTO'
.db_file_name_convert='/opt/oracle/','/opt/oracle/'
*.log_file_name_convert='/opt/oracle/','/opt/oracle/'

STANDBY SITE:

cd / opt/oracle/oradata/dgtest9i>

[oracle@itlinuxdevblade08 dgtest9i]$ /bin/ls –l…. only few files are shown here to save space.
total 1035568
-rwxrwxr-x    1 oracle   dba      41951232 Feb 23 22:38 temp01.dbf
-rwxrwxr-x    1 oracle   dba      78651392 Mar  4 14:37 undotbs01.dbf
-rwxrwxr-x    1 oracle   dba      22028288 Mar  4 09:08 users01.dbf

1.Add a datafile.

In primary database :

. setdgtest9i

SQL> alter tablespace  users add datafile '/opt/oracle/oradata/dgtest9i/users02.dbf'  size 10m;

Tablespace altered.

SQL> alter system switch logfile;

(To make sure the a log is shipped to the standby site and changes applied.)

System altered.

If you check in the standby site you will see the new file ….

STANDBY SITE

[oracle@itlinuxdevblade08 dgtest9i]$ ls -lrt users*
total 1045832
-rw-r-----    1 oracle   dba      10493952 Mar  4 14:46 users02.dbf
-rw-r-----    1 oracle   dba      22028288 Mar  4 14:46 users01.dbf

2. Resize datafile.

PRIMARY SITE:

SQL> Alter database datafile  ‘/opt/oracle/oradata/dgtest9i/users02.dbf' RESIZE 5m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

Now in  STANDBY database machine…

[oracle@itlinuxdevblade08 dgtest9i]$ ls –lrt users*
total 1040708
-rw-r-----    1 oracle   dba       5251072 Mar  4 18:00 users02.dbf
-rwxrwxr-x    1 oracle   dba      22028288 Mar  4 18:00 users01.dbf

Notice the size of the users02.dbf file is 5 m on the standby site as well…

PROBLEMS THAT MAY ARISE :

Now we will look at the scenario where the db_file_convert parameter PATH was wrong..

SHUTDOWN both standby and primary databases…..

Change the db_file_name_convert parameter from standby database init.ora file and recreate
 the spfile from pfile.

EX: db_file_name_convert =/opt/oracle/, /opt/arjun

arjun directory must not exist IN STANDBY machine...

Now startup  the standby db…

SQL> Startup nomount;

SQL> Alter database mount standby database;

Restart PRIMARY DATABASE.

Go back to standby database and

SQL> recover managed standby database disconnect;

SQL> show parameter convert

NAME                                  TYPE      VALUE
------------------------------------ ----------- ---------------------------------------
db_file_name_convert             string /opt/oracle/, /opt/arjun

Now go to PRIMARY database and add a datafile…..

SQL> alter tablespace users add datafile
'/opt/oracle/oradata/dgtest9i/users04.dbf' size 5m;

Tablespace altered.

SQL> alter system switch logfile;

System altered.

SQL> SQL> /

System altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf

In standby site…

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/arjun/oradata/dgtest9i/system01.dbf
/opt/arjun/oradata/dgtest9i/undotbs01.dbf
/opt/arjun/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf

Notice file users04 is missing…

In this case shutdown the standby database….and recreate the spfile making sure the path is right..

Then restart the standby database and start recovery….

SQL>  select message from v$dataguard_status;

MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
Media Recovery Log /opt/oracle/dgtest9i/arch/arch196.log
Media Recovery Waiting for thread 1 seq# 197

10 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf

NEXT SCENARIO….ADDING DATAFILE IN ANOTHER AREA ON PRIMARY MACHINE…

SQL> create tablespace arjun datafile '/u01/ORACLE/dgtest9i/arjun1.dbf' size 5m;..The directory
/u01/ORACLE/dgtest9i does not exist on the STANDBY machine.

Tablespace created.

SQL> alter system switch logfile;

SQL> SELECT NAME FROM V$DATAFILE;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf
/u01/ORACLE/dgtest9i/arjun1.dbf

On STANDBY site…..

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf
/opt/oracle/product9204/dbs/UNNAMED00007….

7 rows selected.

STANDBY SITE

SQL>  SHOW PARAMETER STANDBY;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO

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

SQL> alter database create datafile '/opt/oracle/product9204/dbs/UNNAMED00007'
  2  as '/opt/oracle/oradata/dgtest9i/arjun1.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf
/opt/oracle/oradata/dgtest9i/arjun1.dbf

7 rows selected.

SQL> alter system set standby_file_management=auto ;

3. DROPPING TABLESPACE.

In PRIMARY…

SQL> DROP TABLESPACE ARJUN INCLUDING CONTENTS AND DATAFILES;

SQL> Alter system switch logfile;

SQL> SELECT NAME FROM V$DATAFILE;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf

6 rows selected.

In STANDBY Database….

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf

6 rows selected.