In the following paragraphs I am going to describe the Logical standby database creation using HOTBACKUPS. Before we go into the creation of the database what benefits we are having using Logical Standby database.
Benefits of Logical Standby database:- Since we know dataguard can be configured in two ways - Physical Standby Database and Logical Standby Database. The most advantage over Physical standby is Logical standby database can be in open mode and simultaneously it can be used for reporting. This advantage comes handy because in Logical standby the SQL's are being applied on the table which are generated from the archived logs which get transfred from the Primary database. Since applying SQL required the database has to be in the Open mode.
Second Important benefit is Resource Utilization, Logical Standby database can have extra indexes and materialized views which might be required for the reporting purpose Since it is not mandatory to have the same physical structure of the Physical standby.
=====================================================================================
PRE REQUISITES:- After getting the advantages, it is required to check the primary database for following points which enables how far standby database is going to support for log apply service:
1. Primary database should be in Archive log mode.
2. Primary database should be checked for the objects which are good candidate for the log apply service. If found that few objects are not a candidate for the log apply service by creating primary key / unique index we can make them a candidate.
3. Ensure Supplemental logging is enabled for the Primary database.
Below are the commands to check the above requirements:-
Log in as sysdba into the primary database then issue the commands.
1. Archive Log List;
This will show if the database is in Archive log list. if the database is not in Archive log mode. Goahead and put the database in archive log mode.
2. SELECT * FROM dba_logstdby_unsupported;
This will show if the primary database contains tables and datatypes that were not supported by a logical stand by database. If the primary database contains tables that were unsupported, log apply services will exclude the tables applying to the logical stand by database.
3. SELECT owner, table_name, bad_column FROM dba_logstdby_not_unique;
OWNER TABLE_NAME B
VCSUSER VCS N
Bad column ‘N’ indicates that the table contains enough column information to maintain the table in the logical standby database, where as ‘Y’ indicates the table column is defined using an unbounded data type, such as LONG.
Add a primary key to the tables that do not have to improve performance.
If the table has a primary key or a unique index with a non-null column, the amount of information added to the redo log is minimal.
4. Ensure supplemental logging is enabled and log parallelism is enabled on the primary database. Supplemental logging must be enabled because the logical standby database cannot use archived redo logs that contain both supplemental log data and no supplemental log data.
SELECT supplemental_log_data_pk,supplemental_log_data_ui FROM v$database;
SUP SUP
--- ---
YES YES
If the supplemental logging is not enabled, then it is required to put the database for supplemental logging mode. In order to do that execute the following
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS;
ALTER SYSTEM SWITCH LOGFILE;
If log parallelism is not enabled, execute the following:
ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=BOTH;
Since we are going to create Logical Standby using Hot backups we need to start Resource Manager. If you do not have a resource_manager plan, you can use one of the system defined plans and restart the primary database to make sure it is using the defined plan.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=SYSTEM_PLAN SCOPE=BOTH;
SHUTDOWN
STARTUP
====================================================================================
Once the database has been configured to support the Logical standby Use following steps to configure the Logical stand by database.
1. Take a hotbackup of the primary database. Bring a tablespace to backup mode, copy the datafiles of that tablespace using an OS command, and bring the tablespace back online.
SELECT file_name,tablespace_name FROM v$tablespace;
This will give all the tablespace names and the datafiles which we need to backup, this can be done using RMAN or Command Line.
Below is an example how a SYSTEM tablespace can be backedup using command line. Follow the same for all tablespaces.
ALTER TABLESPACE SYSTEM BEGIN BACKUP;
! cp /opt/oracle/oradata/PRIMEDB/system01.dbf /backup/PRIMEDB/HOT/
ALTER TABLESPACE SYSTEM END BACKUP;
2. Create the backup control file.
ALTER DATABASE BACKUP CONTROLFILE TO backup/PRIMEDB/HOT/PRIMEDB_backup.ctl;
3. Bring the database to a QUIESCED State and configure Database Resource Manager for the same.
ALTER SYSTEM QUIESCE RESTRICTED;
Above task can be skip only if you are using 10g relase 2.
4. Build the LogMiner dictionary.
EXECUTE DBMS_LOGSTDBY.BUILD;
5. Identify the archived redo log that contains the LogMiner dictionary and the starting SCN.
You need to switch the log file, to create the archived log, and then query from V$ARCHIVED_LOG.
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT NAME FROM V$ARCHIVED_LOG
WHERE (SEQUENCE#= (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE DICTIONARY_BEGIN = 'YES' AND STANDBY_DEST='NO')
);
NAME
-----------------------------------------------------------
/opt/oracle/ARC/MYFN/MYFN_0001_0000000005.arc
SELECT MAX(FIRST_CHANGE#) FROM V$ARCHIVED_LOG
WHERE DICTIONARY_BEGIN='YES';
MAX(FIRST_CHANGE#)
------------------
2856516
Note: Remember to record the name of the archived redo log for use later in the creation process.
6. Bring the database back to normal (UNQUIESCED).
ALTER SYSTEM UNQUIESCE;
ALTER SYSTEM SWITCH LOGFILE;
7. Create the parameter file from spfile in the Primary database. The pfile created will be used to create the pfile of the standby database.
CREATE PFILE=’/backup/PRIMEDB/HOT/Primary_init.ora’ FROM SPFILE;
8. Copy Files from the Primary Database Location to the Standby Location.
Use an operating system copy utility to copy the following binary files from the primary database site to the standby site:
i) Backup data files
ii) Backup of control files
iii) Latest archived redo log
iv) init.ora file
v) password file
9. Set the init.ora Parameters on the Logical standby site.
DB_NAME='PRIMEDB'
INSTANCE_NAME='PRIMEDB_H'
LOG_ARCHIVE_DEST_1='LOCATION=/OPT/ORACLE/ARC/PRIMEDB MANDATORY'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_FORMAT='PRIMEDB_%T_%S.ARC'
REMOTE_ARCHIVE_ENABLE=RECEIVE
LOG_ARCHIVE_START=TRUE
LOG_PARALLELISM=1
PARALLEL_MAX_SERVERS=9
STANDBY_FILE_MANAGEMENT='AUTO'
STANDBY_ARCHIVE_DEST='/OPT/ORACLE/ARC/PRIMEDB/STDBY'
# The following parameter is required only if the primary and standby databases
# are located on the same system.
LOCK_NAME_SPACE=PRIMEDB_H
10. Configure the Listener for Both the Primary and Standby Databases and Restart/reload the listener(s).
11. Because the online logs were not copied from the primary system, the redo logs will have to be cleared. To clear the redo logs, use the following statement for all the groups:
ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
12. Recover the Logical Standby Database up to the SCN recorded in step 5 above.
Use the command below to recover the database:
ALTER DATABASE RECOVER AUTOMATIC FROM '/opt/oracle/ARC/PRIMEDB_H/'
UNTIL CHANGE 2856516 USING BACKUP CONTROLFILE;
If error 'ORA-279: change %s generated at %s needed for thread %s' comes the recovery will have to be canceled and recover it manually using the following command.
ALTER DATABASE RECOVER LOGFILE 2>'/opt/oracle/ARC/PRIMEDB_H/MYDB_0001_0000000004.arc'
13. Turn on Data Guard on the Logical Standby Database.
ALTER DATABASE GUARD ALL;
14. Open the Logical Standby Database:
ALTER DATABASE OPEN RESETLOGS;
15. Reset the Database Name of the Logical Standby Database:
Run the Oracle DBNEWID (nid) utility to change the database name of the logical standby database. This will change the database name in the control file. Until now, the dbname in the control file is the primary db name.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT PFILE=''
EXIT
$> export ORACLE_SID=MYDB_H
Modify the init.ora file and set parameter DB_NAME=MYDB_H, and if password file is used then delete and recreate the password file.
SHUTDOWN IMMEDIATE;
16. Create a server parameter file for the standby database:
CREATE SPFILE FROM PFILE=
17. Restart the Logical Standby Database:
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;
18. Create a New Temporary File for the Logical Standby Database:
The temporary files are not included as a part of the closed backup operation, so the temporary file will have to be recreated manually.
To create a Temporary file:
ALTER TABLESPACE TEMP ADD TEMPFILE 2> '/opt/oracle/oradata/PRIMEDB_H/temp01.dbf' SIZE 40M REUSE;
19. Register the Archived Redo Log and Start SQL Apply Operations:
To register the most recently archived redo log and begin applying data from the redo logs to the standby database, perform the following steps:
ALTER DATABASE REGISTER LOGICAL LOGFILE
'/opt/oracle/ARC/MYDB_H/MYDB_0001_0000000005.arc’;
Specify the following SQL statement to begin applying redo logs to the logical standby database using the SCN number:
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL 2856516;
Note: The INITIAL keyword has to be used only for the first time. To apply redo logs thereafter, the following statements should be used.
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
ALTER DATABASE START LOGICAL STANDBY APPLY;
20. Enable Archiving in Logical Standby Database:
This step has to be performed in the Primary Database to enable archiving to the Logical Standby Database. Use the following statements to start archiving and remote archiving:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=payroll3 lgwr NO AFFIRM' 2 > SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;
To start remote archiving either of the following statements can be used:
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM SWITCH LOGFILE;
This completes the configuration of Logical Standby Database from Hot Backup.
At the last - SQL Apply with Logical Standby Database is a viable option for customers who need to implement a disaster recovery solution or maximum/high availability solution and use the same resources for reporting and decision support operations. The success in creating a Logical Standby Database depends a lot on how the tasks are executed and on the version is being used. It is very important, before starting the creation of a Logical Standby Database, to make sure that all the Initialization Parameters are set correctly, that all the steps are followed in the correct order and the appropriate parameters are used. If everything is done properly then you should be able to do a clean configuration of the Logical Standby Database in the first go.
No comments:
Post a Comment