使用duplicate搭建备库或者级联备库&主备库switch over切换

gsforget321

已于 2024-08-27 18:14:35 修改

阅读量795

点赞数 13

分类专栏： Oracle日常维护文章标签：数据库 oracle 级联备库 duplicate swichover

于 2024-08-14 15:10:21 首次发布

本文链接：https://blog.csdn.net/gsforget321/article/details/141191567

版权

Oracle日常维护专栏收录该内容

15 篇文章 0 订阅

订阅专栏

使用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

connected to target database: HGXX (DBID=893348868)
connected to auxiliary database: HGXX (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 14-AUG-24
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=357 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwhdccdg1' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwhdccdg5' ;
}
executing Memory Script

Starting backup at 14-AUG-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=305 device type=DISK
Finished backup at 14-AUG-24

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/HGXXDG5/controlfile/control01.ctl';
}
executing Memory Script

Starting backup at 14-AUG-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_hdccdg1.f tag=TAG20240814T111029 RECID=42 STAMP=1176981029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-AUG-24

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/HDCCDG5/datafile/o1_mf_temp_gclf6dvf_.tmp";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/hgxxdg1/system.256.1005835259";
...中间省略
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/hgxxdg5/system.256.1005835259" datafile
...中间省略
}
executing 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
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/hgxxdg1/html.326.1005901859
output file name=/u01/app/oracle/oradata/hgxxdg5/html.326.1005901859 tag=TAG20240814T111036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:06:35

input datafile file number=00004 name=/u01/app/oracle/oradata/hgxxdg1/users.259.1005835259
output file name=/u01/app/oracle/oradata/hgxxdg5/users.259.1005835259 tag=TAG20240814T111036
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

...中间省略

Finished backup at 14-AUG-24

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=42 STAMP=1176985399 file name=/u01/app/oracle/oradata/hgxxdg5/system.256.1005835259

...中间省略

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';

主备库switch over切换

主备库switchover前提是主备库正常同步后再进行切换：

1. 查看主库状态
select database_role,switchover_status from v$database;

2. 主库状态正确，切换成主库
alter database commit to switchover to physical standby with session shutdown;

以下为具体输出步骤
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> select database_role,switchover_status from v$database;
select database_role,switchover_status from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 28863
Session ID: 853 Serial number: 15

备库切换成主库
1. 查看备库状态
select database_role,switchover_status from v$database;

2. 备库状态正确，切换成主库
alter database commit to switchover to primary with session shutdown;

以下为具体输出步骤
SYS>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED

SYS>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY

SYS>alter database commit to switchover to primary with session shutdown;

Database altered.

SYS>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY NOT ALLOWED

SYS>select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PRIMARY