运维开发网

MAA=RAC+RAC DG

运维开发网 https://www.qedev.com 2020-07-13 08:09 出处:网络
MAA=RAC+RAC DG数据库信息:版本:11.2.0.4.0软件:p13390677_112040_Linux-x86-64_1of7.zipp13390677_112040_Linux-x86-64_2of7.zipp13390677_112040_Linux-x86-64_3of7.zip

MAA=RAC+RAC DG

数据库信息:

版本:11.2.0.4.0

软件:

p13390677_112040_Linux-x86-64_1of7.zip

p13390677_112040_Linux-x86-64_2of7.zip

p13390677_112040_Linux-x86-64_3of7.zip

补丁:p28429134_112040_Linux-x86-64.zip

OPATCH:p6880880_112000_Linux-x86-64.zip

OS:

Red Hat Enterprise Linux Server release 7.3 (Maipo)

 

一、环境

主库:

两个节点:

pdb1

pdb2

生产已经在运行4.5T 数据库

*.db_name=‘pdb‘

*.db_unique_name=‘pdb‘

pdb2.instance_number=2

pdb1.instance_number=1

DB STORAGE:ASM

ASM diskgroup for DB files:DATA

ASM Diskgroup for Archive Logs:ARCH

ORACLE_HOME for Grid:/u01/11.2.0/grid

ORACLE_HOME for Oracle:/u01/app/oracle/product/11.2.0/db_1

备库:

两个节点:

pdb1

pdb2

*.db_name=‘pdb‘

*.db_unique_name=‘pdg‘

pdg2.instance_number=2

pdg1.instance_number=1

DB STORAGE:ASM

ASM diskgroup for DB files:DATA

ASM Diskgroup for Archive Logs:SAS_ARCH

ORACLE_HOME for Grid:/u01/11.2.0/grid

ORACLE_HOME for Oracle:/u01/app/oracle/product/11.2.0/db_1

二、其他配置

2.1 Primary Site

双节点 11g R2 Grid Infrastructure (11.2.0.4)已经安装配置完毕;

双节点 Oracel RAC Software (11.2.0.4)已经安装配置完毕;

数据库”PDB”已经创建于 ASM 上;

数据库运行于归档模式;

2.2 Standby Site

双节点 11g R2 Grid Infrastructure (11.2.0.4)已经安装配置完毕;

双节点 Oracel RAC Software (11.2.0.4)已经安装配置完毕;

2.3 /etc/hosts 设置

#Standby

# public

1.3.1.70 pdg1

1.3.1.71 pdg2

# private

2.3.1.16 pdg1-priv

2.3.1.18 pdg2-priv

# vip

1.3.1.72 pdg1-vip

1.3.1.73 pdg2-vip

# scanip

1.3.1.75 scan-pdg

#PRIMARY

#public ip

1.3.1.51 pdb1

1.3.1.52 pdb2

#private ip

2.3.1.36 pdb1-priv

2.3.1.37 pdb2-priv

#Virtual ip

1.3.1.53 pdb1-vip

1.3.1.54 pdb2-vip

#scan ip

1.3.1.55 scan-pdb

 

2.4 配置互信

分别在两个节点的Oracle和grid用户上执行:

su - oracle

mkdir ~/.ssh

chmod 755 ~/.ssh

ssh-keygen -t rsa

ssh-keygen -t dsa

在一个节点1 上

cat ~/.ssh/id_rsa.pub >> ./.ssh/authorized_keys

cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

在传到另一个节点

ssh pdg2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

ssh pdg2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

scp ~/.ssh/authorized_keys pdg2:~/.ssh/authorized_keys

在一个节点2 上

cat ~/.ssh/id_rsa.pub >> ./.ssh/authorized_keys

cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

在传到另一个节点

ssh pdg1 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

ssh pdg1 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

scp ~/.ssh/authorized_keys pdg1:~/.ssh/authorized_keys

---验证

ssh pdg1 date

ssh pdg2 date

ssh pdg1-priv date

ssh pdg2-priv date

三、准备 Primary RAC Database

3.1 打开 Force Logging

SQL> ALTER DATABASE FORCE LOGGING;

3.2 创建 Standby Redo 日志(SLRs)

10:36:36 [email protected](pdb1)> select MAX(BYTES/1024/1024/1024) g, count (1) from v$log;

G COUNT(1)

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

2 4

10:36:44 [email protected](posdb1)> select thread#,bytes/1024/1024/1024 "SIZE(g)",members from v$log;

THREAD# SIZE(g) MEMBERS

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

1 2 2

1 2 2

2 2 2

2 2 2

 

select member from v$logfile;

可以看出目前有两个节点,每个节点有两个日志组,每个日志组成员大小为 2g,每个日志组里都

有2个成员,每个成员1G。 下面给每个节点添加三个日志组:

alter system set standby_file_management=manual scope=both sid=‘*‘;

alter database add standby logfile thread 1 group 5 (‘+DATA/pdb/onlinelog/standby05a.log‘,‘+DATA/pdb/onlinelog/standby05b.log‘) size 1G ;

alter database add standby logfile thread 1 group 6 (‘+DATA/pdb/onlinelog/standby06a.log‘,‘+DATA/pdb/onlinelog/standby06b.log‘) size 1G ;

alter database add standby logfile thread 1 group 7 (‘+DATA/pdb/onlinelog/standby07a.log‘,‘+DATA/pdb/onlinelog/standby07b.log‘) size 1G ;

alter database add standby logfile thread 2 group 8 (‘+DATA/pdb/onlinelog/standby08a.log‘,‘+DATA/pdb/onlinelog/standby08b.log‘) size 1G ;

alter database add standby logfile thread 2 group 9 (‘+DATA/pdb/onlinelog/standby09a.log‘,‘+DATA/pdb/onlinelog/standby09b.log‘) size 1G ;

alter database add standby logfile thread 2 group 10 (‘+DATA/pdb/onlinelog/standby10a.log‘,‘+DATA/pdb/onlinelog/standby10b.log‘) size 1G ;

alter system set standby_file_management=auto scope=both sid=‘*‘;

select group#,thread#,status from v$standby_log;

3.3 修改参数

修改 Dataguard 相关的初始化参数,这里需要修改的有如下参数:

DB_UNIQUE_NAME=pdb

LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(pdb,pdg)‘

LOG_ARCHIVE_DEST_1=‘LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pdb‘

LOG_ARCHIVE_DEST_2=‘SERVICE=pdg ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdg‘

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

--REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=pdb_%t_%s_%r.arc

--LOG_ARCHIVE_MAX_PROCESSES=10

FAL_SERVER=pdg

DB_FILE_NAME_CONVERT=‘+DATA‘,‘+DATA‘

LOG_FILE_NAME_CONVERT= ‘+DATA‘,‘+DATA‘

STANDBY_FILE_MANAGEMENT=AUTO

---命令行

select force_logging from v$database; --查询是否开启

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

alter database archivelog;--开启归档

alter system set standby_file_management=auto;--手动

alter system set log_archive_dest_1=‘location=+ARCH valid_for=(all_logfiles,all_roles) db_unique_name=pdb‘ scope=spfile;--开启本地归档

alter system set log_archive_dest_2=‘SERVICE=pdg LGWR NOASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=pdg‘ scope=spfile;--开启远程归档

alter system set log_archive_config=‘dg_config=(pdb,pdg)‘;--开启DG功能

alter system set fal_client=‘pdb‘;--指定源端

alter system set fal_server=‘pdg‘;--指定目标端

alter system set log_archive_dest_state_2=enable;--启动应用,可以先暂时:DEFER 等 搭建完成在enable;

alter system set log_archive_dest_state_1=enable;--启动应用

alter system set log_archive_format=‘pdb%t_%s_%r.dbf‘ scope=spfile;--指定归档文件形式

--源端---先写备端地址,在写源端地址

alter system set db_file_name_convert=‘+DATA/pdg/DATAFILE‘,‘+DATA/pdb/DATAFILE‘ scope=spfile;

alter system set log_file_name_convert=‘+DATA/pdg/DATAFILE‘,‘+DATA/pdb/DATAFILE‘ scope=spfile;

--ARCH 方式

alter system set LOG_ARCHIVE_DEST_2=‘SERVICE=pdg ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdg‘ scope=spfile sid=‘*‘;

为使修改生效,重启 Primary Database:

srvctl stop database -d pdb

srvctl start database -d pdb

重启完后,可通过下面语句查看修改地方:

set linesize 500 pages 0

col value for a90

col name for a50

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_state_1‘,

‘log_archive_dest_state_2‘,

‘remote_login_passwordfile‘,

‘log_archive_format‘,

‘log_archive_max_processes‘,

‘fal_server‘,

‘db_file_name_convert‘,

‘log_file_name_convert‘,

‘standby_file_management‘);

3.4 备份数据

如果是生产环境,配置之前先进性全备份,在采用 DUPLICATE TARGET DATABASE FOR STAND

BY NOFILENAMECHECK;方式进行 RMAN 复制的时候也会用到备份,但 Oracle 从 11g 开始, DUP

LICATE 复制功能有所增强,可以实现在线的复制。----此次使用的ADG 方式;在线复制!

如需备份,方法:

创建备份路径

[[email protected] ~]$ mkdir /u01/app/oracle/rman

[[email protected] ~]$ rman target /

run{

backup database format ‘/u01/app/oracle/rman/full_%d%t%s%p‘ tag ‘fullbackup‘;

sql ‘alter system archive log current‘;

backup format ‘/u01/app/oracle/rman/%d_arch_%s_%p_%h‘ ARCHIVELOG ALL;

backup format ‘/u01/app/oracle/rman/control_%d‘ current controlfile for standby;

}

3.5 创建 Standby 数据库的 Pfile

create pfile=‘/home/oracle/initpdg.ora‘ from spfile;

--参数文件内容

*._gc_policy_time=0

*._gc_undo_affinity=FALSE

*._optim_peek_user_binds=FALSE

*._serial_direct_read=‘NEVER‘

*._undo_autotune=FALSE

*.audit_file_dest=‘/u01/app/oracle/admin/pdb/adump‘

*.audit_trail=‘NONE‘

*.cluster_database=true

*.compatible=‘11.2.0.4.0‘

*.control_files=‘+DATA/pdb/controlfile/control01.ctl‘,‘+DATA/pdb/controlfile/control02.ctl‘,‘+DATA/pdb/controlfile/control03.ctl‘

*.db_block_size=8192

*.db_create_file_dest=‘+DATA‘

*.db_domain=‘‘

*.db_file_name_convert=‘+DATA/pdg/DATAFILE‘,‘+DATA/pdb/DATAFILE‘

*.db_files=4096

*.db_name=‘pdb‘

*.deferred_segment_creation=FALSE

*.diagnostic_dest=‘/u01/app/oracle‘

*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=posdbXDB)‘

*.enable_ddl_logging=TRUE

*.enable_goldengate_replication=TRUE

*.fal_client=‘pdb‘

*.fal_server=‘pdg‘

pdb2.instance_number=2

pdb1.instance_number=1

*.job_queue_processes=1

*.log_archive_config=‘dg_config=(pdb,pdg)‘

*.log_archive_dest_1=‘location=+ARCH valid_for=(all_logfiles,all_roles) db_unique_name=pdb‘

*.log_archive_dest_2=‘service=pdg lgwr async NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=pdg‘

*.log_archive_dest_state_2=‘DEFER‘

*.log_archive_format=‘pdb_%t_%s_%r.arc‘

*.log_file_name_convert=‘+DATA/pdg/ONLINELOG‘,‘+DATA/pdb/ONLINELOG‘

*.open_cursors=1000

*.pga_aggregate_target=85899345920

*.processes=5000

*.recyclebin=‘OFF‘

*.remote_listener=‘scan-pdb:1521‘

*.remote_login_passwordfile=‘exclusive‘

*.result_cache_max_size=0

*.session_cached_cursors=100

*.sessions=7552

*.sga_max_size=270g

*.sga_target=270g

*.standby_file_management=‘AUTO‘

pdb2.thread=2

pdb1.thread=1

*.undo_retention=7200

pdb1.undo_tablespace=‘UNDOTBS1‘

pdb2.undo_tablespace=‘UNDOTBS2‘

3.6 更新 tnsnames.ora

修改 Primary 和 Standby 的 TNSNAMES.ORA

On Primary Node1 and Node2:

使用 oracle 用户

$ cd $ORACLE_HOME/network/admin

$ vi tnsnames.ora

pdb =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb)

)

)

pdb1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb)

(SID = pdb1)

)

)

pdb2 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb)

(SID = pdb2)

)

)

#Standby

pdg =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdg)

)

)

pdg1 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdg)

(SID = pdg1)

)

)

pdg2 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdg)

(SID = pdg2)

)

)

On Standby Node1 and Node2 with oracle user:

$ cd $ORACLE_HOME/network/admin

$ vi tnsnames.ora

pdg =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = pdg)

)

)

pdg1 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdg)

(SID = pdg1)

)

)

pdg2 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdg)

(SID = pdg2)

)

)

pdb =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb)

)

)

PRIMARY 服务名务必要添加 pdb,否则启动 MRP(Managed Recovery Process)的时

候日志里报如下错误:

Error 12154 received logging on to the standby

FAL[client, MRP0]: Error 12154 connecting to maa for fetching gap sequence

关于 FAIL_CLIENT 和 FAIL_SERVER 的信息:

FAL_CLIENT = Oracle_Net_service_name Required if the FAL_SERVER parameter is specified.

Specifies the Oracle Net service name used by the FAL server (typically the primary database) to refer to the FAL client (standby database).

FAL_SERVER = Oracle_Net_service_name

Required if the FAL_CLIENT parameter is specified. Specifies one or more Oracle Net service names for the databases

from which this standby database can fetch (request) missing archived redo log files.

很显然,两个参数值都需指定网络服务名,并非是 DB Unique Name。

 

 

四、准备 Standby RAC Database

4.1 复制参数文件

把从 Primary Database 备份出来的参数文件拷贝到 Standby Database 上,选择一个节点即可。

On Primary:

[[email protected] oracle]$ scp initpdg.ora pdg1:/u01/app/oracle/product/11.2.0/db_1/dbs/

4.2 复制密码文件

配置 DataGuard 需要两边数据库密码保持一致,把 Primary Database 的密码文件分别拷贝到 Sta

ndby Database 两个节点即可。

On Primary:

[[email protected] ~]$ cd $ORACLE_HOME/dbs

[[email protected] dbs]$ scp orapwpdb1 pdg1:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwpdg1

[[email protected] dbs]$ scp orapwpdb1 pdg2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwpdg2

如果你是拿 root 用户去操作了,那拷贝之后还需赋相应的权限。拷贝完后修改 Standby 第二节点

的密码文件名。

On Standby Node2:

[[email protected] ~]$ cd $ORACLE_HOME/dbs

[[email protected] dbs]$ mv orapwpdb1 orapwpdg2

4.3 创建相关目录

需要创建目录结构

On Standby Node1:

[[email protected] ~]$ mkdir -p /u01/app/oracle/admin/pdg/adump

[[email protected] ~]$ chmod 775 /u01/app/oracle/admin/pdg/adump

On Standby Node2:

[[email protected] ~]$ mkdir -p /u01/app/oracle/admin/pdg/adump

[[email protected] ~]$ chmod 775 /u01/app/oracle/admin/pdg/adump

4.4 修改参数文件

[[email protected] ~]$ cd $ORACLE_HOME/dbs

[[email protected] dbs]$ cat initpdg.ora

*._gc_policy_time=0

*._gc_undo_affinity=FALSE

*._optim_peek_user_binds=FALSE

*._serial_direct_read=‘NEVER‘

*._undo_autotune=FALSE

*.audit_file_dest=‘/u01/app/oracle/admin/pdg/adump‘ ---修改成备库目录

*.audit_trail=‘NONE‘

*.cluster_database=true

*.compatible=‘11.2.0.4.0‘

*.control_files=‘+DATA/pdg/controlfile/control01.ctl‘,‘+DATA/pdg/controlfile/control02.ctl‘,‘+DATA/pdg/controlfile/control03.ctl‘ ---修改成备库目录

*.db_block_size=8192

*.db_create_file_dest=‘+DATA‘ ---修改成备库目录

*.db_domain=‘‘

*.db_file_name_convert=‘+DATA/pdb/DATAFILE‘,‘+DATA/pdg/DATAFILE‘ -----修改成先主库,后备库

*.db_files=4096

*.db_name=‘pdb‘

*.db_unique_name=‘pdg‘ ---添加备库 db_unique_name

*.deferred_segment_creation=FALSE

*.diagnostic_dest=‘/u01/app/oracle‘

*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=posdbXDB)‘

*.enable_ddl_logging=TRUE

*.enable_goldengate_replication=TRUE

*.fal_client=‘pdg‘ ---改成备库

*.fal_server=‘pdb‘ ---改成主库

pdg2.instance_number=2 --改成现在备库instance 信息

pdg1.instance_number=1 --改成现在备库instance 信息

*.job_queue_processes=1

*.log_archive_config=‘dg_config=(pdb,pdg)‘ ---跟主库一致就行

*.log_archive_dest_1=‘location=+SAS_ARCH valid_for=(all_logfiles,all_roles) db_unique_name=pdg‘ ---改成备库

*.log_archive_dest_2=‘service=pdb lgwr async NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=pdb‘ ---改成主库

*.log_archive_dest_state_2=‘DEFER‘

*.log_archive_format=‘pdg_%t_%s_%r.arc‘ ---改成备库归档模式

*.log_file_name_convert=‘+DATA/pdb/ONLINELOG‘,‘+DATA/pdg/ONLINELOG‘ -----修改成先主库,后备库

*.open_cursors=1000

*.remote_listener=‘scan-pdg:1521‘ --改成备库 SCAN

*.pga_aggregate_target=85899345920

*.processes=5000

*.recyclebin=‘OFF‘

*.remote_login_passwordfile=‘exclusive‘

*.result_cache_max_size=0

*.session_cached_cursors=100

*.sessions=7552

*.sga_max_size=270g

*.sga_target=270g

*.standby_file_management=‘AUTO‘

pdg2.thread=2 --改成现在备库instance 信息

pdg1.thread=1 --改成现在备库instance 信息

*.undo_retention=7200

pdg1.undo_tablespace=‘UNDOTBS1‘ --改成现在备库instance 信息

pdg2.undo_tablespace=‘UNDOTBS2‘ --改成现在备库instance 信息

4.5 创建 ASM 路径

通过 grid 用户进入到 asmcmd,在 DATA 磁盘组下创建 standby 目录。

[[email protected] ~]# su - grid

[[email protected] ~]$ asmcmd

ASMCMD> mkdir DATA/pdg

ASMCMD>cd DATA/pdg

mkdir ONLINELOG

mkdir CONTROLFILE

mkdir DATAFILE

mkdir TEMPFILE

五、创建 Physical Standby Database

5.1 使用修改好的参数 Standby 实例启动到 NOMOUNT 状态

连接到 oracle 用户,使用上面已修改好的参数文件将 Standby 实例启动到 NOMOUNT。

On Standby Node1(仅在第一个节点操作):

[[email protected] ~]$ echo $ORACLE_SID

pdg1

[[email protected] ~]$ sqlplus / as sysdba

SQL> startup nomount pfile=‘$ORACLE_HOME/dbs/initpdg.ora‘

5.2 RMAN DUPLICATE 复制数据库

为使用在线方式的 RMAN DUPLICATE 技术,先配置监听,添加静态服务名。

Oracle 11g R2 开始, 只要是高可用架构,比如 oracle restart,oracle rac 等,监听器的创建和管

理都是拿 grid 用户来完成。

[[email protected] admin]$ cp listener.ora listener.ora_bak0823

[[email protected] admin]$ cp endpoints_listener.ora endpoints_listener.ora_bak0823

[[email protected] admin]$ vi listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = pdg)

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

(SID_NAME = pdg1)

)

)

[[email protected] admin]$ srvctl stop listener -n pdg1

[[email protected] admin]$ srvctl start listener -n pdg1

[[email protected] admin]$ lsnrctl status

[[email protected] admin]$ lsnrctl status

Service "pdg" has 2 instance(s).

Instance "pdg1", status UNKNOWN, has 1 handler(s) for this service...

Instance "pdg1", status BLOCKED, has 1 handler(s) for this service...

 

--操作过程

[[email protected] ~]$ lsnrctl tatus

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-AUG-2019 14:46:17

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

NL-00853: undefined command "tatus". Try "help"

[[email protected] ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-AUG-2019 14:46:21

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 23-AUG-2019 14:22:53

Uptime 0 days 0 hr. 23 min. 27 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/11.2.0/grid/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/pdg1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.3.1.70)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.3.1.72)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

The command completed successfully

[[email protected] ~]$

[[email protected] ~]$

[[email protected] ~]$ cd /u01/11.2.0/grid/network/admin/

[[email protected] admin]$ ls

endpoints_listener.ora listener19082311AM0203.bak listener.ora listener.ora.bak.pdg1 samples shrept.lst sqlnet.ora

[[email protected] admin]$ ll -thr

total 24K

-rw-r--r-- 1 grid oinstall 835 Sep 19 2018 shrept.lst

drwxr-xr-x 2 grid oinstall 61 Aug 23 10:24 samples

-rw-r--r-- 1 grid oinstall 184 Aug 23 10:43 listener.ora.bak.pdg1

-rw-r--r-- 1 grid oinstall 184 Aug 23 11:02 listener19082311AM0203.bak

-rw-r--r-- 1 grid oinstall 212 Aug 23 11:02 sqlnet.ora

-rw-r--r-- 1 grid oinstall 350 Aug 23 11:02 listener.ora

-rw-r--r-- 1 grid oinstall 184 Aug 23 11:02 endpoints_listener.ora

[[email protected] admin]$

[[email protected] admin]$

[[email protected] admin]$ cp listener.ora listener.ora_bak0823

[[email protected] admin]$ vi listener.ora

[[email protected] admin]$

[[email protected] admin]$ srvctl stop listener -n pdg1

[[email protected] admin]$ srvctl start listener -n pdg1

[[email protected] admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-AUG-2019 15:48:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 23-AUG-2019 14:47:31

Uptime 0 days 1 hr. 0 min. 47 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/11.2.0/grid/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/pdg1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.3.1.70)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.3.1.72)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "pdg" has 2 instance(s).

Instance "pdg1", status UNKNOWN, has 1 handler(s) for this service...

Instance "pdg1", status BLOCKED, has 1 handler(s) for this service...

The command completed successfully

[[email protected] admin]$

在 Primary Site tnsnames.ora 中加入

On Primary Node1:

[[email protected] ~]$ cd $ORACLE_HOME/network/admin

[[email protected] admin]$ vi tnsnames.ora

添加如下内容

pdg =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdg)

)

)

[[email protected] admin]$ tnsping pdg 3

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1.3.1.75)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdg)))

OK (10 msec)

OK (0 msec)

OK (0 msec)

在进行 RMAN DUPLICATE 操作之前,我们还要修改 oracle 二进制命令的权限。 参考下面一段:

[[email protected] soft]# ll /u01/app/oracle/product/11.2.0/db_1/bin/oracle

-rwsr-s--x 1 oracle oinstall 239826136 Aug 23 14:41 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[[email protected] soft]# chown oracle:asmadmin /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[[email protected] soft]# ll /u01/app/oracle/product/11.2.0/db_1/bin/oracle

-rwxr-x--x 1 oracle asmadmin 239826136 Aug 23 14:41 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[[email protected] soft]# chmod 6751 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[[email protected] soft]# ll /u01/app/oracle/product/11.2.0/db_1/bin/oracle

-rwsr-s--x 1 oracle asmadmin 239826136 Aug 23 14:41 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[[email protected] soft]#

[[email protected] soft]# ll /u01/app/oracle/product/11.2.0/db_1/bin/oracle

-rwsr-s--x 1 oracle oinstall 239826136 Aug 23 14:42 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[[email protected] soft]# chown oracle:asmadmin /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[[email protected] soft]# ll /u01/app/oracle/product/11.2.0/db_1/bin/oracle

-rwxr-x--x 1 oracle asmadmin 239826136 Aug 23 14:42 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[[email protected] soft]# chmod 6751 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[[email protected] soft]# ll /u01/app/oracle/product/11.2.0/db_1/bin/oracle

-rwsr-s--x 1 oracle asmadmin 239826136 Aug 23 14:42 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[[email protected] soft]#

 

released channel: ch05

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 08/26/2019 13:08:42

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ch01 channel at 08/26/2019 13:08:42

ORA-17628: Oracle error 19505 returned by remote Oracle server

 

---改完权限最好重启一下 crs 两个节点上

/etc/init.d/ohasd stop

/etc/init.d/ohasd start

--如果不重启可能会报如下错

[[email protected] ~]$ rman target sys/[email protected] auxiliary sys/[email protected]/pdg

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 26 13:10:13 2019

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

connected to target database: pdb (DBID=161385843)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04006: error from auxiliary database: ORA-12537: TNS:connection closed

---即便已经是 静态注册;---怀疑此次报错的原因是 通过 OPATCH 打完 补丁 没有重启集群导致的

[email protected] admin]$ rman target sys/[email protected] auxiliary sys/[email protected]

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Aug 23 15:57:54 2019

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

connected to target database: pdb (DBID=161385843)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04006: error from auxiliary database: ORA-12154: TNS:could not resolve the connect identifier specified

[[email protected] admin]$

造成这个问题的原因是,实例虽然启动,但是没有注册到监听。实例是通过PMON进程注册到监听上的,而PMON进程需要在MOUNT状态下才会启动。因此造成了上面的错误。

Oracle给出了两种解决方案,一种方法是对AUXILIARY数据库直接使用/,对TARGET数据库通过网络访问。

 

5.3 RMAN DUPLICATE 开始复制数据库

rman target sys/[email protected] auxiliary sys/[email protected]

screen -S dup ---开始录屏,名字 dup

screen -list --查询正在进行的录屏

screen -r dup --调出正在进行的录屏

---screen -S dup ---后台录屏

run{

allocate channel ch01 type disk rate 50M;

allocate channel ch02 type disk rate 50M;

allocate channel ch03 type disk rate 50M;

allocate channel ch04 type disk rate 50M;

allocate auxiliary channel ch05 type disk;

duplicate target database for standby nofilenamecheck from active database;

release channel ch01;

release channel ch02;

release channel ch03;

release channel ch04;

release channel ch05;

}

--duplicate target database for standby from active database DORECOVER;

screen -list --查询正在录屏

screen -r dup --调出录屏

5.4 启动 Managed Recovery Process

在 RMAN DUPLICATE 操作进行过程中, Standby Database 自动切换到 MOUNT 模式

select instance_name,status from v$instance;

我们启动 Managed Recovery Process 来应用日志, Oracle 11g R2 开始,物理 standby 在打开的

状态下也可以应用日至,称为 acitve dataguard。

On Standby Node1:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

测试应用日志过程,我们手动切换 Primary Site 的日志,并观察日志:

On Primary:

SQL> alter system switch logfile;

可执行下面脚本查看一些应用日志相关信息:

select * from v$archive_gap;

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

select sequence#, first_time, next_time, applied from v$archived_log;

select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;

select thread#, max (sequence#) from v$log_history group by thread#;

select thread#, max (sequence#) from v$archived_log where APPLIED=‘YES‘ group by thread#;

5.5 创建 spfile 并使用 spfile 启动 RAC Database

我们在上面是通过 Pfile 启动的实例,我们需要创建 spfile,并使用它来启动 RAC 实例。

On Standby Node1 or Node2(其中一个节点上操作) with oracle user:

SQL> create spfile=‘+DATA/pdg/parameterfile/spfilepdg.ora‘ from pfile=‘$ORACLE_HOME/dbs/initpdg.ora‘;

On Standby Node1 or Node2(其中一个节点上操作) with grid user:

ASMCMD> ls -l +DATA/pdg/parameterfile

然后将 spfile 路径分别添加到 Standby 节点的 init.ora 里,操作如下:

On Standby Node1:

[[email protected] dbs]$ pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

[[email protected] dbs]$ cat initpdg1.ora

spfile=‘+DATA/pdg/parameterfile/spfilepdg.ora‘

On Standby Node2:

[[email protected] dbs]$ pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

[[email protected] dbs]$ cat initpdg2.ora

spfile=‘+DATA/pdg/parameterfile/spfilepdg.ora

5.6 Standby Database 注册到 OCR 里---这步骤,没操作

添加 Standby Database 和实例到 OCR 中,好让 Clusterware 可以管理资源。

On Standby Node1 or Node2(其中一个节点上操作):

[[email protected] ~]$ srvctl add database -d pdg -n pdg -o $ORACLE_HOME -m luocs.com -p +DATA/pdg/parameterfile/spfilepdg.ora -r physical_standby -a DATA

参考:

Usage: srvctl add database -d <db_unique_name> -o <oracle_home> [-c {RACONENODE

| RAC | SINGLE} [-e <server_list>] [-i <inst_name>] [-w <timeout>]] [-m <domain_na

me>] [-p <spfile>] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSH

OT_STANDBY}] [-s <start_options>] [-t <stop_options>] [-n <db_name>] [-y {AUTOMAT

IC | MANUAL | NORESTART}] [-g "<serverpool_list>"] [-x <node_name>] [-a "<diskgrou

p_list>"] [-j "<acfs_path_list>"]

添加实例

[[email protected] ~]$ srvctl add instance -d pdg -i pdg1 -n pdg1

[[email protected] ~]$ srvctl add instance -d pdg -i pdg2 -n pdg2

查看配置

[[email protected] ~]$ srvctl config database -d pdg

Database unique name: pdg

Database name: maa

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA/pdg/parameterfile/spfilepdg.ora

Domain: luocs.com

Start options: open

Stop options: immediate

Database role: PHYSICAL_STANDBY

Management policy: AUTOMATIC

Server pools: standby

Database instances: pdg1,pdg2

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Database is administrator managed

 

5.7 启动 Active Dataguard.

重启动 Standby Database

On Standby Node1:

SQL> alter database recover managed standby database cancel;

[[email protected] ~]$ srvctl stop instance -d pdg -i pdg1

[[email protected] ~]$ srvctl start database -d pdg

查看 Standby Database 启动状态:

SQL> set line 120

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

NAME DATABASE_ROLE OPEN_MODE

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

PDG PHYSICAL STANDBY READ ONLY

PDG PHYSICAL STANDBY READ ONLY

启动 Managed Recovery Process

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

简单进行测试

On Primary:

SQL> create tablespace L datafile size 50M;

SQL> grant resource,connect to L identified by oracle;

SQL> alter user l default tablespace L;

SQL> create table l.test as select object_id,object_name from dba_objects where rownum<= 500;

On Standby:SQL> select count(*) from l.test;COUNT(*)----------500

0

精彩评论

暂无评论...
验证码 换一张
取 消