{{parent page="Oracle"}}

Creating a non-managed oracle standby (data guard) database. Managed Data Guard offers more robust and reliable standby features, but it's only available on Oracle Enterprise Edition. In 10g, RAC is what they call it.

=== 1.1 Assumpion ===

1.1.1.Production and Standby uses the same version and release of Oracle

=== 1.2 Ensure primary db is in archive log mode ===

1.2.1. Checking
%%
SQL> select log_mode from v$database;
%%

1.2.2. Enabling
%%
connect / as sysdba
SQL> startup mount exclusive
SQL> alter database archivelog
SQL> archive log start
SQL> alter database open
SQL> shutdown
SQL> startup
%%

=== 1.3 Ensure primary database is in force logging mode ===

1.3.1. Checking
%%
SQL> select force_logging from v$database;
%%

1.3.2. To enable
%%
SQL> alter database force logging;
%%

=== 1.4 Transferring datafiles to standby server ===

1.4.1. Obtain all datafiles on primary database, which will then be copied to the standby database. Files should be copied to a similar location on standby database, if not identical.
%%
SQL> select name from v$datafile
SQL> /osysdb/xpc8db/system01.dbf
/osysdb/xpc8db/undo01.dbf
/osysdb/xpc8db/undo02.dbf
/odata1/xpc8db/saksdb_tbs1.dbf
%%

1.4.2.Shutdown the primary instance
%%
SQL> shutdown immediate
%%

1.4.3.Copy datafiles to standby server
1.4.4. After datafiles are copied to standby server, primary database can be started up and resume operation
%%
SQL> startup;
%%

=== 1.5. Create control files for standby database ===

1.5.2. Create a control file for standby database from the primary server
%%
SQL> alter database create standby controlfile as '/tmp/control_sb01.ctl';
%%

1.5.3.Copy this control file to standby server. Refer to standby instance's init pfile for locations. Most likely there will be more than 1 control file. Make copies of this control file.

=== 1.6.Copy pfile from primary instance to standby instance ===
1.6.2. An init pfile is necessary for any Oracle database. The pfile on standby database should be very similar to the primary instance. You can first copy primary's pfile to standby server. You may need to modify several parameters. If the primary database is using spfile instead of pfile, you will need to create a pfile by
%%
SQL> create pfile='/tmp/initstdbxpc.ora' from spfile;
%%

1.6.2.1. Parameters that should not be modified:
db_name, compatible, log_archive
1.6.2.2. Parameters to be added
1.6.2.2.1.db_file_name_convert and log_file_name_convert is necessary when the subjects' location is different from primary instance's location. For example, you may store datafile on /odata/primary_db/ on your primary server and /odata/backup_db/ on your standby server. In that case, you will need to specify the conversion in the pfile. These two parameters takes form of 'source 1', 'dest 1', 'source 2', 'dest 2'... Example:
%%
db_file_name_convert = '/odata1/xpc8db','/odata_m1/xpc8db','/odata2/xpc8db','/odata_m2/xpc8db','/odata3/xpc8db','/odata_m3/xpc8db'
log_file_name_convert = '/osys3/xpc8db','/osys2/xpc8db'
%%

1.6.2.2.2. standby_archive_dest and standby_file_management should be set as the following example:
%%
standby_archive_dest - location of archive redo logs from primary
standby_file_management = AUTO
%%

1.6.2.2.3. remote_archive_enable tells Oracle whether archiving log to a remote server is enabled. Accepted values are TRUE, SEND, and RECEIVE. However, this parameter is only useful on managed standby database.
Example:
%%
remote_archive_enable = TRUE #(set to SEND on primary db)
%%

=== 1.7.Start up standby db ===
%%
SQL> startup pfile='/oracle/9.2.0/dbs/initxpc8db.ora' nomount;
SQL> alter database mount standby database;
%%

=== 1.8. Produce archive log on primary server. ===
After some DML are operated on primary, force primary to produce archive log
%%
SQL> alter system switch logfile;
OR
SQL> alter system archive log current;
%%

=== 1.9. Transfer and apply archive log to standby server ===
1.9.2.Transfer archive log to standby
Create a script to transfer archive log files on primary server to standby server. Archive log on primary will be produced to a location one can check by
%%
SQL> archive log list
%%

Archive log should be copied to a location on standby database one can check by
%%
SQL> show parameters standby_archive_dest
%%

1.9.3.Apply archive log on standby database
%%
SQL> RECOVER AUTOMATIC STANDBY DATABASE;
%%

1.9.4.Checking archive log apply status
With managed standby, you can check v$archived_log for such information:
%%
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
%%

With non-managed standby, one can only check the status by looking at Oracle's alert log, which is located in $ORACLE_HOME/rdbms/alert_xxx.log
The following shell script will monitor the log file and send out alert when archive log are not applied for more than 24 hours:

%%
#!/bin/sh
if [ ! -f /dba/last_seq.txt ]; then
echo "1" > /dba/last_seq.txt
fi
last_seq=`cat /dba/last_seq.txt`
current_seq=`grep arc /oracle/9.2.0/rdbms/log/alert_xpc8db.log | tail -1 | cut -d_ -f3 | cut -d. -f1`
echo $last_seq $current_seq
if [ $last_seq -lt $current_seq ]; then
echo "last < current, good!"
echo $current_seq > /dba/last_seq.txt
else
echo "current equals last, check time!"
current_time=`date +%s`
file_time=`stat -c %Y /dba/last_seq.txt`
diff=`expr $current_time - $file_time`
if [ $diff -gt 86400 ]; then
echo "serious problem. alog not applied for more than 1 day"
mail -s "StDB alog not applied for more than 1 day" sysadmin@domain.com < /dba/last_seq.txt
else
echo "alog not applied for less than 1 day. normal."
fi
fi
%%

=== 1.10 Mounting standby database for reading or reporting ===
1.10.1. Mount database for read only access
%%
SQL> alter database mount read only;
%%

1.10.2. Close database and resume standby
%%
SQL> shutdown immediate;
SQL> startup pfile='/oracle/9.2.0/dbs/initxpc8db.ora' nomount;
SQL> alter database mount standby database;
%%

=== 1.11 Activating standby database as active ===
**WARNING: resetlogs will be performed, activation cannot be reversed. Will need to repeat from step 1**

%%
SQL> alter database activate standby database;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open read write;
%%

check changes applied on standby or not

may need to run PUPBLD.SQL as system
SQL> @?/sqlplus/admin/pupbld.sql