Oracle12CDG的搭建教程：RAC-RAC/RAC-单机

主库上操作

1.开启RAC的 force logging

SQL> alter database force logging;

SQL>

2.修改RAC初始化参数文件

SQL> alter system set log_archive_config='DG_CONFIG=(eisoo,eisoos)';

SQL> alter system set log_archive_dest_2='SERVICE=eisoos ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eisoos' scope=spfile;

SQL> alter system set log_archive_dest_state_1=ENABLE;

SQL> alter system set log_archive_dest_state_2=ENABLE;

SQL> alter system set fal_server=eisoos;

SQL> alter system set db_file_name_convert='eisoos','eisoo' scope=spfile;

SQL> alter system set db_file_name_convert='/data/oradata/eisoos','+DATA/EISOO/DATAFILE' scope=spfile; //如果备库没有使用asm

SQL> alter system set log_file_name_convert='eisoos','eisoo' scope=spfile;

SQL> alter system set log_file_name_convert='/data/oradata/eisoos','+DATA/EISOO/ONLINELOG' scope=spfile;//如果备库没有使用asm

SQL> alter system set standby_file_management='AUTO';

SQL> alter system set log_archive_max_processes=30;

3.开启归档模式

srvctl stop database -d eisoo

srvctl start database -d eisoo-i eisoo -o mount

SQL>alter database archivelog;

SQL>alter database open;

4.创建standby logfile;

SQL> select thread#,group#,bytes/1024/1024 from v$log;

THREAD# GROUP# BYTES/1024/1024

---------- ---------- ---------------

1 1 50

1 2 50

2 3 50

2 4 50

SQL> alter database add standby logfile thread 1 group 10 size 50M;

SQL> alter database add standby logfile thread 1 group 11 size 50M;

SQL> alter database add standby logfile thread 1 group 12 size 50M;

SQL> alter database add standby logfile thread 2 group 13 size 50M;

SQL> alter database add standby logfile thread 2 group 14 size 50M;

SQL> alter database add standby logfile thread 2 group 15 size 50M;

SQL> select thread#,group#,bytes/1024/1024 from v$standby_log;

THREAD# GROUP# BYTES/1024/1024

---------- ---------- ---------------

1 10 50

1 11 50

1 12 50

2 13 50

2 14 50

2 15 50

5.创建备库参数文件

SQL> show parameter spfile;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string +DATA/EISOO/PARAMETERFILE/spfile.281.923255053

SQL> create pfile='/tmp/initeisoos.ora' from spfile='+DATA/EISOO/PARAMETERFILE/spfile.281.923255053';

[oracle@rac1 tmp]$ scp initeisoos.ora 192.168.180.48:$ORACLE_HOME/dbs

备库上操作：

1.修改参数文件

使用asm：

*.audit_file_dest='/u01/app/oracle/admin/eisoos/adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/data/oradata/eisoos/control01.ctl'

*.db_block_size=8192

*.db_create_file_dest='/data/oradata/eisoos'

*.db_domain=''

*.db_file_name_convert='eisoo','eisoos'

*.db_name='eisoo'

*.db_unique_name='eisoos'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=eisoosXDB)'

*.fal_server='EISOOS'

*.log_archive_config='DG_CONFIG=(eisoo,eisoos)'

*.log_archive_dest_1='LOCATION=/data/oradata/eisoos/archivelog'

*.log_archive_dest_2='SERVICE=eisoo ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eisoo'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=30

*.log_file_name_convert='eisoo','eisoos'

*.open_cursors=300

*.pga_aggregate_target=453m

*.processes=300

*.remote_login_passwordfile='exclusive'

*.sga_target=1361m

*.standby_file_management='AUTO'

eisoos.undo_tablespace='UNDOTBS1'

单机未使用asm：

*.audit_file_dest='/u01/app/oracle/admin/eisoos/adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/data/oradata/eisoos/control01.ctl'#Restore Controlfile

*.db_block_size=8192

*.db_create_file_dest='/data/oradata/eisoos'

*.db_domain=''

*.db_file_name_convert='+DATA/EISOO/DATAFILE','/data/oradata/eisoos','+DATA/EISOO/TEMPFILE','/data/oradata/eisoos'

*.db_name='eisoo'

*.db_unique_name='eisoos'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=eisoosXDB)'

*.fal_server='EISOOS'

*.log_archive_config='DG_CONFIG=(eisoo,eisoos)'

*.log_archive_dest_1='LOCATION=/data/oradata/eisoos/archivelog'

*.log_archive_dest_2='SERVICE=eisoo ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eisoo'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=30

*.log_file_name_convert='+DATA/EISOO/ONLINELOG','/data/oradata/eisoos'

*.open_cursors=300

*.pga_aggregate_target=453m

*.processes=300

*.remote_login_passwordfile='exclusive'

*.sga_target=1361m

*.standby_file_management='AUTO'

eisoos.undo_tablespace='UNDOTBS1'

2.启动到 nomout状态

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initeisoos.ora';

ORACLE instance started.

Total System Global Area 1442840576 bytes

Fixed Size 2924448 bytes

Variable Size 486539360 bytes

Database Buffers 939524096 bytes

Redo Buffers 13852672 bytes

SQL>

SQL>ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=false;

备注：SEC_CASE_SENSITIVE_LOGON参数是决定密码文件是否可以在本地创建，否则只能从主库拷贝到备库。默认值是“true”,

3.在本地创建密码文件

ocrl:/u01/app/oracle/product/12.1.0/db_1/dbs@oracle1>orapwd file=orapweisoos password=oracle entries=10 ignorecase=y force=y

4.配置监听文件，保证primary和standby能够互连

备库:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.180.41)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = eisoos)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = eisoos)

)

)

或者

SID_LIST_LISTENER_EISOOS =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = eisoos)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = eisoos)

)

)

主库和备库是tnsname.ora 配置如下：

eisoo =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.180.51)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = eisoo)

)

)

eisoos =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.180.41)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = eisoos)

)

)

主备分别验证：

[oracle@rac1 ~]$ sqlplus sys/oracle@eisoo as sysdba

[oracle@rac1 ~]$ sqlplus sys/oracle@eisoos as sysdba

[oracle@rac2 ~]$ sqlplus sys/oracle@eisoo as sysdba

[oracle@rac2 ~]$ sqlplus sys/oracle@eisoos as sysdba

5.备份恢复数据

eisoos:/home/oracle@oracle1>rman target sys/oracle@eisoo auxiliary sys/oracle@eisoos

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Sep 26 16:50:42 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: EISOO (DBID=3774196505)

connected to auxiliary database: EISOO (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 2016/09/26 16:52:12

6.开启实时同步

SQL> alter database recover managed standby database using current logfile disconnect from session;

验证:

1)

SQL> select dest_name,error from v$archive_dest;//通过查看archive_log_dest_2列是否有error报错，如果有报错，则需要先根据报错内容解决问题

2)

查询主库最大归档序号和备库最大归档序号

select max(sequence#) from v$archived_log;

然后在主库切换日志：alter system switch logfile;

再次查询备库最大归档序号，一致即归档同步成功。

主库：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

133

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

134

SQL>

备库：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

134

SQL>

3)

主库验证

SQL>select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

备库验证

SQL>select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;

备注：观察主备库日志是否同步，如一致则表示日志CDP同步正常。

主备切换

1.检查DG是否同步是否正常

主库：

SQL> select switchover_status,database_role from gv$database;

SWITCHOVER_STATUS DATABASE_ROLE

-------------------- ----------------

TO STANDBY PRIMARY

TO STANDBY PRIMARY

备库:

SQL> select switchover_status,database_role from gv$database;

SWITCHOVER_STATUS DATABASE_ROLE

-------------------- ----------------

NOT ALLOWED PHYSICAL STANDBY

2.准备切换工作:

关闭RAC库，并把rac1起到open 状态

[oracle@rac1 ~]$ srvctl stop database -d eisoo

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 27 09:12:25 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1442840576 bytes

Fixed Size 2924448 bytes

Variable Size 553648224 bytes

Database Buffers 872415232 bytes

Redo Buffers 13852672 bytes

Database mounted.

Database opened.

SQL>

3.开始切换

主库：

SQL> alter database commit to switchover to physical standby with session shutdown;

重启数据库到mount状态

SQL> startup mount

ORACLE instance started.

Total System Global Area 1442840576 bytes

Fixed Size 2924448 bytes

Variable Size 553648224 bytes

Database Buffers 872415232 bytes

Redo Buffers 13852672 bytes

Database mounted.

SQL>

查看数据库角色与状态

SQL> select status from v$instance;

STATUS

------------

MOUNTED

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY

SQL>

此时rac1已变成备库

备库：

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> alter database open;

Database altered.