Standby Database
How does it work? In a nutshell redolog data is shipped from the source data to the target database through Oracle Net services.
SOURCE log_archive_dest_1=’localpath/’ INSTANCE NAME:MHGTST
TARGET log_archive_dest_1=’localpath/’ INSTANCE NAME:STDTST
Creating a Physical Standby 1. Verify that the database is in force logging mode.
SQL>SELECT FORCE_LOGGING FROM v$database;
_________ NO
2. If it is not in force logging mode set the database in force logging mode.
SQL>ALTER DATABASE FORCE_LOGGING;
Also ensure that the database in archivelog mode, no archive log mode no STANDBY
3. Check whether log_archive_dest_2 is already set. If it is set then use log_archive_dest_3
SQL> ALTER SYSTEM SET log_archive_dest_3=’SERVICE=stdtst’;
System altered.
4. Shutdown the source database
SQL> shutdown immediate;
Database closed. Database dismounted. ORACLE instance shut down.
5. cp the datafiles to the new location
cd /home/oracle/oracle/oradata/mhgtst /home/oracle/oracle/oradata/stdtst copy the admin tree ( bdump, cdump, pfile etc. ) to the new location cp -rp /home/oracle/oracle/product/10.2.0/db_1/admin/mhgtst \ /home/oracle/oracle/product/10.2.0/db_1/admin/stdtst
6. SQL> startup mount;
ORACLE instance started.
7. create the standby control file
SQL> alter database create standby controlfile as ‘/tmp/stdtst.ctl’;
Database altered.
8. open the source db for other users SQL> alter database open;
Database altered.
9.CREATE the pfile from spfile and make changes so that all references to mhgtst now point to stdtst. db_unique_name should still be the same in both the databases.
Make the following changes to the init.ora file of the STANDBY dataabase:- *.user_dump_dest=’/home/oracle/oracle/product/10.2.0/db_1/admin/stdtst/udump’ *.db_file_name_convert=’mhgtst’,’stdtst’ *.log_file_name_convert=’mhgtst’,’stdtst’; *.db_name=’stdtst’; *.db_unique_name=stdtst
10. Create password file for the standby database. orapwd file=stdtst entries=2 password=oracle
11. Make final changes Remove the log_archive_dest_3 from the standby init.ora file. Create the appropriate entires for log_archive_dest_1 and log_archive_dest_2
mkdir -p /home/oracle/oracle/logarch/stdtst/dest_1 mkdir -p /home/oracle/oracle/logarch/stdtst/dest_2
12. Mount the database
export ORACLE_SID=stdtst SQL>startup nomount;
Oracle Instance started
13. Try to mount the database:- copy the control file from the source database
[oracle@racdb1 stdtst]$ cp /tmp/stdtst.ctl ./control01.ctl [oracle@racdb1 stdtst]$ cp /tmp/stdtst.ctl ./control02.ctl [oracle@racdb1 stdtst]$ cp /tmp/stdtst.ctl ./control03.ctl
SQL> alter database mount standby database;
alter database mount standby database * ERROR at line 1: ORA-01103: database name ‘MHGTST’ in control file is not ‘;’
In previous versions of oracle database we had to set LOCK_NAME_SPACE parameter, this is a obsolete / deprecated parameter in 10g.
VERY IMPORTANT
Your db_name in the standby instance should be pointing to the source db name. db_name=mhgtst This is the source db name.
SQL> alter database mount standby database 2 /
Database altered.
14. Recover the stand by database SQL> recover standby database; ORA-00279: change 1174794 generated at 08/04/2006 08:48:11 needed for thread 1 ORA-00289: suggestion : /home/oracle/oracle/logarch/stdtst/dest_2/1_8_597228601.dbf ORA-00280: change 1174794 for thread 1 is in sequence #8
Specify log: {suggested | filename | AUTO | CANCEL}
If you want the process to do recovery in background issue the command below:-
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
15. Validation Switch the logfile on the source so that the log file is applied to the target database. mhgtst -> stdtst
The alert.log should have the following entries in it:- Media Recovery Log /home/oracle/oracle/logarch/stdtst/dest_1/1_51_597228601.dbf Media Recovery Log /home/oracle/oracle/logarch/stdtst/dest_1/1_52_597228601.dbf Media Recovery Waiting for thread 1 sequence 53 Fri Aug 4 12:33:53 2006 RFS[1]: No standby redo logfiles created RFS[1]: Archived Log: ‘/home/oracle/oracle/logarch/stdtst/dest_1/1_53_597228601.dbf’ Fri Aug 4 12:33:58 2006 Media Recovery Log /home/oracle/oracle/logarch/stdtst/dest_1/1_53_597228601.dbf Media Recovery Waiting for thread 1 sequence 54 Fri Aug 4 12:34:50 2006 RFS[1]: No standby redo logfiles created RFS[1]: Archived Log: ‘/home/oracle/oracle/logarch/stdtst/dest_1/1_54_597228601.dbf’ Fri Aug 4 12:34:53 2006
16. Canceling recover managed database
SQL>ALTER DATABASE STANDBY DATABASE CANCEL;
Database Altered
17.ALTER DATABASE OPEN;
DB is now open for normal operations
18. Use queries till the application is up and running. In the mean time remember there are a few log switches in the source database. TARGET SHUTDOWN IMMEDIATE and put the physical database in recover mode.
STARTUP MOUNT; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION /
ERRORS ORA-01678: parameter log_file_name_convert must be pairs of pattern and replacement strings There was a semicolon at the end of the log_file_name_convert, remnove that and the database was able to start in nomount mode. Please check your setting for log file name convert parameter.
Media Recovery Start Managed Standby Recovery not using Real Time Apply ORA-279 signalled during: ALTER DATABASE RECOVER standby database …
SQL> alter database recover managed standby database disconnect from SQL> session;
ORA-12154: TNS:could not resolve the connect identifier specified PING[ARC0]: Heartbeat failed to connect to standby ‘stdtst’. Error is 12154.
This error is because we have not yet configured the name stdtst service.
Configure the listener SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = mhgtst) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = stdtst) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) )
Reload the listener Listening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))) Services Summary… Service “PLSExtProc” has 1 instance(s). Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service… Service “mhgtst” has 1 instance(s). Instance “mhgtst”, status UNKNOWN, has 1 handler(s) for this service… Service “stdtst” has 1 instance(s). Instance “stdtst”, status UNKNOWN, has 1 handler(s) for this service…
Configure tnsnames STDTST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STDTST) ) )
Verify tnsping Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STDTST))) OK (20 msec)
Switch the log file and see if it is been sent to the service.
ORA-28547: connection to server failed, probable Oracle Net admin error PING[ARC0]: Heartbeat failed to connect to standby ‘stdtst’. Error is 28547.
Usually this error means that there is some incompatibility in sqlnet.ora , tnsnames.ora and listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = mhgtst ) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) ) (SID_DESC = (SID_NAME = stdtst ) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) ) )
SQL> alter database mount standby database; alter database mount standby database * ERROR at line 1: ORA-00205: error in identifying control file, check alert log for more info This error is because we didn’t put the control file from the source whe we issued a standby control file command.
SOURCE log_archive_dest_1=’localpath/’ INSTANCE NAME:MHGTST
TARGET log_archive_dest_1=’localpath/’ INSTANCE NAME:STDTST
Creating a Physical Standby 1. Verify that the database is in force logging mode.
SQL>SELECT FORCE_LOGGING FROM v$database;
_________ NO
2. If it is not in force logging mode set the database in force logging mode.
SQL>ALTER DATABASE FORCE_LOGGING;
Also ensure that the database in archivelog mode, no archive log mode no STANDBY
3. Check whether log_archive_dest_2 is already set. If it is set then use log_archive_dest_3
SQL> ALTER SYSTEM SET log_archive_dest_3=’SERVICE=stdtst’;
System altered.
4. Shutdown the source database
SQL> shutdown immediate;
Database closed. Database dismounted. ORACLE instance shut down.
5. cp the datafiles to the new location
cd /home/oracle/oracle/oradata/mhgtst /home/oracle/oracle/oradata/stdtst copy the admin tree ( bdump, cdump, pfile etc. ) to the new location cp -rp /home/oracle/oracle/product/10.2.0/db_1/admin/mhgtst \ /home/oracle/oracle/product/10.2.0/db_1/admin/stdtst
6. SQL> startup mount;
ORACLE instance started.
7. create the standby control file
SQL> alter database create standby controlfile as ‘/tmp/stdtst.ctl’;
Database altered.
8. open the source db for other users SQL> alter database open;
Database altered.
9.CREATE the pfile from spfile and make changes so that all references to mhgtst now point to stdtst. db_unique_name should still be the same in both the databases.
Make the following changes to the init.ora file of the STANDBY dataabase:- *.user_dump_dest=’/home/oracle/oracle/product/10.2.0/db_1/admin/stdtst/udump’ *.db_file_name_convert=’mhgtst’,’stdtst’ *.log_file_name_convert=’mhgtst’,’stdtst’; *.db_name=’stdtst’; *.db_unique_name=stdtst
10. Create password file for the standby database. orapwd file=stdtst entries=2 password=oracle
11. Make final changes Remove the log_archive_dest_3 from the standby init.ora file. Create the appropriate entires for log_archive_dest_1 and log_archive_dest_2
mkdir -p /home/oracle/oracle/logarch/stdtst/dest_1 mkdir -p /home/oracle/oracle/logarch/stdtst/dest_2
12. Mount the database
export ORACLE_SID=stdtst SQL>startup nomount;
Oracle Instance started
13. Try to mount the database:- copy the control file from the source database
[oracle@racdb1 stdtst]$ cp /tmp/stdtst.ctl ./control01.ctl [oracle@racdb1 stdtst]$ cp /tmp/stdtst.ctl ./control02.ctl [oracle@racdb1 stdtst]$ cp /tmp/stdtst.ctl ./control03.ctl
SQL> alter database mount standby database;
alter database mount standby database * ERROR at line 1: ORA-01103: database name ‘MHGTST’ in control file is not ‘;’
In previous versions of oracle database we had to set LOCK_NAME_SPACE parameter, this is a obsolete / deprecated parameter in 10g.
VERY IMPORTANT
Your db_name in the standby instance should be pointing to the source db name. db_name=mhgtst This is the source db name.
SQL> alter database mount standby database 2 /
Database altered.
14. Recover the stand by database SQL> recover standby database; ORA-00279: change 1174794 generated at 08/04/2006 08:48:11 needed for thread 1 ORA-00289: suggestion : /home/oracle/oracle/logarch/stdtst/dest_2/1_8_597228601.dbf ORA-00280: change 1174794 for thread 1 is in sequence #8
Specify log: {suggested | filename | AUTO | CANCEL}
If you want the process to do recovery in background issue the command below:-
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
15. Validation Switch the logfile on the source so that the log file is applied to the target database. mhgtst -> stdtst
The alert.log should have the following entries in it:- Media Recovery Log /home/oracle/oracle/logarch/stdtst/dest_1/1_51_597228601.dbf Media Recovery Log /home/oracle/oracle/logarch/stdtst/dest_1/1_52_597228601.dbf Media Recovery Waiting for thread 1 sequence 53 Fri Aug 4 12:33:53 2006 RFS[1]: No standby redo logfiles created RFS[1]: Archived Log: ‘/home/oracle/oracle/logarch/stdtst/dest_1/1_53_597228601.dbf’ Fri Aug 4 12:33:58 2006 Media Recovery Log /home/oracle/oracle/logarch/stdtst/dest_1/1_53_597228601.dbf Media Recovery Waiting for thread 1 sequence 54 Fri Aug 4 12:34:50 2006 RFS[1]: No standby redo logfiles created RFS[1]: Archived Log: ‘/home/oracle/oracle/logarch/stdtst/dest_1/1_54_597228601.dbf’ Fri Aug 4 12:34:53 2006
16. Canceling recover managed database
SQL>ALTER DATABASE STANDBY DATABASE CANCEL;
Database Altered
17.ALTER DATABASE OPEN;
DB is now open for normal operations
18. Use queries till the application is up and running. In the mean time remember there are a few log switches in the source database. TARGET SHUTDOWN IMMEDIATE and put the physical database in recover mode.
STARTUP MOUNT; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION /
ERRORS ORA-01678: parameter log_file_name_convert must be pairs of pattern and replacement strings There was a semicolon at the end of the log_file_name_convert, remnove that and the database was able to start in nomount mode. Please check your setting for log file name convert parameter.
Media Recovery Start Managed Standby Recovery not using Real Time Apply ORA-279 signalled during: ALTER DATABASE RECOVER standby database …
SQL> alter database recover managed standby database disconnect from SQL> session;
ORA-12154: TNS:could not resolve the connect identifier specified PING[ARC0]: Heartbeat failed to connect to standby ‘stdtst’. Error is 12154.
This error is because we have not yet configured the name stdtst service.
Configure the listener SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = mhgtst) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = stdtst) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) )
Reload the listener Listening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))) Services Summary… Service “PLSExtProc” has 1 instance(s). Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service… Service “mhgtst” has 1 instance(s). Instance “mhgtst”, status UNKNOWN, has 1 handler(s) for this service… Service “stdtst” has 1 instance(s). Instance “stdtst”, status UNKNOWN, has 1 handler(s) for this service…
Configure tnsnames STDTST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STDTST) ) )
Verify tnsping Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STDTST))) OK (20 msec)
Switch the log file and see if it is been sent to the service.
ORA-28547: connection to server failed, probable Oracle Net admin error PING[ARC0]: Heartbeat failed to connect to standby ‘stdtst’. Error is 28547.
Usually this error means that there is some incompatibility in sqlnet.ora , tnsnames.ora and listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = mhgtst ) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) ) (SID_DESC = (SID_NAME = stdtst ) (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1) ) )
SQL> alter database mount standby database; alter database mount standby database * ERROR at line 1: ORA-00205: error in identifying control file, check alert log for more info This error is because we didn’t put the control file from the source whe we issued a standby control file command.
No comments:
Post a Comment