DBA強化實戰系列第三期:oracle11.2.0.4搭建adg全過程

huangdazhu發表於2015-05-19

    從oracle11g開始,支援windows與linux異構dg,同時也開時支援備節點只讀開啟。所以在企業中,可以實現讀寫分離,客戶知道這個新特性後,要求我們幫他們部署一套這樣的active dataguard,來分擔他們生產庫的壓力。下面,我就把我的實施過程釋出出來與大家共享!

1、安裝作業系統及資料庫軟體

具體的安裝、建庫等操作,請參閱《DBA強化實戰系列第一期:centos6.4安裝oracle11.2.0.4單例項asm》。注意,建庫,只需要在一臺機器上建就可以了!

2、開始配置adg的準備工作

2.1主節點操作,配置歸檔
[oracle@mytest1 bin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 2 16:08:29 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

配置強制歸檔,這是建dg必須的,因為在我們實際過程中,不允許有nologging的方式存在,這樣會造成資料不一致

SQL> alter database force logging;

Database altered.

配置歸檔設定歸檔路徑及格式

SQL> alter system set log_archive_format='%S_%t_%r.log' scope=spfile;

System altered.

SQL> alter system set log_archive_dest_state_1='ENABLE';

System altered.

SQL> alter system set log_archive_dest_1='location=+datadg';

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2989854720 bytes
Fixed Size                  2256912 bytes
Variable Size             822087664 bytes
Database Buffers         2147483648 bytes
Redo Buffers               18026496 bytes
Database mounted.
SQL> !lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-JAN-2014 16:11:59

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

--注意,到這一步,主節點的歸檔就基本上配置完成了。資料庫重啟後,註冊一下監聽

SQL> alter system register;

System altered.

2.2主節點操作,線上備份資料庫,引數檔案等,並傳輸到備節點

本次備份的時候,把資料庫及控制檔案也進行了備份,注意control的選擇為standby的方式了!

[oracle@mytest1 bin]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 2 16:26:36 2014

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

connected to target database: oradgdb (DBID=3596765127)

RMAN> backup as compressed backupset full database include current controlfile for standby format '/u02/dbfull_%T_%s_%p.bak' plus archivelog format '/u02/archfull_%T_%s_%p.bak';


Starting backup at 2012-03-05 16:26:43
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=835806404
channel ORA_DISK_1: starting piece 1 at 2012-03-05 16:26:45
channel ORA_DISK_1: finished piece 1 at 2012-03-05 16:26:46
piece handle=/u02/archfull_20140102_1_1.bak tag=TAG20140102T162645 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2012-03-05 16:26:46

Starting backup at 2012-03-05 16:26:46
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATADG/oradgdb/datafile/system.256.835804411
input datafile file number=00002 name=+DATADG/oradgdb/datafile/sysaux.257.835804411
input datafile file number=00003 name=+DATADG/oradgdb/datafile/undotbs1.258.835804411
input datafile file number=00004 name=+DATADG/oradgdb/datafile/users.259.835804411
channel ORA_DISK_1: starting piece 1 at 2012-03-05 16:26:46
channel ORA_DISK_1: finished piece 1 at 2012-03-05 16:27:21
piece handle=/u02/dbfull_20140102_2_1.bak tag=TAG20140102T162646 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2012-03-05 16:27:23
channel ORA_DISK_1: finished piece 1 at 2012-03-05 16:27:24
piece handle=/u02/dbfull_20140102_3_1.bak tag=TAG20140102T162646 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2012-03-05 16:27:24

Starting backup at 2012-03-05 16:27:24
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=2 STAMP=835806444
channel ORA_DISK_1: starting piece 1 at 2012-03-05 16:27:24
channel ORA_DISK_1: finished piece 1 at 2012-03-05 16:27:25
piece handle=/u02/archfull_20140102_4_1.bak tag=TAG20140102T162724 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2012-03-05 16:27:25

RMAN> quit


Recovery Manager complete.
[oracle@mytest1 bin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 2 16:35:02 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

--建立standby的引數檔案

SQL> create pfile='/u02/standby.ora' from spfile;

File created.

[oracle@mytest1 u02]$ scp standby.ora 10.10.1.52:/u02
password:
standby.ora                                   100%  772     0.8KB/s   00:00   

[oracle@mytest1 u02]$ cd $ORACLE_HOME
[oracle@mytest1 db1]$ cd dbs
[oracle@mytest1 dbs]$ ls -a
.   hc_oradgdb.dat  initoradgdb.ora  orapworadgdb
..  init.ora       lkoradgdb        snapcf_oradgdb.f

--建立完成後,將$ORACLE_HOME/dbs/的所有檔案,以及剛才備份的內容、引數檔案都傳到備節點上!

[oracle@mytest1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db1/dbs
[oracle@mytest1 dbs]$ scp * 10.10.1.52:/u01/app/oracle/product/11.2.0/db1/dbs/
password:
hc_oradgdb.dat                                 100% 1544     1.5KB/s   00:00   
init.ora                                      100% 2851     2.8KB/s   00:00   
initoradgdb.ora                                100%   41     0.0KB/s   00:00   
lkoradgdb                                      100%   24     0.0KB/s   00:00   
orapworadgdb                                   100% 1536     1.5KB/s   00:00   
snapcf_oradgdb.f                               100% 9520KB   9.3MB/s   00:00

[oracle@mytest1 u02]$ scp * 10.10.1.52:/u02
password:
archfull_20140102_1_1.bak                     100% 5679KB   5.6MB/s   00:00   
archfull_20140102_4_1.bak                     100% 3072     3.0KB/s   00:00   
dbfull_20140102_2_1.bak                       100%  278MB  69.5MB/s   00:04   
dbfull_20140102_3_1.bak                       100% 1072KB   1.1MB/s   00:00   
standby.ora                                   100%  772     0.8KB/s   00:00   

輸入密碼等,很快傳完

3、備節點操作,修改引數檔案等

--修改從主節點傳過來的引數檔案

[oracle@mytest2 u02]$ vi standby.ora

*.fal_client='prdb'

*.fal_server='stdb'

*.standby_file_management='AUTO'

注意,這裡的prdb,stdb,這個配置是對應tnsnames裡面配置的別名

加入以上3行,即可。如果檔案路徑不同,還需要加入以下引數
*.db_file_name_convert
*.log_file_name_convert

我本次都使用了相同的路徑,資料使用的是datadg。

--建立備節點引數檔案

[oracle@mytest2 u02]$ export ORACLE_HOME=/u01/app/grid/product/11.2.0/crs
[oracle@mytest2 u02]$ export ORACLE_SID=+ASM
[oracle@mytest2 u02]$ cd /u01/app/grid/product/11.2.0/crs/bin
[oracle@mytest2 bin]$ ./asmcmd
ASMCMD> cd DATADG
ASMCMD> mkdir oradgdb

然後退出後
[oracle@mytest2 u02]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db1
[oracle@mytest2 u02]$ export ORACLE_SID=oradgdb
[oracle@mytest2 u02]$ sqlplus "/as sysdba"


SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 2 16:08:29 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create spfile='+DATADG/oradgdb/spfileoradgdb.ora' from pfile='/u02/standby.ora';

這個路徑可以從主節檢視show parameter spfile。
說明:由於我安裝grid以及oracle rdbms均是採用的oracle使用者,所以在操作過程中需要切換例項等

4、編輯主備節點的tnsnames.ora,listener.ora,sqlnet.ora

注意,這個操作在主備節點均要進行
--編輯tnsnames.ora,sqlnet.ora
[oracle@mytest2 admin]$ vi tnsnames.ora

prdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.51)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oradgdb)
    )
  )


stdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.52)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = oradgdb)
    )
  )
       
sqlnet.ora進行如下編輯即可:

MES.DIRECTORY_PATH= (TNSNAMES)
     
說明,tnsnames.ora,sqlnet.ora主備節點相同,編輯好後,直接相互複製就行了!
注意,在這裡備節點的配置: SID = oradgdb ,有朋友在這裡卡住了,因為在恢復的過程中,備節點只能到nomount狀態,只能用sid連線,否則過不了!

--編輯監聽

主節點監聽:

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.51)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oradgdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db1)
      (SID_NAME = oradgdb)
    )
  )   

備節點監聽:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oradgdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db1)
      (SID_NAME = oradgdb)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.52)(PORT = 1521))
      )
    )
  )


5、開始還原操作

還原操作在主節點進行操作,透過連線備節點的到開始狀態的例項進行

5.1備節點啟動到nomount狀態

SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925

由於oracle11g是預設開啟了audit功能,所以要先建路徑
[oracle@mytest2 bin]$ mkdir -p /u01/app/oracle/admin/oradgdb/adump
[oracle@mytest2 bin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 2 17:19:51 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> startup nomount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 2989854720 bytes
Fixed Size                  2256912 bytes
Variable Size             822087664 bytes
Database Buffers         2147483648 bytes
Redo Buffers               18026496 bytes

到此,備節點已經啟動到nomount狀態了,可以在主節點操作還原了!
另外,特別注意,在備節點、主節點注意關閉防火牆,否則在連線的時候會報以下錯誤!
RMAN> connect auxiliary ;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-12543: TNS:destination host unreachable

同時,監聽也要注意,我們使用crs_stop,停掉監聽,用lsnrctl 開啟!

5.2主節點操作還原資料庫到備節點
[oracle@mytest1 bin]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 2 20:30:04 2014

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

connected to target database: oradgdb (DBID=3596765127)

RMAN> connect auxiliary ;

connected to auxiliary database: oradgdb (DBID=3596765127)

RMAN> duplicate target database for standby nofilenamecheck

Starting Duplicate Db at 2012-03-05 18:02:38
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATADG/oradgdb/controlfile/current.257.835812159'' comment=
 ''Set by RMAN'' scope=spfile";
   restore clone standby controlfile;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATADG/oradgdb/controlfile/current.257.835812159'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 2012-03-05 18:02:39
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u02/dbfull_20140102_3_1.bak
channel ORA_AUX_DISK_1: piece handle=/u02/dbfull_20140102_3_1.bak tag=TAG20140102T162646
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATADG/oradgdb/controlfile/current.258.835812161
Finished restore at 2012-03-05 18:02:41

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATADG in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2012-03-05 18:02:47
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATADG
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATADG
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATADG
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATADG
channel ORA_AUX_DISK_1: reading from backup piece /u02/dbfull_20140102_2_1.bak
channel ORA_AUX_DISK_1: piece handle=/u02/dbfull_20140102_2_1.bak tag=TAG20140102T162646
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 2012-03-05 18:03:43

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=835812223 file name=+DATADG/oradgdb/datafile/system.259.835812169
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=835812223 file name=+DATADG/oradgdb/datafile/sysaux.260.835812169
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=835812223 file name=+DATADG/oradgdb/datafile/undotbs1.261.835812169
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=835812223 file name=+DATADG/oradgdb/datafile/users.262.835812169
Finished Duplicate Db at 2012-03-05 18:03:44

在這裡,可以看到還原已經完成,並且,從備節點上檢視,資料庫已經到mount狀態了

--在備節點檢視,並執行還原
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> recover managed standby database disconnect;

6、在備節點新增standby log

SQL> alter database add standby logfile group 5 size 50m;

Database altered.

SQL> alter database add standby logfile group 6 size 50m;

Database altered.

SQL> alter database add standby logfile group 7 size 50m;

Database altered.

7、引數調整

--主節點操作

SQL> alter system set log_archive_dest_state_2='ENABLE';

System altered.

SQL> alter system set ARCHIVE_LAG_TARGET=3600 scope=both;

System altered.

日誌的切換時間為3600秒,即一個小時,這個可以根據需要來調整

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

System altered.

主節點增加檔案後,備節也主動增加檔案,這個要注意!

SQL> alter system set log_archive_dest_2='SERVICE=stdb lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=stdb';

System altered.

注意傳輸模式的調整,這個根據安全級別來定

8、將備節點啟動到active狀態

SQL> startup mount;

Total System Global Area 2989854720 bytes
Fixed Size                  2256912 bytes
Variable Size             822087664 bytes
Database Buffers         2147483648 bytes
Redo Buffers               18026496 bytes
Database mounted.

SQL> select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
MOUNTED              PHYSICAL STANDBY oradgdb

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

Database altered.

SQL> select open_mode,database_role,db_unique_name from v$database;

OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY oradgdb

可以看到,資料庫已經以只讀的方式開啟了

SQL> select status from v$standby_log;

STATUS
----------
UNASSIGNED
UNASSIGNED
UNASSIGNED

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATADG/oradgdb/onlinelog/group_3.265.835812225
+DATADG/oradgdb/onlinelog/group_2.264.835812223
+DATADG/oradgdb/onlinelog/group_1.263.835812223
+DATADG/oradgdb/onlinelog/group_5.266.835812379
+DATADG/oradgdb/onlinelog/group_6.267.835812395
+DATADG/oradgdb/onlinelog/group_7.268.835812403

9、驗證資料

主節點操作

SQL> create table mytest as select * from dba_users;

Table created.

備節點查詢看一下

SQL> select count(*) from mytest;

  COUNT(*)
----------
        30
       
證明資料已經傳過來了       

主備節點的日誌情況
主節點
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATADG
Oldest online log sequence     20
Next log sequence to archive   22
Current log sequence           22

備節點:
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATADG
Oldest online log sequence     20
Next log sequence to archive   0
Current log sequence           22

完全一致。我們的設定是lgwr的同步模式!

至此配置全部完成

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28869493/viewspace-1661826/,如需轉載,請註明出處,否則將追究法律責任。

相關文章