Example Renaming Standby Database Files
- Anton Els (Deactivated)
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.