standby数据库的一些笔记
时间：2010-08-31 来源：osdba
使用LGWR 进程的SYNC 方式
alter system set log_archive_dest_2 = 'SERVICE=ST LGWR SYNC NET_TIMEOUT=30' scope=both;
alter system set log_archive_dest_2 = 'SERVICE=ST LGWR SYNC AFFIRM NET_TIMEOUT=30' scope=both;
使用LGWR进程的ASYNC 方式 alter system set log_archive_dest_2 = 'SERVICE=ST LGWR ASYNC ' scope=both;
在日志接收中，需要注意的是归档日志会被放在什么位置： 1） 如果配置了STANDBY_ARCHIVE_DEST 参数，则使用该参数指定的目录。 2） 如果某个LOG_ARCHIVE_DEST_n 参数明确定义了VALID_FOR=(STANDBY_LOGFILE,*)选项，则使用这个参数指定的目录。 3） 如果数据库的COMPATIBLE参数大于等于10.0，则选取任意一个LOG_ARCHIVE_DEST_n的值。 4） 如果STANDBY_ARCHIVE_DEST 和 LOG_ARCHIVE_DEST_n 参数都没有配置，使用缺省的STANDBY_ARCHIVE_DEST参数值，这个缺省值是$ORACLE_HOME/dbs/arc.
修改数据保护模式步骤 1）关闭数据库，重启到Mount 状态，如果是RAC，需要关闭所有实例，然后只启动一个实例到mount状态。 2）修改模式： 语法：ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE}; 如：SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION; 3) 打开数据库： alter database open; 4) 确认修改数据保护模式： SQL>select protection_mode,protection_level from v$database;
alter database add standby logfile group 11 '/opt/oracle/oradata/orateststb/redo_11_1.log' size 200M;
如果是Physical Standby，可以使用下面命令启用Real-Time： Alter database recover managed standby database using current logfile disconnect; //Alter database recover managed standby database using current logfile; 如果是Logical Standby，可以使用下面命令启用Real-Time： Alter database start logical standby apply immediate; 查看是否使用Real-Time apply： Select recovery_mode from v$archive_dest_status;
SELECT * FROM V$DATAGUARD_STATS;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
=======================================11g的搭建方法 ============================================ 把主库的password文件拷贝到standby数据库上, password文件的格式为orapw<sid> 主要主库上没有使用password文件，需要建在主库上建password文件： orapwd file=orapworateststd password=sys entries=10
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/create mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/scripts mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID
在要建standby数据库的主机上建安pfile： vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora，加入下面两行：
remote_login_passwordfile=exclusive DB_NAME=oratest
在standby数据库上建立监听 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oratest2)(PORT = 1521)) ) )
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = oratest.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/11.1.0) (SID_NAME = orateststd) ) )
在主库上的tnsnames.ora文件中添加到standby的服务名 orateststb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.129.148)(PORT = 1521)) ) (CONNECT_DATA = (sid = orateststd) ) )
在主库上：
先进sqlplus中切换几组日志： sqlplus "/ as sysdba" alter system switch logfile; alter system switch logfile;
然后进rman复制数据库： rman
connect target /; connect auxiliary sys/sys@orateststb;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT '/opt/oracle/oradata/oratest', '/opt/oracle/oradata/orateststb' SET db_unique_name='oratest' SET SGA_MAX_SIZE = '2608M' SET SGA_TARGET = '2608M' SET LOG_FILE_NAME_CONVERT = '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb' DB_FILE_NAME_CONVERT '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb';
也可以指定： set control_files='+data'
如果是复制数据库，然后并打开，而不是建一个standby数据库，则语句如下： DUPLICATE TARGET DATABASE for standby TO orateststb FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT '/opt/oracle/oradata/oratest', '/opt/oracle/oradata/orateststb' SET SGA_MAX_SIZE = '2608M' SET SGA_TARGET = '2608M' SET LOG_FILE_NAME_CONVERT = '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb' DB_FILE_NAME_CONVERT '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb';
如果报下面的错误，可能是主库的归档设置不正确，导致无法正确归档
RMAN-03002: failure of Duplicate Db command at RMAN-20208: UNTIL CHANGE is before RESETLOGS change
alter system switch logfile;
主库上： log_archive_dest_1='location=/u01/app/oracle/arch/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' log_archive_dest_2='SERVICE=orateststb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.fal_client=oratest *.fal_server=orateststb
alter system set db_file_name_convert='/opt/oracle/oradata/orateststb','/opt/oracle/oradata/oratest' scope=spfile; alter system set log_file_name_convert='/opt/oracle/oradata/orateststb','/opt/oracle/oradata/oratest' scope=spfile;
备库上： log_archive_dest_1='location=/u01/app/oracle/arch/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' log_archive_dest_2='SERVICE=oratest LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' fal_client=orateststb fal_server=oratest
alter system set db_file_name_convert='/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb' scope=spfile;
另外的示例：
connect target sys/oracle123@prolin11 connect auxiliary sys/oracle123@pro11sb run { allocate channel c1 type disk; allocate auxiliary channel s1 type disk; duplicate target database for standby from active database dorecover spfile parameter_value_convert 'prolin11','pro11sb' set db_unique_name='pro11sb' set db_file_name_convert='/prolin11/','/pro11sb/' set log_file_name_convert='/prolin11/','/pro11sb/' set control_files='/oradata/pro11sb/control01.ctl' set fal_client='pro11sb' set fal_server='prolin11' set standby_file_management='AUTO' set log_archive_config='dg_config=(prolin11,pro11sb)' set log_archive_dest_2='service=prolin11 LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=pro11sb' set log_archive_dest_state_2='enable' set log_archive_format='pro11sb_%t_%s_%r.arc' ; sql channel c1 "alter system archive log current"; sql channel s1 "alter database recover managed standby database using current logfile disconnect"; }
=========================================== 方法一 =========================================== 一、搭建standby实例环境。 1. 在主数据库中： create pfile from spfile; 通过产生的这个initboss.ora文件拷贝到standby数据库的机器上（如果在同一台机器中改名为initbossstd.ora)，修改或添加如下内容: *.background_dump_dest='D:\oracle\admin\bossstd\bdump' *.control_files='D:\oracle\oradata\bossstd\control01.ctl' *.core_dump_dest='D:\oracle\admin\bossstd\cdump' *.db_file_name_convert='D:\oracle\oradata\boss','D:\oracle\oradata\bossstd' *.log_file_name_convert='D:\oracle\oradata\boss','D:\oracle\oradata\bossstd' *.standby_archive_dest='D:\oracle\oradata\bossstd\LOG' *.log_archive_dest_1='location=D:\oracle\oradata\bossstd\log' *.user_dump_dest='D:\oracle\admin\bossstd\udump' *.lock_name_space=bossstd *.instance_name='bossstd' *.fal_client=boss *.fal_server=bossstd *.standby_file_management=AUTO 注意如果在与主数据库相同的机器上建standby数据库，需要修改instance_name和lock_name_space。
*.db_files=2000 2.把主库的passwd文件拷贝到standby库的主机上： orapwd file=PWDbossstd password=sys entries=10 检查standby数据库是否使用了password file: select * from v$pwfile_users; show parameter remote_login_passwordfile; remote_login_passwordfile参数需要是：EXCLUSIVE
测试是否能连接到数据库： sqlplus sys/sys@bossstb as sysdba 3.启动standby实例，看是否正常 export ORACLE_SID=bossstd sqlplus "sys/sys as sysdba" startup nomount pfile='....../initbossstd.ORA'; 能正常启动。 二、拷贝standby的数据文件。 生成standby的控制文件： alter database create standby controlfile as '/u01/oracle/backup/contrl01.ctl'; 使用冷拷贝或rman工具把数据文件拷贝到新的standby数据库环境。 在standby数据库环境： alter database mount standby database; 三、配置监听，让主库能把log文件归档到standby数据库环境中 #主库的监听 BOSS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hq-it-092486m)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = boss) ) ) #standby库的监听 BOSSSTD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hq-it-092486m)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = bossstd) ) ) 用sqlplus "sys/sys@bossstd"测试监听是否正常。 到主数据库中，修改参数，让其能归档到standby数据库中： alter system set log_archive_dest_2='SERVICE=bossstd REOPEN=300' scope=both; alter system set log_archive_dest_state_2=enable scope=both; 在主数据库中做测试，看归档是否正常 alter system switch logfile; 四、在standby库中恢复日志 可以先： recover standby database; 最后让其自动滚日志： alter database recover managed standby database disconnect from session;
select sequence#,applied from v$archived_log;
=========================================== 方法二 duplicate standby =========================================== duplicate standby
在Oracle9i中使用RMAN进行DataGuard数据库的创建过程中，可能会遇到如下错误：
RMAN> duplicate target database for standby dorecover;
RMAN-03002: failure of Duplicate Db command at 11/16/2007 10:23:12 RMAN-05507: standby controlfile checkpoint (23456133) is more recent than duplication point in time (23412363)
这个错误是说控制文件教新，也就是说相较数据文件而言，控制文件是新的，这是由于控制文件的持续更新的原因。 所以在执行Duplicate之前，我们需要在源数据库执行一次LOG ARCHIVE动作，增加一下检查点。
SQL> alter system archive log current; System altered.
Oracle文档对这个错误的解释如下：
RMAN-05507: standby controlfile checkpoint (string) is more recent than duplication point in time (string) Cause: A DUPLICATE FOR STANDBY command was issued, but the checkpoint of the controlfile is more recent than the last archivelog or the specified point in time. Action: If an explicit point in time was specified, change it to be at least the controlfile checkpoint; otherwise archive (and backup/copy) the current log.
archive log current正式解决这个问题的方法之一！
===========================通过修改数据库实例名的方法复制数据库=====================
在复制操作中最重要的步骤一个是需要修改参数配置中的相应实例名信息，
另一个就是需要删除控制文件，并且重建控制文件时要更改 reuse database old_sid noresetlogs 为 set database new_sid resetlogs. 步骤如下:
源数据库为oratest1，复制的目标数据库为oratest2
1. export ORACLE_SID=oratest2 mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump
istener.ora中增加监听 vi /opt/oracle/products/10.2/network/admin/listener.ora
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = oratest.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/10.2) (SID_NAME = oratest) ) (SID_DESC = (GLOBAL_DBNAME = oratest2.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/10.2) (SID_NAME = oratest2) ) )
orapwd file=orapwaux password=sys entries=10 select * from v$pwfile_users;
创建init.ora文件 cp $ORACLE_HOME/dbs/initoratest1.ora $ORACLE_HOME/dbs/initoratest2.ora 注意修改db_name，instance_name及control_files参数
2.创建服务(WINDOWS平台使用) cd %ORACLE_HOME%/bin oradim -new -sid orcl -startmode manual -pfile "c:/.../initorcl.ora"
3.准备重建控制文件的语句 在原先的实例中： alter database backup controlfile to trace; 找到相应的trace file，修改中间的建control file的语句， 主要修改 REUSE DATABASE "ORATEST" NORESETLOGS 为set DATABASE "ORATEST2" RESETLOGS
4. 把新的数据文件复制到新的位置 需要把源数据库停下来拷贝 cp /opt/oracle/oradata/oratest1/* /opt/oracle/oradata/oratest2
5. 创建控制文件，打开新的数据库
startup nomount
CREATE CONTROLFILE set DATABASE "ORATEST2" RESETLOGS NOARCHIVELOG MAXLOGFILES 20 MAXLOGMEMBERS 5 MAXDATAFILES 500 MAXINSTANCES 2 MAXLOGHISTORY 584 LOGFILE GROUP 1 '/opt/oracle/oradata/oratest2/redo_1_1.log' SIZE 500M, GROUP 2 '/opt/oracle/oradata/oratest2/redo_2_1.log' SIZE 500M, GROUP 3 '/opt/oracle/oradata/oratest2/redo_3_1.log' SIZE 500M -- STANDBY LOGFILE DATAFILE '/opt/oracle/oradata/oratest2/system01.dbf', '/opt/oracle/oradata/oratest2/undotbs01.dbf', '/opt/oracle/oradata/oratest2/sysaux01.dbf', '/opt/oracle/oradata/oratest2/user01.dbf' CHARACTER SET US7ASCII ;
alter database open resetlogs;
给数据库的临时表空间增加文件 alter tablespace temp add tempfile '/opt/oracle/oradata/oratest2/temp01.dbf' size 2048M reuse;
=============================duplicate_database==========================
orapwd file=orapwaux password=sys entries=10 select * from v$pwfile_users;
建aux实例的init$ORACLE_SID.ora文件： db_domain='db.alibaba.com' db_name='aux'
sga_max_size=2600M sga_target=2600M java_pool_size=16777216 large_pool_size=16777216 shared_pool_size=838860800 pga_aggregate_target=1G
processes=500
audit_file_dest='/opt/oracle/admin/aux/adump' background_dump_dest='/opt/oracle/admin/aux/bdump' user_dump_dest='/opt/oracle/admin/aux/udump' core_dump_dest='/opt/oracle/admin/aux/cdump'
compatible='10.2.0.1.0'
db_block_size=8192 job_queue_processes=10 open_cursors=300 remote_os_authent=FALSE remote_login_passwordfile=EXCLUSIVE
export ORACLE_SID=aux mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump
listener.ora中增加监听 vi /opt/oracle/products/10.2/network/admin/listener.ora
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = oratest.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/10.2) (SID_NAME = oratest) ) (SID_DESC = (GLOBAL_DBNAME = oratest.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/10.2) (SID_NAME = oratest2) ) (SID_DESC = (GLOBAL_DBNAME = oratest.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/10.2) (SID_NAME = aux) ) )
connect target sys/sys@oratest;
connect auxiliary sys/sys@oratest2;
run { ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK; set newname for datafile '/opt/oracle/oradata/oratest/system01.dbf' to '/opt/oracle/oradata/oratest2/system01.dbf'; set newname for datafile '/opt/oracle/oradata/oratest/undotbs01.dbf' to '/opt/oracle/oradata/oratest2/undotbs01.dbf'; set newname for datafile '/opt/oracle/oradata/oratest/sysaux01.dbf' to '/opt/oracle/oradata/oratest2/sysaux01.dbf'; set newname for datafile '/opt/oracle/oradata/oratest/user01.dbf' to '/opt/oracle/oradata/oratest2/user01.dbf'; set newname for tempfile '/opt/oracle/oradata/oratest/temp01.dbf' to '/opt/oracle/oradata/oratest2/temp01.dbf'; duplicate target database to oratest2 logfile GROUP 1 ('/opt/oracle/oradata/oratest2/tmpfs/redo_1_1.log') SIZE 500M REUSE, GROUP 2 ('/opt/oracle/oradata/oratest2/tmpfs/redo_2_1.log') SIZE 500M REUSE, GROUP 3 ('/opt/oracle/oradata/oratest2/tmpfs/redo_3_1.log') SIZE 500M REUSE; }
alter system set log_archive_dest_2 = 'SERVICE=ST LGWR SYNC AFFIRM NET_TIMEOUT=30' scope=both;
使用LGWR进程的ASYNC 方式 alter system set log_archive_dest_2 = 'SERVICE=ST LGWR ASYNC ' scope=both;
在日志接收中，需要注意的是归档日志会被放在什么位置： 1） 如果配置了STANDBY_ARCHIVE_DEST 参数，则使用该参数指定的目录。 2） 如果某个LOG_ARCHIVE_DEST_n 参数明确定义了VALID_FOR=(STANDBY_LOGFILE,*)选项，则使用这个参数指定的目录。 3） 如果数据库的COMPATIBLE参数大于等于10.0，则选取任意一个LOG_ARCHIVE_DEST_n的值。 4） 如果STANDBY_ARCHIVE_DEST 和 LOG_ARCHIVE_DEST_n 参数都没有配置，使用缺省的STANDBY_ARCHIVE_DEST参数值，这个缺省值是$ORACLE_HOME/dbs/arc.
修改数据保护模式步骤 1）关闭数据库，重启到Mount 状态，如果是RAC，需要关闭所有实例，然后只启动一个实例到mount状态。 2）修改模式： 语法：ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE}; 如：SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION; 3) 打开数据库： alter database open; 4) 确认修改数据保护模式： SQL>select protection_mode,protection_level from v$database;
alter database add standby logfile group 11 '/opt/oracle/oradata/orateststb/redo_11_1.log' size 200M;
如果是Physical Standby，可以使用下面命令启用Real-Time： Alter database recover managed standby database using current logfile disconnect; //Alter database recover managed standby database using current logfile; 如果是Logical Standby，可以使用下面命令启用Real-Time： Alter database start logical standby apply immediate; 查看是否使用Real-Time apply： Select recovery_mode from v$archive_dest_status;
SELECT * FROM V$DATAGUARD_STATS;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
=======================================11g的搭建方法 ============================================ 把主库的password文件拷贝到standby数据库上, password文件的格式为orapw<sid> 主要主库上没有使用password文件，需要建在主库上建password文件： orapwd file=orapworateststd password=sys entries=10
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/create mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/scripts mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID
在要建standby数据库的主机上建安pfile： vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora，加入下面两行：
remote_login_passwordfile=exclusive DB_NAME=oratest
在standby数据库上建立监听 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oratest2)(PORT = 1521)) ) )
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = oratest.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/11.1.0) (SID_NAME = orateststd) ) )
在主库上的tnsnames.ora文件中添加到standby的服务名 orateststb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.129.148)(PORT = 1521)) ) (CONNECT_DATA = (sid = orateststd) ) )
在主库上：
先进sqlplus中切换几组日志： sqlplus "/ as sysdba" alter system switch logfile; alter system switch logfile;
然后进rman复制数据库： rman
connect target /; connect auxiliary sys/sys@orateststb;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT '/opt/oracle/oradata/oratest', '/opt/oracle/oradata/orateststb' SET db_unique_name='oratest' SET SGA_MAX_SIZE = '2608M' SET SGA_TARGET = '2608M' SET LOG_FILE_NAME_CONVERT = '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb' DB_FILE_NAME_CONVERT '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb';
也可以指定： set control_files='+data'
如果是复制数据库，然后并打开，而不是建一个standby数据库，则语句如下： DUPLICATE TARGET DATABASE for standby TO orateststb FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT '/opt/oracle/oradata/oratest', '/opt/oracle/oradata/orateststb' SET SGA_MAX_SIZE = '2608M' SET SGA_TARGET = '2608M' SET LOG_FILE_NAME_CONVERT = '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb' DB_FILE_NAME_CONVERT '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb';
如果报下面的错误，可能是主库的归档设置不正确，导致无法正确归档
RMAN-03002: failure of Duplicate Db command at RMAN-20208: UNTIL CHANGE is before RESETLOGS change
alter system switch logfile;
主库上： log_archive_dest_1='location=/u01/app/oracle/arch/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' log_archive_dest_2='SERVICE=orateststb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.fal_client=oratest *.fal_server=orateststb
alter system set db_file_name_convert='/opt/oracle/oradata/orateststb','/opt/oracle/oradata/oratest' scope=spfile; alter system set log_file_name_convert='/opt/oracle/oradata/orateststb','/opt/oracle/oradata/oratest' scope=spfile;
备库上： log_archive_dest_1='location=/u01/app/oracle/arch/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' log_archive_dest_2='SERVICE=oratest LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' fal_client=orateststb fal_server=oratest
alter system set db_file_name_convert='/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb' scope=spfile;
另外的示例：
connect target sys/oracle123@prolin11 connect auxiliary sys/oracle123@pro11sb run { allocate channel c1 type disk; allocate auxiliary channel s1 type disk; duplicate target database for standby from active database dorecover spfile parameter_value_convert 'prolin11','pro11sb' set db_unique_name='pro11sb' set db_file_name_convert='/prolin11/','/pro11sb/' set log_file_name_convert='/prolin11/','/pro11sb/' set control_files='/oradata/pro11sb/control01.ctl' set fal_client='pro11sb' set fal_server='prolin11' set standby_file_management='AUTO' set log_archive_config='dg_config=(prolin11,pro11sb)' set log_archive_dest_2='service=prolin11 LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=pro11sb' set log_archive_dest_state_2='enable' set log_archive_format='pro11sb_%t_%s_%r.arc' ; sql channel c1 "alter system archive log current"; sql channel s1 "alter database recover managed standby database using current logfile disconnect"; }
=========================================== 方法一 =========================================== 一、搭建standby实例环境。 1. 在主数据库中： create pfile from spfile; 通过产生的这个initboss.ora文件拷贝到standby数据库的机器上（如果在同一台机器中改名为initbossstd.ora)，修改或添加如下内容: *.background_dump_dest='D:\oracle\admin\bossstd\bdump' *.control_files='D:\oracle\oradata\bossstd\control01.ctl' *.core_dump_dest='D:\oracle\admin\bossstd\cdump' *.db_file_name_convert='D:\oracle\oradata\boss','D:\oracle\oradata\bossstd' *.log_file_name_convert='D:\oracle\oradata\boss','D:\oracle\oradata\bossstd' *.standby_archive_dest='D:\oracle\oradata\bossstd\LOG' *.log_archive_dest_1='location=D:\oracle\oradata\bossstd\log' *.user_dump_dest='D:\oracle\admin\bossstd\udump' *.lock_name_space=bossstd *.instance_name='bossstd' *.fal_client=boss *.fal_server=bossstd *.standby_file_management=AUTO 注意如果在与主数据库相同的机器上建standby数据库，需要修改instance_name和lock_name_space。
*.db_files=2000 2.把主库的passwd文件拷贝到standby库的主机上： orapwd file=PWDbossstd password=sys entries=10 检查standby数据库是否使用了password file: select * from v$pwfile_users; show parameter remote_login_passwordfile; remote_login_passwordfile参数需要是：EXCLUSIVE
测试是否能连接到数据库： sqlplus sys/sys@bossstb as sysdba 3.启动standby实例，看是否正常 export ORACLE_SID=bossstd sqlplus "sys/sys as sysdba" startup nomount pfile='....../initbossstd.ORA'; 能正常启动。 二、拷贝standby的数据文件。 生成standby的控制文件： alter database create standby controlfile as '/u01/oracle/backup/contrl01.ctl'; 使用冷拷贝或rman工具把数据文件拷贝到新的standby数据库环境。 在standby数据库环境： alter database mount standby database; 三、配置监听，让主库能把log文件归档到standby数据库环境中 #主库的监听 BOSS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hq-it-092486m)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = boss) ) ) #standby库的监听 BOSSSTD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hq-it-092486m)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = bossstd) ) ) 用sqlplus "sys/sys@bossstd"测试监听是否正常。 到主数据库中，修改参数，让其能归档到standby数据库中： alter system set log_archive_dest_2='SERVICE=bossstd REOPEN=300' scope=both; alter system set log_archive_dest_state_2=enable scope=both; 在主数据库中做测试，看归档是否正常 alter system switch logfile; 四、在standby库中恢复日志 可以先： recover standby database; 最后让其自动滚日志： alter database recover managed standby database disconnect from session;
select sequence#,applied from v$archived_log;
=========================================== 方法二 duplicate standby =========================================== duplicate standby
在Oracle9i中使用RMAN进行DataGuard数据库的创建过程中，可能会遇到如下错误：
RMAN> duplicate target database for standby dorecover;
RMAN-03002: failure of Duplicate Db command at 11/16/2007 10:23:12 RMAN-05507: standby controlfile checkpoint (23456133) is more recent than duplication point in time (23412363)
这个错误是说控制文件教新，也就是说相较数据文件而言，控制文件是新的，这是由于控制文件的持续更新的原因。 所以在执行Duplicate之前，我们需要在源数据库执行一次LOG ARCHIVE动作，增加一下检查点。
SQL> alter system archive log current; System altered.
Oracle文档对这个错误的解释如下：
RMAN-05507: standby controlfile checkpoint (string) is more recent than duplication point in time (string) Cause: A DUPLICATE FOR STANDBY command was issued, but the checkpoint of the controlfile is more recent than the last archivelog or the specified point in time. Action: If an explicit point in time was specified, change it to be at least the controlfile checkpoint; otherwise archive (and backup/copy) the current log.
archive log current正式解决这个问题的方法之一！
===========================通过修改数据库实例名的方法复制数据库=====================
在复制操作中最重要的步骤一个是需要修改参数配置中的相应实例名信息，
另一个就是需要删除控制文件，并且重建控制文件时要更改 reuse database old_sid noresetlogs 为 set database new_sid resetlogs. 步骤如下:
源数据库为oratest1，复制的目标数据库为oratest2
1. export ORACLE_SID=oratest2 mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump
istener.ora中增加监听 vi /opt/oracle/products/10.2/network/admin/listener.ora
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = oratest.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/10.2) (SID_NAME = oratest) ) (SID_DESC = (GLOBAL_DBNAME = oratest2.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/10.2) (SID_NAME = oratest2) ) )
orapwd file=orapwaux password=sys entries=10 select * from v$pwfile_users;
创建init.ora文件 cp $ORACLE_HOME/dbs/initoratest1.ora $ORACLE_HOME/dbs/initoratest2.ora 注意修改db_name，instance_name及control_files参数
2.创建服务(WINDOWS平台使用) cd %ORACLE_HOME%/bin oradim -new -sid orcl -startmode manual -pfile "c:/.../initorcl.ora"
3.准备重建控制文件的语句 在原先的实例中： alter database backup controlfile to trace; 找到相应的trace file，修改中间的建control file的语句， 主要修改 REUSE DATABASE "ORATEST" NORESETLOGS 为set DATABASE "ORATEST2" RESETLOGS
4. 把新的数据文件复制到新的位置 需要把源数据库停下来拷贝 cp /opt/oracle/oradata/oratest1/* /opt/oracle/oradata/oratest2
5. 创建控制文件，打开新的数据库
startup nomount
CREATE CONTROLFILE set DATABASE "ORATEST2" RESETLOGS NOARCHIVELOG MAXLOGFILES 20 MAXLOGMEMBERS 5 MAXDATAFILES 500 MAXINSTANCES 2 MAXLOGHISTORY 584 LOGFILE GROUP 1 '/opt/oracle/oradata/oratest2/redo_1_1.log' SIZE 500M, GROUP 2 '/opt/oracle/oradata/oratest2/redo_2_1.log' SIZE 500M, GROUP 3 '/opt/oracle/oradata/oratest2/redo_3_1.log' SIZE 500M -- STANDBY LOGFILE DATAFILE '/opt/oracle/oradata/oratest2/system01.dbf', '/opt/oracle/oradata/oratest2/undotbs01.dbf', '/opt/oracle/oradata/oratest2/sysaux01.dbf', '/opt/oracle/oradata/oratest2/user01.dbf' CHARACTER SET US7ASCII ;
alter database open resetlogs;
给数据库的临时表空间增加文件 alter tablespace temp add tempfile '/opt/oracle/oradata/oratest2/temp01.dbf' size 2048M reuse;
=============================duplicate_database==========================
orapwd file=orapwaux password=sys entries=10 select * from v$pwfile_users;
建aux实例的init$ORACLE_SID.ora文件： db_domain='db.alibaba.com' db_name='aux'
sga_max_size=2600M sga_target=2600M java_pool_size=16777216 large_pool_size=16777216 shared_pool_size=838860800 pga_aggregate_target=1G
processes=500
audit_file_dest='/opt/oracle/admin/aux/adump' background_dump_dest='/opt/oracle/admin/aux/bdump' user_dump_dest='/opt/oracle/admin/aux/udump' core_dump_dest='/opt/oracle/admin/aux/cdump'
compatible='10.2.0.1.0'
db_block_size=8192 job_queue_processes=10 open_cursors=300 remote_os_authent=FALSE remote_login_passwordfile=EXCLUSIVE
export ORACLE_SID=aux mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump
listener.ora中增加监听 vi /opt/oracle/products/10.2/network/admin/listener.ora
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = oratest.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/10.2) (SID_NAME = oratest) ) (SID_DESC = (GLOBAL_DBNAME = oratest.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/10.2) (SID_NAME = oratest2) ) (SID_DESC = (GLOBAL_DBNAME = oratest.db.alibaba.com) (ORACLE_HOME = /opt/oracle/products/10.2) (SID_NAME = aux) ) )
connect target sys/sys@oratest;
connect auxiliary sys/sys@oratest2;
run { ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK; set newname for datafile '/opt/oracle/oradata/oratest/system01.dbf' to '/opt/oracle/oradata/oratest2/system01.dbf'; set newname for datafile '/opt/oracle/oradata/oratest/undotbs01.dbf' to '/opt/oracle/oradata/oratest2/undotbs01.dbf'; set newname for datafile '/opt/oracle/oradata/oratest/sysaux01.dbf' to '/opt/oracle/oradata/oratest2/sysaux01.dbf'; set newname for datafile '/opt/oracle/oradata/oratest/user01.dbf' to '/opt/oracle/oradata/oratest2/user01.dbf'; set newname for tempfile '/opt/oracle/oradata/oratest/temp01.dbf' to '/opt/oracle/oradata/oratest2/temp01.dbf'; duplicate target database to oratest2 logfile GROUP 1 ('/opt/oracle/oradata/oratest2/tmpfs/redo_1_1.log') SIZE 500M REUSE, GROUP 2 ('/opt/oracle/oradata/oratest2/tmpfs/redo_2_1.log') SIZE 500M REUSE, GROUP 3 ('/opt/oracle/oradata/oratest2/tmpfs/redo_3_1.log') SIZE 500M REUSE; }
相关阅读 更多 +
