来源: https://www.ucloud.cn/yun/129443.html 作者: IT那活儿 发布日期: 发布于2023-01-11 13:19

使用rman duplicate从standby创建新克隆

点击上方“IT那活儿**”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！**

环境信息

主库：

• Hosts: ora19c-node1
• ora19c-node2 db_unique_name: dbv
• instance name: dbv1, dbv2

第一备库：

• Hosts: ora19c-stby1
• ora19c-stby2 db_unique_name: dbv_stby
• instance name: dbv_stby1,dbv_stby2

第二备库或克隆：

• Hosts: ora19c-fga
• db_unique_name: dbv_fga
• instance_name dbv_fga

使用rman从第一备库创建第二备库

1. 确认第一个standby database 是open read only，并且在应用恢复：

SQL> select open_mode from v$database;OPEN_MODE--------------------READ ONLY WITH APPLY

2. 在第二备库配置静态监听：

LISTENER_duplicate =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = hwzsc-nyhd-bbk-db01)(PORT = 1521))))SID_LIST_LISTENER_duplicate =(SID_LIST =(SID_DESC =(SID_NAME = nyhdbbk1)(ORACLE_HOME = /db/oracle/product/19.3)))

3. 修改第一个备库的tnsname.ora 增加第二备库的tnsname：

DUP =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.38.30.17)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SID = nyhdbbk1)))

4. 从第一备库复制密码文件到第二备库：

cp orapwdbv_stby orapwdbv_fga

5. 创建参数文件,并启动实例到nomount：

cd $ORACLE_HOME/dbsvi initdbv_fga.oradb_name=dbvdb_unique_name=dbv_fgasga_target=5gsqlplus / as sysdbastartup nomount;

6. 在第二备库创建需要的目录：

mkdir -p /u01/app/oracle/dbv_fga/adump

7. 如果cluster_interconnections参数设置在第一个备用服务器上，则需要取消它：

SQL> alter system reset cluster_interconnects scope=spfile sid=dbv_stby1;SQL> alter system reset cluster_interconnects scope=spfile sid=dbv_stby2;

8. 在第一备库创建并执行rman 复制脚本：

#/bin/bashsource /home/oracle/.bash_profilerman target / auxiliary sys/xxxx@dup msglog=/home/oracle/dup.log<run {allocate channel prmy1 type disk;allocate channel prmy2 type disk;allocate channel prmy3 type disk;allocate channel prmy4 type disk;allocate auxiliary channel stby type disk;duplicate target database for standby from active databasespfileparameter_value_convert dbv_sdby,dbv_fgaset db_unique_name= dbv_fgaset db_create_file_dest=/db/oracle/oradataset db_recovery_file_dest=/db/oracle/oradata/fast_recovery_areaset db_file_name_convert=+DATA1,DATA1 ,+ARCH,+ARCHset log_file_name_convert=+DATA1,DATA1 ,+ARCH,+ARCHset control_files=/db/oracle/oradata/dbv_stby/control01.ctl, /db/oracle/oradata/stby/control02.ctlset log_archive_max_processes=5set fal_client=dbv_fgaset fal_server=dbv_stbyset standby_file_management=MANUALset log_archive_config=dg_config=(dbv,dbv_stby,dbv_fga)set log_archive_dest_2=service=nyhd ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dbv_fgaset cluster_database=false;}exitEOF

完成第二备库的RAC配置

1. 创建pfile 并修改RAC相关的参数：

SQL> create pfile=/tmp/p.ora from spfile;

修改 cluster_database = true

2. 创建spfile 到磁盘组：

SQL> create spfile=+/dbv_fga/spfile_fga.ora from pfile=/tmp/p.ora;

3. 添加数据库资源，重新启动standby 数据库：

srvctl add database -d dbv_fga –o srvctl add instance -d dbv_fga -i dbv_fga1 -n exa505srvctl add instance -d dbv_fga -i dbv_fga2 -n exa506srvctl modify database –d dbv_fga –r physical_standby

完成级联备库的配置

1. 设置第二备库的归档和fal_server：

SQL> alter system set log_archive_config=DG_CONFIG=(db112,dbv_stby,dbv_fga) scope=both;SQL> alter system set fal_server=dbv_stby scope=both;

2. 设置第一备库的dg 配置，传输归档到第二备库：

SQL> alter system set log_archive_config=DG_CONFIG=(db112,dbv_stby,dbv_fga) scope=both;SQL> alter system set log_archive_dest_3=service=dbv_fga ASYNC valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dbv_fga scope=both;

3. 检查当主数据库切换日志时，备库的同步状态：

• 查询主备的同步情况：

set linesize 150;set pagesize 20;column name format a13;column value format a20;column unit format a30;column TIME_COMPUTED format a30;select name,value,unit,time_computed from v$dataguard_stats where name in (transport lag,apply lag);

• 查看备库已经归档的redo：

select registrar,creator,thread#,sequence#,first_change#,next_change#,applied from v$archived_log;

转换级联备库为新的克隆数据库

1. 取消第一备库传输到第二备库的设置：

SQL> alter system set log_archive_dest_state_3=defer scope=both;

2. 修改第二备库归档配置：

SQL> alter system reset log_archive_config;SQL> alter system reset fal_server;

3. 停止dbv_fga 的recover ，并激活第二备库：

SQL> recover managed standby database cancel;Media recovery complete.SQL> recover managed standby database finish;Media recovery complete.SQL> alter database commit to switchover to primary with session shutdown;Database altered.SQL> alter system reset log_archive_config;SQL> alter system set log_archive_dest_state_2=defer scope=both;SQL> alter system set log_archive_dest_state_3=defer scope=both;

4. 打开dbv_fga 数据库：

SQL> alter database open;Database altered.SQL> select database_role from v$database;DATABASE_ROLE----------------PRIMARY

5. 将新FGA 数据库以独占方式打开到mount阶段：

SQL> alter system set cluster_database=false scope=spfile;SQL> shutdown immediateSQL> startup mount

6. 使用nid 更改database id 和dbname：

$ nid target=system/ dbname=fgaDBNEWID: Release 11.2.0.4.0 - Production on Tue Mar 11 14:20:54 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to database DBV (DBID=830667753)Connected to server version 11.2.0Control Files in database:+/dbv_fga/standby.ctlChange database ID and database name DBV to FGA? (Y/[N]) => yProceeding with operationChanging database ID from 3753909469 to 1201662104Changing database name from DBV to FGAControl File +/dbv_fga/standby.ctl – modifiedDatafile +/DBV_FGA/DATAFILE/system.410.84192081 - dbid changed, wrote new nameDatafile +/DBV_FGA/DATAFILE/sysaux.411.84192080 - dbid changed, wrote new nameDatafile +/DBV_FGA/DATAFILE/undotbs1.414.84192081 - dbid changed, wrote new nameDatafile +/DBV_FGA/DATAFILE/undotbs2.412.84192081 - dbid changed, wrote new nameDatafile +/DBV_FGA/DATAFILE/users.423.84192081 - dbid changed, wrote new nameDatafile +/DBV_FGA/TEMPFILE/temp.427.84192190 - dbid changed, wrote new nameControl File +/dbv_fga/standby.ctl - dbid changed, wrote new nameInstance shut downDatabase name changed to FGA.Modify parameter file and generate a new password file before restarting.Database ID for database FGA changed to 1201662104.All previous backups and archived redo logs for this database are unusable.Database is not aware of previous backups and archived logs in Recovery Area.

7. 修改db_name参数以匹配新的数据库名称，并使用resetlogs选项打开数据库：

SQL> startup nomountSQL> alter system set db_name=fga scope=spfile;SQL> startup mount force;SQL> alter database open resetlogs;SQL> select name,dbid from v$database;NAME DBID-------- ----------FGA 1201662104

END

本文作者：张 龙（上海新炬王翦团队）

本文来源：“IT那活儿”公众号