Oracle11g + CentOS6 + DG installation record, oracle11gcentos6

Oracle11g + CentOS6 + DG installation record, oracle11gcentos6
Based on a practical operation record, this article describes how to use rman for Hot Standby to create a physical Active standby database of dataguard. In this way, the master database only needs to be restarted once, so that the master database has higher availability!
Before performing operations in this document, the primary database is in the archive operation mode by default. The general process is to configure the parameters of the primary database and perform rman backup, then, the oracle software and configuration listening are installed on the slave database, and dbca is not required to create the database. Then, the parameter files of the slave database are modified and rman is restored. Then, the test is performed.
Environment Description:
Primary: CentOS6.3 64-bit 192.168.100.117, db_name: ahqy, db_unique_name: ahqy
Standby: CentOS6.3 64-bit 192.168.100.118, db_name: ahqy, db_unique_name: standby
Oracle version: 11.2.0.1 64-bit Enterprise Edition
1. primary server configuration

1: Enable Forced Logging on the master database

alter database force logging;

2: Create a password file on the slave database. Copy the password file from the master database to the slave database.

scp /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwahqy 192.168.100.118:/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwahqy

3: Configure Standby Redo Log on the master database

select member from v$logfile;du -h /home/oracle/oradata/ahqy/redo01.logsqlplus / as sysdbamkdir standbyalter database add standby logfile group 4 '/home/oracle/oradata/ahqy/standby/standby04.log' size 200M;alter database add standby logfile group 5 '/home/oracle/oradata/ahqy/standby/standby05.log' size 200M;alter database add standby logfile group 6 '/home/oracle/oradata/ahqy/standby/standby06.log' size 200M;

4. Modify the initialization parameters of the master database.

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ahqy,standby)';alter system set log_archive_dest_1='LOCATION=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=ahqy' scope=spfile;alter system set log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=spfile;alter system set log_archive_dest_state_1=enable; alter system set log_archive_dest_state_2=enable;alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;show parameter remote_login;alter system set log_archive_max_processes=30;alter system set fal_server=standby;alter system set fal_client=ahqy;alter system set standby_file_management=auto; shutdown immediate; startup;

5: configure the tnsnames. ora file of the master database. The backup database must perform the same operation.

cat /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin//tnsnames.ora # tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.LISTENER_AHQY =  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))AHQY =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = ahqy)    )  )STANDBY =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.118)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = standby)    )  )

6. Prepare the pfile and Database Backup files and control files required by the standby database on the primary database.

mkdir -p /home/oracle/backup/ create pfile='/home/oracle/backup/initahqy.ora' from spfile;rman target /backup tag 'dg_20141226' format '/home/oracle/backup/dg_%U' incremental level 0 database plus archivelog; backup format '/home/oracle/backup/controlfile_%U' current controlfile for standby;

Ii. standby server configuration
1: Set oracle_sid and configure tnsnames. ora

echo $ORACLE_SIDscp /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora oracle@192.168.100.118:/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

2: copy the backup files backed up by the master database on the slave database and prepare related directories.

Mkdir-p/home/oracle/backup/scp-rp oracle@192.168.100.117:/home/oracle/backup/*/home/oracle/backup/cp/home/oracle/backup/initahqy. ora $ ORACLE_HOME/dbsgrep 'fal _ '$ ORACLE_HOME/dbs/initahqy. ora *. fal_client = 'standby '*. fal_server = 'ahqy' grep' log _ archive_dest _ '$ ORACLE_HOME/dbs/initahqy. ora *. log_archive_dest_1 = 'location =/home/oracle/arch valid_for = (all_logfiles, all_roles) db_unique_name = standby '*. Log_archive_dest_2 = 'service = ahqy lgwr async valid_for = (online_logfiles, primary_role) db_unique_name = ahqy '*. log_archive_dest_state_1 = 'enable '*. log_archive_dest_state_2 = 'enable '*. db_unique_name = 'standby' ---------------------------- this sentence is missing, resulting in AHQY by default. You must add *. LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (standby, ahqy) '-- switch between the master and slave databases; otherwise, the switchover may fail. --- alter system set LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (standby, ahqy) '; mkdir-p/home/o Racle/arch/ahqymkdir-p/home/oracle/app/oracle/admin/ahqy/{adump, bdump, cdump, dpdump, udump, pfile} -- mkdir-p/home/oracle/oradata/ahqymkdir-p/u01/app/oracle/flash_recover_area/ORCL may not be used in Several folders -- not found -- (the corresponding the master database file is directly moved to the slave database, for example:/home/oracle/arch/,/home/oracle/oradata/,/home/oracle/oradiag_oracle) mkdir-p/home/oracle/app/oracle/diag/rdbms/ahqy/{alert, cdump, hm, incident, incpkg, ir, lck, metadata, stage, sw Eep, trace} Because folders may not be created, I will copy several main folders directly from the master database to the corresponding directory of the slave database.

3: Start the slave database to the nomount status.

sqlplus / as sysdbastartup nomount;

4: Use rman to restore the standby Database

rman target sys/ORACLE@AHQY auxiliary / duplicate target database for standby nofilenamecheck;

5. Place the standby database in the application redolog mode.

Sqlplus/as sysdbaarchive log list; alter database recover managed standby database disconnect from session; select SEQUENCE #, FIRST_TIME, NEXT_TIME, APPLIED from v $ archived_log; select member from v $ logfile; log switching can be performed on the master database to speed up the application log of the slave database! Sqlplus/as sysdbaalter system switch logfile; archive log list; secondary database query: select SEQUENCE #, FIRST_TIME, NEXT_TIME, APPLIED from v $ archived_log; select name, database_role from v $ database;

Iii. Data Testing
1. Create a tablespace and a table in the master database

select name,database_role from v$database;create tablespace ahqy datafile  '/home/oracle/oradata/ahqy/ahqy.dbf'  size 10M autoextend on;  create table dg01 as select * from dba_source;alter system switch logfile;

2. After the application logs of the slave database are opened in read-only mode, view the data

alter database recover managed standby database cancel;alter database open read only; select type from dg01;

3: place the slave database in the Application Log Mode

sqlplus / as sysdbashutdown immediate;startup nomount;alter database mount standby database;alter database recover managed standby database disconnect from session;

4. Set the standby database to Active Mode
1) view the current status of the standby database.

select open_mode,database_role,db_unique_name from v$database;

2) cancel Automatic Recovery of the slave Database

alter database recover managed standby database cancel;

3) The OPEN standby database is in read-only mode (the slave uard can only start in readonly mode)

alter database open;select open_mode from v$database;alter database recover managed standby database using current logfile disconnect;select open_mode,database_role,db_unique_name from v$database;

V. Master/Slave database switchover
Master database operations:

alter database commit to switchover to physical standby with session shutdown;shutdown immediatestartup mountalter database recover managed standby database disconnect from session;

Slave database operations:

shutdown immediatestartup mountalter database commit to switchover to primary;alter database open;

Vi. Precautions
1: both the master and backup use static listeners.

[oracle@118 admin]$ cat listener.ora # listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.118)(PORT = 1521))    )  )SID_LIST_LISTENER =    (SID_LIST =      (SID_DESC =        (SID_NAME = PLSExtProc)        (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)        (PROGRAM = extproc)      )      (SID_DESC =      (SID_NAME = ahqy)      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)      (GLOBAL_DBNAME = standby)    )  )  ADR_BASE_LISTENER = /home/oracle/app/oracle[oracle@117 admin]$ cat listener.ora # listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))    )  )SID_LIST_LISTENER =    (SID_LIST =      (SID_DESC =        (SID_NAME = PLSExtProc)        (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)        (PROGRAM = extproc)      )      (SID_DESC =      (SID_NAME = ahqy)      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)      (GLOBAL_DBNAME = ahqy)    )  )ADR_BASE_LISTENER = /home/oracle/app/oracle

2: TNSNAME Configuration
The SERVICE_NAME in tnsnames can be configured according to show parameter name.

[oracle@117 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.LISTENER_AHQY =  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))AHQY =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = ahqy)    )  )standby =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.118)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = standby)    )  )  [oracle@118 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.LISTENER_AHQY =  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))AHQY =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.117)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = ahqy)    )  )STANDBY =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.118)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = standby)    )  )

VII. References
Http://ylw6006.blog.51cto.com/470441/661105
Http://www.educity.cn/shujuku/1177494.html
Http://blog.csdn.net/w63667329/article/details/7900270