暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片
首页 / ORACLE 11g 级联 DG 搭建

ORACLE 11g 级联 DG 搭建

原创 lps 2023-03-09
1572

[toc]

ORACLE 级联 DG 搭建

image.png

信息确认

数据库信息

IP DB_NAME DB_UNIQUE_NAME tnsnames 备注 hostname
10.0.8.5 mesdb mesdb mesdb 主库 mesdb
10.0.8.8 mesdb mesdg mesdg 备库 mesdg
10.0.8.16 mesdb mesdg2 mesdg2 级联备库 mesdg2

OS 版本

cat /etc/redhat-release

CentOS Linux release 7.6.1810 (Core)

数据库版本

11.2.0.4

环境变量信息

ORACLE_TERM=xterm; export ORACLE_TERM
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1; export ORACLE_HOME
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; export NLS_DATE_FORMAT
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib;export LD_LIBRARY_PATH
PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch; export PATH
ORACLE_SID=mesdb;export ORACLE_SID
alias s='sqlplus / as sysdba'

监听信息

[oracle@mesdb ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-FEB-2023 10:59:32

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mesdb)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                22-FEB-2023 10:43:12
Uptime                    0 days 0 hr. 16 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/mesdb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mesdb)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "mesdb" has 1 instance(s).
  Instance "mesdb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@mesdb ~]$ cat /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mesdb)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

数据库参数信息

SET LINESIZE 170
set pages 200
COL name FOR a36
COL value FOR a130
SELECT name, VALUE
  FROM v$parameter
 WHERE name IN
          ('db_name',
           'db_unique_name',
           'log_archive_config',
           'log_archive_dest_1',
           'log_archive_dest_2',
           'log_archive_dest_3',
           'log_archive_dest_state_1',
           'log_archive_dest_state_2',
           'log_archive_dest_state_3',
           'remote_login_passwordfile',
           'log_archive_format',
           'log_archive_max_processes',
           'archive_lag_target',
           'fal_server',
           'db_file_name_convert',
           'log_file_name_convert',
           'standby_file_management',
          'db_create_file_dest',
          'db_create_online_log_dest_1');
NAME				               VALUE
--------------------------- ------------------------------------------------
db_file_name_convert
log_file_name_convert
log_archive_dest_1		     location=/u01/arch/mesdb
log_archive_dest_2
log_archive_dest_3
log_archive_dest_state_1	     enable
log_archive_dest_state_2	     enable
log_archive_dest_state_3	     enable
fal_server
log_archive_config
log_archive_format		     %t_%s_%r.arc
log_archive_max_processes	     4
archive_lag_target		     900
db_create_file_dest		     /u01/oradata/
db_create_online_log_dest_1	     /u01/oradata/
standby_file_management 	     MANUAL
remote_login_passwordfile	     EXCLUSIVE
db_name 			     mesdb
db_unique_name			     mesdb

19 rows selected.

数据文件信息

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/mesdb/system01.dbf
/u01/oradata/mesdb/sysaux01.dbf
/u01/oradata/mesdb/undotbs01.dbf
/u01/oradata/mesdb/users01.dbf
/u01/oradata/MESDB/datafile/o1_mf_usertbs_kzc2ocxc_.dbf

SQL> show parameter db_crea

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest		     string	 /u01/oradata/
db_create_online_log_dest_1	     string	 /u01/oradata/

redo 文件确认

SQL> select * from v$Logfile;

    GROUP# STATUS  TYPE    MEMBER					      IS_
---------- ------- ------- -------------------------------------------------- ---
	 5	   ONLINE  /u01/oradata/mesdb/redo5_01.log		      NO
	 4	   ONLINE  /u01/oradata/mesdb/redo4_01.log		      NO
	 3	   ONLINE  /u01/oradata/mesdb/redo3_01.log		      NO
	 2	   ONLINE  /u01/oradata/mesdb/redo2_01.log		      NO
	 1	   ONLINE  /u01/oradata/mesdb/redo1_01.log		      NO

说明，本次搭建DG 数据文件都采用 OMF 自动管理，不配置 convert 参数转换了，每个数据库都配置 OMF。

DG 搭建

确认归档模式和 force logging

select log_mode,force_logging from v$database;

结果如下，如果不是进行以下的操作进行开始

SQL>  select log_mode,force_logging from v$database;

LOG_MODE		 FORCE_
------------------------ ------
ARCHIVELOG		 YES

开启强制记录日志

alter database force logging;  --强制记录日志

数据库归档模式开启

  • STEP1：以sysdba角色登陆数据库
sqlplus / as sysdba
  • STEP2：关闭数据库
shutdown immediate
  • STEP3：将数据库启动到mount状态
startup mount
  • STEP4：开启归档
alter database archivelog;
  • STEP5：打开数据库
alter database open

确认归档和确认归档模式和force logging

SQL>  select log_mode,force_logging from v$database;

LOG_MODE		 FORCE_
------------------------ ------
ARCHIVELOG		 YES

mesdb 新增 standby log

SQL> select * from v$standby_log;

no rows selected

SQL> alter database add standby logfile group 10('/u01/oradata/mesdb/sty_log10.log') size 52428800;
alter database add standby logfile group 11('/u01/oradata/mesdb/sty_log11.log') size 52428800;
alter database add standby logfile group 12('/u01/oradata/mesdb/sty_log12.log') size 52428800;
alter database add standby logfile group 13('/u01/oradata/mesdb/sty_log13.log') size 52428800;
alter database add standby logfile group 14('/u01/oradata/mesdb/sty_log14.log') size 52428800;
alter database add standby logfile group 15('/u01/oradata/mesdb/sty_log15.log') size 52428800;
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>

Database altered.

SQL>
SQL>  select * from v$standby_log;

    GROUP# DBID 				       THREAD#	SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS	  FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
	10 UNASSIGNED					     0		0   52428800	    512 	 0 YES UNASSIGNED
	11 UNASSIGNED					     0		0   52428800	    512 	 0 YES UNASSIGNED
	12 UNASSIGNED					     0		0   52428800	    512 	 0 YES UNASSIGNED
	13 UNASSIGNED					     0		0   52428800	    512 	 0 YES UNASSIGNED
	14 UNASSIGNED					     0		0   52428800	    512 	 0 YES UNASSIGNED
	15 UNASSIGNED					     0		0   52428800	    512 	 0 YES UNASSIGNED

6 rows selected.

配置监听静态注册

mesdg 监听新增静态注册

修改前信息：

[oracle@mesdg ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-FEB-2023 14:12:22

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mesdg)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                22-FEB-2023 10:49:58
Uptime                    0 days 3 hr. 22 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/mesdg/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mesdg)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

vi listener.ora 新增静态注册新增如下：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mesdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
      (SID_NAME = mesdb)
    )
  )

重新 reload 监听

[oracle@mesdg ~]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-FEB-2023 14:12:26

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mesdg)(PORT=1521)))
The command completed successfully
[oracle@mesdg ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-FEB-2023 14:12:30

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mesdg)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                22-FEB-2023 10:49:58
Uptime                    0 days 3 hr. 22 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/mesdg/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mesdg)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "mesdb" has 1 instance(s).
  Instance "mesdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

说明：新增了静态注册 mesdb

同样 mesdg2 也新增静态注册。略。

tnsnames.ora 修改

主库、DG、级联DG 三台主机都配置 tnsnames.ora 如下：

MESDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.8.5)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mesdb)
    )
  )

MESDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.8.8)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mesdb)
    )
  )

MESDG2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.8.16)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mesdb)
    )
  )

确认 tnsnames

tnsping mesdb
tnsping mesdg
tnsping mesdg2

主库修改参数

alter system set log_archive_config='dg_config=(mesdb,mesdg,mesdg2)';
alter system set log_archive_dest_1='location=/u01/arch/mesdb valid_for=(all_logfiles,all_roles) db_unique_name=mesdb';
alter system set log_archive_dest_2='SERVICE=mesdg  lgwr async valid_for=(ONLINE_LOGFILES,primary_role) db_unique_name=mesdg';
alter system set standby_file_management='AUTO';
alter system set fal_server=mesdg;
alter system set log_archive_dest_state_2=enable;

SQL> alter system set log_archive_config='dg_config=(mesdb,mesdg,mesdg2)';
alter system set log_archive_dest_1='location=/u01/arch/mesdb valid_for=(all_logfiles,all_roles) db_unique_name=mesdb';
alter system set log_archive_dest_2='SERVICE=mesdg  lgwr async valid_for=(ONLINE_LOGFILES,primary_role) db_unique_name=mesdg';
alter system set standby_file_management='AUTO';
alter system set fal_server=mesdg;
alter system set log_archive_dest_state_2=enable;
System altered.

SQL>
System altered.

SQL>

System altered.

SQL>
System altered.

SQL>
System altered.

SQL>
System altered.

复制密码文件

cd /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
scp orapwmesdb  10.0.8.8:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
scp orapwmesdb  10.0.8.16:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/

mesdg 启动数据库到 nomount 状态

配置参数，mesdg 参数文件，只需要写 db_name=mesdb 即可。

[oracle@mesdg admin]$ cd $ORACLE_HOME/dbs
[oracle@mesdg dbs]$ touch initmesdb.ora
[oracle@mesdg dbs]$ echo "db_name=mesdb">initmesdb.ora

启动到 nomount 状态

vi st.sh
export ORACLE_SID=mesdb
sqlplus / as sysdba<<_EOF
startup nomount
_EOF

[oracle@mesdg ~]$ sh st.sh

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 23 21:51:51 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area  229683200 bytes
Fixed Size		    2251936 bytes
Variable Size		  171967328 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5132288 bytes
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mesdg ~]$

验证密码文件

[oracle@mesdb trace]$ sqlplus  sys/oracle@mesdb as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 23 22:17:20 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
[oracle@mesdb trace]$ sqlplus  sys/oracle@mesdg as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 23 22:17:20 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

运行 duplicate 搭建 DG (mesdg)

mesdg 进行 duplicate 搭建 DG

duplicate 脚本如下

[oracle@mesdg ~]$  cat r.sh

rman<<_EOF
connect target sys/oracle@mesdb
connect auxiliary sys/oracle@mesdg
 run {
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    allocate auxiliary channel s1 type disk;
    allocate auxiliary channel s2 type disk;
    duplicate target database
         for standby
         from active database nofilenamecheck
         dorecover
         spfile
         parameter_value_convert 'mesdb','mesdg','MESDB','MESDG'
         set db_unique_name='mesdg'
         set fal_server='mesdb'
         set standby_file_management='AUTO'
         set log_archive_config='dg_config=(mesdb,mesdg,mesdg2)'
         set log_archive_dest_1='location=/u01/arch/mesdb valid_for=(all_logfiles,all_roles) db_unique_name=mesdg'
         set log_archive_dest_2='SERVICE=mesdb lgwr async VALID_FOr=(ONLINE_LOGFILES,primary_role) DB_UNIQUE_NAME=mesdb'
         set log_archive_dest_state_2='enable'
        ;
       sql channel c1 "alter system archive log current";
       sql channel s1 "alter database open";
       sql channel s1 "alter database recover managed standby database using current logfile  disconnect";
     }
_EOF

运行 duplicate 脚本

[oracle@mesdg ~]$ sh r.sh

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 23 22:04:13 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN>
connected to target database: MESDB (DBID=3145668188)

RMAN>
connected to auxiliary database: MESDB (not mounted)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=408 device type=DISK

allocated channel: c2
channel c2: SID=22 device type=DISK

allocated channel: s1
channel s1: SID=171 device type=DISK

allocated channel: s2
channel s2: SID=10 device type=DISK

Starting Duplicate Db at 2023-02-23 22:04:15

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwmesdb'   targetfile
 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilemesdb.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilemesdb.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilemesdb.ora''";
}
executing Memory Script

Starting backup at 2023-02-23 22:04:15
Finished backup at 2023-02-23 22:04:16

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilemesdb.ora''

contents of Memory Script:
{
   sql clone "alter system set  audit_file_dest =
 ''/u01/app/oracle/admin/mesdg/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''/u01/oradata/mesdg/control01.ctl'', ''/u01/oradata/mesdg/control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=mesdgXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''mesdg'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server =
 ''mesdb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config =
 ''dg_config=(mesdb,mesdg)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''location=/u01/arch/mesdb valid_for=(all_logfiles,all_roles) db_unique_name=mesdg'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_2 =
 ''SERVICE=mesdb lgwr async VALID_FOr=(ONLINE_LOGFILES,primary_role) DB_UNIQUE_NAME=mesdb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_state_2 =
 ''enable'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/mesdg/adump'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u01/oradata/mesdg/control01.ctl'', ''/u01/oradata/mesdg/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=mesdgXDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''mesdg'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''mesdb'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_config =  ''dg_config=(mesdb,mesdg)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''location=/u01/arch/mesdb valid_for=(all_logfiles,all_roles) db_unique_name=mesdg'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_2 =  ''SERVICE=mesdb lgwr async VALID_FOr=(ONLINE_LOGFILES,primary_role) DB_UNIQUE_NAME=mesdb'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_state_2 =  ''enable'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1553305600 bytes

Fixed Size                     2253544 bytes
Variable Size                989859096 bytes
Database Buffers             553648128 bytes
Redo Buffers                   7544832 bytes
allocated channel: s1
channel s1: SID=10 device type=DISK
allocated channel: s2
channel s2: SID=395 device type=DISK

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/oradata/mesdg/control01.ctl';
   restore clone primary controlfile to  '/u01/oradata/mesdg/control02.ctl' from
 '/u01/oradata/mesdg/control01.ctl';
}
executing Memory Script

Starting backup at 2023-02-23 22:04:23
channel c1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_mesdb.f tag=TAG20230223T220423 RECID=3 STAMP=1129586663
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2023-02-23 22:04:24

Starting restore at 2023-02-23 22:04:24

channel s2: skipped, AUTOBACKUP already found
channel s1: copied control file copy
Finished restore at 2023-02-23 22:04:32

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 clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   datafile  5 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oradata/MESDG/datafile/o1_mf_temp_%u_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 2023-02-23 22:04:37
channel c1: starting datafile copy
input datafile file number=00001 name=/u01/oradata/mesdb/system01.dbf
channel c2: starting datafile copy
input datafile file number=00002 name=/u01/oradata/mesdb/sysaux01.dbf
output file name=/u01/oradata/MESDG/datafile/o1_mf_system_041l87vl_.dbf tag=TAG20230223T220437
channel c1: datafile copy complete, elapsed time: 00:00:15
channel c1: starting datafile copy
input datafile file number=00005 name=/u01/oradata/MESDB/datafile/o1_mf_usertbs_kzc2ocxc_.dbf
output file name=/u01/oradata/MESDG/datafile/o1_mf_sysaux_051l87vl_.dbf tag=TAG20230223T220437
channel c2: datafile copy complete, elapsed time: 00:00:15
channel c2: starting datafile copy
input datafile file number=00003 name=/u01/oradata/mesdb/undotbs01.dbf
output file name=/u01/oradata/MESDG/datafile/o1_mf_usertbs_061l8804_.dbf tag=TAG20230223T220437
channel c1: datafile copy complete, elapsed time: 00:00:03
channel c1: starting datafile copy
input datafile file number=00004 name=/u01/oradata/mesdb/users01.dbf
output file name=/u01/oradata/MESDG/datafile/o1_mf_undotbs1_071l8804_.dbf tag=TAG20230223T220437
channel c2: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/oradata/MESDG/datafile/o1_mf_users_081l8807_.dbf tag=TAG20230223T220437
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2023-02-23 22:04:56

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u01/arch/mesdb/1_599_1129056222.arc" auxiliary format
 "/u01/arch/mesdb/1_599_1129056222.arc"   archivelog like
 "/u01/arch/mesdb/1_598_1129056222.arc" auxiliary format
 "/u01/arch/mesdb/1_598_1129056222.arc"   ;
   catalog clone archivelog  "/u01/arch/mesdb/1_599_1129056222.arc";
   catalog clone archivelog  "/u01/arch/mesdb/1_598_1129056222.arc";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 2023-02-23 22:04:56
channel c1: starting archived log copy
input archived log thread=1 sequence=599 RECID=595 STAMP=1129586696
channel c2: starting archived log copy
input archived log thread=1 sequence=598 RECID=594 STAMP=1129586680
output file name=/u01/arch/mesdb/1_599_1129056222.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
output file name=/u01/arch/mesdb/1_598_1129056222.arc RECID=0 STAMP=0
channel c2: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2023-02-23 22:04:57

cataloged archived log
archived log file name=/u01/arch/mesdb/1_599_1129056222.arc RECID=1 STAMP=1129586697

cataloged archived log
archived log file name=/u01/arch/mesdb/1_598_1129056222.arc RECID=2 STAMP=1129586697

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=1129586697 file name=/u01/oradata/MESDG/datafile/o1_mf_system_041l87vl_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=1129586697 file name=/u01/oradata/MESDG/datafile/o1_mf_sysaux_051l87vl_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1129586697 file name=/u01/oradata/MESDG/datafile/o1_mf_undotbs1_071l8804_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1129586697 file name=/u01/oradata/MESDG/datafile/o1_mf_users_081l8807_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1129586697 file name=/u01/oradata/MESDG/datafile/o1_mf_usertbs_061l8804_.dbf

contents of Memory Script:
{
   set until scn  1237844;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2023-02-23 22:04:57

starting media recovery

archived log for thread 1 with sequence 598 is already on disk as file /u01/arch/mesdb/1_598_1129056222.arc
archived log for thread 1 with sequence 599 is already on disk as file /u01/arch/mesdb/1_599_1129056222.arc
archived log file name=/u01/arch/mesdb/1_598_1129056222.arc thread=1 sequence=598
archived log file name=/u01/arch/mesdb/1_599_1129056222.arc thread=1 sequence=599
media recovery complete, elapsed time: 00:00:00
Finished recover at 2023-02-23 22:04:58
Finished Duplicate Db at 2023-02-23 22:05:01

sql statement: alter system archive log current

sql statement: alter database open

sql statement: alter database recover managed standby database using current logfile  disconnect
released channel: c1
released channel: c2
released channel: s1
released channel: s2

RMAN>

Recovery Manager complete.
[oracle@mesdg ~]$

验证DG 完成

主库切换多个归档

SQL> alter system switch logfile
  2  ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

mesdg 检测DG状态

dginfo.sh 脚本内容

#!/usr/bin/env sh
sqlplus / as sysdba <<EOF
set lines 123
set pages 200
col  CTIME format a20
col  NAME format a20
col  VALUE format a20
col  DATUM_TIME format a20
select open_mode, DATABASE_ROLE from v\$database;
SELECT  TO_NUMBER( SUBSTR ( (SUBSTR (VALUE, 5)), 0, 2) * 3600 + SUBSTR ( (SUBSTR (VALUE, 5)), 4, 2) * 60 + SUBSTR ( (SUBSTR (VALUE, 5)), 7, 2)) dgbehind, TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss'
) CTIME, NAME, VALUE,DATUM_TIME FROM V\$DATAGUARD_STATS WHERE NAME ='apply lag';
select process,block#,blocks ,status ,sequence# from v\$managed_standby;
exit
EOF

[oracle@mesdg ~]$ sh dginfo.sh

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 23 22:10:37 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> SQL> SQL> SQL> SQL> SQL>
OPEN_MODE	     DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>   2
  DGBEHIND CTIME		NAME		     VALUE		  DATUM_TIME
---------- -------------------- -------------------- -------------------- --------------------
	 0 20230223 22:10:37	apply lag	     +00 00:00:00	  02/23/2023 22:10:36

SQL>
PROCESS       BLOCK#	 BLOCKS STATUS	      SEQUENCE#
--------- ---------- ---------- ------------ ----------
ARCH		   0	      0 CONNECTED	      0
ARCH		   0	      0 CONNECTED	      0
ARCH		   0	      0 CONNECTED	      0
ARCH		   1	    323 CLOSING 	    605
MRP0		  18	 102400 APPLYING_LOG	    606
RFS		   0	      0 IDLE		      0
RFS		   0	      0 IDLE		      0
RFS		   0	      0 IDLE		      0
RFS		  18	      1 IDLE		    606

9 rows selected.

级联 DG 搭建

同样级联 DG 配置参数文件启动数据库到 nomount 状态

mesdg2 启动数据库到 nomount 状态

配置参数，mesdg 参数文件，只需要写 db_name=mesdb 即可。

[oracle@mesdg admin]$ cd $ORACLE_HOME/dbs
[oracle@mesdg dbs]$ touch initmesdb.ora
[oracle@mesdg dbs]$ echo "db_name=mesdb">initmesdb.ora

启动到 nomount 状态

export ORACLE_SID=mesdb
sqlplus / as sysdba<<_EOF
startup nomount
_EOF

[oracle@mesdg2 ~]$ export ORACLE_SID=mesdb
[oracle@mesdg2 ~]$ sqlplus / as sysdba<<_EOF
> startup nomount
> _EOF

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 23 22:21:13 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area  229683200 bytes
Fixed Size		    2251936 bytes
Variable Size		  171967328 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5132288 bytes
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mesdg2 ~]$

验证密码文件

[oracle@mesdb trace]$ sqlplus  sys/oracle@mesdb as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 23 22:17:20 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
[oracle@mesdb trace]$ sqlplus  sys/oracle@mesdg as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 23 22:17:20 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

运行 duplicate 搭建级连 DG(mesdg2)

duplicate 脚本

rman<<_EOF
connect target sys/oracle@mesdg
connect auxiliary sys/oracle@mesdg2
 run {
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    allocate auxiliary channel s1 type disk;
    allocate auxiliary channel s2 type disk;
    duplicate target database
         for standby
         from active database nofilenamecheck
         dorecover
         spfile
         parameter_value_convert 'mesdg','mesdg2','MESDG','MESDG2'
         set db_unique_name='mesdg2'
         set fal_server='mesdg'
         set standby_file_management='AUTO'
         set log_archive_dest_1='location=/u01/arch/mesdb valid_for=(all_logfiles,all_roles) db_unique_name=mesdg'
         set log_archive_dest_3=''
        ;
       sql channel c1 "alter system archive log current";
       sql channel s1 "alter database recover managed standby database using current logfile  disconnect";
     }
_EOF

验证级连DG(mesdg2)

检查dg状态

运行 sh dginfo.sh


SQL> SQL> SQL> SQL> SQL> SQL> SQL>
OPEN_MODE	     DATABASE_ROLE
-------------------- ----------------
MOUNTED 	     PHYSICAL STANDBY

SQL>   2
  DGBEHIND CTIME		NAME		     VALUE		  DATUM_TIME
---------- -------------------- -------------------- -------------------- --------------------
	   20230301 16:05:46	apply lag

SQL>
PROCESS       BLOCK#	 BLOCKS STATUS	      SEQUENCE#
--------- ---------- ---------- ------------ ----------
ARCH		   0	      0 CONNECTED	      0
ARCH		   0	      0 CONNECTED	      0
ARCH		   0	      0 CONNECTED	      0
ARCH		   0	      0 CONNECTED	      0
MRP0		   0	      0 WAIT_FOR_LOG	   1174

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mesdg2 ~]$

在主库上运行多次强制归档。

然后再次运行 sh dginfo.sh

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> SQL> SQL> SQL> SQL> SQL>
OPEN_MODE	     DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>   2
  DGBEHIND CTIME		NAME		     VALUE		  DATUM_TIME
---------- -------------------- -------------------- -------------------- --------------------
       847 20230301 15:59:21	apply lag	     +00 00:14:07	  03/01/2023 15:59:10

SQL>
PROCESS       BLOCK#	 BLOCKS STATUS	      SEQUENCE#
--------- ---------- ---------- ------------ ----------
ARCH		   0	      0 CONNECTED	      0
ARCH		   0	      0 CONNECTED	      0
ARCH		   0	      0 CONNECTED	      0
ARCH		   0	      0 CONNECTED	      0
MRP0		   0	      0 WAIT_FOR_LOG	   1173
RFS		   0	      0 IDLE		      0
RFS		   0	      0 IDLE		      0
RFS		   0	      0 IDLE		      0

8 rows selected.

由于 11g 级连 DG 只能传输归档。可以看到 MRP 状态为 WAIT_FOR_LOG，数据同步有延迟。

墨力计划级联dg
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论