Oracle 11g RAC+ADG实施文档

Oracle 11g RAC+ADG实施文档

谢海鹏 2022年9月16日

环境说明

开始配置 Oracle Active Data Gurad 之前， 需要事先准备好相应的环境， 根据规划安装主备库的数据库环境，备库只需安装数据库软件，不需要创建实例，本次实施环境全程通过Oracle VM VirtualBox搭建的虚拟机环境进行的测试,，先搭建一个rac主库环境，不建库，然后通过虚拟机克隆的方式生成备库的集群环境，再在主库上建库，配置adg的环境。

1、基础环境

操作系统

主机名

IP地址

主库

Red Hat 7.6

node1

node2

# Public node

192.168.56.101 node1

192.168.56.102 node2

# Private node

192.168.88.11 node1-priv

192.168.88.12 node2-priv

# Virtual node

192.168.56.111 node1-vip

192.168.56.112 node2-vip

# SCAN node

192.168.56.118 scan-cluster

Release 11.2.0.4

orcl

orcl

orcl1

orcl2

orcl

安装GI+数据库软件+创建数据库

+data1/orcl/archive

ASM

+DATA1/orcl

+DATA1/orcl

/u1/app/oracle/product/11.2.0/db_1

备库

Red Hat 7.6

orcl1

orcl2

# Public orcl

192.168.56.201 orcl1

192.168.56.202 orcl2

# Private orcl

192.168.88.21 orcl1-priv

192.168.88.22 orcl2-priv

# Virtual orcl

192.168.56.211 orcl1-vip

192.168.56.212 orcl2-vip

# SCAN orcl

192.168.56.218 scan

Release 11.2.0.4

orcl

sorcl

orcl1

orcl2

orcl

安装GI+数据库软件

+data1/orcl/archive

ASM

+DATA1/sorcl

+DATA1/sorcl

/u1/app/oracle/product/11.2.0/db_1

数据库版本

db_name

db_unique_name

instance_name

service_name

软件安装

ArchiveFile

DB Storage

ASM for DB files

ASM for LOG files

ORACLE_HOME

ADG实施过程

1、查看是否开启归档

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 16 14:06:08 2022

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, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 6

Current log sequence 7

SQL> select name,log_mode,force_logging from gv$database;

NAME LOG_MODE FORCE_LOG

--------------------------- ------------------------------------ ---------

ORCL NOARCHIVELOG NO

ORCL NOARCHIVELOG NO

SQL>

#配置归档路径和开启force logging附加日志

alter system set log_archive_dest_1='location=+data1/orcl/archive' scope=spfile;

alter database force logging;

#在使用DATA GUARD 时，要求使用强制记录日志模式。注：FORCE LOGGING并不比一般的LOGGING记录的日志多,数据库在FORCE LOGGING状态下,NOLOGGING选项将无效,因为NOLOGGING将破坏DATAGUARD的可恢复性.FORCE LOGGING强制数据库在任何状态下必须记录日志。

SQL> alter system set log_archive_dest_1='location=+data1/orcl/archive' scope=spfile;

System altered.

SQL> alter database force logging;

Database altered.

SQL> select name,log_mode,force_logging from gv$database;

NAME LOG_MODE FORCE_LOG

--------------------------- ------------------------------------ ---------

ORCL NOARCHIVELOG YES

ORCL NOARCHIVELOG YES

SQL> exit

cd /u1/app/oracle/diag/rdbms/orcl/orcl1/trace/

tail -300f alert_

2、开启归档

[oracle@node1 ~]$ srvctl stop database -d orcl

[oracle@node1 ~]$

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 16 14:10:10 2022

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 768294912 bytes

Fixed Size 2257192 bytes

Variable Size 566234840 bytes

Database Buffers 192937984 bytes

Redo Buffers 6864896 bytes

Database mounted.

SQL>

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +DATA1/orcl/archive

Oldest online log sequence 6

Next log sequence to archive 7

Current log sequence 7

SQL>

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> exit

#启动数据库

[oracle@node1 ~]$ srvctl start database -d orcl

PRCR-1079 : Failed to start resource

CRS-5017: The resource action " start" encountered the following error:

ORA-16038: log 1 sequence# 7 cannot be archived

ORA-00254: error in archive control string ''

ORA-00312: online log 1 thread 1: '+DATA1/orcl/onlinelog/group_1.261.1115556213'

ORA-00312: online log 1 thread 1: '+ARCH1/orcl/onlinelog/group_1.267.1115556213'

ORA-15173: entry 'archive' does not exist in directory 'orcl'

alert日志

ORACLE Instance orcl1 - Archival Error

ORA-16038: log 1 sequence# 7 cannot be archived

ORA-00254: error in archive control string ''

ORA-00312: online log 1 thread 1: '+DATA1/orcl/onlinelog/group_1.261.1115556213'

ORA-00312: online log 1 thread 1: '+ARCH1/orcl/onlinelog/group_1.267.1115556213'

ORA-15173: entry 'archive' does not exist in directory 'orcl'

ARCH: Archival stopped, error occurred. Will continue retrying

创建archive目录

[root@node2 ~]# su - grid

Last login: Fri Sep 16 12:01:27 CST 2022 on pts/0

[grid@node2 ~]$ asmcmd

ASMCMD [+] > ls

ARCH1/

CRS/

DATA1/

ASMCMD [+] > cd DATA1

ASMCMD [+DATA1] > ls

ORCL/

ASMCMD [+DATA1] > cd ORCL

ASMCMD [+DATA1/ORCL] > ls

CONTROLFILE/

DATAFILE/

ONLINELOG/

PARAMETERFILE/

TEMPFILE/

ASMCMD [+DATA1/ORCL] > mkdir archive

#再次启动数据库

[oracle@node1 ~]$ srvctl stop database -d orcl

[oracle@node1 ~]$ srvctl start database -d orcl

#检查归档和附加日志

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 16 14:22:41 2022

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, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> set line 200

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +DATA1/orcl/archive

Oldest online log sequence 9

Next log sequence to archive 10

Current log sequence 10

SQL> select name,log_mode,open_mode,force_logging from gv$database;

NAME LOG_MODE OPEN_MODE FORCE_LOG

------------------- --------------------- ------------------------------------------- ---------

ORCL ARCHIVELOG READ WRITE YES

ORCL ARCHIVELOG READ WRITE YES

SQL> exit

3、主库配置归档删除策略

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 16 14:25:18 2022

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

connected to target database: ORCL (DBID=1643336946)

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

using target database control file instead of recovery catalog

new RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

new RMAN configuration parameters are successfully stored

RMAN-08591: WARNING: invalid archived log deletion policy

RMAN> exit

4、主库配置 Standby Redo Log

#查看 Online redo log 大小及位置：

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 16 14:27:14 2022

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, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> select group#,thread#,members,bytes from v$log;

GROUP# THREAD# MEMBERS BYTES

---------- ---------- ---------- ----------

1 1 2 52428800

2 1 2 52428800

3 2 2 52428800

4 2 2 52428800

SQL> set pagesize 50 linesize 200

SQL> col GROUP# for 9999

SQL> col MEMBER for a60

SQL> select group#,member from v$logfile;

GROUP# MEMBER

------ ------------------------------------------------------------

2 +DATA1/orcl/onlinelog/group_2.278.1115556213

2 +ARCH1/orcl/onlinelog/group_2.266.1115556213

1 +DATA1/orcl/onlinelog/group_1.261.1115556213

1 +ARCH1/orcl/onlinelog/group_1.267.1115556213

3 +DATA1/orcl/onlinelog/group_3.274.1115556379

3 +ARCH1/orcl/onlinelog/group_3.259.1115556381

4 +DATA1/orcl/onlinelog/group_4.273.1115556381

4 +ARCH1/orcl/onlinelog/group_4.258.1115556381

8 rows selected.

SQL>

ASMCMD [+DATA1/ORCL/archive] > cd ../

ASMCMD [+DATA1/ORCL] > mkdir standby

#添加Standby redo log

alter database add standby logfile thread 1 group 5 '+DATA1/orcl/standby/redo05_' size 50M;

alter database add standby logfile thread 1 group 6 '+DATA1/orcl/standby/redo06_' size 50M;

alter database add standby logfile thread 1 group 7 '+DATA1/orcl/standby/redo07_' size 50M;

alter database add standby logfile thread 2 group 8 '+DATA1/orcl/standby/redo08_' size 50M;

alter database add standby logfile thread 2 group 9 '+DATA1/orcl/standby/redo09_' size 50M;

alter database add standby logfile thread 2 group 10 '+DATA1/orcl/standby/redo10_' size 50M;

#standby redo log的大小与redo log大小相同。组数需要比在线日志多一组。

ASMCMD [+DATA1/ORCL/standby] > ls

redo05_

redo06_

redo07_

redo08_

redo09_

redo10_

#检查确认

SQL> select group#,thread#,bytes/1024/1024 Size_MB from v$log;

GROUP# THREAD# SIZE_MB

------ ---------- ----------

1 1 50

2 1 50

3 2 50

4 2 50

SQL> select group#,thread#,bytes/1024/1024 Size_MB from v$standby_log;

GROUP# THREAD# SIZE_MB

------ ---------- ----------

5 1 50

6 1 50

7 1 50

8 2 50

9 2 50

10 2 50

6 rows selected.

SQL>

#切换日志

sqlplus / as sysdba

alter system switch logfile;

5、配置 文件

[oracle@node1 ~]$ cd /u1/app/oracle/product/11.2.0/db_1/network/admin

[oracle@node1 admin]$

[oracle@node1 admin]$ more

# Network Configuration File: /u1/app/oracle/product/11.2.0/db_1/network/admin/

# Generated by Oracle configuration tools.

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

#配置 文件

vi

#原有ORCL

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

#ADG要用到的PORCL,用节点1的vip,原主库

PORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

#ADG要用到的SORCL,用节点1的vip,原备库

SORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = orcl1-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

#将主库的文件拷贝到主库的节点2

scp node2:/u1/app/oracle/product/11.2.0/db_1/network/admin

#将主库的文件拷贝到备库的节点1

scp orcl1:/u1/app/oracle/product/11.2.0/db_1/network/admin

登录orcl1 56.201

cd /u1/app/oracle/product/11.2.0/db_1/network/admin

#原有ORCL,HOST改为备库的scan

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

# SCAN orcl

192.168.56.218 scan omain

#拷贝到备库的节点2

scp orcl2:/u1/app/oracle/product/11.2.0/db_1/network/admin

6、配置主库 ADG 参数

1、备份spfile文件

[oracle@node1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 16 14:43:51 2022

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, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> create pfile='/home/oracle/' from spfile;

File created.

SQL>

2、配置主库参数 ORCL为原主库,SORCL为原备库(备库上没有创建db)

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,SORCL)' scope=both;

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+data1/orcl/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=orcl' scope=both;

alter system set LOG_ARCHIVE_DEST_2='SERVICE=SORCL SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=SORCL' scope=both;

alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both;

alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both;

alter system set LOG_FILE_NAME_CONVERT='+DATA1/orcl','+DATA1/sorcl' scope=spfile;

alter system set DB_FILE_NAME_CONVERT='+DATA1/orcl','+DATA1/sorcl' scope=spfile;

alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=both;

alter system set FAL_SERVER='SORCL' scope=both;

#检查参数配置

SQL> show parameter FAL_SERVER

NAME TYPE VALUE

---------------------------------- --------------------------------- -----------------------------

fal_server string SORCL

SQL> exit

3、主库重启数据库

[oracle@node1 admin]$ srvctl stop database -d orcl

[oracle@node1 admin]$ srvctl start database -d orcl

这里不能按照单实例轮询重启，必须所有节点都关闭重启。

7、配置密码文件

1、将 主库1 节点的密码文件拷贝到主库的所有节点和备库上：

[oracle@node1 admin]$ cd $ORACLE_HOME/dbs

[oracle@node1 dbs]$ scp orapworcl1 orcl1:$ORACLE_HOME/dbs/orapworcl1

orapworcl1

100% 1536 1.1MB/s 00:00

[oracle@node1 dbs]$ scp orapworcl1 orcl2:$ORACLE_HOME/dbs/orapworcl2

orapworcl1

100% 1536 1.0MB/s 00:00

8、创建备库初始化参数文件

1、在主库上生成最新 pfile 文件,并拷贝到备库上,并重命名为

[oracle@node1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 16 14:51:34 2022

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, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> create pfile='/tmp/' from spfile;

File created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

[oracle@node1 dbs]$ scp /tmp/ orcl1:/tmp/

100% 1836 1.3MB/s 00:00

[oracle@node1 dbs]$

2、修改备库上的 pfile 文件

#注意以下几点

1、FAL_SERVER,主库配置SORCL,备库上配置ORCL

2、LOG_ARCHIVE_DEST_1,主库配置standby redo log路径,备库相同,其中DB_UNIQUE_NAME主库为ORCL,备库为SORCL

3、LOG_ARCHIVE_DEST_2,主库配置SORCL,备库配置PORCL,其中DB_UNIQUE_NAME主库为备库SORCL,备库为主库ORCL

4、备库新增*.service_names='orcl',*.db_unique_name='sorcl'

5、备库*.remote_listener='scan-cluster:1521'改为*.remote_listener='scan:1521'和/etc/hosts配置的备库scan ip保持

一致

以上均为中配置的监听名称和pfile中配置的DB_UNIQUE_NAME

log_archive_dest_1一般是主库归档日志存放的地方,log_archive_dest_2是将归档投递到备库,standby log是应用归档日志的日志。

3、和主库对比修改了哪些地方

修改的行如下

*.audit_file_dest='/u1/app/oracle/admin/sorcl/adump'

*.control_files='+DATA1/sorcl/controlfile/current.262.1115556211','+ARCH1/sorcl/controlfile/current.268.1115556211'

*.service_names='orcl'

*.db_unique_name='sorcl'

*.log_archive_dest_1='LOCATION=+data1/orcl/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sorcl'

*.log_archive_dest_2='SERVICE=PORCL SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'

*.remote_listener='scan:1521'

9、创建备库必要目录

1、查看路径

cat /tmp/ | grep /

[oracle@orcl1 ~]$ cat /tmp/ | grep /

*.audit_file_dest='/u1/app/oracle/admin/sorcl/adump'

*.control_files='+DATA1/sorcl/controlfile/current.260.1115407325','+ARCH1/sorcl/controlfile/current.256.1115407325'

*.db_file_name_convert='+DATA1/orcl','+DATA1/orcl'

*.diagnostic_dest='/u1/app/oracle'

*.log_archive_dest_1='LOCATION=+data1/orcl/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sorcl'

*.log_file_name_convert='+DATA1/orcl','+DATA1/orcl'

2、创建路径

服务器

mkdir -p /u1/app/oracle/admin/sorcl/dpdump

mkdir -p /u1/app/oracle/admin/sorcl/hdump

mkdir -p /u1/app/oracle/admin/sorcl/pfile

mkdir -p /u1/app/oracle/admin/sorcl/adump

ASM上

mkdir +ARCH1/sorcl

mkdir +DATA1/sorcl

mkdir +DATA1/sorcl/controlfile

mkdir +ARCH1/sorcl/controlfile

mkdir +data1/orcl

mkdir +data1/orcl/standby

mkdir +data1/orcl/archive

其中sorcl为原备库上的DB_UNIQUE_NAME=sorcl

*.standby_file_management='AUTO'

ORA-17502: ksfdcre:4 Failed to create file +DATA1/sorcl/standby/redo10_

ORA-15173: entry 'standby' does not exist in directory 'sorcl'

mkdir +data1/sorcl/standby

10、备库 LISTENER 配置

1、grid用户修改文件，备库两个节点都需要修改，这里以节点1为例

[grid@orcl2 ~]$ more /u1/app/11.2.0/grid/network/admin/

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line

added by Agent

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME= PLSExtProc)

(ORACLE_HOME = /u1/app/oracle/product/11.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u1/app/oracle/product/11.2.0/db_1)

(SID_NAME=orcl1)

)

)

[grid@orcl2 ~]$

2、重启监听

srvctl stop listener -l LISTENER

srvctl start listener -l LISTENER

lsnrctl status LISTENER_SCAN1

lsnrctl status LISTENER

11、备库 ADG 初始化

1、启动备库到nomount状态

[oracle@orcl1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 16 15:10:34 2022

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/';

ORACLE instance started.

Total System Global Area 768294912 bytes

Fixed Size 2257192 bytes

Variable Size 566234840 bytes

Database Buffers 192937984 bytes

Redo Buffers 6864896 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

[oracle@orcl1 ~]$

cd /u1/app/oracle/diag/rdbms/sorcl/orcl1/trace

tail -300f alert_

2、主库和备库测试连接

sqlplus sys/system@porcl as sysdba

sqlplus sys/system@sorcl as sysdba

3、将备库数据库注册到集群中，1节点执行

srvctl add database -d sorcl -o /u1/app/oracle/product/11.2.0/db_1

srvctl add instance -d sorcl -i orcl1 -n orcl1

srvctl add instance -d sorcl -i orcl2 -n orcl2

此处sorcl最好与备库DB_UNIQUE_NAME=SORCL保持一致，否则dbca删除db时会报找不到资源

#srvctl start database -d sorcl -o mount

[oracle@orcl1 ~]$ srvctl add database -d sorcl -o /u1/app/oracle/product/11.2.0/db_1

[oracle@orcl1 ~]$ srvctl add instance -d sorcl -i orcl1 -n orcl1

[oracle@orcl1 ~]$ srvctl add instance -d sorcl -i orcl2 -n orcl2

4、备库通过 RMAN duplicate 完成进行备库初始化同步

[oracle@orcl1 ~]$ rman target sys/system@porcl auxiliary sys/system@sorcl

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 16 15:13:31 2022

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

connected to target database: ORCL (DBID=1643336946)

connected to auxiliary database: ORCL (not mounted)

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

Starting Duplicate Db at 2022/09/16 15:13:48

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 instance=orcl1 device type=DISK

......

ORACLE error from auxiliary database: ORA-00344: unable to re-create online log '+DATA1/sorcl/standby/redo10_'

ORA-17502: ksfdcre:4 Failed to create file +DATA1/sorcl/standby/redo10_

ORA-15173: entry 'standby' does not exist in directory 'sorcl'

RMAN-05535: WARNING: All redo log files were not defined properly.

Finished Duplicate Db at 2022/09/16 15:15:16

RMAN>

RMAN> exit

Recovery Manager complete.

报错解决

*.standby_file_management='AUTO'

ORA-17502: ksfdcre:4 Failed to create file +DATA1/sorcl/standby/redo10_

ORA-15173: entry 'standby' does not exist in directory 'sorcl'

mkdir +data1/sorcl/standby

5、在线复制完成之后，打开备库

#打开数据库

[oracle@orcl1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 16 15:19:22 2022

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, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

SQL>

#查看状态

set line 200

col NAME for a15

col DB_UNIQUE_NAME for a15

col OPEN_MODE for a25

col database_role for a25

select name, db_unique_name, open_mode, database_role, switchover_status from gv$database;

SQL> select name, db_unique_name, open_mode, database_role, switchover_status from gv$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--------------- --------------- ------------------------- ------------------------- --------------------------------

ORCL sorcl READ ONLY PHYSICAL STANDBY NOT ALLOWED

SQL>

#备库启动 apply应用日志

alter database recover managed standby database disconnect from session using current logfile;

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> select name, db_unique_name, open_mode, database_role, switchover_status from gv$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--------------- --------------- ------------------------- ------------------------- --------------------------------

ORCL sorcl READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

SQL>

12、启动另外一个节点

1、使用pfile生成spfile

create spfile='+DATA1/sorcl/' from pfile='/tmp/';

此处spfile要创建在+DATA1/sorcl目录下，因为DB_UNIQUE_NAME=sorcl

SQL> create spfile='+DATA1/sorcl/' from pfile='/tmp/';

File created.

SQL> exit

2、创建pfile，内容如下，并将节点1的pfile 拷贝到节点2：

echo "SPFILE='+DATA1/sorcl/'" > $ORACLE_HOME/dbs/

scp $ORACLE_HOME/dbs/ orcl2:$ORACLE_HOME/dbs/

[oracle@orcl1 ~]$ echo "SPFILE='+DATA1/sorcl/'" > $ORACLE_HOME/dbs/

[oracle@orcl1 ~]$ more $ORACLE_HOME/dbs/

SPFILE='+DATA1/sorcl/'

[oracle@orcl1 ~]$ scp $ORACLE_HOME/dbs/ orcl2:$ORACLE_HOME/dbs/

100% 36 43.8KB/s 00:00

[oracle@orcl1 ~]$

不能正常重启数据库时用以下方式启动

#startup nomount pfile='/tmp/';

#startup nomount pfile='+DATA1/sorcl/';

#startup pfile='/u1/app/oracle/product/11.2.0/db_1/dbs/';

3、重启数据库

srvctl stop database -d sorcl

srvctl start database -d sorcl

cd /u1/app/oracle/diag/rdbms/sorcl/orcl2/trace

tail -f alert_

ORA-00210: cannot open the specified control file

ORA-00202: control file: '+ARCH1/sorcl/controlfile/current.268.1115556211'

ORA-17503: ksfdopn:2 Failed to open file +ARCH1/sorcl/controlfile/current.268.1115556211

ORA-15012: ASM file '+ARCH1/sorcl/controlfile/current.268.1115556211' does not exist

ORA-00210: cannot open the specified control file

ORA-00202: control file: '+DATA1/sorcl/controlfile/current.262.1115556211'

ORA-17503: ksfdopn:2 Failed to open file +DATA1/sorcl/controlfile/current.262.1115556211

ORA-15012: ASM file '+DATA1/sorcl/controlfile/current.262.1115556211' does not exist

ORA-205 signalled during: ALTER DATABASE

ASMCMD [+data1/sorcl/standby] > ls +ARCH1/sorcl/controlfile/

Current.256.1115565231

ASMCMD [+data1/sorcl/standby] >

ASMCMD [+data1/sorcl/standby] > ls +DATA1/sorcl/controlfile/

Current.281.1115565231

alter system set control_files='+DATA1/sorcl/controlfile/Current.281.1115565231','+ARCH1/sorcl/controlfile/Current.256.1115565231'

scope=spfile;

SQL>

System altered.

SQL>

alter system set

control_files='+DATA1/sorcl/controlfile/Current.281.1115565231','+ARCH1/sorcl/controlfile/Current.256.1115565231' scope=spfile;

4、开启实时日志应用

在备库执行

alter database recover managed standby database disconnect from session using current logfile;

5、查看数据库状态

sqlplus / as sysdba

set line 200

col NAME for a15

col DB_UNIQUE_NAME for a15

col OPEN_MODE for a25

col database_role for a25

col DEST_NAME for a25

col SWITCHOVER_STATUS for a35

select name, db_unique_name, open_mode, database_role, switchover_status from gv$database;

#主库

SQL> select name, db_unique_name, open_mode, database_role, switchover_status from gv$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--------------- --------------- ------------------------- ------------------------- ---------------------------------

ORCL orcl READ WRITE PRIMARY FAILED DESTINATION

ORCL orcl READ WRITE PRIMARY FAILED DESTINATION

SQL>

#备库

SQL> select name, db_unique_name, open_mode, database_role, switchover_status from gv$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--------------- --------------- ------------------------- ------------------------- ---------------------------------

ORCL sorcl READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

ORCL sorcl READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

SQL>

select pid,process,sequence#,status from v$managed_standby;

show parameter log_archive

select dest_id,dest_name,status,error from v$archive_dest;

alter system switch logfile;

主库ORA-03113: end-of-file on communication channel问题解决

SQL> select dest_id,dest_name,status,error from v$archive_dest;

DEST_ID DEST_NAME STATUS ERROR

---------- ------------------------- --------------------------- ----------------------------------------------------

1 LOG_ARCHIVE_DEST_1 VALID

2 LOG_ARCHIVE_DEST_2 ERROR ORA-03113: end-of-file on communication channel

重启数据库

srvctl stop database -d orcl

srvctl start database -d orcl

主库

SQL> select name, db_unique_name, open_mode, database_role, switchover_status from gv$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--------------- --------------- ------------------------- ------------------------- ---------------------------------

ORCL orcl READ WRITE PRIMARY SESSIONS ACTIVE

ORCL orcl READ WRITE PRIMARY SESSIONS ACTIVE

SQL>

备库

SQL> select pid,process,sequence#,status from v$managed_standby;

PID PROCESS SEQUENCE# STATUS

---------- --------------------------- ---------- ------------------------------------

10103 ARCH 20 CLOSING

10105 ARCH 19 CLOSING

10107 ARCH 0 CONNECTED

10109 ARCH 14 CLOSING

10140 MRP0 21 APPLYING_LOG

10354 RFS 0 IDLE

10296 RFS 0 IDLE

10318 RFS 21 IDLE

10320 RFS 0 IDLE

10357 RFS 0 IDLE

10332 RFS 15 IDLE

PID PROCESS SEQUENCE# STATUS

---------- --------------------------- ---------- ------------------------------------

10334 RFS 0 IDLE

12 rows selected.

SQL>

MRP0进程为应用日志进程

6、测试同步

在主库创建表和数据，测试新备库 是否正常同步。

主库

SQL> create table t(id int,name varchar2(64));

Table created.

SQL> insert into t values(1,'xie');

1 row created.

SQL> commit;

Commit complete.

SQL>

备库

SQL> select * from t;

ID NAME

---------- ---------------

1 xie

SQL>

主库

SQL> select name, db_unique_name, open_mode, database_role, switchover_status from gv$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--------------- --------------- ------------------------- ------------------------- ---------------------------------

ORCL orcl READ WRITE PRIMARY SESSIONS ACTIVE

ORCL orcl READ WRITE PRIMARY SESSIONS ACTIVE

SQL>

13、主备切换

注：rac to rac的adg切换只需在其中一个节点执行相应命令即可，执行完后，再把另外的节点open

1、在做切换时，主库需要将除了 1 节点之外的所有节点全部关闭：

[grid@node1 ~]$ srvctl stop instance -d jbdb -i jbdb2

2、在主库上执行如下操作， 将主库切换为备库角色

#将主库切换为备库角色

alter database commit to switchover to physical standby;

当 SWITCHOVER_STATUS 值为 TO SYANDBY 时，表示主库可以切换为备库角色；

如果主库的 SWITCHOVER_STATUS 值为 SESSIONS ACTIVE， 则表示当前有回话正在连接数据库，则可以执行如下操作完成主备角色切换：

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

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

Database altered.

SQL>

3、在备库上执行如下操作，将备库转换为主库角色

#查看备库状态

sqlplus / as sysdba

set line 200

col NAME for a15

col DB_UNIQUE_NAME for a15

col OPEN_MODE for a25

col database_role for a25

col DEST_NAME for a25

col SWITCHOVER_STATUS for a35

select name, db_unique_name, open_mode, database_role, switchover_status from gv$database;

SQL> select name, db_unique_name, open_mode, database_role, switchover_status from gv$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

--------------- --------------- ------------------------- ------------------------- ---------------------------------

ORCL sorcl READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY

ORCL sorcl READ ONLY WITH APPLY PHYSICAL STANDBY SESSIONS ACTIVE

SQL>

#备库切换成主库

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

Database altered.

SQL>

4、检查备库

#如果 STATUS 显示为非 OPEN， 则执行如下操作， 启动数据库

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME STATUS

------------------------------------------------ ------------------------------------

orcl1 MOUNTED

orcl2 MOUNTED

SQL>

su - grid

[grid@orcl1 ~]$ srvctl stop database -d sorcl

[grid@orcl1 ~]$ srvctl start database -d sorcl

sqlplus / as sysdba

set line 200

col NAME for a15

col DB_UNIQUE_NAME for a15

col OPEN_MODE for a25

col database_role for a25

col DEST_NAME for a25

col SWITCHOVER_STATUS for a35

select name, db_unique_name, open_mode, database_role, switchover_status from gv$database;

5、启动备库（原先的主库）

#启动数据库

srvctl start database -d orcl

#查看数据库模式，此时主库已经切换为物理备库

SQL> select name,database_role,switchover_status from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS

--------- ---------------- --------------------

ORCL PHYSICAL STANDBY RECOVERY NEEDED

SQL>

#启动应用日志

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL>

主库（原备库）

SQL> select pid,process,sequence#,status from v$managed_standby;

PID PROCESS SEQUENCE# STATUS

---------- --------------------------- ---------- ------------------------------------

28150 ARCH 23 CLOSING

28153 ARCH 0 CONNECTED

28155 ARCH 0 CONNECTED

28157 ARCH 17 CLOSING

28277 RFS 0 IDLE

28292 RFS 0 IDLE

28271 RFS 18 IDLE

28273 RFS 24 IDLE

28279 RFS 0 IDLE

28281 RFS 0 IDLE

28283 RFS 0 IDLE

PID PROCESS SEQUENCE# STATUS

---------- --------------------------- ---------- ------------------------------------

28285 RFS 0 IDLE

28510 MRP0 24 APPLYING_LOG

13 rows selected.

SQL>

6、测试同步

在新主库创建表和数据，测试新备库 是否正常同步。

主库（原备库）

SQL> select * from t;

ID NAME

---------- ---------------

1 xie

SQL> insert into t values(2,'hai');

1 row created.

SQL> commit;

Commit complete.

SQL>

备库（原主库）

SQL> select * from t;

ID NAME

---------- ---------------

1 xie

2 hai

SQL>