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

原创

xingzhehxiang 2019-07-03 16:43:18 ©著作权

文章标签 dg 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者xingzhehxiang的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、环境介绍

ip	hostname	sid	db_name	db_unique_name	net service name
192.168.56.118	oraclep	yunhaip	yunhaip	yunhaip	yunhaip
192.168.56.117	oracles	yunhaip	yunhaip	yunhais	yunhais

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 SQL*Plus: 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 SQL*Plus: 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>