关于Oracle10g Data Gurad的搭建,虽然官方和网上有很多资料,但是基于物理冷备份后复制到备库的搭建方式。在生产环境中我们可能更多要关注主库的可用性,这是Rman的高级备份恢复功能完全胜任这些需求。
主库服务器名:dbserv ip:192.168.2.3 主库DB_UNIQUE_NAME='testdb'
备库服务器名:dbservdg ip:192.168.2.4 备库db_unique_name='testdbDG'
数据库名:testdb
DG安装过成大致分为如下几个重要步骤:
1、备库安装数据库软件(仅安装软件,不必创建数据库)
2、主库通过Rman进行全备份,备份脚本如下:
run{
DELETE NOPROMPT expired archivelog ALL;
allocate channel d1 TYPE disk maxpiecesize=30G;
allocate channel d2 TYPE disk maxpiecesize=30G;
backup DATABASE include CURRENT controlfile for standby format '/backup/rman_full/data_%d_%T_%s_%p' plus archivelog format '/backup/rman_full/arch_%d_%T_%s' DELETE ALL INPUT;
release channel d1;
release channel d2;
crosscheck backup;
DELETE noprompt obsolete REDUNDANCY 2;
}
[@more@]
3、将主库Rman全备份的迁移至备库服务器对应的目录(在备库执行)。
scp oracle@192.168.2.3:/backup/rman_full/* /backup/rman_full/
为了Rman恢复顺利进行,主库将全备份最好传输到相同目录结构。加入主库存储目录为/backup/rman_full,则在备库也要建立目录相同的目录并给于相应权限。
如果无法建立相同目录,则可以通过建立OS连接命令来处理,如下假如将备份传到备库/backup/rman/full目录的具体配置过程:
在备库:
mkdir –p /backup/rman_full
chown –R oracle:oinstall /backup/rman_full
ln –s /backup/rman/full/* /backup/rman_full
4、确认主库处于归档模式,如果不是改为归档模式
SQL> archive log list
5、 设置主库强制logging
SQL> alter database force logging;
6、配置主备库监听和TNS配置文件
主库监听和TNS配置:
[oracle@dbserv admin]$ pwd
/opt/oracle/product/10.2.0/db_1/network/admin
[oracle@dbserv admin]$ more listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBANAME = testdb)
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
(SID_NAME = testdb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserv)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@dbserv admin]$
[oracle@dbserv admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
testdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserv)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)
testdb.STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@dbserv admin]$
备库监听和TNS配置:
[oracle@dbservdg admin]$ pwd
/opt/oracle/product/10.2.0/db_1/network/admin
[oracle@dbservdg admin]$ more listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBANAME = testdb)
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
(SID_NAME = testdb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbservdg)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@dbservdg admin]$
[oracle@dbservdg admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
testdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
testdb.STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@dbservdg admin]$
7、 复制密码文件到Standby端
scp oracle@192.168.2.3:/opt/oracle/product/10.2.0/db_1/dbs/orapwtestdb /opt/oracle/product/10.2.0/db_1/dbs/orapwtestdb
8、 在Standby端创建目录结构
su – root
mkdir -p /opt/oracle/admin/testdb/adump
mkdir -p /opt/oracle/admin/testdb/bdump
mkdir -p /opt/oracle/admin/testdb/cdump
mkdir -p /opt/oracle/admin/testdb/udump
mkdir -p /oradata/testdb/
mkdir–p /bakup/archive/
chown –R oracle:oinstall /opt/oracle/admin/testdb/
chown –R oracle:oinstall /oradata/testdb/
chown –R oracle:oinstall /bakup/archive/
9、 手工创建并配置备库初始化参数文件
主库初始化文件的导出并修改,以便用于备库初始化个文件:
[oracle@dbserv ~]$ cd /opt/oracle/product/10.2.0/db_1/dbs/
[oracle@dbserv dbs]$ ls
alert_testdb.log hc_testdb.dat initdw.ora inittestdb.ora0717.bak orapwtestdb spfiletestdb.ora
init.ora inittestdb.ora lktestdb snapcf_testdb.f spfiletestdb_bak.ora
[oracle@dbserv dbs]$ sqlplus / as sysdba
SQL> create pfile='/tmp/inittestdb.ora' from spfile;
File created.
scp oracle@192.168.2.3:/tmp/inittestdb.ora /opt/oracle/product/10.2.0/db_1/dbs/
修改从主库传过来的初始化文件,将其符合备库初始化文件的要求:
[oracle@dbservdg dbs] cd /opt/oracle/product/10.2.0/db_1/dbs/
[oracle@dbservdg dbs]$ vi inittestdb.ora
testdb.__db_cache_size=2952790016
testdb.__java_pool_size=50331648
testdb.__large_pool_size=16777216
testdb.__shared_pool_size=704643072
testdb.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/testdb/adump'
*.background_dump_dest='/opt/oracle/admin/testdb/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/oradata/testdb/control01.ctl','/oradata/testdb/control02.ctl','/oradata/testdb/control03.ctl'#Restore Controlfile
*.core_dump_dest='/opt/oracle/admin/testdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='/oradata/testdb','/oradata/testdb'
*.db_name='testdb'
*.db_recovery_file_dest='/bakup/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='testdbDG'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.fal_client='testdb.STD'
*.fal_server='testdb'
*.job_queue_processes=10
*.log_archive_config='dg_config=(testdb,testdbDG)'
*.log_archive_dest_1='location=/bakup/archive LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=testdbDG'
*.log_archive_dest_2='SERVICE=testdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/oradata/testdb','/oradata/testdb','/bakup/archive','/bakup/archive'
*.open_cursors=300
*.pga_aggregate_target=1244659712
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=665
*.sga_target=3735027712
*.standby_archive_dest='/bakup/archive'
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/testdb/udump'
sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup nomount;
10、 使用rman duplicate 恢复standby数据库
(在备库上执行)
rman target sys/abc123@testdb auxiliary /
rman> duplicate target database for standby;
11、如果要实现real time apply 需要在备库创建STANDBY LOGFILE,否则可以忽略此步骤
在单节点主库环境下,如果主库有四组日志文件组,因此备库应添加(4+1)*1=5组standby日志文件组。
TANDBY LOGFILE的大小必须(不能小于)等于主库日志文件大小。
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11
'/oradata/testdb/standby_redo01.log' size 524288000;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12
'/oradata/testdb/standby_redo02.log' size 524288000;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13
'/oradata/testdb/standby_redo03.log' size 524288000;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14
'/oradata/testdb/standby_redo04.log' size 524288000;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15
'/oradata/testdb/standby_redo05.log' size 524288000;
SQL> SELECT * FROM V$STANDBY_LOG;
12、 启动Standby
SQL>ALTER DATABASE recover managed standby database disconnect from session;
如果用real time apply则(可选)
SQL>ALTER DATABASE recover managed standby database using current logfile disconnect;
SQL>alter system set log_archive_dest_2='SERVICE=testdb.STD LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbDG';
13、配置Primary,通过spfile文件动态修改方法可以保证主库可用性并业务不受影响
SQL>alter system set log_archive_config='DG_CONFIG=(testdb,testdbDG)';
SQL>alter system set log_archive_dest_1='location=/bakup/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=testdb';
SQL>alter system set log_archive_dest_2='SERVICE=testdb.STD VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbDG';
SQL>alter system set db_file_name_convert=''/oradata/testdb','/oradata/testdb' ;
SQL>alter system set log_file_name_convert='/oradata/testdb','/oradata/testdb','/bakup/archive','/bakup/archive';
SQL>alter system set standby_file_management=auto;
SQL>alter system set fal_server='testdb.STD';
SQL>alter system set fal_client='testdb';
SQL>alter system set log_archive_dest_state_1='ENABLE';
SQL>alter system set log_archive_dest_state_2='ENABLE';
查看主库完整初始化文件:
[oracle@dbserv dbs]$ sqlplus / as sysdba
SQL> create pfile='/tmp/inittestdb.ora' from spfile;
File created.
[oracle@dbserv ~]$ cd /tmp
[oracle@dbserv tmp]$ more inittestdb.ora
testdb.__db_cache_size=6492782592
testdb.__java_pool_size=16777216
testdb.__large_pool_size=16777216
testdb.__shared_pool_size=973078528
testdb.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/testdb/adump'
*.background_dump_dest='/opt/oracle/admin/testdb/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/oradata/testdb/control01.ctl','/oradata/testdb/control02.ctl','/oradata/testdb/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/testdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='/oradata/testdb','/oradata/testdb'
*.db_name='testdb'
*.db_recovery_file_dest='/bakup/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='testdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.FAL_CLIENT='testdb'
*.FAL_SERVER='testdb.STD'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(testdb,testdbDG)'
*.log_archive_dest_1='location=/bakup/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=testdb'
*.log_archive_dest_2='SERVICE=testdb.STD VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbDG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/oradata/testdb','/oradata/testdb','/bakup/archive','/bakup/archive'
*.open_cursors=1000
*.pga_aggregate_target=2503999488
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=665
*.sga_target=7514095616
*.standby_archive_dest='/bakup/archive'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/testdb/udump'
[oracle@dbserv tmp]$
14、检查备库状态
SQL>SELECT DATABASE_ROLE, PROTECTION_MODE, SWITCHOVER_STATUS, FLASHBACK_ON FROM v$database;
SQL>SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
SQL>SELECT DEST_ID,ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
SQL>Select max(SEQUENCE#) from v$archived_log al,v$database db
2>where al.RESETLOGS_CHANGE#=db.RESETLOGS_CHANGE#
2>and al.APPLIED='YES';
15、验证
Standby端:
select sequence#, thread#, applied from v$archived_log order by sequence#;
Primary端:
SQL>alter database set standby database to maximize AVAILABILITY;
SQL>conn system/onewave
SQL>create table t tablespace users as select * from dba_objects;
SQL>alter system archive log current;
Standby端:
select sequence#, thread#, applied from v$archived_log order by sequence#;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
select count(*) from system.t;
表中有记录,说明备库已应用日志。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18841027/viewspace-1059101/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18841027/viewspace-1059101/