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
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> 
