Example: Renaming standby database files

Owned by KrupeshD (Deactivated)
Last updated: Nov 30, 2012Version comment
5 min read

The example below demonstrates various techniques Dbvisit Standby uses to rename standby database files when creating a standby database.

Primary database structure

 

 SQL> select member from v$logfile; 
 -------------------------------------------------------------------------------- 
 +ONLINELOGS/dbvlx102/onlinelog/group_2.264.768568567 
 +ONLINELOGS/dbvlx102/onlinelog/group_2.261.768568567 
 +ARCHIVELOGS/dbvlx102/onlinelog/redo_1_2 
 +ARCHIVELOGS/dbvlx102/onlinelog/redo_1_1 

 SQL> select name from v$datafile; 
 -------------------------------------------------------------------------------- 
 +DATA/dbvlx102/datafile/system.298.754676807 
 +DATA/dbvlx102/datafile/undotbs1.287.754676863 
 +DATA/dbvlx102/datafile/sysaux.297.754676905 
 +DATA/dbvlx102/users.dbf 
 +DATA/dbvlx102/datafile/users.290.754677031 

 SQL> select name from v$controlfile; 
 -------------------------------------------------------------------------------- 
 +ARCHIVELOGS/dbvlx102/control01.ctl 
 +DATA/dbvlx102/control02.ctl 
 +ONLINELOGS/dbvlx102/control03.ctl 

 SQL> select name from v$tempfile; 
 -------------------------------------------------------------------------------- 
 +DATA/dbvlx102/tempfile/temp1.307.762542407 
 +DATA/dbvlx102/tempfile/temp1.259.762542407 
 +DATA/dbvlx102/tempfile/temp1.258.762542409

Oracle database parameters in the primary database

 

audit_file_dest /oracle/orabase/admin/dbvlx102/adump 
 background_dump_dest /oracle/orabase/admin/dbvlx102/bdump 
 compatible 10.2.0.1.0 
 control_files +ARCHIVELOGS/dbvlx102/control01.ctl, +DATA/dbvlx102/control02.ctl, +ONLINELOGS/dbvlx102/control03.ctl 
 core_dump_dest /oracle/orabase/admin/dbvlx102/cdump 
 db_block_size 8192 
 db_create_file_dest +DATA 
 db_create_online_log_dest_1 +ARCHIVELOGS 
 db_create_online_log_dest_2 +ARCHIVELOGS 
 db_file_multiblock_read_count 16 
 db_name dbvlx102 
 db_recovery_file_dest +DATA 
 db_recovery_file_dest_size 2147483648 
 dispatchers (PROTOCOL=TCP) (SERVICE=dbvlx102XDB) 
 job_queue_processes 10 
 log_archive_dest_1 LOCATION=+DATA 
 log_archive_dest_2 LOCATION=/oracle/oraarch/dbvlx102 
 log_archive_format %t_%s_%r.dbf 
 log_archive_start TRUE 
 open_cursors 300 
 pga_aggregate_target 16777216 
 processes 150 
 remote_login_passwordfile EXCLUSIVE 
 sga_target 167772160 
 spfile OS default 
 standby_file_management MANUAL 
 undo_management AUTO 
 undo_tablespace UNDOTBS1 
 user_dump_dest /oracle/orabase/admin/dbvlx102/udump

We have chosen to create a filesystem standby database and edited the Oracle database parameters for the standby. Note we have enabled creating of OMF files in the standby by setting parameters db_create_file_dest, db_create_online_log_dest_1 and db_create_online_log_dest_2 to valid filesystem locations. We left the parameter control_files empty to create OMF control files,The following oracle database parameters will be set in the standby database pfile or spfile:

 

audit_file_dest /oracle/orabase/admin/dbvlx102/adump 
 background_dump_dest /oracle/orabase/admin/dbvlx102/bdump 
 compatible 10.2.0.1.0 
 control_files 
 core_dump_dest /oracle/orabase/admin/dbvlx102/cdump 
 db_block_size 8192 
 db_create_file_dest /oracle/oradata 
 db_create_online_log_dest_1 /oracle/oradata 
 db_create_online_log_dest_2 /oracle/oradata 
 db_file_multiblock_read_count 6 
 db_name dbvlx102 
 db_recovery_file_dest /oracle/flash_recovery_area 
 db_recovery_file_dest_size 2147483648 
 dispatchers (PROTOCOL=TCP) (SERVICE=dbvlx102XDB) 
 job_queue_processes 10 
 log_archive_dest_1 LOCATION=/oracle/oraarch/dbvlx102 
 log_archive_dest_2 
 log_archive_format %t_%s_%r.dbf 
 log_archive_start TRUE 
 open_cursors 300 
 pga_aggregate_target 16777216 
 processes 150 
 remote_login_passwordfile EXCLUSIVE 
 sga_target 167772160 
 spfile OS default 
 standby_file_management MANUAL 
 undo_management AUTO 
 undo_tablespace UNDOTBS1 
 user_dump_dest /oracle/orabase/admin/dbvlx102/udump

Dbvisit Standby prompted to provide filesystem locations for data/temp files as the primary database contains one non OMF datafile: +DATA/dbvlx102/users.dbf

 

Primary database contains non OMF datafiles and/or tempfiles. 
To create non-OMF standby datafiles and/or tempfiles Dbvisit requires you to 
provide valid filesystem locations on the standby. 
Review and confirm standby locations to create standby non-OMF datafiles and 
tempfiles. 
------------------------------------------------------------------------------- 
Primary ===> Standby 
------------------------------------------------------------------------------- 
+DATA/dbvlx102 ===> /oracle/oradata/DBVLX102/datafile 
------------------------------------------------------------------------------- 
What would you like to do: 
1 - Confirm standby location(s) are correct and continue 
2 - Provide different standby location(s) 
Please enter your choice [1]: 
Standby locations validated.

Dbvisit Standby also prompted us to provide a valid filesystem location for ASM ALIAS redo logs:

+ARCHIVELOGS/dbvlx102/onlinelog/redo_1_2
+ARCHIVELOGS/dbvlx102/onlinelog/redo_1_1 

 

Primary database contains non OMF redo logs. 
To create non OMF redo logs Dbvisit requires you to specify valid filesystem 
locations on the standby. 
Review and confirm standby locations to create standby non OMF redo logs. 
------------------------------------------------------------------------------- 
Primary ===> Standby 
------------------------------------------------------------------------------- 
+ARCHIVELOGS/dbvlx102/onlinelog ===> /oracle/oradata/DBVLX102/onlinelog 
------------------------------------------------------------------------------- 
What would you like to do: 
1 - Confirm standby location(s) are correct and continue 
2 - Provide different standby location(s) 
Please enter your choice [1]: 
Standby locations validated.

The standby database has been created with the following structure:

 

SQL> select name from v$controlfile; 
-------------------------------------------------------------------------------- 
/oracle/oradata/DBVLX102/controlfile/o1_mf_7fbqwj7y_.ctl 
/oracle/oradata/DBVLX102/controlfile/o1_mf_7fbqwjpt_.ctl 
 
SQL> select name from v$datafile; 
-------------------------------------------------------------------------------- 
/oracle/oradata/DBVLX102/datafile/o1_mf_system_7fbqyyoz_.dbf 
/oracle/oradata/DBVLX102/datafile/o1_mf_undotbs1_7fbr06pm_.dbf 
/oracle/oradata/DBVLX102/datafile/o1_mf_sysaux_7fbr1g4z_.dbf 
/oracle/oradata/DBVLX102/datafile/users.dbf 
/oracle/oradata/DBVLX102/datafile/o1_mf_users_7fbr3l3c_.dbf 
 
SQL> select name from v$tempfile; 
-------------------------------------------------------------------------------- 
+DATA/dbvlx102/tempfile/temp1.307.762542407 
+DATA/dbvlx102/tempfile/temp1.259.762542407 
+DATA/dbvlx102/tempfile/temp1.258.762542409 
 
SQL> select member from v$logfile; 
-------------------------------------------------------------------------------- 
+ONLINELOGS/dbvlx102/onlinelog/group_2.264.768568567 
+ONLINELOGS/dbvlx102/onlinelog/group_2.261.768568567 
/oracle/oradata/DBVLX102/onlinelog/redo_1_2 
 /oracle/oradata/DBVLX102/onlinelog/redo_1_1

Note, two OMF control files have been created as we had set:

db_create_online_log_dest_1 = /oracle/oradata
db_create_online_log_dest_2 = /oracle/oradata

OMF datafiles have been created in the location specified by db_create_file_dest = /oracle/oradata. Non-OMF datafile has been created in the location /oracle/oradata/DBVLX102/datafile as we have specified.Tempfiles and redo logs do not get created for a standby database as they are not required, but the standby control file has a record of their names and locations.Two primary ASM ALIAS redo logs group 1 have been renamed to filesystem non-OMF files. However primary Oracle managed files (ASM FQSN) tempfiles and two redo logs group 2 have not been renamed in the standby controlfile.If the standby database is open read only, or activated to become a primary database, Oracle detects that tempfiles are OMF and will create them as OMF files in a location specified by db_create_file_dest:

 

dbv_oraStartStop open dbvlx102 
============================================================= 
Dbvisit Standby Database Technology (pid 4828) 
dbv_oraStartStop started on dbvisit32 
=============================================================  
Open Standby Database dbvlx102 in READ ONLY mode... 
Standby Database dbvlx102 opened in READ ONLY mode. 
Log files cannot be applied to Database while in READ ONLY mode. 
Database tempfile(s) may need to be added to this database. 
============================================================= 
dbv_oraStartStop ended on dbvisit32 
=============================================================  
sqlplus / as sysdba 
SQL> select name from v$tempfile; 
NAME 
-------------------------------------------------------------------------------- 
/oracle/oradata/DBVLX102/datafile/o1_mf_temp1_7fc358km_.tmp 
/oracle/oradata/DBVLX102/datafile/o1_mf_temp1_7fc358h6_.tmp 
/oracle/oradata/DBVLX102/datafile/o1_mf_temp1_7fc358fk_.tmp

Redo logs are created if the standby database gets activated to become primary. In this case Oracle again will detect that redo logs must be created as OMF files and will create them according the settings of db_create_onli_log_dest_n:

 

dbv_oraStartStop activate dbvlx102 
============================================================= 
Dbvisit Standby Database Technology (pid 4900) 
dbv_oraStartStop started on dbvisit32 
============================================================= 
Activating means this database will become a Primary Database. 
It will no longer be a Standby Database for dbvlx102 on dbvisit31. 
Activation cannot be reversed. 
=>Activate Standby Database on dbvisit32? <Yes/No> [No]: y 
Are you sure? <Yes/No> [No]: y 
Activating now... 
Activate Standby Database dbvlx102... 
Standby Database dbvlx102 activated. 
Shutting down standby Database dbvlx102... 
Standby Database dbvlx102 shutdown successfully. 
Starting Activated Standby Database dbvlx102... 
Activated Standby Database dbvlx102 started . 
File dbv_dbvlx102.env copied to dbv_dbvlx102.env.201111301501. 
Dbvisit Database configuration (DDC) file dbv_dbvlx102.env has been
updated and variables have been reversed between primary and standby server. 
 
SOURCE=dbvisit32 DESTINATION=dbvisit31. 
 
Activation complete. Please ensure a backup of this Database is made. 
Old archives from before the activation should be removed to avoid mix-up between new and old archive logs 
 
If the Dbvisit Standby process is to be reversed, then 
Database on dbvisit31 will need to be rebuilt as a Standby Database. 
============================================================= 
dbv_oraStartStop ended on dbvisit32 
============================================================= 
 
sqlplus / as sysdba 
SQL> select member from v$logfile; 
MEMBER 
-------------------------------------------------------------------------------- 
/oracle/oradata/DBVLX102/onlinelog/o1_mf_2_7fc3q3bh_.log 
/oracle/oradata/DBVLX102/onlinelog/o1_mf_2_7fc3q68s_.log 
/oracle/oradata/DBVLX102/onlinelog/redo_1_2 
/oracle/oradata/DBVLX102/onlinelog/redo_1_1 
 
SQL> select name from v$tempfile; 
NAME 
-------------------------------------------------------------------------------- 
/oracle/oradata/DBVLX102/datafile/o1_mf_temp1_7fc358km_.tmp 
/oracle/oradata/DBVLX102/datafile/o1_mf_temp1_7fc358h6_.tmp 
/oracle/oradata/DBVLX102/datafile/o1_mf_temp1_7fc358fk_.tmp

We chose to create a template when we created the standby database, to be able to repeat the creation process if required. Here is the template summary:

 

CREATE STANDBY DATABASE TEMPLATE SUMMARY 
------------------------------------------------------------------------------- 
A - GENERAL SETTINGS 
------------------------------------------------------------------------------- 
TEMPLATE ID 1 
DDC FILE dbvlx102 
SOURCE HOST dbvisit31 
DESTINATION HOST dbvisit32 
STANDBY ASM N 
COPY DATAFILES DIRECTLY N 
COMPRESS DATAFILES Y 
USE TRANSPORTABLE MEDIA N 
PRIMARY TEMP LOCATION /usr/tmp/tmp1 
STANDBY TEMP LOCATION /usr/tmp/tmp2 
STOP POINT SET N 
CREATE DIRECTORIES AUTO Y 
CREATED ON 30-11-2011 10-14-56 
----------------------------------------------------------------------- 
B - INIT ORA PARAMETERS 
----------------------------------------------------------------------- 
audit_file_dest /oracle/orabase/admin/dbvlx102/adump 
background_dump_dest /oracle/orabase/admin/dbvlx102/bdump 
compatible 10.2.0.1.0 
core_dump_dest /oracle/orabase/admin/dbvlx102/cdump 
db_block_size 8192 
db_create_file_dest /oracle/oradata 
db_create_online_log_dest_1 /oracle/oradata 
db_create_online_log_dest_2 /oracle/oradata 
db_file_multiblock_read_count 16 
db_name dbvlx102 
db_recovery_file_dest /oracle/flash_recovery_area 
db_recovery_file_dest_size 2147483648 
dispatchers (PROTOCOL=TCP) (SERVICE=dbvlx102XDB) 
job_queue_processes 10 
log_archive_dest_1 LOCATION=/oracle/oraarch/dbvlx102 
log_archive_format %t_%s_%r.dbf 
log_archive_start TRUE 
log_file_name_convert +ARCHIVELOGS/dbvlx102/onlinelog, /oracle/oradata/DBVLX102/onlinelog 
open_cursors 300 
pga_aggregate_target 16777216 
processes 150 
remote_login_passwordfile EXCLUSIVE 
sga_target 167772160 
spfile default 
standby_file_management MANUAL 
undo_management AUTO 
undo_tablespace UNDOTBS1 
user_dump_dest /oracle/orabase/admin/dbvlx102/udump 
------------------------------------------------------------------------------- 
C - DATABASE FILE LOCATIONS 
------------------------------------------------------------------------------- 
TYPE ID STANDBY FILE NAME 
DATA 4 /oracle/oradata/DBVLX102/datafile/users.dbf

Dbvisit Standby has added the following parameter to the standby spfile:

log_file_name_convert = +ARCHIVELOGS/dbvlx102/onlinelog, /oracle/oradata/DBVLX102/onlinelog

This parameter was used to rename primary ASM ALIAS redo logs. The section "C – Database file locations" contains a record for datafile 4 with a standby name /oracle/oradata/DBVLX102/datafile/users.dbf. This information is used to generate "set newname" RMAN command to rename datafile 4 when restoring the standby controlfile.Â