Oracle ADG構築

19511 ワード

Oracle Active Data Gard構築
一：据え付け
1.基礎環境の配置
1.1.強制ログ記録を開く

DGログ送信方式では、ARCHプロセスとLGWRプロセスのASYNCモードは、ログ同期に基づいていますので、データベースのすべての操作をログ

に強制的に記録しなければなりません。
RAC 2：ライブラリを閉じる

  SQL> shutdown
  Database closed.
  Database dismounted.
  ORACLE instance shut down.

RAC 1：強制ログ記録を開く

  SQL> alter database force logging;   

  Database altered.

ALLノード：rac 2をオープンし、ログ記録モードを検証する。

  RAC1
  SQL> select log_mode,force_logging from v$database;

  LOG_MODE     FOR
  ------------ ---
  ARCHIVELOG   YES

  RAC2
  SQL> startup  
  ORACLE instance started.

  Total System Global Area 2020970496 bytes
  Fixed Size            2214776 bytes
  Variable Size      1224737928 bytes
  Database Buffers    788529152 bytes
  Redo Buffers          5488640 bytes
  Database mounted.
  Database opened.
  SQL> select log_mode,force_logging from v$database;

  LOG_MODE     FOR
  ------------ ---
  ARCHIVELOG   YES

1.2.メインライブラリのバックアップ

rmanバックアップディレクトリ

を作成します。

  [root@racnode1 u01]# mkdir rmanbak
  [root@racnode1 u01]# ls
  app  rmanbak
  [root@racnode1 u01]# chown oracle:oinstall rmanbak

.rmanを使ってdatabaseとarchive logsをバックアップします。

  [oracle@racnode1 ~]$ rman target /

  Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 9 10:26:21 2017

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

  connected to target database: ORCL (DBID=1479188731)

  RMAN> run {
  allocate channel c1 type disk;
  allocate channel c2 type disk;
  allocate channel c3 type disk;
  allocate channel c4 type disk;
  backup database format '/u01/rmanbak/FULL_%U.bak';
  backup archivelog all format '/u01/rmanbak/ARC_%U.bak';
  release channel c1;
  release channel c2;
  release channel c3;
  release channel c4;
  }

  using target database control file instead of recovery catalog
  allocated channel: c1
  channel c1: SID=148 instance=orcl1 device type=DISK

  allocated channel: c2
  channel c2: SID=29 instance=orcl1 device type=DISK

  allocated channel: c3
  channel c3: SID=156 instance=orcl1 device type=DISK

  allocated channel: c4
  channel c4: SID=32 instance=orcl1 device type=DISK

  Starting backup at 09-AUG-2017 10:26:44
  channel c1: starting full datafile backup set
  channel c1: specifying datafile(s) in backup set
  input datafile file number=00001 name=+DATA/orcl/datafile/system.256.951407925
  channel c1: starting piece 1 at 09-AUG-2017 10:26:44
  channel c2: starting full datafile backup set
  channel c2: specifying datafile(s) in backup set
  input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.951407925
  input datafile file number=00004 name=+DATA/orcl/datafile/users.259.951407925
  channel c2: starting piece 1 at 09-AUG-2017 10:26:44
  channel c3: starting full datafile backup set
  channel c3: specifying datafile(s) in backup set
  input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.951407925
  input datafile file number=00005 name=+DATA/orcl/datafile/undotbs2.264.951408055
  channel c3: starting piece 1 at 09-AUG-2017 10:26:45
  channel c4: starting full datafile backup set
  channel c4: specifying datafile(s) in backup set
  including current control file in backup set
  channel c4: starting piece 1 at 09-AUG-2017 10:26:52
  channel c3: finished piece 1 at 09-AUG-2017 10:26:56
  piece handle=/u01/rmanbak/FULL_03sbfaj4_1_1.bak tag=TAG20170809T102644 comment=NONE
  channel c3: backup set complete, elapsed time: 00:00:11
  channel c3: starting full datafile backup set
  channel c3: specifying datafile(s) in backup set
  including current SPFILE in backup set
  channel c3: starting piece 1 at 09-AUG-2017 10:26:57
  channel c4: finished piece 1 at 09-AUG-2017 10:26:57
  piece handle=/u01/rmanbak/FULL_04sbfaj5_1_1.bak tag=TAG20170809T102644 comment=NONE
  channel c4: backup set complete, elapsed time: 00:00:05
  channel c1: finished piece 1 at 09-AUG-2017 10:26:57
  piece handle=/u01/rmanbak/FULL_01sbfaj4_1_1.bak tag=TAG20170809T102644 comment=NONE
  channel c1: backup set complete, elapsed time: 00:00:13
  channel c2: finished piece 1 at 09-AUG-2017 10:26:58
  piece handle=/u01/rmanbak/FULL_02sbfaj4_1_1.bak tag=TAG20170809T102644 comment=NONE
  channel c2: backup set complete, elapsed time: 00:00:14
  channel c3: finished piece 1 at 09-AUG-2017 10:26:58
  piece handle=/u01/rmanbak/FULL_05sbfajh_1_1.bak tag=TAG20170809T102644 comment=NONE
  channel c3: backup set complete, elapsed time: 00:00:01
  Finished backup at 09-AUG-2017 10:26:58

  Starting backup at 09-AUG-2017 10:26:59
  current log archived
  channel c1: starting archived log backup set
  channel c1: specifying archived log(s) in backup set
  input archived log thread=1 sequence=5 RECID=1 STAMP=951408120
  input archived log thread=2 sequence=1 RECID=2 STAMP=951408150
  input archived log thread=1 sequence=6 RECID=5 STAMP=951447623
  channel c1: starting piece 1 at 09-AUG-2017 10:26:59
  channel c2: starting archived log backup set
  channel c2: specifying archived log(s) in backup set
  input archived log thread=2 sequence=2 RECID=3 STAMP=951408156
  input archived log thread=2 sequence=3 RECID=4 STAMP=951447623
  input archived log thread=2 sequence=4 RECID=8 STAMP=951555111
  channel c2: starting piece 1 at 09-AUG-2017 10:26:59
  channel c3: starting archived log backup set
  channel c3: specifying archived log(s) in backup set
  input archived log thread=1 sequence=7 RECID=6 STAMP=951516208
  input archived log thread=1 sequence=8 RECID=7 STAMP=951555108
  channel c3: starting piece 1 at 09-AUG-2017 10:26:59
  channel c4: starting archived log backup set
  channel c4: specifying archived log(s) in backup set
  input archived log thread=1 sequence=9 RECID=11 STAMP=951560819
  input archived log thread=2 sequence=5 RECID=9 STAMP=951558975
  input archived log thread=2 sequence=6 RECID=10 STAMP=951560429
  channel c4: starting piece 1 at 09-AUG-2017 10:26:59
  channel c1: finished piece 1 at 09-AUG-2017 10:27:00
  piece handle=/u01/rmanbak/ARC_06sbfajj_1_1.bak tag=TAG20170809T102659 comment=NONE
  channel c1: backup set complete, elapsed time: 00:00:01
  channel c2: finished piece 1 at 09-AUG-2017 10:27:00
  piece handle=/u01/rmanbak/ARC_07sbfajj_1_1.bak tag=TAG20170809T102659 comment=NONE
  channel c2: backup set complete, elapsed time: 00:00:01
  channel c3: finished piece 1 at 09-AUG-2017 10:27:00
  piece handle=/u01/rmanbak/ARC_08sbfajj_1_1.bak tag=TAG20170809T102659 comment=NONE
  channel c3: backup set complete, elapsed time: 00:00:01
  channel c4: finished piece 1 at 09-AUG-2017 10:27:00
  piece handle=/u01/rmanbak/ARC_09sbfajj_1_1.bak tag=TAG20170809T102659 comment=NONE
  channel c4: backup set complete, elapsed time: 00:00:01
  Finished backup at 09-AUG-2017 10:27:00

  released channel: c1

  released channel: c2

  released channel: c3

  released channel: c4

.バックアップの検証

  [oracle@racnode1 ~]$ ll -th /u01/rmanbak/
      1.3G
  -rw-r----- 1 oracle asmadmin  89M 8    9 10:27 ARC_08sbfajj_1_1.bak
  -rw-r----- 1 oracle asmadmin  81M 8    9 10:27 ARC_07sbfajj_1_1.bak
  -rw-r----- 1 oracle asmadmin 4.5M 8    9 10:26 ARC_09sbfajj_1_1.bak
  -rw-r----- 1 oracle asmadmin  30M 8    9 10:26 ARC_06sbfajj_1_1.bak
  -rw-r----- 1 oracle asmadmin 424M 8    9 10:26 FULL_02sbfaj4_1_1.bak
  -rw-r----- 1 oracle asmadmin  96K 8    9 10:26 FULL_05sbfajh_1_1.bak
  -rw-r----- 1 oracle asmadmin 588M 8    9 10:26 FULL_01sbfaj4_1_1.bak
  -rw-r----- 1 oracle asmadmin  18M 8    9 10:26 FULL_04sbfaj5_1_1.bak
  -rw-r----- 1 oracle asmadmin 3.6M 8    9 10:26 FULL_03sbfaj4_1_1.bak

1.3.PrimaryにStandby Databaseの制御ファイルを作成する

SQL> alter database create standby controlfile as'/u01/rmanbak/standby.ctl';

Database altered.

1.4.PrimaryにStandby Database初期化パラメータファイルを作成する

SQL> create pfile='/u01/rmanbak/initphyracdb.ora' from spfile;

File created.

1.5.パスワードファイルをコピーする

[oracle@racnode1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@racnode1 dbs]$ cp orapworcl1 /u01/rmanbak/

1.6.バックアップファイルをすべて準備庫にコピー/rmanbakでコピーする

[oracle@racnode1 rmanbak]$ scp * [email protected]:/u01/rmanbak/
The authenticity of host '192.168.30.134 (192.168.30.134)' can't be established.
RSA key fingerprint is 43:e6:68:ce:37:01:1f:12:46:19:27:39:a5:ba:02:7d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.30.134' (RSA) to the list of known hosts.
[email protected]'s password: 
ARC_06sbfajj_1_1.bak                                     100%   29MB  29.4MB/s   00:00    
ARC_07sbfajj_1_1.bak                                     100%   81MB  80.8MB/s   00:00    
ARC_08sbfajj_1_1.bak                                     100%   88MB  88.0MB/s   00:01    
ARC_09sbfajj_1_1.bak                                     100% 4598KB   4.5MB/s   00:00    
FULL_01sbfaj4_1_1.bak                                    100%  587MB 117.4MB/s   00:05    
FULL_02sbfaj4_1_1.bak                                    100%  424MB  84.8MB/s   00:05    
FULL_03sbfaj4_1_1.bak                                    100% 3680KB   3.6MB/s   00:00    
FULL_04sbfaj5_1_1.bak                                    100%   18MB  17.7MB/s   00:01    
FULL_05sbfajh_1_1.bak                                    100%   96KB  96.0KB/s   00:00    
initphyracdb.ora                                         100% 1390     1.4KB/s   00:00    
orapworcl1                                               100% 1536     1.5KB/s   00:00    
standby.ctl                                              100%   18MB  17.6MB/s   00:00

1.7.予備ライブラリにコピーした初期化ファイルを修正する

orcl.__db_cache_size=771751936
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=822083584
orcl.__sga_target=1207959552
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=385875968
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
#         
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/orcl'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
orcl.instance_number=1
#     
*.log_archive_config='dg_config=(orcl,rac)'
#     
*.log_archive_dest_1='LOCATION=/u01/archive_log VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
#     
*.log_archive_dest_2='SERVICE=rac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rac'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=5
*.memory_target=2030043136
*.open_cursors=300
*.processes=150
#       ，    
*.remote_login_passwordfile='exclusive'
orcl.thread=1
*.service_names='racdg'
*.standby_file_management='auto'
orcl.undo_tablespace='UNDOTBS1'
# dbf      
*.db_file_name_convert='+DATA/ORCL/DATAFILE','/u01/app/oracle/oradata/orcl'
*.db_unique_name='orcl'
#     【      】
*.fal_client='orcl'
#    【      】
*.fal_server='rac1','rac2'
#         
*.log_file_name_convert='+REDO/ORCL','/u01/archive_log','+DATA/orcl','/u01/group_log'

1.8.修正後の初期化ファイルを使って準備ライブラリをnomount状態に起動する

SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initphyracdb.ora' nomount;
ORACLE instance started.

Total System Global Area 2020970496 bytes
Fixed Size          2254664 bytes
Variable Size        1224739000 bytes
Database Buffers      788529152 bytes
Redo Buffers            5447680 bytes

1.9.在庫回復制御ファイル

restore controlfile from '/u01/rmanbak/standby.ctl';

1.10.準備ライブラリをmount状態に切り替える

SQL> alter database mount;

Database altered.

SQL> select  status from v$instance;

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

1.20.rmanを使ってメインライブラリのデータを準備ライブラリに戻す
現在のディレクトリのバックアップファイルは自動的に認識されます。

RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
restore database ;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
} 

released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=129 device type=DISK

allocated channel: c2
channel c2: SID=193 device type=DISK

allocated channel: c3
channel c3: SID=7 device type=DISK

allocated channel: c4
channel c4: SID=69 device type=DISK

Starting restore at 09-AUG-17
Starting implicit crosscheck backup at 09-AUG-17
Crosschecked 9 objects
Finished implicit crosscheck backup at 09-AUG-17

Starting implicit crosscheck copy at 09-AUG-17
Finished implicit crosscheck copy at 09-AUG-17

searching for all files in the recovery area
cataloging files...
no files cataloged


channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl//undotbs1.258.951407925
channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl//undotbs2.264.951408055
channel c1: reading from backup piece /u01/rmanbak/FULL_03sbfaj4_1_1.bak
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00001 to /u01/app/oracle/oradata/orcl//system.256.951407925
channel c2: reading from backup piece /u01/rmanbak/FULL_01sbfaj4_1_1.bak
channel c3: starting datafile backup set restore
channel c3: specifying datafile(s) to restore from backup set
channel c3: restoring datafile 00002 to /u01/app/oracle/oradata/orcl//sysaux.257.951407925
channel c3: restoring datafile 00004 to /u01/app/oracle/oradata/orcl//users.259.951407925
channel c3: reading from backup piece /u01/rmanbak/FULL_02sbfaj4_1_1.bak
channel c1: piece handle=/u01/rmanbak/FULL_03sbfaj4_1_1.bak tag=TAG20170809T102644
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
channel c3: piece handle=/u01/rmanbak/FULL_02sbfaj4_1_1.bak tag=TAG20170809T102644
channel c3: restored backup piece 1
channel c3: restore complete, elapsed time: 00:00:07
channel c2: piece handle=/u01/rmanbak/FULL_01sbfaj4_1_1.bak tag=TAG20170809T102644
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:15
Finished restore at 09-AUG-17

released channel: c1

released channel: c2

released channel: c3

released channel: c4

1.11.予備ライブラリを追加したロググループ

SQL> alter database add standby logfile thread 1 group 5 size 50M,group 6 size 50M,group 7 size 50M;

Database altered.

SQL> alter database add standby logfile thread 2 group 8 size 50M,group 9 size 50M,group 10 size 50M;

Database altered.

1.12.マスタパラメータ調整

#         
SQL> alter system set log_archive_dest_1='LOCATION=+REDO VALID_FOR=(ALL_LOGFILES,ALL_ROLES)';

System altered.

#         
SQL> alter system set log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl';

System altered.


#       
SQL> show parameter log_archive

#         
SQL> alter system set log_archive_dest_state_2=enable;

System altered.

1.13.準備ライブラリのアプリケーションログを開く

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

Database altered.

前のコマンドを実行した後、Standby databaseは遅延伝送であり、アーカイブ時にのみアプリケーションが発生しますので、ログアプリケーションの状況を調べると、現在のログにIN MEMORYが表示されます。

SQL> select thread#, sequence#, applied from v$archived_log;
   THREAD#  SEQUENCE# APPLIED
---------- ---------- ---------
     1    132 YES
     2    126 YES
     1    133 YES
     1    134 IN-MEMORY

1.14.ADGのオープン
ADG：11 Gの新特性は、備庫上ですでにopen状態であり、ログアプリケーションを開いて、デフォルトではREAD ONLY WITH APPLY状態モードであり、アプリケーションログの同時にstandby databaseを読んで照会することができます。11 G間でログを適用する場合は、mountモードでのみ開くことができます。
スタンダーズ

キャンセルログアプリケーション

  SQL> alter database recover managed standby database cancel;

  Database altered.

オープンモードでデータベース

を開く

  SQL> alter database open;

  Database altered.

オープンログアプリケーション

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

  Database altered.

データベースモードが正常かどうかを問い合わせる

  SQL> select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

        DBID NAME      OPEN_MODE          CURRENT_SCN PROTECTION_MODE
  ---------- --------- -------------------- ----------- --------------------
  DATABASE_ROLE  FOR OPEN_MODE        SWITCHOVER_STATUS
  ---------------- --- -------------------- --------------------
  1479188731 ORCL      READ ONLY WITH APPLY     3638179 MAXIMUM PERFORMANCE
  PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

2.DG状態が正常か確認する
2.1.archive logsシーケンスが一致しているか確認する
Primary Database

SQL> archive log list;
Database log mode          Archive Mode
Automatic archival         Enabled
Archive destination        +REDO
Oldest online log sequence     21
Next log sequence to archive   22
Current log sequence           22

スタンダーズ

SQL> archive log list;
Database log mode          Archive Mode
Automatic archival         Enabled
Archive destination        /u01/app/oracle/arch
Oldest online log sequence     21
Next log sequence to archive   0
Current log sequence           22

2.2.DGの状態を確認する
Primary Database

SQL> select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

      DBID NAME      OPEN_MODE        CURRENT_SCN PROTECTION_MODE
---------- --------- -------------------- ----------- --------------------
DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS
---------------- --- -------------------- --------------------
1479188731 ORCL      READ WRITE           1407507 MAXIMUM PERFORMANCE
PRIMARY      YES READ WRITE       TO STANDBY

PROTECTION_MODE：MAXIMUM PER FOREMANCEデフォルト最大性能モード

DATABASE_ROLE：PRIMARYキャラクター

SWEET OVER_STATUS：切替状態（現在のDG状態が確認できます）

NOT ALLOWEDカレントライブラリ

TO STANDBYカレントライブラリを準備ライブラリ

に切り替えることができます。

RESOLVABLE GAPマスターライブラリと予備庫の間にGAPギャップ

があります。
スタンダーズ
2.3.認証ログアプリケーション

select thread#, sequence#, applied from v$archived_log;

2.4.該当プロセスが起きているか確認する

select process from v$managed_standby;

転載先:https://www.cnblogs.com/GXLo/p/7493047.html

ユニオン共同クエリ