rman duplicate standby database
经过几天的折腾，自己的备库终于算搞好了，以前很模糊的概念，经过这次折腾也理解的差不多了。
系统环境和数据库版本
VM+CENT OS 4.4+ORACLE 10.2 FOR LINUX 32
先说下创建步骤吧
一，首先把备库的系统和ORACLE软件安装好，并建好相关目录。比如说
/u01/app/admin/orcl/bdump
/u01/app/admin/orcl/adump
/u01/app/admin/orcl/cdump
‘/u01/app/admin/orcl/bdump
/u01/app/oradata/orcl
/u01/app/flash_recovery_area/
/u01/app/flash_recovery_area/ORCL/archivelog 我的日志存储位置
二、在主库创建数据全备和备库控制文件
注意在备库创建的备份位置，和要主库备份位置一样
登陆rman做全库备份和STANDBY控制文件
backup full database format=‘/u01/app/backup/fulldatabase_%U.dbf‘ include current controlfile for standby;
做完备份后
在备库scp 10.64.21.240:/u01/app/backup/* .把备份拷贝过来。
三、制作备库的参数文件和密码文件
首先确认当前数据的模式，如果是不是归档模式 ，重启数据库到MOUNT状态
startup mount
alter database archivelog ;
alter database flashback on;
alter database open;
然后将数据库改为强制归档模式
alter database force logging;
查询以下 是否是强制归档模式：
SQL> select force_logging from v$database;
FOR
—
YES
设置主库和备库的日志存放和FAL_SERVER FAL_CLIENT
alter system set log_archive_dest_1 = ‘locattion=/u01/app/flash_recovery_area/ORCL/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=primary’scope=spfile;
alter system set log_archive_dest_2= ‘’service=standby valid_for=(online_logfiles,primary_role) db_unique_name=standby lgwr sync affirm’ 我选择的实时日志创送模式
然后将日志1，2启用
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2= ENABLE;
设置FAL_SERVER 和FAL_CLIENT
ALTER SYSTEM SET FAL_SERVER =’standby’
ALTER SYSTEM SET FAL_client =’primary
启用备库文件自动管理： ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO;
设置备库接收日志的位置
alter system set standby_archive_dest=’/u01/app/flash_recovery_area/ORCL/archivelog ’scope=spfile
设置实例的唯一名字
alter system set db_unique_name=primary scope=spfile;
设置 服务名
alter system set service_names=primary scope=spfile;
alter system set standby_archive_config=(primary,standby)scope=spfile;
为备库创建参数文件
create pfile from spfile
然后将备库的参数文件传送到备库。
修改pfile文件。
具体参数文件如下：要注意修改的红色部分，我已经修改好
orcl.__db_cache_size=79691776
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=75497472
orcl.__streams_pool_size=0
*.audit_file_dest=’/u01/app/admin/orcl/adump’
*.background_dump_dest=’/u01/app/admin/orcl/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u01/app/oradata/orcl/control01.ctl’,'/u01/app/oradata/orcl/control02.ctl’,'/u01/app/oradata/orcl/control03.ctl’
*.core_dump_dest=’/u01/app/admin/orcl/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’orcl’
*.db_recovery_file_dest=’/u01/app/flash_recovery_area/’
*.db_recovery_file_dest_size=2147483648
*.db_unique_name=’STANDBY’
*.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=/u01/app/flash_recovery_area/ORCL/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=standby’
*.log_archive_dest_2 = ’service=primary valid_for=(online_logfiles,primary_role) db_unique_name=primary lgwr sync affirm ‘
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=167772160
*.standby_file_management=’AUTO’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/admin/orcl/udump’
以下为主库的参数文件：
orcl.__db_cache_size=79691776
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=75497472
orcl.__streams_pool_size=0
*.audit_file_dest=’/u01/app/admin/orcl/adump’
*.background_dump_dest=’/u01/app/admin/orcl/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u01/app/oradata/orcl/control01.ctl’,'/u01/app/oradata/orcl/control02.ctl’,'/u01/app/oradata/orcl/control03.ctl’
*.core_dump_dest=’/u01/app/admin/orcl/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’orcl’
*.db_recovery_file_dest=’/u01/app/flash_recovery_area/’
*.db_recovery_file_dest_size=2147483648
*.db_unique_name=’PRIMARY’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.fal_client=’primary’
*.fal_server=’standby’
*.job_queue_processes=10
*.log_archive_config=’DG_CONFIG=(primary,standby)’
*.log_archive_dest_1 = ‘location=/u01/app/flash_recovery_area/ORCL/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=primary’
*.log_archive_dest_2 = ’service=standby valid_for=(online_logfiles,primary_role) db_unique_name=standby lgwr sync affirm’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=167772160
*.standby_archive_dest=’/u01/app/flash_recovery_area/ORCL/archivelog’
*.standby_file_management=’AUTO’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/admin/orcl/udump’
四、配置监听和TNS
我的主库监听如下：要 注意的就是SERVIE_NAME，如果你修改过，那最好看下是否一直
cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SERVICE_NAME=primary)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=ORCL)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.64.21.240)(PORT = 1521))
)
)
主库的TNS配置：
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.64.21.241)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME = standby)
)
)
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.64.21.240)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME = primary)
)
)
备库的监听：
cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SERVICE_NAME=standby)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.64.21.241)(PORT = 1521))
)
)
备库的TNS配置：
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.64.21.241)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME = standby)
)
)
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.64.21.240)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME = primary)
)
)
配置完后最好用TNSPING下能相互PING通
五、创建备库：
在主库登陆RMAN并且登陆到 备库
RMAN TAREGET /
CONNECT AUXILIARY SYS/BOSON@STANDBY
然后开始恢复备库（注意此时主库必须处于MOUNT状态 备库处于NOMOUNT状态）
duplicate target database for standby database (如果出现重名的错误加上nofilenamecheck参数）
然后可以登陆到备库
添加重做日志文件，因为我选的是LGWR实时的。必须创建最好比主库多(注意大小 和主库一样）
alter database add standby logfile group 4 /u01/app/oradata/orcl/redo04.log’size 50m;
alter database add standby logfile group 5 /u01/app/oradata/orcl/redo05.log’size 50m;
alter database add standby logfile group 6 /u01/app/oradata/orcl/redo06.log’size 50m;
alter database add standby logfile group 7/u01/app/oradata/orcl/redo07.log’size 50m;
然后将备库置于恢复模式
alter database reover managed standby database disconnect from session;
现在可以在备库执行日志切换看备库的ALER.LOG文件。或者查看视图
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/flash_recovery_area/ORCL/archivelog
Oldest online log sequence 44
Next log sequence to archive 46
Current log sequence 46
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 5: ‘/u01/app/oradata/orcl/redo04.log’
Fri Dec 7 02:37:12 2007
Media Recovery Log /u01/app/flash_recovery_area/ORCL/archivelog/1_45_640523332.dbf
Media Recovery Waiting for thread 1 sequence 46 (in transit)
然后 看下备库的日志应用情况：
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
———- —
37 YES
38 YES
39 YES
40 YES
41 YES
42 YES
43 YES
44 YES
45 YES
9 rows selected.
看下 MAP和GAP状态
SQL> select process,status from v$managed_standby;
PROCESS STATUS
——— ————
ARCH CLOSING
ARCH CLOSING
RFS IDLE
MRP0 WAIT_FOR_LOG
RFS IDLE
RFS IDLE
6 rows selected.
都是正常状态。
还有一些注意的参数：
LOCK_NAME_SPACE —-这是当主数据库和备用数据在同一台机上时要设该参数，设为备用数据库的SID
DB_FILE_NAME_CONVERT —-当primary和standby的数据文件路径不一致时使用
LOG_FILE_NAME_CONVERT —-当primary和standby的数据文件路径不一致时使用
在standby上，当设置standby_file_management为auto时，不允许下列操作
alter database rename
alter database add/drop logfile
alter database add/drop standby logfile member
alter database create datafile as
