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 bySQL> 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.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 logSQL> 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 standbyCreate 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 accessSQL> 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 1SQL> 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
There are no comments on this page. [Add comment]