oracle 11g dg 部署rman方式要点记录

1、环境介绍

2、修改hostname

hostnamectl set-hostname oraclep

3、db部署

主：创建库，从：不创建数据库
就是没有dbca那一步，网络和基础环境还是需要的

4、主库开启归档并设置强制日志 force logging

SQL> shutdown immediate

停止数据库操作

startup mount

启动到mount状态

alter database archivelog;

开启归档

alter database force logging;

强制记录日志，即对数据库中的所有操作都产生日志信息，并将该信息写入到联机重做日志文件。

alter database open;

打开数据库

archive log list;

想查看数据的归档模式

select force_logging from v$database;

确认是否为强制日志

5、主库添加standby redo log

select member from v$logfile;

查看redo和standby redo

select * from v$log;

查看redo情况

alter database add standby logfile group 21 '/u01/app/oradata/yunhaip/standby21.log' size 50M;
alter database add standby logfile group 22 '/u01/app/oradata/yunhaip/standby22.log' size 50M;
alter database add standby logfile group 23 '/u01/app/oradata/yunhaip/standby23.log' size 50M;
alter database add standby logfile group 24 '/u01/app/oradata/yunhaip/standby24.log' size 50M;

增一组大小为50M的standby redo，这里的group号不得与online redo重复,正式环境文件大小需要调整

6、配置文件修改
6.1、主库pfile创建，以便做出修改

SQL>create pfile from spfile;
SQL> host
[oracle@oraclep ~]$ cd $ORACLE_HOME/dbs
[oracle@oraclep dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
cat >> /u01/app/oracle/product/11.2.0/db_1/dbs/inityunhaip.ora << "EOF"
*.db_unique_name='yunhaip'
*.fal_server='yunhais'
*.log_archive_config='dg_config=(yunhaip,yunhais)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=yunhaip'
*.log_archive_dest_2='service=yunhais lgwr async valid_for=(online_logfile,primary_role) db_unique_name=yunhais'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.standby_file_management='AUTO'
*.db_file_name_convert='/u01/app/oradata/yunhaip','/u01/app/oradata/yunhaip'
*.log_file_name_convert='/u01/app/oradata/yunhaip','/u01/app/oradata/yunhaip'
EOF

6.2、拷贝主库的pfile到从库，并修改如下内容：

[oracle@oraclep dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@oraclep dbs]$ scp inityunhaip.ora
192.168.56.117:/u01/app/oracle/product/11.2.0/db_1/dbs/

cat >> /u01/app/oracle/product/11.2.0/db_1/dbs/inityunhaip.ora << "EOF"
*.db_unique_name='yunhais'
*.fal_server='yunhaip'
*.log_archive_config='dg_config=(yunhaip,yunhais)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=yunhais'
*.log_archive_dest_2='service=yunhaip lgwr async valid_for=(online_logfile,primary_role) db_unique_name=yunhaip'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.standby_file_management='AUTO'
*.db_file_name_convert='/u01/app/oradata/yunhaip','/u01/app/oradata/yunhaip'
*.log_file_name_convert='/u01/app/oradata/yunhaip','/u01/app/oradata/yunhaip'
EOF

6.3、说明：

dg_config=(yunhaip,yunhais) 以外，其他情况主从的相关信息对调即可

6.4、创建新的主库spfile文件，并重新启动主库

SQL> shutdown immediate
SQL> create spfile from pfile;
SQL> startup
SQL> ALTER USER SYS IDENTIFIED BY sys;

修改sys密码为以后rman连接使用

6.5、 复制主库的密码文件到备库

scp orapwyunhaip 192.168.56.117:/u01/app/oracle/product/11.2.0/db_1/dbs/

7、从库创建相关目录

strings spfileyunhaip.ora

获得目录，我观察的是主库的

mkdir -p /u01/app/oracle
mkdir -p /u01/app/admin/yunhaip/{a,b,c,d,u}dump
mkdir -p /u01/app/oradata/yunhaip/
mkdir -p /u01/app/fast_recovery_area/yunhaip/

8、创建tnsnames.ora ，主从一致即可

cat >> /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora << "EOF"
yunhaip =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.118)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = yunhaip)
)
)

yunhais =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.117)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = yunhais)
)
)
EOF

9、修改备份库的listener.ora

cat >> /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora << "EOF"
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = yunhais)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = yunhaip)
)
)

EOF

原因如下：
[oracle@oracles ~]$ rman target sys/sys@yunhaip auxiliary sys/sys@yunhais
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 3 15:43:07 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: YUNHAIP (DBID=665781658)
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-12528: TNS:listener: all appropriate instances are blocking new connections

10、尝试启动从库到nomount

SQL> create spfile from pfile;
SQL> startup nomount

11、RMAN复制主库到备库
11.1、首先RMAN连接到主数据库和备数据库

rman target sys/sys@yunhaip auxiliary sys/sys@yunhais

如果有相关报错，请注意9，6.4步骤

11.2、使用RMAN的duplicate命令进行复制，两边目录结构相同，需要添加nofilenamecheck参数

duplicate target database for standby from active database nofilenamecheck;

12、复制完毕，对从库进行相关处理

select status from v$instance;

查询从库是否处于MOUNTED状态

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

在备库开启实时日志应用

13、观察主从正确状态
13.1、观察主库alert日志

vim alert_yunhaip.log

Error 12154 received logging on to the standby发现这个错误

13.2、重启主库

SQL> shutdown immediate;
SQL> startup;

13.3观察主库状态：

SQL> select switchover_status,database_role from v$database;

SWITCHOVER_STATUS DATABASE_ROLE

---

TO STANDBY PRIMARY

13.4、观察从库状态

SQL> select switchover_status,database_role from v$database;

SWITCHOVER_STATUS DATABASE_ROLE

---

NOT ALLOWED PHYSICAL STANDBY

14、通过切换日志观察同步情况
14.1、主库切换

SQL> archive log list;
SQL> alter system switch logfile;
SQL> archive log list;

14.2、从库观察

SQL> archive log list;

15、从库open，以便用户能够读取

alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect ;

[oracle@oracles ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 15:59:46 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

16、实验过程
16.1、观察从库现有数据

[oracle@oracles ~]$ sqlplus test/test
SQLPlus: Release 11.2.0.3.0 Production on Wed Jul 3 16:01:59 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select from test;
ID NUMS

---

1 2
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracles ~]$ exit
登出
Connection to 192.168.56.117 closed.

16.2、主库添加新数据

[oracle@oraclep trace]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 16:02:19 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into test values(2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

16.3、从库观察新数据

[oracle@oraclep trace]$ ssh 192.168.56.117
oracle@192.168.56.117's password:
Last login: Wed Jul 3 16:01:52 2019 from 192.168.56.118
[oracle@oracles ~]$ sqlplus test/test
SQLPlus: Release 11.2.0.3.0 Production on Wed Jul 3 16:02:36 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select from test;
ID NUMS

---

1 2
2 2

补充：
补充一、 只读方式开始从库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  768294912 bytes
Fixed Size                  2232312 bytes
Variable Size             452984840 bytes
Database Buffers          310378496 bytes
Redo Buffers                2699264 bytes
SQL> alter database mount standby database;

Database altered.

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

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

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

Database altered.

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

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 09:32:48 2019

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

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

SQL> select *from test;

    ID       NUMS
---------- ----------
     1          2
     2          2
     2          3
     2          4

SQL> select *from test;

    ID       NUMS
---------- ----------
     1          2
     2          2
     2          3
     2          4
     2          5

SQL>