Oracle 12C DG 搭建(RAC-RAC/RAC-单机）

小鱼儿 2022-07-12 02:09 419阅读 0赞

主库上操作  
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.