免费视频下载手机在线_ 單實(shí)例數據庫遷移到rac環(huán)境-DATAGUARD

在前面的3個(gè)小節中，分別介紹了使用expdp，傳輸表空間以及rman的異機恢復等方式實(shí)現單實(shí)例數據庫向rac環(huán)境的遷移，本節中將介紹下使用physical dataguard實(shí)現這一過(guò)程，核心思想是構建基于單實(shí)例數據庫的物理備庫(rac環(huán)境)，而后進(jìn)行主備庫的切換，盡可能的減少遷移數據的時(shí)間消耗和對生產(chǎn)環(huán)境的影響！但同前3種方式相比，遷移的難度也增加了不少！由于文章篇幅限制，本節演示將分為上下兩篇文章。本文主要參考下列的文章，感謝作者分享！
http://space.itpub.net/4227/viewspace-622322

一：環(huán)境介紹
primary數據庫(單實(shí)例環(huán)境)
IP:192.168.1.49/24
操作系統版本：rhel5.4 64位
數據庫版本：10.2.0.5

physical standby數據庫(rac環(huán)境，2個(gè)節點(diǎn))
節點(diǎn)1 IP:192.168.1.41/24
節點(diǎn)2 IP:192.168.1.42/24
操作系統版本：centos4.8 64位
數據庫版本：10.2.0.5

二：配置primary數據庫的tnsnames.ora文件以及dataguard相關(guān)的設置，由于即將構建的備庫為rac環(huán)境，因而需要增加undotbs2表空間和thread 2日志組

[oracle@server49 ~]$ cd $ORACLE_HOME/network/admin
[oracle@server49 ~]$ cat tnsnames.ora
PRIMARY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server49.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary.yang.com)
    )
)

STANDBY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl1)
      (SERVICE_NAME = standby.yang.com)
    )
)

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      orcl
db_unique_name                       string      primary
global_names                         boolean     FALSE
instance_name                        string      orcl
lock_name_space                      string
log_file_name_convert                string
service_names                        string      primary.yang.com

SQL> select name,open_mode,log_mode,force_logging from v$database;

NAME OPEN_MODE LOG_MODE FOR
--------- ---------- ------------ ---
ORCL READ WRITE ARCHIVELOG NO

SQL> alter database force logging;
Database altered.

SQL> select bytes/(1024*1024) MB,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';

MB AUT
---------- ---
30 YES

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 30M autoextend on maxsize 10G;
Tablespace created.

SQL> select bytes/(1024*1024) MB,autoextensible from dba_data_files where tablespace_name in ('UNDOTBS1','UNDOTBS2');

        MB AUT
---------- ---
        30 YES
        30 YES

QL> select a.group#,a.member,b.bytes/(1024*1024) MB,b.thread# from v$logfile a,v$log b where a.group#=b.group#;

SQL> alter database add logfile thread 2 group 4 '/u01/app/oracle/oradata/orcl/redo04.log' size 50M;
Database altered.

SQL> alter database add logfile thread 2 group 5 '/u01/app/oracle/oradata/orcl/redo05.log' size 50M;
Database altered.

SQL> alter database add logfile thread 2 group 6 '/u01/app/oracle/oradata/orcl/redo06.log' size 50M;
Database altered.

SQL> select a.group#,a.member,b.bytes/(1024*1024) MB,b.thread# from v$logfile a,v$log b where a.group#=b.group#;

    GROUP# MEMBER                                           MB    THREAD#
---------- ---------------------------------------- ---------- ----------
         3 /u01/app/oracle/oradata/orcl/redo03.log          50          1
         2 /u01/app/oracle/oradata/orcl/redo02.log          50          1
         1 /u01/app/oracle/oradata/orcl/redo01.log          50          1
         4 /u01/app/oracle/oradata/orcl/redo04.log          50          2
         5 /u01/app/oracle/oradata/orcl/redo05.log          50          2
         6 /u01/app/oracle/oradata/orcl/redo06.log          50          2

SQL> alter database enable thread 2;
Database altered.

SQL> alter system set log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)';
System altered.

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

SQL> !mkdir -p /u01/app/oracle/archive_log
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archive_log VALID_FOR=ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL';
System altered.

SQL> alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
System altered.

SQL> alter system set fal_server=standby;
System altered.

SQL> alter system set fal_client=primary;
System altered.

SQL> alter system set standby_file_management=auto;
System altered.SQL> !mkdir -p /home/oracle/rman_bak

三：在primar庫上生成pfile，并使用rman備份數據庫,將備份的數據復制到rac節點(diǎn)1上

SQL> create pfile='/home/oracle/rman_bak/initorcl.ora' from spfile;
File created.

[oracle@server49 ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Sun Jan 8 14:49:21 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1299224612)

RMAN> run {
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> backup incremental level 0
5> format '/home/oracle/rman_bak/inr0_%U'
6> tag 'full_bak_for_rac_standby'
7> database plus archivelog delete all input;
8> release channel c1;
9> release channel c2;
10> }

RMAN> backup format '/home/oracle/rman_bak/control01.ctl' current controlfile for standby;
[oracle@server49 ~]$ scp -rp rman_bak/ rac1:$(pwd)

四：standby(節點(diǎn)1)數據庫上，同樣配置tnsnames.ora文件，創(chuàng )建密碼文件，修改參數文件

[oracle@rac1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
ORCL1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby.yang.com)
      (INSTANCE_NAME = orcl1)
    )
)

ORCL2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby.yang.com)
      (INSTANCE_NAME = orcl2)
    )
)

ORCL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.yang.com)
    )
)

LISTENERS_ORCL =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))
)
PRIMARY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server49.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary.yang.com)
      (INSTANCE_NAME = orcl)
    )
)

STANDBY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby.yang.com)
      (INSTANCE_NAME = orcl1)
    )
)

[oracle@rac1 ~]$ crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host
----------------------------------------------------------------------
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    rac1
ora....C1.lsnr application    0/5    0/0    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    0/5    0/0    ONLINE    ONLINE    rac1
ora.rac1.ons   application    0/3    0/0    ONLINE    ONLINE    rac1
ora.rac1.vip   application    0/0    0/0    ONLINE    ONLINE    rac1
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    rac2
ora....C2.lsnr application    0/5    0/0    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    0/5    0/0    ONLINE    ONLINE    rac2
ora.rac2.ons   application    0/3    0/0    ONLINE    ONLINE    rac2
ora.rac2.vip   application    0/0    0/0    ONLINE    ONLINE    rac2

[oracle@rac1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapworcl1 password=123456
[oracle@rac1 ~]$ cat /home/oracle/rman_bak/initorcl.ora
*.__db_cache_size=79691776
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__shared_pool_size=109051904
*.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
*.compatible='10.2.0.5.0'
*.control_files='+DATA/ORCL/CONTROLFILE/CONTROL01.CTL','+FRA/ORCL/CONTROLFILE/CONTROL02.CTL'
*.db_block_size=8192
*.db_domain='yang.com'
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=21474836480
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='STANDBY'
*.fal_server='PRIMARY'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'
*.log_archive_dest_1='LOCATION=+FRA/ORCL/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
*.log_archive_dest_2='SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=213909504
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.cluster_database=true
*.cluster_database_instances=2
*.db_unique_name='standby'
*.log_file_name_convert=('/u01/app/oracle/flash_recover_area', '+FRA/ORCL/ARCHIVELOG')
*.db_file_name_convert=('/u01/app/oracle/oradata/orcl', '+DATA/ORCL/DATAFILE')
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'
orcl1.instance_number=1
orcl2.instance_number=2
orcl1.thread=1
orcl2.thread=2
orcl1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.41)(PORT=1521))'
orcl2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.42)(PORT=1521))'

六：在節點(diǎn)1數據庫上恢復控制文件

[oracle@rac1 ~]$ rman target /
connected to target database: orcl (not mounted)
RMAN> restore controlfile from '/home/oracle/rman_bak/control01.ctl';
備注：ASM磁盤(pán)內的相應目錄要先創(chuàng )建好

ASMCMD> pwd
+FRA/ORCL/controlfile
ASMCMD> ls
control02.ctl
ASMCMD> cd +DATA/ORCL/controlfile
ASMCMD> ls
control01.ctl

七：節點(diǎn)1數據庫連接primary進(jìn)行duplicate操作

[oracle@rac1 ~]$ rman target sys/123456@primary auxiliary /
connected to target database: ORCL (DBID=1299224612)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby;
輸出省略

[oracle@rac1 ~]$ rman target /
connected to target database: ORCL (DBID=1299224612, not open)

RMAN> report schema;

Starting implicit crosscheck backup at 2012-01-09 15:35:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
Crosschecked 9 objects
Finished implicit crosscheck backup at 2012-01-09 15:35:31

Starting implicit crosscheck copy at 2012-01-09 15:35:31
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2012-01-09 15:35:31

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

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    450      SYSTEM               ***     +DATA/orcl/datafile/system01.dbf
2    30       UNDOTBS1             ***     +DATA/orcl/datafile/undotbs01.dbf
3    260      SYSAUX               ***     +DATA/orcl/datafile/sysaux01.dbf
4    5        USERS                ***     +DATA/orcl/datafile/users01.dbf
5    100      EXAMPLE              ***     +DATA/orcl/datafile/example01.dbf
6    410      EXP_RAC              ***     +DATA/orcl/datafile/exp_rac01.dbf
7    300      EXP_RAC_INDEX        ***     +DATA/orcl/datafile/exp_rac_index01.dbf
8    300      EXP_RAC1             ***     +DATA/orcl/datafile/exp_rac1_01.dbf
9    300      EXP_RAC1_INDEX       ***     +DATA/orcl/datafile/exp_rac1_index_01.dbf
10   30       UNDOTBS2             ***     +DATA/orcl/datafile/undotbs02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 0 TEMP 32767 +DATA/orcl/datafile/temp01.dbf

ASMCMD> pwd
+data/ORCL/datafile
ASMCMD> ls
example01.dbf
exp_rac01.dbf
exp_rac1_01.dbf
exp_rac1_index_01.dbf
exp_rac_index01.dbf
sysaux01.dbf
system01.dbf
undotbs01.dbf
undotbs02.dbf
users01.dbf

八：?jiǎn)?dòng)節點(diǎn)1數據庫到應用日志模式下，中間若出現歸檔日志的gap，則需要在主庫上restore相應的歸檔日志文件

[oracle@rac1 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 9 15:43:34 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA/orcl/archivelog
Oldest online log sequence     26
Next log sequence to archive   0
Current log sequence           28

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select name,database_role from gv$database;

NAME DATABASE_ROLE
--------------------------- ------------------------------------------------
ORCL PHYSICAL STANDBY

SQL> select first_time,next_time,sequence#,applied from v$archived_log;

FIRST_TIME NEXT_TIME SEQUENCE# APPLIED
------------------- ------------------- ---------- ---------
2012-01-09 14:17:57 2012-01-09 14:52:34 27 NO

[oracle@rac1 ~]$ tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl1.log
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Mon Jan 09 15:44:26 CST 2012
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl1_mrp0_15952.trc:
ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Clearing online redo logfile 1 complete
Media Recovery Waiting for thread 1 sequence 26
Fetching gap sequence in thread 1, gap sequence 26-26
Mon Jan 09 15:44:26 CST 2012
Completed: alter database recover managed standby database disconnect from session
Mon Jan 09 15:45:00 CST 2012
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 26-26
DBID 1299224612 branch 771443882
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------

primary數據庫操作：
RMAN> restore archivelog from sequence 24;

[oracle@server49 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 9 16:15:16 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive_log
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28
SQL> alter system switch logfile;
System altered.

[oracle@rac1 ~]$ tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl1.log
RFS[2]: Assigned to RFS process 28510
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
Mon Jan 09 16:15:46 CST 2012
RFS[1]: Archived Log: '+FRA/orcl/archivelog/1_28_771443882.dbf'
RFS[1]: Archived Log: '+FRA/orcl/archivelog/2_6_771443882.dbf'
Mon Jan 09 16:15:50 CST 2012
Fetching gap sequence in thread 2, gap sequence 5-5
Mon Jan 09 16:16:25 CST 2012
FAL[client]: Failed to request gap sequence
GAP - thread 2 sequence 5-5
DBID 1299224612 branch 771443882
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------

SQL>select first_time,next_time,sequence#,applied from v$archived_log;

FIRST_TIME          NEXT_TIME            SEQUENCE# APPLIED
------------------- ------------------- ---------- ---------
2012-01-09 14:17:57 2012-01-09 14:52:34         27 NO
2012-01-09 14:13:14 2012-01-09 14:17:57         26 NO
2012-01-09 14:52:34 2012-01-09 16:15:37         28 NO
2012-01-09 14:17:56 2012-01-09 16:15:44          6 NO

primary數據庫操作：
RMAN> restore archivelog from sequence 5 thread 2;

節點(diǎn)1數據庫繼續查詢(xún)：
SQL>select first_time,next_time,sequence#,applied from v$archived_log;

FIRST_TIME          NEXT_TIME            SEQUENCE# APPLIED
------------------- ------------------- ---------- ---------
2012-01-09 14:17:57 2012-01-09 14:52:34         27 YES
2012-01-09 14:13:14 2012-01-09 14:17:57         26 YES
2012-01-09 14:52:34 2012-01-09 16:15:37         28 YES
2012-01-09 14:17:56 2012-01-09 16:15:44          6 YES
2012-01-09 14:13:12 2012-01-09 14:17:56          5 YES
2012-01-09 16:15:37 2012-01-09 16:30:00         29 NO
6 rows selected.

上述過(guò)程沒(méi)有問(wèn)題后，就可以啟動(dòng)節點(diǎn)2的數據庫實(shí)例到mount狀態(tài)！
SQL> select name,db_unique_name,open_mode,database_role from gv$database;

NAME                 DB_UNIQUE_ OPEN_MODE            DATABASE_ROLE
-------------------- ---------- -------------------- --------------------
ORCL                 standby    MOUNTED              PHYSICAL STANDBY
ORCL                 standby    MOUNTED              PHYSICAL STANDBY

最后確定下主庫和備庫的日志傳輸和應用狀況是否正常！
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive_log
Oldest online log sequence     34
Next log sequence to archive   36
Current log sequence           36

SQL> select first_time,next_time,sequence#,applied,thread# from v$archived_log order by 5,3 ;

FIRST_TIME          NEXT_TIME            SEQUENCE# APPLIED      THREAD#
------------------- ------------------- ---------- --------- ----------
2012-01-09 14:13:14 2012-01-09 14:17:57         26 YES                1
2012-01-09 14:17:57 2012-01-09 14:52:34         27 YES                1
2012-01-09 14:52:34 2012-01-09 16:15:37         28 YES                1
2012-01-09 16:15:37 2012-01-09 16:30:00         29 YES                1
2012-01-09 16:30:00 2012-01-09 18:04:06         30 YES                1
2012-01-09 18:04:06 2012-01-09 18:04:18         31 YES                1
2012-01-09 18:04:18 2012-01-09 18:06:40         32 YES                1
2012-01-09 18:06:40 2012-01-09 19:55:25         33 YES                1
2012-01-09 19:55:25 2012-01-09 19:55:38         34 YES                1
2012-01-09 19:55:38 2012-01-09 19:56:30         35 NO                 1
2012-01-09 14:13:12 2012-01-09 14:17:56          5 YES                2

FIRST_TIME          NEXT_TIME            SEQUENCE# APPLIED      THREAD#
------------------- ------------------- ---------- --------- ----------
2012-01-09 14:17:56 2012-01-09 16:15:44          6 YES                2
2012-01-09 16:15:44 2012-01-09 18:04:05          7 YES                2
2012-01-09 18:04:05 2012-01-09 18:04:17          8 YES                2
2012-01-09 18:04:17 2012-01-09 18:06:36          9 YES                2
2012-01-09 18:06:36 2012-01-09 19:55:23         10 YES                2
2012-01-09 19:55:23 2012-01-09 19:55:37         11 YES                2
2012-01-09 19:55:37 2012-01-09 19:56:26         12 YES                2
18 rows selected.

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL primary PRIMARY SESSIONS ACTIVE

至此，單實(shí)例主庫和rac物理備庫的環(huán)境以及基本完成，下一節中將介紹下switchover過(guò)程

在前面的章節中，完成了單實(shí)例主庫和rac物理備庫的環(huán)境的構建，本節中將介紹下如何完成主備庫的手動(dòng)角色切換，在這個(gè)場(chǎng)合如果使用data guard broker進(jìn)行切換，會(huì )由于crs的原因而切換失敗，因為數據庫還未向crs進(jìn)行注冊，回顧下在rac環(huán)境上的使用data guard broker的使用條件：在rac環(huán)境下還需要配置DB_BROKER_CONFIG_FILEn參數，將該參數指定共享存儲上，需要在OCR中要設定start_options參數為mount；因而在本例中必須采用手動(dòng)切換，在開(kāi)始之前，需要確保主庫和備庫的日志應用正常！

一：查看主庫和備庫的switchover狀態(tài)，同時(shí)關(guān)閉節點(diǎn)2數據庫

SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STAT
---------- --------------- ------------ --------------- ---------------
ORCL primary READ WRITE PRIMARY SESSIONS ACTIVE

[oracle@rac2 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 9 20:31:14 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database;

NAME DB_UNIQUE_ OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------- ------------ ----------------- --------------------
ORCL standby MOUNTED PHYSICAL STANDBY SESSIONS ACTIVE

二：primary數據庫庫上運行下列的命令后，重新將數據庫啟動(dòng)到mount狀態(tài)，并開(kāi)啟應用日志模式

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

SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 213909504 bytes
Fixed Size                  2095152 bytes
Variable Size             125831120 bytes
Database Buffers           79691776 bytes
Redo Buffers                6291456 bytes

SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STAT
---------- --------------- ------------ ---------------- ---------------
ORCL primary MOUNTED PHYSICAL STANDBY TO PRIMARY

SQL> alter database recover managed standby database cancel;
Database altered.

三：將standby數據庫轉換為主庫，出現下面的錯誤信息，需要手動(dòng)rename下在線(xiàn)日志組

SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory

SQL> alter system set db_create_file_dest='+DATA';
System altered.

SQL> alter system set db_create_online_log_dest_1='+DATA';
System altered.

SQL> alter system set standby_file_management=manual;
System altered.

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '+DATA/ORCL/ONLINELOG/redo01.log';
Database altered.

SQL> alter system set standby_file_management=auto;
System altered.

SQL> alter database commit to switchover to primary;
Database altered.

SQL> alter database open;
Database altered.

SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database;

NAME DB_UNIQUE_ OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------- ------------ ----------------- --------------------
ORCL standby READ WRITE PRIMARY SESSIONS ACTIVE

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STAT
---------- --------------- ------------ ---------------- ---------------
ORCL primary MOUNTED PHYSICAL STANDBY SESSIONS ACTIVE

四：?jiǎn)?dòng)節點(diǎn)2數據庫實(shí)例

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 213909504 bytes
Fixed Size                  2095152 bytes
Variable Size             125831120 bytes
Database Buffers           79691776 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.

五：測試

SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database;

NAME       DB_UNIQUE_ OPEN_MODE    DATABASE_ROLE     SWITCHOVER_STATUS
---------- ---------- ------------ ----------------- --------------------
ORCL       standby    READ WRITE   PRIMARY           SESSIONS ACTIVE
ORCL       standby    READ WRITE   PRIMARY           SESSIONS ACTIVE

SQL> select owner,table_name from dba_tables where owner like 'TEST%';

OWNER                TABLE_NAME
-------------------- --------------------
TEST1                SOURCE
TEST1                MIGRATE
TEST2                SOURCE2

SQL> select count(*) from test1.source;

COUNT(*)
----------
2363928

SQL> select count(*) from test1.migrate;

COUNT(*)
----------
1

SQL> select count(*) from test2.source2;

COUNT(*)
----------
1181964

節點(diǎn)1數據庫實(shí)例：
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA/orcl/archivelog
Oldest online log sequence     47
Next log sequence to archive   48
Current log sequence           48

節點(diǎn)2數據庫實(shí)例：
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA/orcl/archivelog
Oldest online log sequence     22
Next log sequence to archive   24
Current log sequence           24

備庫：
SQL> select first_time,next_time,sequence#,applied,thread# from v$archived_log where thread#=1 and sequence# >40 ;

FIRST_TIME       NEXT_TIME         SEQUENCE# APP    THREAD#
---------------- ---------------- ---------- --- ----------
2012-01-20:24:52 2012-01-20:24:58         41 YES          1
2012-01-20:24:52 2012-01-20:24:58         41 YES          1
2012-01-20:24:58 2012-01-20:25:51         42 YES          1
2012-01-20:24:58 2012-01-20:25:51         42 YES          1
2012-01-20:25:51 2012-01-20:40:18         43 YES          1
2012-01-20:25:51 2012-01-20:40:18         43 YES          1
2012-01-20:40:18 2012-01-20:45:09         44 YES          1
2012-01-20:40:18 2012-01-20:45:09         44 YES          1
2012-01-20:45:09 2012-01-20:49:01         45 YES          1
2012-01-20:45:09 2012-01-20:49:01         45 NO           1
2012-01-20:49:01 2012-01-20:49:43         46 YES          1

FIRST_TIME NEXT_TIME SEQUENCE# APP THREAD#
---------------- ---------------- ---------- --- ----------
2012-01-20:49:01 2012-01-20:49:43 46 NO 1

SQL> select first_time,next_time,sequence#,applied,thread# from v$archived_log where thread#=2 and sequence# >20 ;

主庫上切換日志測試日志是否能成功應用到備庫
SQL> alter system archive log current;
System altered.

SQL> alter system archive log current;
System altered.

備庫查詢(xún)：
SQL> select first_time,next_time,sequence#,applied,thread# from v$archived_log where thread#=2 and sequence# >20 ;

FIRST_TIME       NEXT_TIME         SEQUENCE# APP    THREAD#
---------------- ---------------- ---------- --- ----------
2012-01-20:45:11 2012-01-20:49:41         21 YES          2
2012-01-20:45:11 2012-01-20:49:41         21 NO           2
2012-01-21:09:32 2012-01-21:10:58         22 YES          2
2012-01-21:14:02 2012-01-21:14:13         23 YES          2
2012-01-21:14:13 2012-01-21:25:03         24 YES          2
2012-01-21:25:03 2012-01-21:29:51         25 NO           2

SQL> select first_time,next_time,sequence#,applied,thread# from v$archived_log where thread#=1 and sequence# >40 ;

FIRST_TIME       NEXT_TIME         SEQUENCE# APP    THREAD#
---------------- ---------------- ---------- --- ----------
2012-01-20:49:01 2012-01-20:49:43         46 NO           1
2012-01-21:09:32 2012-01-21:10:51         47 YES          1
2012-01-21:10:51 2012-01-21:25:05         48 YES          1
2012-01-21:25:05 2012-01-21:29:48         49 YES          1

主庫建表測試，同時(shí)切換日志
SQL> create table test1.maa as select * from test1.migrate;
Table created.

SQL> alter system archive log current;
System altered.

備庫的alert日志摘要信息
[oracle@server49 ~]$ tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log
Mon Jan 09 21:33:39 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
Mon Jan 09 21:33:40 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
Mon Jan 09 21:33:41 CST 2012
Media Recovery Log /u01/app/oracle/flash_recovery_area/PRIMARY/archivelog/2012_01_09/o1_mf_1_50_7jotz6nc_.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/PRIMARY/archivelog/2012_01_09/o1_mf_2_26_7jotz6wr_.arc
Media Recovery Waiting for thread 2 sequence 27 (in transit)

將備庫啟動(dòng)到只讀狀態(tài)，查看數據
SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database open read only;
Database altered.

SQL> select * from test1.maa;

A
--------------------
successful

SQL> alter database recover managed standby database disconnect from session;
Database altered.

六：收尾工作

SQL> @$ORACLE_HOME/rdbms/admin/catclust.sql;
PL/SQL procedure successfully completed.

[oracle@rac1 ~]$ srvctl add database -d orcl -o $ORACLE_HOME
[oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl1 -n rac1
[oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl1 -n rac2
[oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl2 -n rac2
[oracle@rac1 ~]$ crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host
----------------------------------------------------------------------
ora.orcl.db    application    0/0    0/1    ONLINE    ONLINE    rac1
ora....l1.inst application    0/5    0/0    ONLINE    ONLINE    rac1
ora....l2.inst application    0/5    0/0    ONLINE    ONLINE    rac2
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    rac1
ora....C1.lsnr application    0/5    0/0    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    0/5    0/0    ONLINE    ONLINE    rac1
ora.rac1.ons   application    0/3    0/0    ONLINE    ONLINE    rac1
ora.rac1.vip   application    0/0    0/0    ONLINE    ONLINE    rac1
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    rac2
ora....C2.lsnr application    0/5    0/0    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    0/5    0/0    ONLINE    ONLINE    rac2
ora.rac2.ons   application    0/3    0/0    ONLINE    ONLINE    rac2
ora.rac2.vip   application    0/0    0/0    ONLINE    ONLINE    rac2

本站僅提供存儲服務(wù)，所有內容均由用戶(hù)發(fā)布，如發(fā)現有害或侵權內容，請點(diǎn)擊舉報。

欧美性猛交XXXX免费看蜜桃,成人网18免费韩国,亚洲国产成人精品区综合,欧美日韩一区二区三区高清不卡,亚洲综合一区二区精品久久