how to add new datafile in prod db and show changes in standby db

[dbstudent]

This is a simulation situation. I have two databases production and standby. My standby database is not set up for managed recovery. I added a new datafile to my users tablespace in the production database. I took a backup of the tablespace. I made a new entry of the datafile in the standby pfile. Lastly i executed afew ALTER SYSTEM ARCHIVE LOG CURRENT; commands and backed the archived logs to the standby db. When i try to recover the standby database am getting the error below. I hope this makes sense. Basically am trying to add a new datafile and show this change in the standby database.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 286858140 bytes
Fixed Size 453532 bytes
Variable Size 184549376 bytes
Database Buffers 100663296 bytes
Redo Buffers 1191936 bytes
SQL> alter database mount standby database;

Database altered.

SQL> recover standby database;
ORA-00279: change 1525644 generated at 03/03/2006 12:19:52 needed for thread 1
ORA-00289: suggestion : C:\U01\APP\ORACLE\ADMIN\OKCDBST2\ARCHARCH_1_5.ARC
ORA-00280: change 1525644 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1531663 generated at 03/03/2006 17:24:53 needed for thread 1
ORA-00289: suggestion : C:\U01\APP\ORACLE\ADMIN\OKCDBST2\ARCHARCH_1_6.ARC
ORA-00280: change 1531663 for thread 1 is in sequence #6
ORA-00278: log file 'C:\U01\APP\ORACLE\ADMIN\OKCDBST2\ARCHARCH_1_5.ARC' no
longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00283: recovery session canceled due to errors
ORA-01670: new datafile 8 needed for standby database recovery
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: 'C:\U01\ORADATA\OKCDBST1\USERS02.DBF'


ORA-01112: media recovery not started

 

[hoinz]

Hello,

adding datafiles in a standby scenario is a bit tricky; I never tried adding an entry in pfile for this; and I don't know if there are new features of Oracle that will make life easier.
But here is a way that worked for me in the past, on Unix:

Just create the new file at the standby side as well, using operating system tools, with correct path and name. File size does not matter, it may well be 0 bytes. But take care of ownership and permissions.
An alternative solution is copying the new file from primary to standy. I think it has to be in backup mode for this.
Then start a 'recover standby database'.
This should still work if you previously got the error you described.

hope this helps

 

[Scunningham99]

Hi there are two ways you can add a datafile to a standby.

This depends on parameter setting standby_file_management = {MANUAL | AUTO}

If set to auto the following applies:-
This works simply add it to the standby database init.ora file, so the datafiles are created and deleted automatically on the standby the option to CREATE and DELETE files automatically on the standby using the following (only in 9i, 9.2 ONWARDS) :-

STANDBY_FILE_MANAGEMENT
Parameter type String
Syntax STANDBY_FILE_MANAGEMENT = {MANUAL | AUTO}
Default value MANUAL
Parameter class Dynamic: ALTER SYSTEM
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.

THIS WORKS PERFECTLY WHENEVER YOU CREATE OR DROP A TABLESPACE OR DATAFILE ON PROD IT IS DONE AUTOMATICALLY ON THE STANDBY!! We can see this in the alertlog on the standby….ie it says creating datafile….
Note However it does not remove the datafiles from the O/S like it does on the Primary when we issue drop tablespace <tablespace> including contents and datafiles, we have to remove them manually!


IF SET TO MANUAL the following can can be issued:-

How to add a tablespace or datafile to the primary database and create the datafile in the standby database:

1.Create a tablespace on the primary database as usual. For example, to create new quest.dbf tablespace issue:

SQL> CREATE TABLESPACE quest DATAFILE '<path to datafie>/quest,dbf' SIZE 2M;

If the standby database is currently in managed recovery mode, skip to step 4.

3.Mount the standby database, then place it in managed recovery mode:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE;

4.Switch redo logs on the primary database to initiate redo archival to the standby database (as this is where the DDL is for the datafile etc):

SQL> ALTER SYSTEM SWITCH LOGFILE;

If the recovery process on the standby database tries to apply the redo containing the CREATE TABLESPACE statement, it stops because the new datafile does not exist on the standby site.

5.Either wait for the standby database to cancel recovery because it cannot find the new datafile, or manually cancel managed recovery: You can check the alert log for this.

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
Note that CREATE TABLESPACE redo adds the new filename to the standby control file. The following alert.log entry is generated: -

----------------------------------------------------------------------------
WARNING! Recovering datafile 2 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command.

Successfully added datafile 2 to media recovery
Datafile #2: '<path to datafile>quest.dbf'

----------------------------------------------------------------------------
6.Create the datafile on the standby database. For example, issue: (as the DML is there just No datafile for it to go in so):-

---issue the command below when in mounted state (ie alter database mount standby database)
SQL> ALTER DATABASE CREATE DATAFILE '<path to datafile>/quest.dbf'
AS '<path to datafile>/dbf'; eg:-
SQL> alter database create datafile '/u02/oradata/ALIVE_SB/QUEST.ora' as '/u02/o
radata/ALIVE_SB/QUEST.ora';

7.Place the standby database in managed recovery mode:

SQL> RECOVER MANAGED STANDBY DATABASE;
I prefer to use this one:-
SQL> RECOVER MANAGED STANDBY DATABASEDISCONNECT FROM SESSION;

Continue normal processing on the primary database. The primary and standby databases are now synchronized. (check the alert log, should look like the following):-
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE D
Thu Mar 4 11:12:33 2004
Media Recovery Log /u02/archive/ALIVE_SB/SWPROD_39088.arc
Media Recovery Log /u02/archive/ALIVE_SB/SWPROD_39089.arc
Media Recovery Log /u02/archive/ALIVE_SB/SWPROD_39090.arc
Thu Mar 4 11:13:00 2004
Media Recovery Waiting for thread 1 seq# 39091 ? notice it is waiting for next log!


HTH











Sy UK