Steps for creation of PHYSICAL STANDBY DATABASE MANUALLY AND CONFIGURE DATAGUARD
PREREQUISITES:-
1) Operating System same on both primary and standby servers.
2) Oracle Software of same version/patch on both servers.
3) Oracle Home Paths same on primary and standby servers.
4) Primary Database should be in archive mode and force logging enabled.
SETUP:-
PRIMARY: - Database Version 10.2.0.1.0 Operating system:- Oracle Enterprise Linux 4.5
PHYSICAL: - Database Version 10.2.0.1.0 Operating system:- Oracle Enterprise Linux 4.5
STEPS ON PRIMARY DATABASE:-
1. Enable forced logging on your primary database:
SQL> ALTER DATABASE FORCE LOGGING;
2. Create a password file if it doesn’t exist.
1) To check if a password file already exists, run the following command:
SQL> select * from v$pwfile_users;
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)
3. Configure a Standby Redo log.
INFO:- A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone. It is also recommended to multiplex the standby redolog files same as online redologs.
Although the standby redo log is only used when the database is running in the standby role, it is recommended that you create a standby redo log on the primary database so that the primary database can switch over quickly to the standby role without the need for additional DBA intervention.
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;
3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;
4. Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
5. Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
1) Create pfile from spfile for the primary database:
SQL>create pfile=’/u01/pfilePRIMARY_DBNAME.ora’ from spfile;
2) Edit pfilePRIMARY_DBNAME.ora to add the new primary and standby role parameters:
db_name=PRIMARY
db_unique_name=PRIMARY
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'
LOG_ARCHIVE_DEST_1=
'LOCATION=F:\Oracle\flash_recovery_area\PRIMARY\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PRIMARY'
LOG_ARCHIVE_DEST_2=
'SERVICE=STANDBY LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STANDBY'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=STANDBY
FAL_CLIENT=PRIMARY
STANDBY_FILE_MANAGEMENT=AUTO
DB_FILE_NAME_CONVERT='\u01\oradata\STANDBY\DATAFILE','\u01\oradata\PRIM\DATAFILE'
LOG_FILE_NAME_CONVERT=’\u01\oradata\STANDBY\ONLINELOG’,’\u01\oradata\PRIMARY\ONLINELOG’,’\u02\Oracle\flash_recovery_area\STANDBY\ONLINELOG’,’\u01\Oracle\flash_recovery_area\PRIMARY\ONLINELOG’
6. Create spfile from pfile, and restart primary database using the new spfile.
FOR DATAGURAD SPFILE is required.
SQL> shutdown immediate;
SQL> startup nomount pfile=’/u01/pfilePRIMARY.ora’;
SQL>create spfile from pfile=’/u01/pfilePRIMARY.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
STEPS ON STANDBY DATABASE:-
1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:
SQL>shutdown immediate;
On Standby Server (While the Primary database is shut down):
1) Create directory for data files ‘\u01\oradata\STANDBY\DATAFILE’
2) Copy the data files and temp files over.
3) Create directory (multiplexing) for online logs,
\u01\oradata\STANDBY\ONLINELOG and \u02\flash_recovery_area\STANDBY\ONLINELOG.
4) Copy the online logs over.
2. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘STANDBY.ctl;
SQL>ALTER DATABASE OPEN;
3. Copy the Primary DB pfile to Standby server and rename/edit the file.
1) Copy pfilePRIMARY.ora from Primary server to Standby server on default oracle path.
2) Rename it to pfileSTANDBY.ora, and edit the file as below.
*.audit_file_dest='\u01\admin\STANDBY\adump'
*.background_dump_dest='\u01\admin\STANDBY\bdump'
*.core_dump_dest='\u01\admin\STANDBY\cdump'
*.user_dump_dest='\u01\admin\STANDBY\udump'
*.compatible='10.2.0.1.0'
control_files='\u01\oradata\STANDBY\CONTROLFILE\STANDBY.CTL','\U02\FLASH_RECOVERY_AREA\STANDBY\CONTROLFILE\STANDBY.CTL'
db_name='PRIMARY'
db_unique_name=STANDBY
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIMARY,STANDBY)’
LOG_ARCHIVE_DEST_1=
‘LOCATION=\u02\flash_recovery_area\STANDBY\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STANDBY’
LOG_ARCHIVE_DEST_2=
‘SERVICE=PRIMARY LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIMARY’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRIMARY
FAL_CLIENT=STANDBY
remote_login_passwordfile='EXCLUSIVE'
DB_FILE_NAME_CONVERT=’\u01\oradata\PRIMARY\DATAFILE’,’\u01\oradata\STANDBY\DATAFILE’
LOG_FILE_NAME_CONVERT=’\u01\oradata\PRIMARY\ONLINELOG’,’\u01\oradata\STANDBY\ONLINELOG’,’\u01\flash_recovery_area\PRIMARY\ONLINELOG’,’\u01\flash_recovery_area\STANDBY\ONLINELOG’
STANDBY_FILE_MANAGEMENT=AUTO
4. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
5. Copy the standby control file ‘STANDBY.ctl’ from primary to standby destinations on above path mentioned in PFILE. If needed Multiplexing can be done.
6. Copy the Primary password file to standby and rename it to pwdSTANDBY.ora in default oracle path.
7. Configure listeners for the primary and standby databases and add entries in both servers TNSNAMES.ora
After configuring the Listener stop and start the listener on both databases.
$lsnrctl stop
$lsnrctl start
After adding entries in TNSNAMES file use tnsping to check it works.
8. Export Environment Variables.
$ export ORACLE_SID= STANDBY
Also set ORACLE_HOME env
9. Generate SPFILE on standby database.
SQL>startup nomount pfile=’pfileSTANDBY.ora’;
SQL>create spfile from pfile=’pfileSTANDBY.ora’;
SQL>shutdown immediate;
SQL>startup mount;
10. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
To stop log apply services:
SQL> alter database recover managed standby database cancel;
13. Check Standby database is working fine or not and General Monitoring.
a) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;
On Primary, force a logfile switch:
SQL>alter system switch logfile;
On Standby, verify the archived redo log files were applied:
SQL>select sequence#, applied from v$archived_log order by sequence#;
b) by examining V$archive_gap ---Eventually you will have gaps in the archive logs sequences maybe due to network issues, remember these occur when an archive log is generated on the primary database but not received at the standby site. Archive gaps logs can be monitored by examining the low and high sequence numbers in the v$archive_gap
select thread#, low_sequence#, high_sequence# from v$archive_gap;
c) Identify Missing Logs on the Primary.
select L.thread#, L.sequence#
from
(select thread#, sequence# from v$archived_log where dest_id=1) L
where L.sequence# not in
(select sequence# from v$archived_log where dest_id=2 and thread# = L.thread#);
from
(select thread#, sequence# from v$archived_log where dest_id=1) L
where L.sequence# not in
(select sequence# from v$archived_log where dest_id=2 and thread# = L.thread#);
d) Monitor the apply rate and active
rate using below.
select to_char(start_time, 'DD-MON-RR HH24:MI:SS') start_time, item ,
sofar
from v$recovery_progress
where item in ('Active Apply Rate', 'Average Apply Rate', 'Redo Applied')
;
Note: the redo applied is measured in megabytes, while the average apply rate and the active apply rate is measured in kilobytes.
e) You can find out how far behind the redo
transport and redo apply processes are, the transport lag column will indicate
how far in time that the shipment of redo from the primary to the standby
database is behind, remember this is what you would lose if the primary were to
go down. The apply lag indicates how much time it is behind in redo apply.from v$recovery_progress
where item in ('Active Apply Rate', 'Average Apply Rate', 'Redo Applied')
;
Note: the redo applied is measured in megabytes, while the average apply rate and the active apply rate is measured in kilobytes.
col name for a13
col value for a13
col unit for a30
set lines 132
select name, value, unit, time_computed from v$dataguard_stats where name in ('transport lag', 'apply lag');
f) Review the progress of the standby database in managed recovery mode, you can see the redo apply and redo transport services using below query.
select pid, process, status, client_process, client_pid, thread#, sequence#, block#, blocks from v$managed_standby;
g) To check recovery mode on standby use below.
SQL> select recovery_mode from v$archive_dest_status;
RECOVERY_MODE
-----------------------
IDLE
If recovery mode is managed then it is in managed recovery mode idle means it is open for readonly.
14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.
To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;
Daily Maintenance/Housekeeping and Backups on Data guard setup
1. Alert log file monitoring of Primary and Standby database use some scripts for monitoring alert log file errors.
2. Cleanup the archive logs on Primary and Standby servers.
Primary Backup:- Weekly Full database RMAN backup and Daily incremental and backup archive logs and delete.
Standby Backup:- Backup Archive log only and delete.
RMAN>backup archivelog all delete input;
3. Password Maintenance
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server.
I will update the backup strategy in more details in another post shortly.
Sandeep Magdum
