oracle 10g dataguard部署
本文档只是部署dataguard的过程，不涉及概念的解释和说明，更没有dataguard理论原理的介绍，关于这方面内容，请查看oracle官方文档。操作系统：RedHat 4.2
$ uname -a
Linux dg1 2.6.9-22.EL #1 Mon Sep 19 18:20:28 EDT 2005 i686 i686 i386 GNU/Linux
Oracle 软件：ORACLE 10g> 主机信息：
机器名：dg1 IP地址：192.168.1.90
备机信息：
机器名：dg2 IP地址：192.168.1.91
以下操作，#表示主机root用户登录 $表示主机oracle用户登录
#表示备机root用户登录 $表示备机oracle用户登录
操作前提：在主机dg1上，数据库软件和oracle数据库（db01）已经安装和创建完毕，在备机dg2上，只安装了oracle软件，没有数据库，物理备数据库通过RMAN命令来创建，主库的归档已经打开。
第一步：主机数据库设置force logging，只在主库上执行操作
$ sqlplus / as sysdba
SQL*Plus:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition> With the Partitioning, OLAP and Data Mining options
SQL>
SQL>> 第二步：配置主、备机器的网络环境
通过修改主、备机器tnsnames.ora 文件配置客户端连接，通过修改主、备机器的listener.ora文件配置服务器端监听，在文件中加入静态注册
文件位置都在 /u01/app/oracle/product/10.2.0/network/admin下
以下仅以主机dg1上的文件举例，备机文件 listener.ora中的主机ip要改成192.168.1.91
关闭监听，编辑listener.ora文件内容，在备库关闭监听的操作不需要做，备库本身就没有监听
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.90)(PORT=1521)))
The command completed successfully
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db01)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
(SID_NAME = db01)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.90)(PORT = 1521))
)
启动监听 ，LSNRCTL> start
tnsnames.ora文件内容
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.90)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db01)
)
)
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.90)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db01)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.91)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db01)
)
)
第三步：使用oracle用户在备机$ORACLE_HOME/dbs目录创建口令文件，口令要和主机sys用户的口令一致，本例中是oracle
$orapwd file=orapwdb01 password=oracle entries=5
第四步：使用oracle用户在备机上创建standby db需要的目录结构
# su - oracle
$ pwd
/home/oracle
$ mkdir backup
$ cd /u01/app/oracle
$ pwd
/u01/app/oracle
$ mkdir -p admin/db01/adump
$ mkdir -p admin/db01/bdump
$ mkdir -p admin/db01/cdump
$ mkdir -p admin/db01/ddump
$ mkdir -p admin/db01/udump
$ mkdir flash_recovery_area
$ mkdir archdest
$ mkdir -p oradata/db01
第五步：修改主备机的参数文件，添加和编辑dataguard db环境需要的参数，此步骤也可以通过alter system命令实现，以下仅以主机dg1举例，
如果数据库是打开状态，执行下面的命令
SQL> create pfile from spfile;
File created.
关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition> With the Partitioning, OLAP and Data Mining options
到dbs目录下，找到initdb01.ora 使用vi编辑器添加和修改相关的参数，保存退出。具体编辑过程省略。
$ cd $ORACLE_HOME/dbs
$ ls -l
total 6960
-rw-rw----1 oracle oinstall 1544 Jan 13 16:16 hc_db01.dat
-rw-r--r--1 oracle oinstall 1421 Feb 17 17:34 initdb01.ora
-rw-r-----1 oracle oinstall 12920 May32001 initdw.ora
-rw-r-----1 oracle oinstall 8385 Sep 111998 init.ora
-rw-rw----1 oracle oinstall 24 Jan 13 16:17 lkDB01
-rw-rw----1 oracle oinstall 24 Jan 14 21:00 lkPRIMARY
-rw-r-----1 oracle oinstall 1536 Feb 16 21:19 orapwdb01
-rw-r-----1 oracle oinstall 7061504 Jan 14 21:04 snapcf_db01.f
-rw-r-----1 oracle oinstall 3584 Feb 17 17:33 spfiledb01.ora
编辑后的参数文件内容如下：
主机参数文件initdb01.ora
#***********************************************************************************
db01.__db_cache_size=142606336
db01.__java_pool_size=4194304
db01.__large_pool_size=4194304
db01.__shared_pool_size=62914560
db01.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/db01/adump'
*.background_dump_dest='/u01/app/oracle/admin/db01/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/db01/control01.ctl','/u01/app/oracle/oradata/db01/control02.ctl','/u01/app/oracle/oradata/db01/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/db01/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='db01'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db01XDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=50331648
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=216006656
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/db01/udump'
# for dataguard primary DB only add by tianjie
db_unique_name= primary
instance_name = db01
log_archive_config='DG_CONFIG=(primary,standby)'
log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archdest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary'
log_archive_dest_2 = 'SERVICE=standby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=standby'
log_archive_format = %t_%s_%r.dbf
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
fal_server=standby
fal_client=primary
standby_file_management = AUTO
#***************************************************************************************
编辑参数文件后，连接数据库，从新生成spfile文件，使参数生效。
$ sqlplus / as sysdba
SQL*Plus:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to an> SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area314572800 bytes
Fixed>
Variable> Database Buffers 222298112 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
如果是在备机dg2的话，备机本身没有参数文件，可以拷贝主机dg1的参数文件到备机的$ORACLE_HOME/dbs目录，然后修改就可以了
备机参数文件需要改动的参数如下：
db_unique_name= standby
log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archdest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby'
log_archive_dest_2 = 'SERVICE=primaryLGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=primary'
fal_server = primary
fal_client = standby
第六步：Oracle 物理备库创建（主库与备库的识别，通过提示符判断）
1.在开始做操作的时候，主数据库是在open状态。
$ sqlplus / as sysdba
SQL*Plus:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition> With the Partitioning, OLAP and Data Mining options
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archdest
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition> With the Partitioning, OLAP and Data Mining options
$ rman target /
Recovery Manager:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
connected to target database: DB01 (DBID=1346844761)
RMAN> backup full database include current controlfile for standby format '/home/oracle/backup/db01_std_%U.dbf'
2> plus archivelog format '/home/oracle/backup/db01_arc_%U.dbf';
Starting backup at 06-APR-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=715586447
input archive log thread=1 sequence=3 recid=2 stamp=715597863
input archive log thread=1 sequence=4 recid=3 stamp=715598127
input archive log thread=1 sequence=5 recid=4 stamp=715612501
channel ORA_DISK_1: starting piece 1 at 06-APR-10
channel ORA_DISK_1: finished piece 1 at 06-APR-10
piece handle=/home/oracle/backup/db01_arc_0flaeoan_1_1.dbf tag=TAG20100406T131502 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 06-APR-10
Starting backup at 06-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/db01/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/db01/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/db01/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/db01/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/db01/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-APR-10
channel ORA_DISK_1: finished piece 1 at 06-APR-10
piece handle=/home/oracle/backup/db01_std_0glaeoav_1_1.dbf tag=TAG20100406T131511 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:06
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 06-APR-10
channel ORA_DISK_1: finished piece 1 at 06-APR-10
piece handle=/home/oracle/backup/db01_std_0hlaeoet_1_1.dbf tag=TAG20100406T131511 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 06-APR-10
Starting backup at 06-APR-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=6 recid=5 stamp=715612644
channel ORA_DISK_1: starting piece 1 at 06-APR-10
channel ORA_DISK_1: finished piece 1 at 06-APR-10
piece handle=/home/oracle/backup/db01_arc_0ilaeof4_1_1.dbf tag=TAG20100406T131724 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 06-APR-10
RMAN> exit
Recovery Manager complete.
$ pwd
/u01/app/oracle/product/10.2.0/network/admin
$ cd
$ cd backup
$ ls
db01_arc_01ma6e3q_1_1.dbfdb01_arc_04ma6e60_1_1.dbfdb01_std_02ma6e3v_1_1.dbfdb01_std_03ma6e5s_1_1.dbf
$ ll
total 650320
-rw-r-----1 oracle oinstall48220672 Apr 19 18:12 db01_arc_01ma6e3q_1_1.dbf
-rw-r-----1 oracle oinstall 7168 Apr 19 18:13 db01_arc_04ma6e60_1_1.dbf
-rw-r-----1 oracle oinstall 609886208 Apr 19 18:13 db01_std_02ma6e3v_1_1.dbf
-rw-r-----1 oracle oinstall 7143424 Apr 19 18:13 db01_std_03ma6e5s_1_1.dbf
使用tar命令把备份文件打包，便于传递
$ tar -cvfbackup.tar *.*
db01_arc_01ma6e3q_1_1.dbf
db01_arc_04ma6e60_1_1.dbf
db01_std_02ma6e3v_1_1.dbf
db01_std_03ma6e5s_1_1.dbf
$ ll
total 1300632
-rw-r--r--1 oracle oinstall 665262080 Apr 19 18:18 backup.tar
-rw-r-----1 oracle oinstall48220672 Apr 19 18:12 db01_arc_01ma6e3q_1_1.dbf
-rw-r-----1 oracle oinstall 7168 Apr 19 18:13 db01_arc_04ma6e60_1_1.dbf
-rw-r-----1 oracle oinstall 609886208 Apr 19 18:13 db01_std_02ma6e3v_1_1.dbf
-rw-r-----1 oracle oinstall 7143424 Apr 19 18:13 db01_std_03ma6e5s_1_1.dbf
使用scp命令copy刚刚生成的backup.tar文件到备机
$ scp backup.tar 192.168.1.91:/home/oracle/backup/.
oracle@192.168.1.91's password:
backup.tar 100%634MB 6.4MB/s 01:39 $
2.登录备机dg2，确认监听启动，必要的目录结构、参数文件、口令文件已经创建，启动备用数据库实例（备用数据库启动到nomount状态），就可以在主库连接从库进行恢复。
$ ll
total 650312
-rw-r--r--1 oracle oinstall 665262080 Apr 19 18:22 backup.tar
$ tar xvf backup.tar
db01_arc_01ma6e3q_1_1.dbf
db01_arc_04ma6e60_1_1.dbf
db01_std_02ma6e3v_1_1.dbf
db01_std_03ma6e5s_1_1.dbf
$
根据在备机dg2上$ORACLE_HOME/dbs目录下的initdb01.ora文件，创建spfile文件，把数据库实例启动
$ sqlplus / as sysdba
SQL*Plus:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to an> SQL> create spfile from pfile;
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area218103808 bytes
Fixed>
Variable> Database Buffers 142606336 bytes
Redo Buffers 2973696 bytes
SQL>
3.回到主机dg1，进入rman工具，通过duplicate target database命令创建物理备库
$ rman target /
Recovery Manager:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
connected to target database: DB01 (DBID=1380159132)
RMAN>
RMAN> connect auxiliary sys/oracle@standby
connected to auxiliary database: DB01 (not mounted)
RMAN>duplicate target database for standby dorecover nofilenamecheck;
Starting Duplicate Db at 06-APR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
contents of Memory Script:
{
set until scn498654;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 06-APR-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/oracle/backup/db01_std_0hlaeoet_1_1.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/db01_std_0hlaeoet_1_1.dbf tag=TAG20100406T131511
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output filename=/u01/app/oracle/oradata/db01/control01.ctl
output filename=/u01/app/oracle/oradata/db01/control02.ctl
output filename=/u01/app/oracle/oradata/db01/control03.ctl
Finished restore at 06-APR-10
sql statement:> released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
set until scn498654;
set newname for tempfile1 to
"/u01/app/oracle/oradata/db01/temp01.dbf";
switch clone tempfile all;
set newname for datafile1 to
"/u01/app/oracle/oradata/db01/system01.dbf";
set newname for datafile2 to
"/u01/app/oracle/oradata/db01/undotbs01.dbf";
set newname for datafile3 to
"/u01/app/oracle/oradata/db01/sysaux01.dbf";
set newname for datafile4 to
"/u01/app/oracle/oradata/db01/users01.dbf";
set newname for datafile5 to
"/u01/app/oracle/oradata/db01/example01.dbf";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed temporary file 1 to /u01/app/oracle/oradata/db01/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 06-APR-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=157 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 /u01/app/oracle/oradata/db01/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/db01/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/db01/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/db01/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/db01/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/db01_std_0glaeoav_1_1.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/db01_std_0glaeoav_1_1.dbf tag=TAG20100406T131511
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:18
Finished restore at 06-APR-10
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=7 stamp=715612126 filename=/u01/app/oracle/oradata/db01/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=8 stamp=715612126 filename=/u01/app/oracle/oradata/db01/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=9 stamp=715612126 filename=/u01/app/oracle/oradata/db01/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=10 stamp=715612126 filename=/u01/app/oracle/oradata/db01/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=11 stamp=715612126 filename=/u01/app/oracle/oradata/db01/example01.dbf
contents of Memory Script:
{
set until scn498654;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 06-APR-10
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=6
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/db01_arc_0ilaeof4_1_1.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/db01_arc_0ilaeof4_1_1.dbf tag=TAG20100406T131724
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u01/app/oracle/archdest/1_6_715576737.dbf thread=1 sequence=6
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/archdest/1_6_715576737.dbf recid=1 stamp=715612132
media recovery complete, elapsed time: 00:00:04
Finished recover at 06-APR-10
Finished Duplicate Db at 06-APR-10
RMAN> exit
Recovery Manager complete.
$ sqlplus / as sysdba
SQL*Plus:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition> With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
4.登录备机dg2，启动数据库到mount状态，启动日志应用。
$ sqlplus / as sysdba
SQL*Plus:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to an> SQL> startup mount
ORACLE instance started.
Total System Global Area314572800 bytes
Fixed>
Variable> Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>>
Database> 5.在主机启动数据库到open状态。
$ sqlplus / as sysdba
SQL*Plus:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to an> SQL> startup
ORACLE instance started.
Total System Global Area314572800 bytes
Fixed>
Variable> Database Buffers 222298112 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
第七步：在备机添加备日志文件
备重做日志文件的个数通常是oracle数据库online log文件个数多一个，如果是逻辑数据库，可以设置更多一些，备日志文件在以下三种情况需要使用：
a.主库要设置为最大保护或是最大可用模式
b.备库要使用实时应用（real apply）
c.使用级联目录
$ sqlplus / as sysdba
SQL*Plus:> Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition> With the Partitioning, OLAP and Data Mining options
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>>
2group 4 ('/u01/app/oracle/oradata/db01/redo04.log')>
3group 5 ('/u01/app/oracle/oradata/db01/redo05.log')>
4group 6 ('/u01/app/oracle/oradata/db01/redo06.log')>
5group 7 ('/u01/app/oracle/oradata/db01/redo07.log')>
Database> SQL>
为了能够保证主机将来能够顺利的切换成备机，在主机dg1同样创建备重做日志文件。
alter database add standby logfile
group 4 （'/u01/app/oracle/oradata/db01/redo04.log'）>
group 5 （'/u01/app/oracle/oradata/db01/redo05.log'）>
group 6 （'/u01/app/oracle/oradata/db01/redo06.log'）>
group 7 （'/u01/app/oracle/oradata/db01/redo07.log'）> 检查当前机器备日志文件的个数和状态，使用如下语句：
SQL> select group#,sequence#,status from v$standby_log;
GROUP#SEQUENCE# STATUS
---------- ---------- ----------
4 25 ACTIVE
5 0 UNASSIGNED
6 0 UNASSIGNED
7 0 UNASSIGNED
第八步：数据库操作同步测试
在主库dg1，使用scott用户连接，删掉表demo，并创建了一张新表test
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPECLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
DEMO TABLE
SQL> select * from demo;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
SQL> drop table demo purge;
Table dropped.
SQL> create table test as select * from dept;
Table created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
在备库，我们看不到主库所做的操作，虽然在备库有备重做日志，但是目前备库不是实时应用（real apply）
SQL> recover managed standby database cancel;
Media recovery complete.
SQL>>
Database> SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPECLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
DEMO TABLE
把备库重新至于应用redo的状态
SQL> conn / as sysdba
Connected.
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
回到主库机器，执行一次日志切换
SQL> conn / as sysdba
Connected.
SQL>>
System> 到备库机器，由于主库做了一次日志切换，备库这边会应用redo信息，我们看到，表demo删除，test表创建出来，数据同步过来了。
SQL> recover managed standby database cancel;
Media recovery complete.
SQL>
SQL>
SQL>>
Database> SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPECLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST TABLE
SQL>
我们再通过添加、删除表空间的方法来测试切换日志应用和实时应用，由于我们操作的是表空间，所以备库不用打开就可以直接看到结果
首先在主库dg1，执行删除表空间userdata的动作，通过v$datafile数据字典确认
SQL> drop tablespace userdata including contents and datafiles;
Tablespace dropped.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db01/system01.dbf
/u01/app/oracle/oradata/db01/undotbs01.dbf
/u01/app/oracle/oradata/db01/sysaux01.dbf
/u01/app/oracle/oradata/db01/users01.dbf
/u01/app/oracle/oradata/db01/example01.dbf
在备库dg2上，执行查询确认，我们看到数据文件删除的信息并没有被应用。
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db01/system01.dbf
/u01/app/oracle/oradata/db01/undotbs01.dbf
/u01/app/oracle/oradata/db01/sysaux01.dbf
/u01/app/oracle/oradata/db01/users01.dbf
/u01/app/oracle/oradata/db01/example01.dbf
/u01/app/oracle/oradata/db01/userdata01.dbf
6 rows selected.
回到主库dg1，我们做一次日志切换
SQL>>
System> 再一次观察备库dg2，发现日志已经应用了，文件删除。
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db01/system01.dbf
/u01/app/oracle/oradata/db01/undotbs01.dbf
/u01/app/oracle/oradata/db01/sysaux01.dbf
/u01/app/oracle/oradata/db01/users01.dbf
/u01/app/oracle/oradata/db01/example01.dbf
我们再来看一下实时应用的表现，首先把备数据库改为实时应用日志
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
在主库创建表空间userdata，并检查确认
SQL> create tablespace userdata
2datafile '/u01/app/oracle/oradata/db01/userdata01.dbf'> Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db01/system01.dbf
/u01/app/oracle/oradata/db01/undotbs01.dbf
/u01/app/oracle/oradata/db01/sysaux01.dbf
/u01/app/oracle/oradata/db01/users01.dbf
/u01/app/oracle/oradata/db01/example01.dbf
/u01/app/oracle/oradata/db01/userdata01.dbf
6 rows selected.
在备库检查确认，也马上可以看到文件
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db01/system01.dbf
/u01/app/oracle/oradata/db01/undotbs01.dbf
/u01/app/oracle/oradata/db01/sysaux01.dbf
/u01/app/oracle/oradata/db01/users01.dbf
/u01/app/oracle/oradata/db01/example01.dbf
/u01/app/oracle/oradata/db01/userdata01.dbf
6 rows selected.
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]