环境描述：
| | 主库 | 备库 |
| -------------- | ------------ | ---------- |
| 版本 | windows11204 | linux11204 |
| IP地址 | 209 | 145 |
| SID | orcl | stddb |
| DB_NAME | orcl | orcl |
| DB_UNIQUE_NAME | pridb | stddb |
| SERVICE_NAME | pridb | stddb |
**一、主库的前期准备工作**
**1.Enable Forced Logging --主库设置强制日志，保证所有的操作都记录到日志文件**
SQL> select force_logging from v$database;
FOR
\---
NO
SQL> alter database force logging;
**数据库已更改**。
SQL> select force_logging from v$database;
FOR
\---
YES
**2.检查主库的密码文件，同一个Data Guard 配置中所有数据库必须都拥有独立的密码文件，**
--并且必须保证同一个Data Guard 配置中所有数据库服务器的SYS 用户拥有相同密码以保证redo 数据的顺利传输，
--因为redo传输服务通过认证的网络会话来传输redo 数据，而会话使用包含在密码文件中的SYS 用户密码来认证。
**3.设置主库的参数文件**
手动编辑
C:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ORA
内容如下：
orcl.__db_cache_size=352321536
orcl.__java_pool_size=4194304
orcl.__large_pool_size=12582912
orcl.__oracle_base='C:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=343932928
orcl.__sga_target=515899392
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=134217728
orcl.__streams_pool_size=0
*.audit_file_dest='C:\app\Administrator\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='C:\app\Administrator\oradata\orcl\control01.ctl','C:\app\Administrator\oradata\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='C:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=857735168
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
db_unique_name=pridb
log_archive_config='dg_config=(pridb,stddb)'
log_archive_dest_1='location=C:\app\Administrator\arch valid_for=(all_logfiles,all_roles) db_unique_name=pridb'
log_archive_dest_2='service=stddb valid_for=(online_logfiles,primary_role) db_unique_name=stddb'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
db_file_name_convert='/oracle/app/oradata/stddb/','C:\APP\ADMINISTRATOR\ORADATA\ORCL\'
log_file_name_convert='/oracle/app/oradata/stddb/','C:\APP\ADMINISTRATOR\ORADATA\ORCL\'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
**4.创建相应目录：**
C:\app\Administrator\arch
**5.重新应用新的参数文件：**
SYS> shutdown immediate; --一致性停库
SYS> create spfile from pfile; --创建新的spfile
SYS> startup;
**6.给主库增加standby redo logfile**
主库不需要这个文件，当主库变成备库的时候才使用，个数一般是比现有日志组的数量+1
SYS> alter database add standby logfile group 4
'C:\APP\ADMINISTRATOR\ORADATA\ORCL\STD_REDO04.LOG' size 100M;
SYS> alter database add standby logfile group 5
'C:\APP\ADMINISTRATOR\ORADATA\ORCL\STD_REDO05.LOG' size 100m;
SYS> alter database add standby logfile group 6
'C:\APP\ADMINISTRATOR\ORADATA\ORCL\STD_REDO06.LOG' size 100M;
SYS> alter database add standby logfile group 7
'C:\APP\ADMINISTRATOR\ORADATA\ORCL\STD_REDO07.LOG' size 100m;
SYS> select group#,status,used from v$standby_log; --验证standby redo log 文件组是否创建成功
GROUP# STATUS USED
---------- ------------------ ----------
4 UNASSIGNED 0
5 UNASSIGNED 0
6 UNASSIGNED 0
7 UNASSIGNED 0
**7.主库要设置为归档模式**
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 C:\app\Administrator\arch
最早的联机日志序列 15
下一个存档日志序列 17
当前日志序列 17
**二、设置standby database**
**1.设置一个密码文件**
[oracle@service1 dbs]$ export ORACLE_SID=stddb
--指定standby database的ORACLE_SID
[oracle@service1 dbs]$ cd $ORACLE_HOME/dbs
[oracle@service1 dbs]$ orapwd file=orapwstddb password=oracle --设置standby database的密码文件
--主库备库的sys用户的密码必须一致，如果出现ora-16191错误
**2.设置standby database的参数文件**
[oracle@service1~]$ $ cd $ORACLE_HOME/dbs
//从主库复制一份修改
**备库参数文件内容：**
stddb.__db_cache_size=352321536
stddb.__java_pool_size=4194304
stddb.__large_pool_size=8388608
stddb.__oracle_base='/u01/app/oracle'
stddb.__pga_aggregate_target=335544320
stddb.__sga_target=503316480
stddb.__shared_io_pool_size=0
stddb.__shared_pool_size=125829120
stddb.__streams_pool_size=0
*.audit_file_dest='/oracle/app/admin/stddb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/app/oradata/stddb/control01.ctl','/oracle/app/oradata/stddb/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.diagnostic_dest='/oracle/app'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=170
*.undo_tablespace='UNDOTBS1'
db_unique_name=stddb
log_archive_config='dg_config=(pridb,stddb)'
log_archive_dest_1='location=/oracle/app/arch/stddb valid_for=(all_logfiles,all_roles) db_unique_name=stddb'
log_archive_dest_2='service=pridb valid_for=(online_logfiles,primary_role) db_unique_name=pridb'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
fal_server=pridb
db_file_name_convert='C:\APP\ADMINISTRATOR\ORADATA\ORCL\','/oracle/app/oradata/stddb/'
log_file_name_convert='C:\APP\ADMINISTRATOR\ORADATA\ORCL\','/oracle/app/oradata/stddb/'
STANDBY_FILE_MANAGEMENT=AUTO
**3.创建相应的路径：**
[oracle@server1 ~]$ mkdir /oracle/app/oradata/stddb -pv
[oracle@server1 ~]$ mkdir /oracle/app/admin/stddb/adump -pv
[oracle@server1 ~]$ mkdir /oracle/app/arch/stddb -pv
**4.启动备库实例**
[oracle@service1 dbs]$ export ORACLE_SID=stddb
[oracle@service1 dbs]$ sqlplus / as sysdba
SYS@stddb>create spfile from pfile;
SYS@stddb> startup nomount;
**三、配置网络**
**1.备库需要配置静态注册 --主库使用动态注册即可**
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.145)(PORT=1522))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=stddb)
(ORACLE_HOME=/oracle/app/product/11.2.0/db_1)
(SID_NAME=stddb))
)
**2.重载监听**
[oracle@service2 admin]$ lsnrctl start
[oracle@service2 admin]$ lsnrctl reload
SYS@stddb>alter system register;
[oracle@service2 admin]$ lsnrctl status
Service "stddb.neves.com" has 2 instance(s).
Instance "stddb", status UNKNOWN, has 1 handler(s) for this service...
Instance "stddb", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
**3.在主备库配置本地服务名**
**主库：**
C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
pridb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.209)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pridb)
)
)
stddb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.145)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stddb)
)
)
**备库：**
[oracle@service1 admin]$ vim tnsnames.ora
pridb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.209)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pridb)
)
)
stddb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.145)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stddb)
)
)
**4.测试网络连接**
--tnsping：
C:\Users\Administrator>tnsping pridb
C:\Users\Administrator>tnsping stddb
--sqlplus测试
C:\Users\Administrator>sqlplus sys/oracle@pridb as sysdba
C:\Users\Administrator>sqlplus sys/oracle@stddb as sysdba
**四：恢复备库**
**1.在主库使用RMAN同时连接主库以及备库**
C:\Users\Administrator>rman target sys/oracle@pridb auxiliary sys/oracle@stddb
恢复管理器: Release 11.2.0.4.0 - Production on 星期二 8月 11 10:34:49 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: ORCL (DBID=1574596224)
已连接到辅助数据库: PROD (未装载)
**RMAN> duplicate target database for standby from active database**
//from active database ：
//通过网络直接传输，如果使用此方式，需要将主库的备份拷贝到备库相应位置。
//恢复完之后备库自动启动到mount状态
**2.查看日志传输情况：**
SYS@stddb>select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
18 NO
20 NO
19 NO
3.启动日志应用服务：
SQL> alter database recover managed standby database disconnect from session;
4.查看日志应用情况：
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
19 YES
20 YES
21 YES
18 YES
17 YES
扫一扫
587
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
