<< Oracle高可用>>部分书面作业 - 第六课 Data Gaurd 概念和配置

1.配置DG为物理standby,分别使用应用归档日志方式和应用在线redo方式进行数据同步。

配置oracle 10g物理DG的一般流程如下。

（11g流程可参照Note：Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE [ID 1075908.1]）

On both servers, configure TNSNAMES.ORA
LONDON =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = london1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LONDON)
)
)
READING =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = reading1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = READING)
)
)

On primary server create backup location
mkdir /u01/oradata/PRIMARY/backup;

On primary server run RMAN to backup database
$ORACLE_HOME/bin/rman NOCATALOG TARGET sys/oracle@PRIMARY

In RMAN configure backup
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/u01/oradata/PRIMARY/backup/%F';
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/oracle@PRIMARY';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/u01/app/oracle/admin/PRIMARY/backup/snapcf_PRIMARY.f';

In RMAN backup database
BACKUP
FORMAT '/u01/oradata/PRIMARY/backup/%d_D_%T_%u_s%s_p%p'
DATABASE;

 In RMAN create controlfile for standby database
BACKUP CURRENT CONTROLFILE FOR STANDBY
FORMAT '/u01/oradata/PRIMARY/backup/%d_C_%U';

In RMAN switch and archive current online redo log
SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";

In RMAN backup archived redo logs
BACKUP
FILESPERSET 10
ARCHIVELOG ALL
FORMAT '/u01/oradata/PRIMARY/backup/%d_A_%T_%u_s%s_p%p';

On standby server create administrative directories
$ mkdir $ORACLE_BASE/admin/PRIMARY;
$ mkdir $ORACLE_BASE/admin/PRIMARY/adump;
$ mkdir $ORACLE_BASE/admin/PRIMARY/bdump;
$ mkdir $ORACLE_BASE/admin/PRIMARY/cdump;
$ mkdir $ORACLE_BASE/admin/PRIMARY/dpdump;
$ mkdir $ORACLE_BASE/admin/PRIMARY/pfile;
$ mkdir $ORACLE_BASE/admin/PRIMARY/udump;

On standby server create database location
$ mkdir /u01/oradata/PRIMARY

On standby server create archived redo log location
$ mkdir /u01/oradata/PRIMARY/arch

On standby server create backup location
$ mkdir /u01/oradata/PRIMARY/backup;

On standby server create password file
$ORACLE_HOME/bin/orapwd \
file=$ORACLE_HOME/dbs/orapwPRIMARY \
password=oracle \

Copy SPFILE from primary to standby
scp london1:$ORACLE_HOME/dbs/spfilePRIMARY.ora \
reading1:$ORACLE_HOME/dbs

On primary server set parameters
ALTER SYSTEM SET db_unique_name = LONDON
SCOPE = SPFILE;
ALTER SYSTEM SET log_archive_dest_1 =
'LOCATION=/u01/oradata/PRIMARY/arch'
SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=READING
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=READING'
SCOPE=SPFILE;
ALTER SYSTEM SET fal_client = LONDON
SCOPE = SPFILE;
ALTER SYSTEM SET fal_server = READING
SCOPE = SPFILE;
ALTER SYSTEM SET standby_file_management = AUTO
SCOPE = SPFILE;

Restart the primary instance

On standby server configure LISTENER.ORA
SID_LIST_LISTENER_SERVER4 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = PRIMARY)
)
)

Reload the listener
$ lsnrctl reload

Start the standby instance (NOMOUNT)
$ export ORACLE_SID=PRIMARY
$ sqlplus / as sysdba
SQL> startup nomount

On standby server set parameters
ALTER SYSTEM SET db_unique_name = READING
SCOPE = SPFILE;
ALTER SYSTEM SET log_archive_dest_1 =
'LOCATION=/u01/oradata/PRIMARY/arch'
SCOPE = SPFILE;
ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=LONDON
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LONDON'
SCOPE=SPFILE;
ALTER SYSTEM SET fal_client = READING
SCOPE = SPFILE;
ALTER SYSTEM SET fal_server = LONDON
SCOPE = SPFILE;
ALTER SYSTEM SET standby_file_management = AUTO
SCOPE = SPFILE;

Restart the standby instance

On primary server run RMAN connecting to standby server as AUXILIARY
$ORACLE_HOME/bin/rman NOCATALOG TARGET / AUXILIARY
sys/oracle@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=reading1)
(PORT=1521))(CONNECT_DATA=(SID=PRIMARY)))"

Run the following command to clone the database backup on the standby
server
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY DO RECOVER ;

On standby server restart the instance
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT

Mount the standby database
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Start managed recovery
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;

On standby server cancel managed recovery
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


采用archivelog还是online redo同步主要看log_archive_dest_n的配置，其他步骤都一样。

应用归档日志

LOG_ARCHIVE_DEST_2='SERVICE=CHICAGO ARCH NOAFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)
DB_UNIQUE_NAME=CHICAGO'

应用在线redo

同步模式：

LOG_ARCHIVE_DEST_2='SERVICE=CHICAGO SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)
DB_UNIQUE_NAME=CHICAGO'

异步模式：

LOG_ARCHIVE_DEST_2='SERVICE=CHICAGO ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)
DB_UNIQUE_NAME=CHICAGO'

以前做过的一个11g 物理DG配置流水：http://blog.csdn.net/t0nsha/article/details/7668699

2.配置DG为物理standby，且保护模式分别是最大性能，最大高可用和最大保护模式。

最大性能模式
LOG_ARCHIVE_DEST_2='SERVICE=CHICAGO ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)
DB_UNIQUE_NAME=CHICAGO'
LOG_ARCHIVE_DEST_2='SERVICE=CHICAGO ARCH NOAFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)
DB_UNIQUE_NAME=CHICAGO'
 alter database set standby database to maximize PERFORMANCE;

最大可用性模式
LOG_ARCHIVE_DEST_2='SERVICE=CHICAGO SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)
DB_UNIQUE_NAME=CHICAGO'
 alter database set standby database to maximize AVAILABILITY;

最大保护模式
设置方式:
1.创建standby redo logs
alter database add standby logfile group 5 ('/oracle/u02/ORA10GDG/STANDBYRD02.LOG') size 200M;
alter database add standby logfile group 5 ('/oracle/u02/ORA10GDG/STANDBYRD02.LOG') size 200M;
alter database add standby logfile group 6 ('/oracle/u02/ORA10GDG/STANDBYRD03.LOG') size 200M;
2.配置对应的日志传递相关属性参数
LOG_ARCHIVE_DEST_2='SERVICE=CHICAGO SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)
DB_UNIQUE_NAME=CHICAGO'
3.转换成最大保护模式
alter database set standby database to maximize PROTECTION;

3.配置DG为逻辑standby模式。

On standby server cancel managed recovery
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

On primary server modify the LOG_ARCHIVE_DEST_2 parameter
SQL> ALTER SYSTEM SET
log_archive_dest_2 = 'SERVICE=READING
VALID_FOR=(ONLINE_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=READING'
SCOPE=SPFILE;

On primary server build the logical standby dictionary
SQL> EXECUTE LOGSTDBY.BUILD;

On standby server enable recovery and change the name of the standby
database
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY standby;

On standby server restart the instance and reset the logs
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> ALTER DATABASE OPEN RESETLOGS;

On standby server start SQL apply

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;