VMware中配置ORACLE DATAGUARD步驟

Vmware 中配置和建立ORACLE 10.2.0.1的physical standby資料庫

一、 配置條件

1.1、 軟體條件

Vmware workstation ORACLE 10.2.0.1

1.2、 硬體條件

RAC2 (192.168.188.54） primary

RAC1 (192.168.188.58) standby

1.3、伺服器配置的監聽

二、前提服務的配置

在（192.168.188.54）primary上配置資料庫軟體和資料庫SID（NACEC）

在（192.168.188.58）standby上配置和primary上ORACLE安裝的那些相同的目錄

2.1、建立$ORACLE_BASE下的目錄一樣

由於我們的機器是安裝RAC資料庫的primary和standby的目錄基本上是相同的。

我們為了省事，我們直接的從primary(RAC2)的機器上把/home/DBSoftware/oracle/admin/NACEC COPY到standby(RAC1)的機器上/home/DBSoftware/oracle/admin 目錄下來

[oracle@rac2 admin]$ pwd

/home/DBSoftware/oracle/admin

[oracle@rac2 admin]$ ls

NACEC

把RAC2機器上/home/DBSoftware/oracle/admin/NACEC COPY 到RAC1下的/home/DBSoftware/oracle/admin/NACEC

[oracle@rac1 admin]$ pwd

/home/DBData/admin

[oracle@rac1 admin]$ scp -r rac2: /home/DBSoftware/oracle/admin/NACEC

[oracle@rac1 admin]$ ls

NACEC

$ORACLE_BASE的目錄就這樣建立無全一樣了。

2.2、建立$ORACLE_DATA下的目錄一樣

由於primary(RAC2)的資料檔案目錄在/home/DBData/oradata/NACEC下

[oracle@rac2 admin]$ ls

NACEC

[oracle@rac2 admin]$ cd ..

[oracle@rac2 DBData]$ ls

oradata

[oracle@rac2 app]$ cd oradata

[oracle@rac2 oradata]$ ls

NACEC

[oracle@rac2 oradata]$ pwd

/home/DBData/oradata

那麼我們就在standby(RAC1)上建立和primary(RAC2)相同的資料檔案目錄

[oracle@rac1 oradata]$ pwd

/home/DBData/oradata

[oracle@rac1 oradata]$ mkdir NACEC

[oracle@rac1 oradata]$ ls

NACEC

2.3、在primary(RAC2)上建立資料庫的歸檔日誌目錄和rman備份檔案的目錄

[oracle@rac2 oradata]$ ls

NACEC

[oracle@rac2 oradata]$ mkdir archive ----/放（RAC2）歸檔日誌的

[oracle@rac2 oradata]$ mkdir backup ----/放（RAC2）的RMAN備份檔案

[oracle@rac2 oradata]$ ls

archive backup NACEC

那麼我就在（RAC1）standby建立和RAC2相同的目錄結構

[oracle@rac1 oradata]$ mkdir archive

[oracle@rac1 oradata]$ mkdir backup

2.4、在主庫上建立pfile引數，並且修改其引數

2.4.1、修改（RAC2）primary資料庫到歸檔方式

[oracle@rac2 dbs]$ export ORACLE_SID=NACEC

[oracle@rac2 dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 9 16:45:29 2010

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1260696 bytes

Variable Size 88081256 bytes

Database Buffers 75497472 bytes

Redo Buffers 2932736 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database force logging;

Database altered.

2.4.2、修改（RAC2）primary資料庫上的PFILE

SQL> create pfile='$ORACLE_HOME/dbs/initNACEC.ora' from spfile;

File created.

我們近來編輯$ORACLE_HOME/dbs/initNACEC.ora檔案

NACEC.__db_cache_size=75497472

NACEC.__java_pool_size=4194304

NACEC.__large_pool_size=4194304

NACEC.__shared_pool_size=79691776

NACEC.__streams_pool_size=0

*.audit_file_dest='/home/DBSoftware/oracle/admin/NACEC/adump'

*.background_dump_dest='/home/DBSoftware/oracle/admin/NACEC /bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/DBData/oradata/NACEC/control01.ctl','/home/DBData/oradata/NACEC/control02.ctl','/home/DBData/oradata/NACEC /control03.ctl'

*.core_dump_dest='/home/DBSoftware/oracle/admin/NACEC/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='NACEC'

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

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/DBSoftware/oracle/admin/NACEC/udump'

###############################################

#####DATA GUARD ---primary database

###############################################

*.db_unique_name='nacecprim'

*.log_archive_config='DG_CONFIG=(primary,standby)'

*.log_archive_dest_1='location=/home/DBData/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=nacecprim

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

*.log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=nacecstdb

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.log_file_name_convert='/home/DBData/oradata/archive','/home/DBData/oradata/archive'

*.db_file_name_convert='/home/DBData/oradata/NACEC','/home/DBData/oradata/NACEC'

#*.standby_archive_dest='/home/DBData/oradata/archive'

*.standby_file_management='AUTO'

*.fal_server='nacecprim'

*.fal_client= ’nacecstdb’

###############################################

#####DATA GUARD ---primary database引數的解釋

###############################################

*.db_unique_name='primary' ####這個是主資料庫的網路服務名（NET）

*.log_archive_config='DG_CONFIG=(nacecprim,nacecstdb)' ##是主庫和備用庫的網路服務名

*.log_archive_dest_1='location=/home/DBData/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=nacecprim' ##本地的日誌應該是放在本地的db_unique_name=主庫的服務名

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

*.log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=nacecstdb' ##通過日誌傳輸到備用庫db_unique_name=備用庫的網路服務名

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.log_file_name_convert='/home/DBData/oradata/archive','/home/ DBData /oradata/archive'

*.db_file_name_convert='/home/ DBData /oradata/NACEC,'/home/ DBData /oradata/NACEC

#*.standby_archive_dest='/home/ DBData /oradata/archive'

*.standby_file_management='AUTO'

*.fal_server='nacecprim' ###主庫的網路服務名

*.fal_client='nacecstdb' ###備用庫的網路服務名

儲存，關閉資料庫然後用這個引數重新啟動資料庫。OK。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1260696 bytes

Variable Size 92275560 bytes

Database Buffers 71303168 bytes

Redo Buffers 2932736 bytes

Database mounted.

Database opened.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /home/ DBData /oradata/archive

Oldest online log sequence 8

Next log sequence to archive 10

Current log sequence 10

發現引數正確。OK。

2.5、在備用庫上建立pfile引數，並修改其引數

同樣的我們要在備用庫（RAC1） standby資料庫建立initNACEC.ora檔案，並要編輯他

我們可以從（RAC2）primary上COPE一個initNACEC.ora檔案到$ORACLE_HOME/dbs/下。

[oracle@rac1 dbs]$ scp rac2:$ORACLE_HOME/dbs/initNACEC.ora .

initNACEC.ora

100% 1555 1.5KB/s 00:00

現在我們就來修改standby（RAC1）上的initNACEC.ora檔案，修改如下：

NACEC.__db_cache_size=75497472

NACEC.__java_pool_size=4194304

NACEC.__large_pool_size=4194304

NACEC.__shared_pool_size=79691776

NACEC.__streams_pool_size=0

*.audit_file_dest='/home/DBSoftware/oracle/admin/ NACEC /adump'

*.background_dump_dest='/home/DBSoftware/oracle/admin/ NACEC /bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/DBData/oradata/ NACEC /control01.ctl','/home/DBData/oradata/ NACEC /control02.ctl','/home/DBData/oradata/ NACEC /control03.ctl'

*.core_dump_dest='/home/DBSoftware/oracle/admin/ NACEC /cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='NACEC'

*.dispatchers='(PROTOCOL=TCP) (SERVICE= NACEC XDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/DBSoftware/oracle/admin/ NACEC /udump'

###############################################

#####DATA GUARD --standby database

###############################################

*.db_unique_name='nacecstdb'

*.log_archive_config='DG_CONFIG=(nacecprim,nacecstdb)'

*.log_archive_dest_1='location=/home/DBData/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=nacecstdb'

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

*.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=nacecprim'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.log_file_name_convert='/home/DBData/oradata/archive','/home/ DBData /oradata/archive'

*.db_file_name_convert='/home/ DBData /oradata/NACEC,'/home/ DBData /oradata/ NACEC,

#*.standby_archive_dest='/home/ DBData /oradata/archive'

*.standby_file_management='AUTO'

*.fal_server='nacecstdb'

*.fal_client='nacecprim'

###############################################

#####DATA GUARD --standby database備用庫引數的解釋

###############################################

*.db_unique_name='standby' ###備用庫網路服務名

*.log_archive_config='DG_CONFIG=(nacecprim,nacecstdb)' ###主庫和備用庫的網路服務名

*.log_archive_dest_1='location=/home/DBData/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=nacecstdb'

###備用庫的網路服務名

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

*.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=nacecprim'

##主庫的網路服務名

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.log_file_name_convert='/home/DBData/oradata/archive','/home/ DBData /oradata/archive'

*.db_file_name_convert='/home/ DBData /oradata/NACEC','/home/ DBData /oradata/NACEC'

#*.standby_archive_dest='/home/ DBData /oradata/archive'

*.standby_file_management='AUTO'

*.fal_server='nacecstdb' ####備用庫的網路服務名

*.fal_client='nacecprim' ####主庫的網路服務名

2.6、在備用庫上建立備用庫的password檔案

我們可以從（RAC2）primary上COPY 密碼檔案過來

[oracle@rac1 dbs]$ scp rac2:$ORACLE_HOME/dbs/orapwNACEC .

orapwNACEC

100% 1536 1.5KB/s 00:00

三、通過rman備份primary資料庫，然後恢復到standby資料庫上

3.1、通過RMAN備份primary資料庫

在主庫上操作（primary）

[oracle@rac2 dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Dec 11 13:40:54 2010

connected to target database: NACEC (DBID=1771818397)

為備用庫，備份控制檔案和全庫。

RMAN> run {

2> allocate channel c1 type disk;

3> backup tag='fulldatabase' format '/home/DBData/oradata/backup/full_%U_%I.dbf' database;

4> backup current controlfile for standby tag='controlfile' format '/home/DBData/oradata/backup/control_%U_%I.ctl';

5> }

released channel: ORA_DISK_1

allocated channel: c1

channel c1: sid=143 devtype=DISK

Starting backup at 14-SEP-10

channel c1: starting full datafile backupset

channel c1: specifying datafile(s) in backupset

input datafile fno=00001 name=/home/DBData/oradata/NACEC/system01.dbf

input datafile fno=00003 name=/home/DBData/oradata/NACEC/sysaux01.dbf

input datafile fno=00002 name=/home/DBData/oradata/NACEC/undotbs01.dbf

input datafile fno=00004 name=/home/DBData/oradata/NACEC/users01.dbf

channel c1: starting piece 1 at 14-SEP-10

channel c1: finished piece 1 at 14-SEP-10

piece handle=/home/DBData/oradata/backup/full_05k206eg_1_1_1771818397.dbf tag=FULLDATABASE comment=NONE

channel c1: backup set complete, elapsed time: 00:01:05

channel c1: starting full datafile backupset

channel c1: specifying datafile(s) in backupset

including current control file in backupset

channel c1: starting piece 1 at 14-SEP-10

channel c1: finished piece 1 at 14-SEP-10

piece handle=/home/DBData/oradata/backup/full_06k206gh_1_1_1771818397.dbf tag=FULLDATABASE comment=NONE

channel c1: backup set complete, elapsed time: 00:00:04

Finished backup at 14-SEP-10

Starting backup at 14-SEP-10

channel c1: starting full datafile backupset

channel c1: specifying datafile(s) in backupset

including standby control file in backupset

channel c1: starting piece 1 at 14-SEP-10

channel c1: finished piece 1 at 14-SEP-10

piece handle=/home/DBData/oradata/backup/control_07k206gl_1_1_1771818397.ctl tag=CONTROLFILE comment=NONE

channel c1: backup set complete, elapsed time: 00:00:02

Finished backup at 14-SEP-10

released channel: c1

3.2、掛載主庫（RAC2）的/home/DBData/oradata/backup/ 為NFS目錄，以讓（RAC1看見）

在RAC2（primary）上通過root使用者編輯/etc/exports 檔案，在其中加入一行

/home/DBData/oradata/backup *(sync,rw) 然後儲存退出

然後在啟動portmap服務

Service portmap start

Service nfs start

再在RAC1（STANDBY）的機器上掛載這個目錄到相同的目錄

以root使用者操作

mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 rac2:/home/DBData/oradata/backup /home/DBData/oradata/backup

注意 –o 選項，如果有的選項不正確，將到導致/home/DBData/oradata/backup在執行rman的時候不好使。

3.3、恢復standby資料庫

○1、在備用庫（RAC1）STANDBY上操作

[oracle@rac1 ~]$ export ORACLE_SID=NACEC

[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 11 13:35:52 2010

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1260696 bytes

Variable Size 92275560 bytes

Database Buffers 71303168 bytes

Redo Buffers 2932736 bytes

○2、啟動RMAN來執行恢復standby資料庫的命令

[oracle@rac1 ~]$ export ORACLE_SID=NACEC

[oracle@rac1 ~]$ rman auxiliary sys/tadertader@nacecstdb target sys/tadertader@nacecprim

這裡的standby是備用庫的網路服務名

這裡的primary是主庫的網路服務名

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 14 13:57:16 2010

connected to target database: NACEC (DBID=1771818397)

connected to auxiliary database: NACEC (not mounted)

RMAN> run {

2> set until sequence=33 thread=1;

3> duplicate target database for standby nofilenamecheck dorecover;

4> }

###sequence=33 thread=1是在主庫執行alter system switch logfile;

Select sequence# from v$archived_log order by sequence#;的最後一行。

executing command: SET until clause

using target database control file instead of recovery catalog

Starting Duplicate Db at 14-SEP-10

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=152 devtype=DISK

contents of Memory Script.:

{

set until scn 482409;

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

executing command: SET until clause

Starting restore at 14-SEP-10

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /home/DBData/oradata/backup/control_07k206gl_1_1_1771818397.ctl

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/home/DBData/oradata/backup/control_07k206gl_1_1_1771818397.ctl tag=CONTROLFILE

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16

output filename=/home/DBData/oradata/NACEC/control01.ctl

output filename=/home/DBData/oradata/NACEC/control02.ctl

output filename=/home/DBData/oradata/NACEC/control03.ctl

Finished restore at 14-SEP-10

sql statement: alter database mount standby database

released channel: ORA_AUX_DISK_1

contents of Memory Script.:

{

set until scn 482409;

set newname for tempfile 1 to

"/home/DBData/oradata/NACEC/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/home/DBData/oradata/NACEC/system01.dbf";

set newname for datafile 2 to

"/home/DBData/oradata/NACEC/undotbs01.dbf";

set newname for datafile 3 to

"/home/DBData/oradata/NACEC/sysaux01.dbf";

set newname for datafile 4 to

"/home/DBData/oradata/NACEC/users01.dbf";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to /home/DBData/oradata/NACEC/temp01.dbf in control file

executing command: SET NEWNAME

Starting restore at 14-SEP-10

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=152 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /home/DBData/oradata/NACEC/system01.dbf

restoring datafile 00002 to /home/DBData/oradata/NACEC/undotbs01.dbf

restoring datafile 00003 to /home/DBData/oradata/NACEC/sysaux01.dbf

restoring datafile 00004 to /home/DBData/oradata/NACEC/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /home/DBData/oradata/backup/full_05k206eg_1_1_1771818397.dbf

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/home/DBData/oradata/backup/full_05k206eg_1_1_1771818397.dbf tag=FULLDATABASE

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:07

Finished restore at 14-SEP-10

contents of Memory Script.:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=5 stamp=673192969 filename=/home/DBData/oradata/NACEC/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=6 stamp=673192969 filename=/home/DBData/oradata/NACEC/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=7 stamp=673192969 filename=/home/DBData/oradata/NACEC/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=8 stamp=673192969 filename=/home/DBData/oradata/NACEC/users01.dbf

contents of Memory Script.:

{

set until scn 482409;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 14-SEP-10

using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /home/DBData/oradata/archive/log_1_6_673190306.arc

archive log thread 1 sequence 7 is already on disk as file /home/DBData/oradata/archive/log_1_7_673190306.arc

archive log thread 1 sequence 8 is already on disk as file /home/DBData/oradata/archive/log_1_8_673190306.arc

archive log filename=/home/DBData/oradata/archive/log_1_6_673190306.arc thread=1 sequence=6

archive log filename=/home/DBData/oradata/archive/log_1_7_673190306.arc thread=1 sequence=7

archive log filename=/home/DBData/oradata/archive/log_1_8_673190306.arc thread=1 sequence=8

media recovery complete, elapsed time: 00:00:04

Finished recover at 14-SEP-10

Finished Duplicate Db at 14-SEP-10

○3、然後在備用庫上啟動備用庫到恢復模式。

[oracle@rac1 ~]$ export ORACLE_SID=NACEC

[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 14 13:35:52 2010

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1260696 bytes

Variable Size 92275560 bytes

Database Buffers 71303168 bytes

Redo Buffers 2932736 bytes

SQL> conn / as sysdba

Connected.

SQL> alter database recover managed standby database disconnect from session;

至此，我們的dataguard就已經建立了。OK

四、啟動DATAGUARD

4.1啟動（RAC1）standby備用資料庫

[oracle@rac1 ~]$ export ORACLE_SID=NACEC

[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 14 14:33:44 2010

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1260696 bytes

Variable Size 92275560 bytes

Database Buffers 71303168 bytes

Redo Buffers 2932736 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

檢查日誌的應用情況。

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

SEQUENCE# APP

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

14 YES

11 YES

12 YES

13 YES

15 YES

SQL> select name,PROTECTION_MODE,PROTECTION_LEVEL,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database

2 ;

NAME PROTECTION_MODE PROTECTION_LEVEL REMOTE_A DATABASE_ROLE

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

SWITCHOVER_STATUS

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

NACEC MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE ENABLED PHYSICAL STANDBY

SESSIONS ACTIVE

SQL> set linesize 140

SQL> /

NAME PROTECTION_MODE PROTECTION_LEVEL REMOTE_A DATABASE_ROLE SWITCHOVER_STATUS

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

NACEC MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE ENABLED PHYSICAL STANDBY SESSIONS ACTIVE

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

SEQUENCE# APP

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

14 YES

11 YES

12 YES

13 YES

15 YES

啟動（RAC2）primary資料庫

[oracle@rac2 ~]$ export ORACLE_SID=NACEC

[oracle@rac2 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 14 14:34:45 2010

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1260696 bytes

Variable Size 92275560 bytes

Database Buffers 71303168 bytes

Redo Buffers 2932736 bytes

Database mounted.

Database opened.

SQL> select name,PROTECTION_MODE,PROTECTION_LEVEL,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

NAME PROTECTION_MODE PROTECTION_LEVEL REMOTE_A DATABASE_ROLE

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

SWITCHOVER_STATUS

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

NACEC MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE ENABLED PRIMARY

SESSIONS ACTIVE

SQL> set linesize 140

SQL> /

NAME PROTECTION_MODE PROTECTION_LEVEL REMOTE_A DATABASE_ROLE SWITCHOVER_STATUS

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

NACEC MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE ENABLED PRIMARY SESSIONS ACTIVE

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

SEQUENCE# APP

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

10 NO

11 NO

12 NO

13 NO

14 NO

14 YES

11 NO

12 NO

13 NO

15 NO

15 YES

11 rows selected.

五、做DATAGUARD的PRIMARY和STANDBY的切換

5.1做主庫（RAC2）primary到備庫（RAC1）standby的切換

SQL> select name,PROTECTION_MODE,PROTECTION_LEVEL,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

NAME PROTECTION_MODE PROTECTION_LEVEL REMOTE_A DATABASE_ROLE

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

SWITCHOVER_STATUS

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

NACEC MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE ENABLED PRIMARY

SESSIONS ACTIVE

SQL> select status from v$instance;

STATUS

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

OPEN

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 nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1260696 bytes

Variable Size 92275560 bytes

Database Buffers 71303168 bytes

Redo Buffers 2932736 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

5.2從備庫（RAC1）standby到主庫（RAC2）primary的切換

SQL> select name,PROTECTION_MODE,PROTECTION_LEVEL,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database

2 ;

NAME PROTECTION_MODE PROTECTION_LEVEL REMOTE_A DATABASE_ROLE

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

SWITCHOVER_STATUS

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

NACEC MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE ENABLED PHYSICAL STANDBY

SESSIONS ACTIVE

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> alter database recover managed standby database cancel;

alter database recover managed standby database cancel

*

ERROR at line 1:

ORA-16136: Managed Standby Recovery not active

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> alter database commit to switchover to primary;

Database altered.

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1260696 bytes

Variable Size 92275560 bytes

Database Buffers 71303168 bytes

Redo Buffers 2932736 bytes

Database mounted.

Database opened.

看看日誌能不能傳輸過去。也是我們下一步的測試dataguard是否配置成功。

切換的順序應該是先切換主庫到備用庫，然後從備用庫再切換到主庫。

六、測試我們的DATAGUARD是否成功。

在RAC1（STANDBY）資料庫上切換日誌，看日誌能不能傳到RAC2（PRIMARY）資料庫上。

在RAC1（STANDBY）上看日誌的情況

SQL> select count(*) from left;

COUNT(*)

----------

458752

SQL> delete from left where rownum <200001;

200000 rows deleted.

SQL> commit;

Commit complete.

SQL> conn scott/tiger

Connected.

SQL> select count(*) from left;

COUNT(*)

----------

258752

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

SEQUENCE# APP

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

14 YES

11 YES

12 YES

13 YES

15 YES

16 YES

17 YES

18 NO

18 YES

19 NO

20 NO

SEQUENCE# APP

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

19 NO

21 NO

22 NO

20 YES

23 NO

21 NO

22 NO

24 NO

23 NO

24 YES

21 rows selected.

在RAC2（PRIMARY）上看到日誌已經傳輸過來了

QL> select sequence#,applied from v$archived_log;

SEQUENCE# APP

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

10 YES

11 YES

12 YES

13 YES

14 YES

11 NO

12 NO

13 NO

15 YES

SEQUENCE# APP

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

16 YES

17 YES

17 NO

18 YES

19 YES

20 YES

21 YES

22 YES

23 YES

24 YES

22 rows selected.

現在我們啟動（RAC2）PRIMARY資料庫到open read only狀態

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> conn scott/tiger

Connected.

SQL> select count(*) from left;

COUNT(*)

----------

458752

我們發現資料scott.left使用者表中的資料庫是正確的。證明我們的switchover 切換是成功的。

日誌也傳輸成功。至此我們的DATAGUARD配置就完成了。

七、附件

7.1在主庫切換到備用庫的過程中，要注意的。

在切換之前，我們看正常的備用庫和主庫的switchover_status的狀態

備用庫切換之前。

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1260696 bytes

Variable Size 92275560 bytes

Database Buffers 71303168 bytes

Redo Buffers 2932736 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY SESSIONS ACTIVE MOUNTED

在切換之後。

SQL> /

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY TO PRIMARY MOUNTED

注意到在切換之後備用庫中switchover_status 從session action變化到了 to primary的狀態。

如在主庫上切換之前。

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PRIMARY SESSIONS ACTIVE

SQL> select status from v$instance;

STATUS

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

OPEN

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

Database altered.

在切換的過程中，我們要注意SWITCHOVER_STATUS 他的值，如果是SESSIONS ACTIVE我在切換的語句要加with session shutdown,在這裡執行完後，我們看到備用庫上。

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1260696 bytes

Variable Size 92275560 bytes

Database Buffers 71303168 bytes

Redo Buffers 2932736 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

在主庫切換到備用庫後。

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY SESSIONS ACTIVE MOUNTED

7.2在備用庫切換到主庫上。要注意的地方。

在備用庫上：檢視switchover_status的狀態，

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PHYSICAL STANDBY TO PRIMARY MOUNTED

發現switchover_status的狀態已經到了to primary

我們現在切換。

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile='$ORACLE_HOME/dbs/initNACEC.ora'

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1260696 bytes

Variable Size 92275560 bytes

Database Buffers 71303168 bytes

Redo Buffers 2932736 bytes

Database mounted.

Database opened.

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

DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE

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

PRIMARY SESSIONS ACTIVE READ WRITE

至此，我們的切換就完成了。

測試日誌是否傳輸。

在主庫上執行

SQL> conn scott/tiger

Connected.

SQL> select count(*) from left;

COUNT(*)

----------

258752

SQL> delete from left where rownum < 200001;

200000 rows deleted.

SQL> commit;

Commit complete.

SQL> conn / as sysdba

Connected.

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

SEQUENCE# APP

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

10 YES

11 YES

12 YES

13 YES

14 YES

11 NO

12 NO

13 NO

15 YES

SEQUENCE# APP

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

16 YES

17 YES

17 NO

18 YES

19 YES

20 YES

21 YES

22 YES

23 YES

24 YES

SEQUENCE# APP

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

25 YES

26 YES

27 NO

27 YES

28 NO

29 NO

28 NO

30 NO

31 NO

29 NO

32 NO

SEQUENCE# APP

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

33 NO

30 NO

31 YES

32 NO

33 NO

38 rows selected.

SQL> conn scott/tiger

Connected.

SQL> select count(*) from left;

COUNT(*)

----------

58752

然後檢視備用庫：

SQL> /

SEQUENCE# APP

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

14 YES

11 YES

12 YES

13 YES

15 YES

16 YES

17 YES

18 YES

19 YES

20 YES

SEQUENCE# APP

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

19 NO

21 YES

22 YES

20 YES

23 YES

21 NO

22 NO

24 YES

23 NO

24 YES

25 YES

SEQUENCE# APP

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

25 YES

26 YES

26 NO

27 YES

28 YES

29 YES

30 YES

31 YES

32 YES

33 YES

32 rows selected.

SQL> alter database recover managed standby database disconnect from session;

alter database recover managed standby database disconnect from session

ERROR at line 1:

ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database finished;

alter database recover managed standby database finished

*

ERROR at line 1:

ORA-00274: illegal recovery option FINISHED

Alter database recover managed standby database finish;

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> conn scott/tiger

Connected.

SQL> select count(*) from left;

COUNT(*)

----------

58752

至此我們資料庫兩邊是同步的。說明切換是成功的。OK。

注意：在我們的資料庫要切換或者是關閉的時候，我們最好，讓主庫的聯機線上日誌，進行切換。以使其歸檔，並且讓這個歸檔傳輸到備用庫上去。

八、經常用的命令解釋

8.1備用庫的啟動命令中用到的

Conn / as sysdba

Startup nomount pfile=c:\oracle\ora92\database\initmyoracle.ora;

Alter database mount standby database;

Alter database recover managed standby database disconnect from session; (讓資料庫處於自動恢復模式)

Alter database recover managed standby database cancel;(讓資料庫結束恢復)

Alter database open read only;

Shutdown immediate;(接可以關閉資料庫了)

(依照此過程,讓standby資料庫啟動到read only狀態)

8.2主庫的啟動和關閉命令

Conn / as sysdba

Startup pfile=’c:\oracle\ora92\database\initmyoracle.ora’;

Shutdown immediate;

8.3備用庫切換到主庫命令

在主庫上執行命令

Conn / as sysdba

Alter system archive log current;

Alter system archive log current;多執行幾次以讓聯機日誌檔案傳輸過去.

在備庫上執行命令備庫切換到主庫

Conn / as sysdba

Select database_role,switchover_status from v$database;

Select status from v$instance;

Alter database recover managed standby database disconnect from session;

Select sequence#,applied from v$archived_log;(以確保兩邊的日誌同步.)

Alter database recover managed standby database cancel;

Alter database commit to switchover to primary with session shutdown;

Shutdown immediate;

Startup pfile=c:\oracle\ora92\database\initmyoracle.ora;

8.4主庫切換到備用庫命令

在主庫上執行命令

Conn / as sysdba

Select database_role,switchover_status from v$database;

Select status from v$instance;

Alter database commit to switchover to physical standby with session shutdown;

Shutdown immediate;

Startup pfile=c:\oracle\ora92\database\initmyoracle.ora;

九、監聽器及本地服務的配置

9.1 primary伺服器的監聽

# listener.ora Network Configuration File: /home/DBSoftware/oracle/product/10.2.

0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = NACEC)

(ORACLE_HOME = /home/DBSoftware/oracle/product/10.2.0/db_1)

(SID_NAME = NACEC)

)

LISTENER =

(DESCRIPTION =

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

)

9.2 primary伺服器的本地服務

# tnsnames.ora Network Configuration File: /home/DBSoftware/oracle/product/10.2.

0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

NACECPRIM =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = NACEC)

)

NACECSTDB =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = NACEC)

)

9.3 standby伺服器的監聽

# listener.ora Network Configuration File: /home/DBSoftware/oracle/product/10.2.

0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = NACEC)

(ORACLE_HOME = /home/DBSoftware/oracle/product/10.2.0/db_1)

(SID_NAME = NACEC)

)

LISTENER =

(DESCRIPTION =

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

)

9.4 standby伺服器的本地服務

# tnsnames.ora Network Configuration File: /home/DBSoftware/oracle/product/10.2.

0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

NACECPRIM =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = NACEC)

)

NACECSTDB =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = NACEC)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

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

VMware中配置ORACLE DATAGUARD步驟

相關文章