Hello,
Setting Up Physical Standby Database
Setting up a Physical Standby database is easy when all prerequisites and setup steps are met. It is easy to build a Data guard Physical Standby Database using Oracle 10g Grid Control Console.
Note:
1. Identical Hardware, OS on both Primary and Secondary
2. Oracle Database 10g Enterprise Edition on Primary and Secondary
3. SSH is configured for user oracle on both nodes
4. Oracle Home is on identical path on both nodes
Implementation Notes:
Follow these steps when once you have your primary database up and running
Primary Database
1. Enable Force Logging
2. Create a Password File
3. Configure a Standby Redo Log
4. Enable Archiving
5. Set Primary Database Initialization Parameters
Standby Database
1. Create a control file for the standby database
2. Backup the Primary Database and transfer a copy to the standby node
3. Prepare an initialization Parameter file for the Standby Database
4. Configure the listener and tnsnames to support the database on both nodes
5. Set Up the Environment to support the standby database on the standby node
6. Start the physical standby database
7. Verify the physical standby database is performing properly
Step by Step implementation of a Physical Standby environment
Primary Database Steps:
1. Check the name of the database by querying the database
SQL>desc v$database
BBMLPROD
SQL>select name from v$database;
2. Check the data file location on primary side
SQL>desc v$dba_data_files
SQL>select filename from v$dba_data_files
3. Check the online log file location on the primary side
SQL>desc v$log, v$logfile
SQL>select group3, member from v$logfile;
SQL>select * from v$log;
4. SQL>show parameter unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string BBMLPROD
5. Enable force Logging
In order to implement standby database enable ‘forced Logging’. This ensures that even in the event that a ‘no logging’ operation is done, force logging takes precedence and all operations are logged into the redo logs.
SQL>alter database force logging;
Database altered
6. Create a Password File
In order to be able to ship and apply archive log files from primary to standby database a password file must be created on the primary and copied the same to the secondary.
Note: Sys password must be identical on both sites.
cd $ORACLE_HOME/dbs
[/opt/oracle]>orapwd file=orapwbbmlprod password=oracle force=y
7. Configure standby redo log is added to enable Data guard Maximum availability and Maximum Protection modes. Configure standby redo logs (SRL) with same size as online redo logs.
SQL> select * from v$logfile;
I have noticed there are 3 groups
SQL> select bytes from v$log;
Bytes
--------
52443300
SQL> alter database add standby logfile group 4 size 50M
Database altered
SQL>alter database add standby logfile group 5 size 50M
Database altered
SQL>alter database add standby logfile group 5 size 50M;
Database altered
Now make sure that u have three more log groups
Use this query
SQL> select * from v$logfile;
6 rows selected
8. Enable Archive log mode
Check whether database is in archive log if not alter the database to archive log mode.
SQL>archive log list
Change the database to archive log mode
SQL> startup mount;
SQL> alter database archive log;
SQL>alter database open
SQL>archive log list
9. Set primary Database init.ora Parameter
Initialization Parameter controls the behavior of a Data Guard environment. Data guard must use spfile, in order to configure this first we create and configure standby parameters on a regular pfile and later we convert this to spfile
SQL> create pfile=’/oradisk/app01/oracle/product/10gDB/dbs/pfileBBMLPROD.ora’ from spfile;
Edit the pfile to add the standby parameters, few parameters are listed which needs to be modified.
Db_unique_name=’BBMLPROD’
Log_Archive_config=’DG_CONFIG=BBMLPROD, BBMLDR’
Log_Archive_Dest_1=’Location=/../../archdest/
Valid_for=(All_logfiles, All_roles)
Db_unique_Name=’BBMLPROD’
Log_Archive_Dest_2=
‘Service=’BBMLDR LGWR ASYNC
Valid_for=(online_logfiles, primary_role)
Db_unique_name=BBMLDR’
Log_archive_dest_state_1=Enable
Log_Archive_Format=%t_%s_%r.arc
Log_Archive_Max_Processes=30
#standby role Parameters
Fal_server=BBMLDR
Fal_client=BBMLPROD
Standby_file_management=auto
Db_file_name_convert=’BBMLDR/BBMLDR’,’ BBMLPROD/BBMLPROD’
Log_file_name_convert=’/../../../’,’/…/../../’
Once the parameter file is ready create spfile from this new pfile
SQL> shutdown immediate;
SQL>startup nomount pfile=’/oradisk/app01/oracle/product/10gDB/dbs/pfileBBMLPROD.ora’;
SQL> create spfile from pfile=’/oradisk/app01/oracle/product/10gDB/dbs/pfileBBMLPROD.ora’;
SQL>shutdown immediate;
SQL>startup;
Standby Database Steps
1. Standby database will use the control file that is generated on the primary database
SQL>startup mount;
SQL>alter database create standby controlfile as ‘/oradisk/app01/oracle/product/10gDB/dbs/BBMLDR.ctl’;
SQL> Alter Database Open;
2. Backup the Primary Database and transfer a copy to the standby node.
Note: Backup the primary database is nothing your .dbf, .log, .tmp files. Perform copy of those files and using scp move that on standby database.
3. On the standby node create necessary directories to get the datafiles, logfiles and so on.
[/opt/oracle]#mkdir –p /oradisk/oracle/BBMLDR/datafile/
[/opt/oracle]#mkdir –p /oradisk/oracle/BBMLDR/onlinelog/
[/opt/oracle/]#chown –R oracle:dba/oradisk/oracle
4. Now Copy the entire database from primary to standby while the main database is down or in backup mode. Copying database includes copying all .dbf,.log, .tmp files
5. Prepare an initialization parameter file for the standby database.
Following list shows the parameter
Edit the following
Db_unique_name=’BBMLDR’
audit_file_dest=’/oradisk/app01/oracle/admin/bbmldr/adump’
background_dump_dest,=’/oradisk/app01/oracle/admin/BBMLDR/bdump’
core_dump_dest=’/oradisk/app01/oracle/admin/BBMLDR/cdump’
user_dump_dest, control_files=’/oradisk/app01/oracle/admin/BBMLDR/udump’
db_create_file_dest=’/oradisk/oracle/BBMLDR’
db_file_name_convert=’/oradisk/oracle/bbmldr/datafile/’,’/../../../bbmlprod/datafile/’
log_file_name_convert=’/oradisk/oracle/bbmldr/onlinelog/’,’/../../../bbmlprod/onlinelog/’
fal_server=’bbmlprod’
fal_client=’bbmldr’
log_archive_dest_1=’location=/oradisk/oracle/bbmldr/archives/’
valid_for=(all_logfiles, all_roles)
db_unique_name=’bbmldr’
log_archive_dest_2=’service=bbmlprod lgwr async
valid_for=(online_logfiles, primary_role)
db_unique_name=’bbmlprod’
6. Create all required directories for dump directories and archived log destination
[/opt/oracle]#mkdir –p /oradisk/app01/oracle/admin/BBMLDR/budmp
[/opt/oracle]#mkdir –p /oradisk/app01/oracle/admin/BBMLDR/cdump
[/opt/oracle]#mkdir –p /oradisk/app01/oracle/admin/BBMLDR/udump
[/opt/oracle]#mkdir –p /oradisk/app01/oracle/admin/BBMLDR/archives/
7. Copy from the primary controlfile to standby control file to its destination using scp –p
[sblbgp01]>scp –p bbmldr.ctl drsblbgp01:/oradisk/BBMLDR/controlfile/bbmldr_02.ctl
[sblbgp01]>scp –p bbmldr.ctl drsblbgp01:/oradisk/BBMLDR/controlfile/bbmldr_01.ctl
8. Configure the listener and tnsnames to support the database on both nodes
#ON sblbgp01
LISTENER_BBMLPROD=
(DESCRIPTION=
(DESCRIPTION=
(ADDRESS =(PROTOCOL = TCP)(HOST=sblbgp01)(PORT=1520)(IP=FIRST))
)
)
SID_LIST_LISTENER_BBMLPROD
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= BBMLPROD)
(ORACLE_HOME = /oradisk/appl01/oracle/product/10gDB)
(SID_NAME = bbmlprod)
)
)
#drsblbgp01
LISTENER_BBMLDR =
(DESCRIPTION_LIST=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = drsblbgp01) (PORT=1520) (IP=FIRST)
)
)
SID_LIST_LISTENER_BBMLDR =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = BBMLDR)
(ORACLE_HOME = /oradisk/app01/oracle/product/10gDB)
(SID_NAME = BBMLDR)
)
)
Configure tnsnames.ora on both servers to hold entries for both databases.
# ON sblbgp01
LISTENER_BBMLPROD =
(DESCRIPTION_LIST=
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP) (HOST = sblbgp01) (PORT = 1520) (IP = FIRST)
)
)
BBMLPROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST =sblbgp01) (PORT = 1520))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BBMLPROD)
)
)
BBMLDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = drsblbgp01) (PORT = 1520))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =BBMLDR)
)
)
#ON drsblbgp01
LISTENER_BBMLDR =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP) (HOST =drsblbgp01) (PORT =1520) (IP = FIRST)
)
)
BBMLDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP) (HOST = drsblbgp01) (PORT =1520))
(CONNECT_DATA=
(SERVER = DEDICATED)
(SERVICE_NAME = BBMLDR)
)
)
BBMLPROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST =SBLBGP01) (PORT =1520))
(CONNECT_DATA =
(SERVER =DEDICATED)
(SERVICE_NAME =BBMLPROD)
)
)
9. Start the listener and check tnsping on both nodes to both
On node 1
[sblbgp01]>Lsnrctl start bbmlprod
[sblbgp01]>Lsnrctl start bbmldr
[sblbgp01]>tnsping bbmlprod
[sblbgp01]>tnsping bbmldr
10. Set up the environment to support the standby database on the standby node.
Copy the password file from primary to standby, sys password must be identical
[sblbgp01]>scp orapwbbmlprod drsblbgp01:/oradisk/app01/oracle/product/10gDB/dbs/orapwbbmldr
Setup the environment variable to point to the standby database
ORACLE_HOME=/oradisk/app01/oracle/product/10gDB
ORACLE_SID=bbmldr
PATH=$ORACLE_HOME/bin:$PATH
Startup nomount the standby database and generate an spfile
[drsblbgp01]> cd $ORACLE_HOME
[drsblbgp01]>Sqlplus /nolog
[drsblbgp01]>conn sys/as sysdba
SQL>startup nomount pfile =’/oradisk/app01/oracle/product/10gDB/dbs/initbbmldr.ora’;
SQL>create spfile from pfile =’/oradisk/app01/oracle/product/10gDB/dbs/initbbmldr.ora’;
SQL> shutdown immediate;
SQL> startup mount
SQL> Alter Database Recover Managed standby database disconnect from session;
Database altered
Check the alert log of both primary and standby which will give you complete information
Verifying the Physical standby database is performing properly
Check archived redo log on standby
SQL>show parameters db_unique_name;
SQL> Select thread#, sequence#, name, first_time, next_time, completion_time, applied from V$ARCHIVED_LOG ORDER BY first_time, thread#;
Switch logfile on primary
SQL>alter system switch logfile;
SQL> archive log list
SQL>alter system switch logfile
SQL>archive log list
Check archived redo log on standby
SQL>Select thread#, sequence#, name, first_time, next_time, completion_time, applied from V$ARCHIVED_LOG ORDER BY first_time, thread#;
Regards,
Sowmya Bandaru (OSR)
Tuesday, June 22, 2010
Subscribe to:
Posts (Atom)
