为了测试在同一台服务器上建立了DATA GUARD环境。
主库状态正常,也存在可用的备份,下面设置主库的FORCE LOGGING和相关的初始化参数:
SQL> alter database force logging;
Database altered.
修改主库的初始化参数:
SQL> alter system set log_archive_config = 'DG_CONFIG=(primary,standby)';
System altered.
SQL> alter system set log_archive_dest_1 = 'LOCATION=/data/oradata/primary/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary';
System altered.
SQL> alter system set log_archive_dest_2 = 'SERVICE=standby LGWR SYNC 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.
在主库建立STANDBY_LOGFILE:
SQL> select group#, thread#, sequence#, bytes/1024/1024 from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024
---------- ---------- ---------- ---------------
1 1 4 500
2 1 5 500
3 1 3 500
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------
/data/oradata/primary/redo01.log
/data/oradata/primary/redo02.log
/data/oradata/primary/redo03.log
SQL> alter database add standby logfile '/data/oradata/primary/standby_redo01.log' size500m;
Database altered.
SQL> alter database add standby logfile '/data/oradata/primary/standby_redo02.log' size500m;
Database altered.
SQL> alter database add standby logfile '/data/oradata/primary/standby_redo03.log' size500m;
Database altered.
SQL> alter database add standby logfile '/data/oradata/primary/standby_redo04.log' size500m;
Database altered.
对于STANDBY LOGFILE应该比REDO LOGFILE多一组。
下面创建STANDBY数据库需要的目录结构:
SQL> host mkdir -p /data/oradata/standby/archivelog
SQL> host mkdir -p /opt/ora10g/admin/standby/bdump
SQL> host mkdir /opt/ora10g/admin/standby/cdump
SQL> host mkdir /opt/ora10g/admin/standby/adump
SQL> host mkdir /opt/ora10g/admin/standby/udump
创建STANDBY数据库需要的初始化文件:
SQL> create pfile='/home/oracle/initstandby.ora' from spfile;
File created.
编辑初始化文件,修改相关的路径,设置对应standby数据库的初始化参数:
[oracle@yans1 ~]$ vi initstandby.ora
primary.__db_cache_size=1644167168
primary.__java_pool_size=16777216
primary.__large_pool_size=16777216
primary.__shared_pool_size=452984832
primary.__streams_pool_size=0
*.audit_file_dest='/opt/ora10g/admin/standby/adump'
*.audit_trail='NONE'
*.background_dump_dest='/opt/ora10g/admin/standby/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/data/oradata/standby/control01.ctl','/data/oradata/standby/control02.ctl','/data/oradata/standby/control03.ctl'
*.core_dump_dest='/opt/ora10g/admin/standby/cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='primary'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.fal_client='STANDBY'
*.fal_server='PRIMARY'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='LOCATION=/data/oradata/standby/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primary LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2147483648
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/ora10g/admin/primary/udump'
db_unique_name=standby
instance_name=standby
service_names=standby
log_file_name_convert=('/data/oradata/primary', '/data/oradata/standby')
db_file_name_convert=('/data/oradata/primary', '/data/oradata/standby')
除了对主库的初始化参数进行修改外,由于STANDBY数据库和PRIMARY处于同一个服务器上,因此还需要增加DB_UNIQUE_NAME、INSTANCE_NAME、SERVICE_NAMES、LOG_FILE_NAME_CONVERT和DB_FILE_NAME_CONVERT参数。
添加PRIMARY和STANDBY数据库的TNS配置:
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yans1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yans1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = standby)
)
)
修改listener.ora,使用静态注册,在SID列表中添加:
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1)
(SID_NAME = standby)
)
重启监听:
[oracle@yans1 ~]$ lsnrctl start
LSNRCTL for Linux: Version10.2.0.3.0 - Production on 23-12月-2010 08:11:45
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Starting /opt/ora10g/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version10.2.0.3.0 - Production
System parameter file is /opt/ora10g/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /opt/ora10g/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yans1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yans1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version10.2.0.3.0 - Production
Start Date 23-12月-2010 08:11:45
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/ora10g/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /opt/ora10g/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yans1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "PlsExtProc" has 1 instance(s).
Instance "PlsExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "primary" has 1 instance(s).
Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
Service "test08" has 1 instance(s).
Instance "test08", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
下面启动STANDBY数据库:
[oracle@yans1 ~]$ export ORACLE_SID=standby
[oracle@yans1 ~]$ sqlplus / as sysdba
SQL*Plus: Release10.2.0.3.0 - Production on星期四12月23 07:58:44 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/home/oracle/initstandby.ora
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074112 bytes
Variable Size 486541824 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14700544 bytes
创建STANDBY数据库密码文件:
SQL> host cp $ORACLE_HOME/dbs/orapwprimary $ORACLE_HOME/dbs/orapwstandby
利用RMAN来创建DATA GUARD:
[oracle@yans1 ~]$ export ORACLE_SID=primary
[oracle@yans1 ~]$ rman target /
Recovery Manager: Release10.2.0.3.0 - Production on星期四12月23 08:16:53 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIMARY (DBID=1574503166)
RMAN> run
2> {
3> allocate channel c1 device type disk format '/data/backup/%U';
4> backup current controlfile for standby;
5> release channel c1;
6> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=148 devtype=DISK
Starting backup at 23-12月-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 23-12月-10
channel c1: finished piece 1 at 23-12月-10
piece handle=/data/backup/05m08u9a_1_1 tag=TAG20101223T081746 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-12月-10
released channel: c1
首先为STANDBY创建数据库控制文件备份。
然后连接代理实例,开始DUPLICATE操作:
RMAN> connect auxiliary sys/test@standby
connected to auxiliary database: PRIMARY (not mounted)
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> allocate auxiliary channel ac1 device type disk;
5> duplicate target database for standby;
6> release channel c1;
7> release channel ac1;
8> }
allocated channel: c1
channel c1: sid=148 devtype=DISK
allocated channel: ac1
channel ac1: sid=157 devtype=DISK
Starting Duplicate Db at 23-12月-10
contents of Memory Script.:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 23-12月-10
channel ac1: starting datafile backupset restore
channel ac1: restoring control file
channel ac1: reading from backup piece /data/backup/05m08u9a_1_1
channel ac1: restored backup piece 1
piece handle=/data/backup/05m08u9a_1_1 tag=TAG20101223T081746
channel ac1: restore complete, elapsed time: 00:00:01
output filename=/data/oradata/standby/control01.ctl
output filename=/data/oradata/standby/control02.ctl
output filename=/data/oradata/standby/control03.ctl
Finished restore at 23-12月-10
sql statement: alter database mount standby database
contents of Memory Script.:
{
set newname for tempfile 1 to
"/data/oradata/standby/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/data/oradata/standby/system01.dbf";
set newname for datafile 2 to
"/data/oradata/standby/undotbs01.dbf";
set newname for datafile 3 to
"/data/oradata/standby/sysaux01.dbf";
set newname for datafile 4 to
"/data/oradata/standby/users01.dbf";
set newname for datafile 5 to
"/data/oradata/standby/test01.dbf";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /data/oradata/standby/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 23-12月-10
channel ac1: starting datafile backupset restore
channel ac1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /data/oradata/standby/system01.dbf
restoring datafile 00002 to /data/oradata/standby/undotbs01.dbf
restoring datafile 00003 to /data/oradata/standby/sysaux01.dbf
restoring datafile 00004 to /data/oradata/standby/users01.dbf
restoring datafile 00005 to /data/oradata/standby/test01.dbf
channel ac1: reading from backup piece /data/backup/primary/02lut2g0_1_1
channel ac1: restored backup piece 1
piece handle=/data/backup/primary/02lut2g0_1_1 tag=TAG20101206T165944
channel ac1: restore complete, elapsed time: 00:00:56
Finished restore at 23-12月-10
contents of Memory Script.:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=7 stamp=738491081 filename=/data/oradata/standby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=8 stamp=738491081 filename=/data/oradata/standby/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=9 stamp=738491081 filename=/data/oradata/standby/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=10 stamp=738491081 filename=/data/oradata/standby/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=11 stamp=738491081 filename=/data/oradata/standby/test01.dbf
Finished Duplicate Db at 23-12月-10
released channel: c1
released channel: ac1
至此DATA GUARD环境创建成功,可以将STANDBY处于恢复状态:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html