重庆思庄Oracle、Redhat认证学习论坛

标题: 如何在单机实现多个dg备库，并切换到其中一个备库 [打印本页]

作者: 郑全 时间: 2019-11-15 16:12
标题: 如何在单机实现多个dg备库，并切换到其中一个备库
本帖最后由郑全于 2019-11-15 16:18 编辑

如何在单机实现多个dg备库，并切换到其中一个备库
环境：
   os:rhel7.6
   db:12.1.0.2
   db-name:orcl
规划：
   主库：  pridb  /u01/app/oracle/oradata/orcl/
   备库1：auxdb /u01/app/oracle/oradata/auxdb/
   备库2：stddb  /u01/app/oracle/oradata/auxdb/
要求：
   在本地实现一个主库orcl，向两个本地备库(auxdb,stddb)进行数据同步,最后，主动切换到一个备库auxdb，销毁原来的主库orcl，并在原来的两个备库之间形成一个新的主备库。

作者: 郑全 时间: 2019-11-15 16:35
一.创建第一个DG备库
1.1 修改主库orcl的参数
   1.启动归档
      如果没有归档：
      Sql> startup mount;
      Sql>alter database archivelog;
   2.开启强制日志
      SQL>alter database force logging;

   3.设置唯一名字，把主库
      SQL>alter system set db_unique_name=pridb scope=spfile;

   4.配置log_archive_config
      SQL>alter system set log_archive_config='dg_config=(pridb,auxdb)';

   5.设置归档目标地
      SQL>alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=pridb' scope=spfile;
      SQL>alter system set log_archive_dest_2='service=auxdb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=auxdb';

   6.设置提取服务器
      SQL>alter system set fal_server=auxdb;
      SQL>alter system set fal_client=pridb;

   7.设置表空间变化自适应
      SQL>alter system set standby_file_management=auto;
1.2 auxdb备库准备
  由于在同一台机器，需要设置路径转换
  1.创建auxdb参数
*.instance_name='auxdb'
*.db_name='orcl'
*.db_unique_name='auxdb
*.log_archive_config='dg_config=(pridb,auxdb)'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/auxdb/'
*. log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/auxdb/'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=auxdb'
*.log_archive_dest_2='service=pridb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=pridb'
*.standby_file_management='AUTO'
*.fal_client='auxdb'
*.fal_server='pridb'

  创建对应的路径
  mkdir -p /u01/app/oracle/oradata/auxdb/
  mkdir -p /u01/app/oracle/admin/auxdb/adump

  2.创建spfile
export ORACLE_SID=auxdb
sql>create spfile from pfile='/home/oracle/initauxdb.ora';

  3.启动到nomount;
sql>startup nomount;

  4.创建口令文件
   在本机，直接复制一个即可
   cp $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapwauxdb
  4.静态监听
   直接修改$ORACLE_HOME/network/admin/listener.ora
   ...

   SID_LIST_LISTENER =
      (SID_LIST =
         (SID_DESC =
            (GLOBAL_DBNAME = auxdb)
            (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
            (SID_NAME = auxdb)
         )
      )

1.3.创建到主备库的连接串
   直接修改 $ORACLE_HOME/network/admin/tnsnames.ora
pridb =
  (DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
(CONNECT_DATA =
   (SERVICE_NAME = pridb)
)
  )

auxdb =
  (DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
(CONNECT_DATA =
   (SERVICE_NAME = auxdb)
)
  )

1.4.测试联通性

SQL> conn sys/oracle_4U@pridb as sysdba
Connected.
SQL> conn sys/oracle_4U@auxdb as sysdba
Connected.
SQL>

1.5.开始复制
rman target sys/oracle_4U@pridb auxiliary sys/oracle_4U@auxdb
rman > duplicate target database for standby NOFILENAMECHECK from active database;

1.6.备库处于恢复模式

SQL> alter database recover managed standby database disconnect from session;

SQL> select process,status from v$managed_standby;

PROCESS STATUS
--------- ------------
RFS    IDLE
ARCH    CLOSING
ARCH    CONNECTED
ARCH    CONNECTED
ARCH    CLOSING
RFS    IDLE
RFS    IDLE
MRP0    APPLYING_LOG
RFS    IDLE

9 rows selected.

到此，第一个备库就搭建完成。

作者: 郑全 时间: 2019-11-15 16:45

郑全发表于 2019-11-15 16:35
一.创建第一个DG备库
1.1 修改主库orcl的参数
1.启动归档

二、创建第二个备库stddb
2.1 调整pridb主库设置
1.配置log_archive_config
SQL>alter system set log_archive_config='dg_config=(pridb,auxdb,stddb)';
2.设置归档目标地
SQL>alter system set log_archive_dest_3='service=stddb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=stddb';
3.设置端点提取
SQL>alter system set fal_server=stddb;
System altered.
2.2 准备新备库stddb
1.建立参数
*.audit_file_dest='/u01/app/oracle/admin/stddb/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/stddb/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.memory_target=600m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_name='orcl'
--下面是调整参数
*.instance_name=stddb
*.db_unique_name='stddb'
*.log_archive_config='dg_config=(pridb,auxdb,stddb)'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/stddb/'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/stddb/'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=stddb'
*.log_archive_dest_2='service=pridb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=pridb'
*.standby_file_management='AUTO'
*.fal_client='stddb'
*.fal_server='pridb'

2.建立对应目录
[oracle@dbserver ~]$ mkdir -p /u01/app/oracle/admin/stddb/adump
[oracle@dbserver ~]$ mkdir -p /u01/app/oracle/oradata/stddb/
3.创建spfile
[oracle@dbserver dbs]$ echo $ORACLE_SID
stddb

sql>create spfile from pfile='/home/oracle/initstddb.ora';

4.创建口令文件
[oracle@dbserver dbs]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1/dbs
[oracle@dbserver dbs]$ cp orapworcl orapwstddb
5.使用spfile启动到nomount;
sql>startup nomount;
SQL> show parameter spfile
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
spfile                            string    /u01/app/oracle/product/12.1.0
                                             /dbhome_1/dbs/spfilestddb.ora
SQL>
6.添加静态监听
   $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC =
   (GLOBAL_DBNAME = auxdb)
   (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
   (SID_NAME = auxdb)
)
(SID_DESC =
   (GLOBAL_DBNAME = stddb)
   (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
   (SID_NAME = stddb)
)
  )
7.加载监听配置
lsnrctl reload
8.验证监听神效
LSNRCTL> status
Service "PRIDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "auxdb" has 2 instance(s).
  Instance "auxdb", status UNKNOWN, has 1 handler(s) for this service...
  Instance "auxdb", status READY, has 1 handler(s) for this service...
Service "stddb" 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
9.建立到新备库连接串
   $ORACLE_HOME/network/admin/tnsnames.ora

stddb =
  (DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
(CONNECT_DATA =
   (SERVICE_NAME = stddb)
)
  )
10.测试联通性
SQL> conn sys/oracle_4U@stddb as sysdba
Connected.

2.3 开始复制
rman target sys/oracle_4U@pridb auxiliary sys/oracle_4U@stddb
rman > duplicate target database for standby NOFILENAMECHECK from active database;

2.4 备库处于恢复模式
recover managed standby database disconnect from session;
导此，两个备库已经建立连接

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)