Oracle Data Guard 创建物理Standby数据库

来源：互联网发布：vivo网络授权书怎么编辑：程序博客网时间：2024/05/01 20:10

创建物理备库

机器名 a1 a2 IP: 192.168.1.10 192.168.1.20 Net_Name a1 a2 SID a1 a2 DB_UNIQUE_NAME a1 a2

注：主节点上创建数据库a1,备节点上只安装oracle软件不创建任何数据库;

1.配置listener.ora
主节点listener.ora：

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=a1)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
          (SID_NAME=a1)
       )
   )

LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.10)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)

备节点listener.ora:

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=a1)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
          (SID_NAME=a1)
       )
   )

LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.20)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)

2.配置tnsnames.ora(主备节点的tnsname.ora内容一样：)

a1=
(DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
     (CONNECT_DATA =
      (SERVICE_NAME = a1)
     )
)

a2=
(DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521))
    )
     (CONNECT_DATA =
      (SERVICE_NAME = a2)
     )
)

3.在主库启用归档并且启用强制归档

步骤1：alter database force logging;

archive log list;

步骤2：在mount状态下

alter database archivelog;

步骤3：alter database open;

archive log list;

4.在主库上创建密码文件

orapwd file='/u01/app/oracle/product/11.2.0/db_1/dbs/orapwa1.ora' password=xyc entries=10

5.创建standby redo log

alter database add standby logfile
group 4 ('/u01/app/oracle/oradata/a1/standby_redo04a.log','/u01/app/oracle/oradata/a1/standby_redo04b.log') size 50m,
group 5 ('/u01/app/oracle/oradata/a1/standby_redo05a.log','/u01/app/oracle/oradata/a1/standby_redo05b.log') size 50m,
group 6 ('/u01/app/oracle/oradata/a1/standby_redo06a.log','/u01/app/oracle/oradata/a1/standby_redo06b.log') size 50m,
group 7 ('/u01/app/oracle/oradata/a1/standby_redo07a.log','/u01/app/oracle/oradata/a1/standby_redo07b.log') size 50m;

注意：Oracle 建议你在创建 standby 时就考虑 standby redolog 配置的问题。standby redologs 与 online redologs
非常类似，应该说两者只是服务对象不同，其它参数属性甚至操作的命令格式几乎都一样，你在设计 standby
redologs 的时候完全可以借鉴创建 online redologs 的思路，比如多个文件组啦，每组多个文件冗余之类的。
除些之外呢，oracle 提供了一些标准的建议如下：
� 确保 standby redo log 的文件大小与 primary 数据库 online redo log 文件大小相同。
这个很好理解的吧，就是为了接收和应用方便嘛。
� 创建适当的日志组
一般而言， standby redo 日志文件组数要比 primary 数据库的 online redo 日志文件组数至少多一个。
推荐 standby redo 日志组数量基于 primary 数据库的线程数(这里的线程数可以理解为 rac 结构中的 rac
节点数)。
有一个推荐的公式可以做参考：(每线程的日志组数+1)*最大线程数
例如 primary 数据库有两个线程，每个线程分配两组日志，则 standby 日志组数建议为 6 组，使用
这个公式可以降低 primary 数据库实例 LGWR 进程锁住的可能性。
提示：逻辑 standby 数据库有可能需要视工作量增加更多的 standby redo log 文件(或增加归档进程)，
因为逻辑 standby 需要同时写 online redo log 文件。

6.在主库创建pfile，然后修改：

create pfile from spfile;

修改pfile

主库：

DB_UNIQUE_NAME='a1'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(a1,a2)'
log_archive_dest_1='location=/archive1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=a1'
log_archive_dest_2='service=a2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=a2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
fal_server=a2
fal_client=a1
remote_login_passwordfile='EXCLUSIVE'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
db_file_name_convert='/u01/app/oracle/oradata/a1','/u01/app/oracle/oradata/a1'
log_file_name_convert='/u01/app/oracle/oradata/a1/','/u01/app/oracle/oradata/a1'
STANDBY_FILE_MANAGEMENT=AUTO

备库：

DB_UNIQUE_NAME='a2'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(a1,a2)'
log_archive_dest_1='location=/archive1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=a2'
log_archive_dest_2='service=a1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=a1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
fal_server=a1
fal_client=a2
remote_login_passwordfile='EXCLUSIVE'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
db_file_name_convert='/u01/app/oracle/oradata/a1','/u01/app/oracle/oradata/a1'
log_file_name_convert='/u01/app/oracle/oradata/a1/','/u01/app/oracle/oradata/a1'
STANDBY_FILE_MANAGEMENT=AUTO

7.创建standby数据控制文件

alter database create standby controlfile as '/u01/app/oracle/oradata/a1/standby.ctl';

‍88.

SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/inita1.ora';
ORACLE instance started.

Total System Global Area 417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             335546320 bytes
Database Buffers           75497472 bytes
Redo Buffers                4288512 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;

‍

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

9.拷贝数据文件，日志文件，standby controlfile，密码文件，参数文件，至a2服务器上相关位置

10.，修改参数文件和修改standby controlfile名字做备库控制文件

a1.__db_cache_size=75497472
a1.__java_pool_size=16777216
a1.__large_pool_size=4194304
a1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
a1.__pga_aggregate_target=150994944
a1.__sga_target=268435456
a1.__shared_io_pool_size=0
a1.__shared_pool_size=155189248
a1.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/a1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/a1/control01.ctl','/u01/app/oracle/oradata/a1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='a1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=a1XDB)'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME='a2'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(a1,a2)'
log_archive_dest_1='location=/archive1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=a2'
log_archive_dest_2='service=a1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=a1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
fal_server=a1
fal_client=a2
remote_login_passwordfile='EXCLUSIVE'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
db_file_name_convert='/u01/app/oracle/oradata/a1','/u01/app/oracle/oradata/a1'
log_file_name_convert='/u01/app/oracle/oradata/a1/','/u01/app/oracle/oradata/a1'
STANDBY_FILE_MANAGEMENT=AUTO

[oracle@a2 a1]$ cp standby.ctl control01.ctl
[oracle@a2 a1]$ cp standby.ctl control02.ctl
[oracle@a2 a1]$ cp standby.ctl control03.ctl

11.确定监听，tns正常后，启动备库到mount状态

startup mount

12.备库上执行以下命令使备库自动应用已归档的日志
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

13.验证：
.在主备库上看当前已归档日志

SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;