宜宾县企业项目建设影响环境登记表网站网站友链查询接口
使用duplicate搭建备库或者级联备库：
主库或者源端：
1. 创建pfile，更改&添加部分参数、传输到备库；
2. 主库（或者源端）的tnsnames.ora文件添加 备库的连接信息
备库：
1. 备库添加静态监听
2. 添加主备库信息到TNSNAMES.ORA文件中（可选）
3. 将主库的orapw$SID 文件拷贝到备库，可使用sqlplus sys/password@primary as sysdba 验证密码正确与否
4. 备库 创建initstb.ora 里面的目录，并使用此pfile启动到Nomount状态
主库或者源端：
以上都做完，在主库或者源端做rman连接，使用duplicate进行复制了
以上是主备库需要做的事情汇总，
以下是使用duplicate复制的具体步骤
主库或者源端：
创建pfile，更改&添加部分参数、传输到备库这里我没有使用主库，而是主库的ADG备库作为源端：
1. sqlplus / as sysdba登录ADG备库
create pfile='/home/oracle/initstb.ora' from spfile;
去/home/oracle找到initstb.ora文件，替换里面所有的hgxx为 hgxxdg5 ,可添加如下参数，scp传输到备库
*.db_file_name_convert='hgxxdg1','hgxxdg5','/u01/app/oracle/oradata/HGXXDG1/datafile','/u01/app/oracle/oradata/HGXXDG5/datafile'
*.log_file_name_convert='hgxxdg1','hgxxdg5'
*.standby_file_management='auto'
*.db_create_file_dest='/u01/app/oracle/oradata'
*.log_archive_config='dg_config=(hgxx,hgxxdg5)'
log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/HDCC/archivelog
2. 主库tnsnames.ora 添加备库信息
cat $ORACLE_HOME/network/admin/tnsnames.ora
hgxxdg5 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hgxxdg5 )
)
)
备库：
1. 备库添加静态监听
cat $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = hgxx)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = hgxxdg5)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
2. 添加主备库信息到TNSNAMES.ORA文件中（可选）
cat $ORACLE_HOME/network/admin/tnsnames.ora
hgxx =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hgxx)
)
)
hgxxdg5 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hgxxdg5 )
)
)
3. 将主库的orapw$SID 文件拷贝到备库，可使用sqlplus sys/password@primary as sysdba 验证密码正确与否
4. 备库 创建initstb.ora 里面的目录，并使用此pfile启动到Nomount状态
4.1 在备库创建initstb.ora里面的目录&数据文件的目录，目录存在的话，跳过
mkdir -p /u01/app/oracle/admin/hgxxdg1/adump
mkdir -p /u01/app/oracle/oradata/hgxxdg1/controlfile/
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/fast_recovery_area
4.2启动要duplicate的备库到nomount状态
startup nomount pfile='/home/oracle/initstb.ora';
create spfile from pfile='/home/oracle/initstb.ora';
再回到主库或者源端使用rman登录，并duplicate复制
rman target sys/j5JP8zIe@HDCC auxiliary sys/j5JP8zIe@hdccdg5
duplicate target database for standby from active database nofilenamecheck;
以下是输出结果：
| [oracle@D2-CC-DB78 admin]$ rman target sys/password@primary auxiliary sys/password@standby Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 14 11:10:24 2024 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: HGXX (DBID=893348868) RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 14-AUG-24 contents of Memory Script: Starting backup at 14-AUG-24 contents of Memory Script: Starting backup at 14-AUG-24 contents of Memory Script: sql statement: alter database mount standby database contents of Memory Script: executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/HGXXDG5/datafile/o1_mf_temp_gclf6dvf_.tmp in control file executing command: SET NEWNAME ...中间省略 Starting backup at 14-AUG-24 input datafile file number=00004 name=/u01/app/oracle/oradata/hgxxdg1/users.259.1005835259 ...中间省略 Finished backup at 14-AUG-24 contents of Memory Script: datafile 1 switched to datafile copy ...中间省略 Finished Duplicate Db at 14-AUG-24 |
主库或者源库修改如下参数，也可以直接写在Init文件中：
alter system set log_archive_config='DG_CONFIG=(hgxxdg1,hgxxdg5)' scope=both sid='*';
alter system set log_archive_dest_5 ='SERVICE=hgxxdg5 LGWR ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=hgxxdg5' scope=both sid='*';
alter system set log_archive_dest_state_5=defer scope=both sid='*';
alter system set log_archive_dest_state_5=enable scope=both sid='*';
备库参数修改如下：
alter system set log_archive_dest_3 ='SERVICE=standby78 LGWR ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=hgxxdg1' scope=both sid='*';
alter system set log_archive_dest_state_3=defer scope=both sid='*';
alter system set log_archive_dest_state_3=enable scope=both sid='*';
备库开启恢复进程
alter database recover managed standby database using current logfile disconnect from session;
查看恢复进程是否开启以及传输日志进程
set linesize 400;
select process,pid,status,thread#,sequence# from v$managed_standby;
PROCESS PID STATUS THREAD# SEQUENCE#
--------------------------- --------------- ------------------------------------ --------------- ---------------
ARCH 31943 CONNECTED 0 0
ARCH 31946 CONNECTED 0 0
ARCH 31948 CONNECTED 0 0
ARCH 31950 CONNECTED 0 0
MRP0 15347 WAIT_FOR_LOG 1 19717
RFS 18007 IDLE 0 0
RFS 15797 IDLE 0 0
查看备库alert日志，正在恢复数据了，这个是自动传输到备库的，并且开始恢复
Wed Aug 14 13:50:51 2024
Media Recovery Waiting for thread 1 sequence 19717
或者执行以下语句，查看备库执行过的log，跟主库或源端进行对比
SYS>select thread#,max(sequence#) from v$log_history group by thread#;
THREAD# MAX(SEQUENCE#)
--------------- ---------------
1 19716
2 15537
开启数据库为只读状态
SYS>alter database open;
Database altered.
SYS>alter database recover managed standby database using current logfile disconnect from session;
Database altered.
查看备库的状态
SYS>select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
以下其他数据字典表也可以作为参考：
查看应用日志延迟时间：
SQL> select value from v$dataguard_stats where name='apply lag';
查看接收日志延迟时间：
SQL> select value from v$dataguard_stats where name='transport lag';
查看接受日志情况：
SQL> select max(sequence#) from v$archived_log where applied='YES';