Oracle Server - Enterprise Edition - Version: 8.1.5.0 to 10.2.0.1 Information in this document applies to any platform.
Goal
To explain how to recover from ORA-1274 encountered on physical standby database after adding a datafile to the primary database.
Solution
Oracle recommends running your physical standby database with the following parameters set:
STANDBY_FILE_MANAGEMENT=AUTO
and
DB_FILE_NAME_CONVERT ... set to an appropriate string value that will help to name datafiles automatically added on the standby database when the create datafile statement is run there.
If these parameters are not set, or accidentally become unset, and a datafile is added to the primary database, it is possible that you will encounter ORA-1274 : cannot add datafile '%s' - file could not be created
The datafile will not be generated on the standby file system but an entry will be added to your standby controlfile that refers to this datafile as an 'UNNAMEDxxx' file.
For example:
File #31 added to control file as 'UNNAMED00031'.
In our example case, standby_file_management was set to AUTO but db_file_name_convert was not set. Therefore while performing managed recovery on the standby database we encounter a create datafile statement and we attempt to create this datafile using the same path as was specified on the primary. Since we are unable to create this file in the designated path, we place an entry into the controlfile indicating that this datafile *should* be here, but is not.
***** To correct this problem we need to create a blank datafile on the file system that we can recover using the archives generated since this datafile was added on the primary database. We also need to update the controlfile to rename this file from 'UNNAMEDxxx' to its appropriate path/filename.
The Step By Step Procedure for Correcting this problem is as follows: ===============================================
1. alter system set standby_file_management=manual scope=memory; 2. alter database create datafile '' as '/oradata/standby/data/data1' ; 3. alter system set standby_file_management=auto scope=both; 4. alter database recover managed standby database disconnect from session; 5. be sure to set your db_file_name_convert to the appropriate string pair as documented in Oracle Reference guide to avoid encountering this problem on your physical standby database in the future.
Please note, this will only work if ALL of the archives generated since this datafile was created are still available.
References
NOTE:29430.1 - How to Recover a Database Having Added a Datafile Since Last Backup
Oracle Server - Enterprise Edition - Version: 11.1.0.6 Information in this document applies to any platform.
Goal
Step by step guide on how to create a physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command with the active database files.
Solution
Steps to be followed are
1. Make the necessary changes to the primary database .
a. Enabling force logging. b. Creating the password file if one does not exist. c. Create standby redologs. d. Change the appropriate changes to the parameter files.
2. Ensure that the sql*net connectivity is working fine.
3. Create the standby database over the network using the active database files.
a.Create the password file b.Create the initialization parameter file for the standby database ( auxiliary database) c.Create the necessary mount points or the folders for the database files d.Run the standby creation command from the primary database.
DUPLICATE TARGET DATABASE TO FOR STANDBY FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT '', '' SET DB_FILE_NAME_CONVERT '', '' SET LOG_FILE_NAME_CONVERT '', '' SET SGA_MAX_SIZE 200M SET SGA_TARGET 125M;
4. Test the physical standby database.
When we are creating the standby database we use the active database files i.e., this command will be use full in creating the physical standby database using active database files over the network.
Example to perform the above steps :
In our example Primary database : core Physical standby database : core1
1. Prepare the production database to be the primary database
Perform the below mentioned steps in the primary database.
a. Ensure that the database is in archivelog mode .
e. On the primary system invoke the RMAN executable and connect to the primary and the auxiliary database ( i.e., the standby)
$rman target sys/sys@core auxiliary sys/sys@core1
f. Run the standby creation command
run { allocate channel prmy1 type disk; allocate channel prmy2 type disk; allocate channel prmy3 type disk; allocate channel prmy4 type disk; allocate auxiliary channel stby type disk; duplicate target database for standby from active database spfile parameter_value_convert 'core','core1' set db_unique_name='core1' set db_file_name_convert='/core/','/core1/' set log_file_name_convert='/core/','/core1/' set control_files='/u01/app/oracle/oradata/control01.ctl' set log_archive_max_processes='5' set fal_client='core1' set fal_server='core' set standby_file_management='AUTO' set log_archive_config='dg_config=(core,core1)' set log_archive_dest_1='service=core ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=core' ; }
The below mentioned would be actual processing
connected to target database: CORE (DBID=761464750) connected to auxiliary database: CORE1 (not mounted)
RMAN> run { allocate channel prmy1 type disk; allocate channel prmy2 type disk; allocate channel prmy3 type disk; allocate channel prmy4 type disk; allocate auxiliary channel stby type disk; duplicate target database for standby from active database spfile parameter_value_convert 'core','core1' set db_unique_name='core1' set db_file_name_convert='/core/','/core1/' set log_file_name_convert='/core/','/core1/' set control_files='/u01/app/oracle/oradata/control01.ctl' set log_archive_max_processes='5' set fal_client='core1' set fal_server='core' set standby_file_management='AUTO' set log_archive_config='dg_config=(core,core1)' set log_archive_dest_1='service=core ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=core' ; }2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21>
using target database control file instead of recovery catalog allocated channel: prmy1 channel prmy1: SID=147 device type=DISK
contents of Memory Script: { backup as copy reuse file '/u02/app/oracle/product/11.1.0/db_1/dbs/orapwcore' auxiliary format '/u02/app/oracle/product/11.1.0/db_1/dbs/orapwcore1' file '/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore.ora' auxiliary format '/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora' ; sql clone "alter system set spfile= ''/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora''"; } executing Memory Script
Starting backup at 19-MAY-08 Finished backup at 19-MAY-08
sql statement: alter system set spfile= ''/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora''
contents of Memory Script: { sql clone "alter system set audit_file_dest = ''/u02/app/oracle/admin/core1/adump'' comment= '''' scope=spfile"; sql clone "alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=core1XDB)'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_dest_2 = ''service=core11 arch async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=core1'' comment= '''' scope=spfile"; sql clone "alter system set db_unique_name = ''core1'' comment= '''' scope=spfile"; sql clone "alter system set db_file_name_convert = ''/core/'', ''/core1/'' comment= '''' scope=spfile"; sql clone "alter system set log_file_name_convert = ''/core/'', ''/core1/'' comment= '''' scope=spfile"; sql clone "alter system set control_files = ''/u01/app/oracle/oradata/control01.ctl'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_max_processes = 5 comment= '''' scope=spfile"; sql clone "alter system set fal_client = ''core1'' comment= '''' scope=spfile"; sql clone "alter system set fal_server = ''core'' comment= '''' scope=spfile"; sql clone "alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_config = ''dg_config=(core,core1)'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_dest_1 = ''service=core ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=core'' comment= '''' scope=spfile"; shutdown clone immediate; startup clone nomount ; } executing Memory Script
sql statement: alter system set audit_file_dest = ''/u02/app/oracle/admin/core1/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=core1XDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=core11 arch async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=core1'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''core1'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/core/'', ''/core1/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/core/'', ''/core1/'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''core1'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''core'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(core,core1)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''service=core ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=core'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started) Oracle instance started
contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/control01.ctl'; sql clone 'alter database mount standby database'; } executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script: { set newname for tempfile 1 to "/u02/app/oracle/oradata/core1/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u02/app/oracle/oradata/core1/system01.dbf"; set newname for datafile 2 to "/u02/app/oracle/oradata/core1/sysaux01.dbf"; set newname for datafile 3 to "/u02/app/oracle/oradata/core1/undotbs01.dbf"; set newname for datafile 4 to "/u02/app/oracle/oradata/core1/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u02/app/oracle/oradata/core1/system01.dbf" datafile 2 auxiliary format "/u02/app/oracle/oradata/core1/sysaux01.dbf" datafile 3 auxiliary format "/u02/app/oracle/oradata/core1/undotbs01.dbf" datafile 4 auxiliary format "/u02/app/oracle/oradata/core1/users01.dbf" ; sql 'alter system archive log current'; } executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u02/app/oracle/oradata/core1/temp01.dbf in control file
Related
Back to top
