oracle 12cR1 datagurad搭建文档

最新推荐文章于 2024-05-28 11:55:30 发布

清霄

最新推荐文章于 2024-05-28 11:55:30 发布

阅读量1k

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/cwg_1992/article/details/13296819

版权

Oracle 专栏收录该内容

57 篇文章 0 订阅

订阅专栏

1.环境

主库环境：

系统：Solaris11_AMD64

ORA：12cR1

IP：188.188.1.176

SID：TESTM

备库环境：

系统：CentOS6.4_AMD64

ORA：12cR1

IP：188.188.3.20

SID：TESTM

2.主库配置

启用强制归档和查看归档状态

SQL> alter database force logging;

数据库已更改。

SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用                         --归档启用了，此处不再多述归档
存档终点            /oracle/archive_log          --归档路径
最早的联机日志序列     124
下一个存档日志序列   126
当前日志序列           126

3.创建备库日志组

查看主库现有的日志组：

SQL> select GROUP#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS      BYTES ARCHIVED  STATUS
---------- ---------- ---------- ---------- --------- ------------------------------------------------
         1        124          1   52428800 YES       INACTIVE
         2        125          1   52428800 YES       INACTIVE
         3        126          1   52428800 NO        CURRENT

日志组别和现有的重复

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/oracle/dataguard/redo01_4.log'，'/oracle/dataguard/redo02_4.log') size 50M;

数据库已更改。

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oracle/dataguard/redo01_5.log'，'/oracle/dataguard/redo02_5.log') size 50M;

数据库已更改。

4.配置Oracle Net

主库listener.ora配置如下

oracle@Sol_ORA:~$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /oracle/12.1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =(SID_LIST=(SID_DESC=(SID_NAME=TESTM)(ORACLE_HOME=/oracle/12.1)))       --添加这一条，静态注册监听
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.1.176)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

主库tnsnames.ora配置如下

oracle@Sol_ORA:~$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/12.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEST_M =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.1.176)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTM)
    )
  )
TEST_S =                       //加上备库的链接字符串
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.3.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTM)
    )
  )

LISTENER_TESTM =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.1.176)(PORT = 1521))

备库的listener.ora配置如下

[oracle@ora_slave admin]$ cat $ORACLE_HOME/network/admin/listener.ora
#SID_LIST_LISTENER =
#  (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) )
#  (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = orcl) )
#  )
SID_LIST_LISTENER =(SID_LIST=(SID_DESC=(SID_NAME=TESTM)(ORACLE_HOME=/oracle/product/12.1.0)))
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.3.20)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

备库tnsnames.ora配置如下

[oracle@ora_slave admin]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/12.1.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEST_M =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.1.176)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTM)
    )
  )
TEST_S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.3.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTM)
    )
  )
LISTENER_TESTM =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 188.188.3.20)(PORT = 1521))

5.创建密码文件和控制文件（在主库）

SQL> alter database create standby controlfile as '/oracle/dataguard/ctlfile/testM.ctl'; --创建备库的控制文件 由这个控制文件区分主备关系

数据库已更改。

SQL> !
oracle@Sol_ORA:~$ ls $ORACLE_HOME/dbs/orapw*
/oracle/12.1/dbs/orapwDBUA3028740  /oracle/12.1/dbs/orapwTEST          --存在就不用创建了，如果不存在需要创建之

创建命令：

orapwd file=$OEACLE_HOME/dbs/orapw${ORACLE_SID} password=admin

6.创建和修改初始化参数文件

创建主库初始化参数文件

SQL> create pfile='/oracle/dataguard/pfile/testm.ora' from spfile;

文件已创建。

SQL> hosts cp /oracle/dataguard/pfile/testm.ora /oracle/dataguard/pfile/tests.ora

修改

oracle@Sol_ORA:/tmp$ cat /oracle/dataguard/pfile/testm.ora

###########################下边新加内容#############################
DB_NAME ='TESTM';          //主库数据库名
DB_UNIQUE_NAME='TEST_M'    //主库唯一标识
LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST_M,TEST_S)'  
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/archive_log VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TEST_M'
LOG_ARCHIVE_DEST_2='service=TEST_S DB_UNIQUE_NAME=TEST_S'
LOG_ARCHIVE_DEST_STATE_1=ENABLE 
LOG_ARCHIVE_DEST_STATE_2=ENABLE 
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
############################上边新加内容#############################
TESTM.__data_transfer_cache_size=0
TESTM.__db_cache_size=1778384896
TESTM.__java_pool_size=33554432
TESTM.__large_pool_size=83886080
TESTM.__oracle_base='/oracle'#ORACLE_BASE set from environment
TESTM.__pga_aggregate_target=855638016
TESTM.__sga_target=2550136832
TESTM.__shared_io_pool_size=134217728
TESTM.__shared_pool_size=486539264
TESTM.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/TESTM/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/oracle/oradata/TESTM/control01.ctl','/oracle/fast_recovery_area/TESTM/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='TESTM'
*.db_recovery_file_dest='/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4815m
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTMXDB)'
*.local_listener='LISTENER_TESTM'
*.log_archive_dest_1='location=/oracle/archive_log'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=810m
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_target=2430m
*.undo_tablespace='UNDOTBS1'

7.用修改完的初始化参数启动主库实例并创建spfile

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile='/oracle/dataguard/pfile/testm.ora'
ORACLE 例程已经启动。

Total System Global Area 2538741760 bytes
Fixed Size                  2757504 bytes
Variable Size            1124076672 bytes
Database Buffers         1392508928 bytes
Redo Buffers               19398656 bytes
数据库装载完毕。
数据库已经打开。

SQL> create spfile from pfile='/oracle/dataguard/pfile/testm.ora';

文件已创建。

8.配置备库初始化文件

配置备库pfile

[oracle@ora_slave pfile]$ more tests.ora 
#############################如下添加###################################
DB_NAME=TESTS
DB_UNIQUE_NAME=TEST_S
LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST_M,TEST_S)'
CONTROL_FILES='/oracle/oradata/TESTM/control01.ctl', '/oracle/oradata/TESTM/control02.ctl'
DB_FILE_NAME_CONVERT='TESTM','TESTS'
LOG_FILE_NAME_CONVERT='/oracle/archive_log/','/oracle/dataguard/archivelog/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TEST_S'
LOG_ARCHIVE_DEST_2='SERVICE=TEST_M ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_M'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=TEST_M
###############################如上添加##################################
TESTS.__data_transfer_cache_size=0
TESTS.__db_cache_size=218103808
TESTS.__java_pool_size=4194304
TESTS.__large_pool_size=8388608
TESTS.__oracle_base='/oracle'#ORACLE_BASE set from environment
TESTS.__pga_aggregate_target=260046848
TESTS.__sga_target=494927872
TESTS.__shared_io_pool_size=12582912
TESTS.__shared_pool_size=243269632
TESTS.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/TESTS/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
#*.control_files='/oracle/oradata/TESTS/control01.ctl','/oracle/fast_recovery_area/TESTS/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='TESTS'
*.db_recovery_file_dest='/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4815m
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTSXDB)'
*.memory_target=720m
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=450
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=500
*.undo_tablespace='UNDOTBS1'

9.准备和拷贝备库的数据文件和控制文件

因为我们要用主库的控制文件，控制文件中的数据文件的位置是一定的，所以我们需要在备库中创建主库的文件路径，并吧数据拷贝过去

1.shutdown主库，拷贝数据文件，down了拷贝就不许要recover介质了

主库
在备库创建相应的数据目录
[oracle@ora_slave dataguard]$ mkdir /oracle/oradata/TESTM

在主库执行如下命令，拷贝控制文件和数据文件包括归档  如果没有目录直接创建之
oracle@Sol_ORA:/tmp$ cd /oracle/oradata/TESTM/
oracle@Sol_ORA:/oracle/oradata/TESTM$ ls
control01.ctl  redo02.log     sysaux01.dbf   temp01.dbf     users01.dbf
redo01.log     redo03.log     system01.dbf   undotbs01.dbf
oracle@Sol_ORA:/oracle/oradata/TESTM$ scp * oracle@188.188.3.20:/oracle/oradata/TESTM
oracle@188.188.3.20's password: 
control01.ctl        100% |***********************************************************************************|  9808 KB    00:00    
redo01.log           100% |***********************************************************************************| 51200 KB    00:02    
redo02.log           100% |***********************************************************************************| 51200 KB    00:01    
redo03.log           100% |***********************************************************************************| 51200 KB    00:02    
sysaux01.dbf         100% |***********************************************************************************|   880 MB    00:51    
system01.dbf         100% |***********************************************************************************|   780 MB    00:41    
temp01.dbf           100% |***********************************************************************************| 68616 KB    00:03    
undotbs01.dbf        100% |***********************************************************************************|  1070 MB    00:53    
users01.dbf          100% |***********************************************************************************|  5128 KB    00:00
oracle@Sol_ORA:/oracle/oradata/TESTM$ scp $ORACLE_HOME/dbs/orapwTESTM  oracle@188.188.3.20:/oracle/product/12.1.0/dbs/
oracle@188.188.3.20's password: 
orapwTESTM           100% |***********************************************************************************|  7680       00:00 
oracle@Sol_ORA:/oracle/oradata/TESTM$ scp /oracle/dataguard/ctlfile/testM.ctl  oracle@188.188.3.20:/oracle/dataguard/ctlfile/
oracle@188.188.3.20's password: 
testM.crl            100% |***********************************************************************************|  7680       00:00 


在备库
参考参数文件中的路径和文件必须存在且放到所位置上，如果没有的目录创建之。
按参数文件中的控制文件指向将/oracle/dataguard/ctlfile/testM.ctl分别拷贝到指定的位置改名为指定的名称

2.在备库启动实例

[oracle@ora_slave pfile]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on 星期二 10月 29 22:49:38 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup nomount pfile='/oracle/dataguard/pfile/oraTESTS.ora'
ORACLE 例程已经启动。

Total System Global Area  751595520 bytes
Fixed Size		    2292912 bytes
Variable Size		  595592016 bytes
Database Buffers	  150994944 bytes
Redo Buffers		    2715648 bytes

SQL> alter database mount standby database ; 

数据库已更改。

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

数据库已更改。

10.重新启动主库

主库启动

SQL> startup  
ORACLE 例程已经启动。

Total System Global Area  751595520 bytes
Fixed Size            2292912 bytes
Variable Size          595592016 bytes
Database Buffers      150994944 bytes
Redo Buffers            2715648 bytes
数据库装载完毕。
数据库已经打开。
SQL> SELECT STATUS, ERROR FROM V$ARCHIVE_DEST;

STATUS                      ERROR   --这个错误列不出错正常 如果出错解决相应的错误即可
--------------------------- -----------------------------------------------------------------
VALID
VALID
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE

已选择 31 行。

备库查看状态

SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';

CLIENT_PROCESS		 PROCESS			THREAD#  SEQUENCE# STATUS
------------------------ --------------------------- ---------- ---------- ------------------------------------
N/A			 MRP0				      1 	18 WAIT_FOR_LOG   --正在等待主库的log  如果是应用日志此处应该是APPLYING_LOG
LGWR			 RFS				      1 	18 IDLE
SQL> SELECT STATUS, ERROR FROM V$ARCHIVE_DEST;

STATUS                ERROR        --此处的状态和主库一样如果有错误则解决之
--------------------------- -----------------------------------------------------------------
VALID
VALID
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
VALID

已选择 32 行。

11.验证

主库

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            25

备库

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    25

主库

SQL> alter system switch logfile;

系统已更改。

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            26

备库

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    26

就说明归档日志已经同步了！

12.错误汇总

SQL> SELECT STATUS, ERROR FROM V$ARCHIVE_DEST;     --主库查询

STATUS                      ERROR
--------------------------- -----------------------------------------------------------------
VALID
ERROR                       ORA-01033: ORACLE 正在初始化或关闭         --没有启动备库实例

SQL> SELECT STATUS, ERROR FROM V$ARCHIVE_DEST;     --主库查询

STATUS                      ERROR
--------------------------- -----------------------------------------------------------------
VALID
ERROR                       ORA-16191: 主日志传送客户机没有登录到备用数据库      --备库的密码文件必须是备库的orapw${ORACLE_SID}命名

################

本文由笔者原创

作者：john

转载请注明出处

清霄

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 12cR1 datagurad搭建文档

1.环境主库环境：系统：Solaris11_AMD64ORA：12cR1IP：188.188.1.176SID：TESTM备库环境：系统：CentOS6.4_AMD64ORA：12cR1IP：188.188.3.20SID：TESTM2.主库配置启用强制归档和查看归档状态SQL> alter database force loggin
复制链接

扫一扫

专栏目录