DG实验

最新推荐文章于 2021-07-31 11:15:03 发布

cindy-berry

最新推荐文章于 2021-07-31 11:15:03 发布

阅读量801

点赞数

分类专栏： DG

本文链接：https://blog.csdn.net/u012366626/article/details/39941451

版权

DG 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

第一步：首先确认主库是否开归档

[oracle@cindy ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 9 14:55:16 2014

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> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/arch_log
Oldest online log sequence     12
Next log sequence to archive   14
Current log sequence           14
SQL>

第二步：确认有没有安装data guard

SQL> select * from v$option where parameter = 'Oracle Data Guard';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Oracle Data Guard
TRUE

第三步：确认数据库是否是force logging模式

SQL> select name, force_logging from v$database;

NAME      FOR
--------- ---
ORA11G    YES

第四步：接下来需要修改一些参数，当primary数据库添加或删除数据文件的时候，这些文件在standby数据库也会被添加或删除

SQL> alter system set standby_file_management='AUTO';

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

第五步：确认primary数据库的备用日志文件的组和大小，以便添加standby数据库的日志文件大小和组（组的公式：（每线程的日志组数+1）*最大线程数）

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         3         ONLINE
/u02/oradata/ora11g/redo03.log
NO

         2         ONLINE
/u02/oradata/ora11g/redo02.log
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---

         1         ONLINE
/u02/oradata/ora11g/redo01.log
NO


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME
---------------- ------------- ------------ ------------ ------------
         1          1         13   52428800        512          1 YES
INACTIVE               1848846 05-OCT-14         1875325 09-OCT-14

         2          1         14   52428800        512          1 NO
CURRENT                1875325 09-OCT-14      2.8147E+14

         3          1         12   52428800        512          1 YES
INACTIVE               1848099 05-OCT-14         1848846 05-OCT-14


SQL> select 52428800/1024/1024 from dual;

52428800/1024/1024
------------------
                50

添加standby的redologs

alter database add standby logfile group 11('/u02/oradata/ora11g/standbylog01.log') size 50m;
alter database add standby logfile group 12('/u02/oradata/ora11g/standbylog02.log') size 50m;
alter database add standby logfile group 13('/u02/oradata/ora11g/standbylog03.log') size 50m;
alter database add standby logfile group 14('/u02/oradata/ora11g/standbylog04.log') size 50m;

第六步：修改远程连接密码参数

SQL> show parameter remote_login_passwordfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

若不是exclusive则进行修改

alter system set remote_login_passwordfile=exclusive scope=spfile;

查看密码文件是否存在

SQL> host
[oracle@cindy ~]$ cd $ORACLE_HOME/dbs
[oracle@cindy dbs]$ ll
total 9712
-rw-rw---- 1 oracle oinstall    1544 Oct  9 14:53 hc_ora11g.dat
-rw-r--r-- 1 oracle oinstall    2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinstall      24 Aug 25 00:24 lkORA11G
-rw-r----- 1 oracle oinstall 9912320 Sep  3 00:33 snapcf_ora11g.f
-rw-r----- 1 oracle oinstall    3584 Oct  9 15:00 spfileora11g.ora

没有的话，就创建，格式是：orapwd file=$ORACLE_HOME/dbs/orapw<SID> password= entries=5

 orapwd file=$ORACLE_HOME/dbs/orapwORA11G password=system entries=5

tip：如果忘记sid，则通过语句select name from v$database; 查到

若忘记system密码，则可以用sys dba身份进入，重新修改一个密码：ALTER USER SYSTEM IDENTIFIED BY system;

第七步：查看primary数据库的名字，主要是为了配置监听文件的时候使用

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ora11g

如果查不到，则用语句进行修改：

alter system set db_unique_name=（自己取一个名字） scope=spfile;

修改监听文件，添加静态注册，步骤如下

[oracle@cindy ~]$ cd $ORACLE_HOME/network/admin
[oracle@cindy admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@cindy admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/oracle/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/oracle/app/oracle
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ora11g)
      (ORACLE_HOME = /u01/oracle/app/oracle/product/11.2.0)
      (SID_NAME = ora11g)
    )
  )

~
"listener.ora" 21L, 561C written

然后重启监听

[oracle@cindy ~]$ lsnrctl stop

[oracle@cindy ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-OCT-2014 15:39:24

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /u01/oracle/app/oracle/product/11.2.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/oracle/app/oracle/product/11.2.0/network/admin/listener.ora
Log messages written to /u01/oracle/app/oracle/diag/tnslsnr/cindy/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.200)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.200)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                09-OCT-2014 15:39:26
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/app/oracle/product/11.2.0/network/admin/listener.ora
Listener Log File         /u01/oracle/app/oracle/diag/tnslsnr/cindy/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.200)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ora11g" has 1 instance(s).
  Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

第八步：然后对net server name进行配置，即修改tnsname.ora文件

[oracle@cindy ~]$ cd $ORACLE_HOME/network/admin
[oracle@cindy admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@cindy admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/app/oracle/product/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora11g)
    )
  )
CHJORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.203)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = chjora11g)
    )
  )

~
"tnsnames.ora" 21L, 524C written

第九步：一些其余参数的配置

（一）修改归档日志文件和重做日志文件的传输

alter system set log_archive_dest_1='location=/u02/arch_log valid_for=(all_logfiles, all_roles) db_unique_name=ora11g';
alter system set log_archive_dest_2='service=chjora11g lgwr async valid_for=(online_logfile, primary_role) db_unique_name=chjora11g';

（二）指定日志传输出现问题时，备库到哪里找缺失的日志（

另一个要设置的参数是 FAL_SERVER。这个参数指定当日志传输出现问题时，备库到哪里去找缺少的归档日志。它用在备库接收的到的重做日志间有缺口的时候。这种情况会发生在日志传输出现中断时，比如你需要对备库进行维护操作。在备库维护期间，没有日志传输过来，这时缺口就出现了。设置了这个参数，备库就会主动去寻找那些缺少的日志，并要求主库进行传输。

）

alter system set fal_server='chjora11g';
alter system set log_archive_config='dg_config=(ora11g,chjora11g)';

SQL> alter system set fal_server='chjora11g';

System altered.

SQL> alter system set log_archive_config='dg_config=(ora11g,chjora11g)';

System altered.

SQL> alter system set fal_server='chjora11g';

System altered.

SQL> alter system set log_archive_config='dg_config=(ora11g,chjora11g)';

System altered.

自此，主库的配置完成

第十步：然后在主库上做一些操作，留给备库使用

一、创建pfile

SQL> create pfile from spfile;

File created.

二、关闭主库

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

三、将主库的密码文件、参数文件、监听文件、tnsname文件、数据文件全部复制

四、将主库启动到Mount 状态，然后为备库创建控制文件

SQL> startup mount
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             318770480 bytes
Database Buffers           92274688 bytes
Redo Buffers                4272128 bytes
Database mounted.
SQL> ALTER  DATABASE  CREATE  STANDBY  CONTROLFILE  AS  '/u01/oracle/standby.ctl';

Database altered.

五、将刚刚创建的这个文件也给复制出来

六、把那些文件都传到备库，然后将创建的控制文件复制到数据文件里，然后将数据文件里原先的控制文件的名字复制一下，接着删除，将这个名字给刚刚传进来的控制文件替换上

七、在备库的/u01/oracle/app/oracle下建一个fast_recovery_area文件夹，然后在/u01/oracle/app/oracle/fast_recovery_area下建一个ora11g的文件夹，然后将那个控制文件复制到这个路径下，只不过改成2即可，即control02.ctl

--------------------------------------------------华丽的分割线----------------------------------------下面做备库的配置-------------------------

第一步：修改参数文件

[oracle@chj dbs]$ vi initora11g.ora
ora11g.__db_cache_size=92274688
ora11g.__java_pool_size=4194304
ora11g.__large_pool_size=4194304
ora11g.__oracle_base='/u01/oracle/app/oracle'#ORACLE_BASE set from environment
ora11g.__pga_aggregate_target=146800640
ora11g.__sga_target=272629760
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=159383552
ora11g.__streams_pool_size=4194304
*.audit_file_dest='/u01/oracle/app/oracle/admin/ora11g/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/oradata/ora11g/control01.ctl','/u01/oracle/app/oracle/fast_recovery_area/ora11g/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11g'
*.db_recovery_file_dest='/u01/oracle/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.fal_server='chjora11g'
*.log_archive_config='dg_config=(ora11g,chjora11g)'
*.log_archive_dest_1='location=/u02/arch_log'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='chjora11g'
*.log_archive_dest_1 = 'location=/u02/arch_log valid_for=(all_logfiles, all_roles) db_unique_name=chjora11g'
*.log_archive_dest_2 = 'service=ora11g async valid_for=(online_logfile,primary_role) db_unique_name=ora11g'
~
~
~
~
~
~
~
~
~
~
~
~
"initora11g.ora" 32L, 1381C written

第二步：创建审计目录

[oracle@chj dbs]$ cd $ORACLE_BASE
[oracle@chj oracle]$ mkdir -p admin/ora11g/adump

第三步：将参数文件应用一下

[oracle@chj oracle]$ exit
exit

SQL>  startup nomount pfile='$ORACLE_HOME/dbs/initora11g.ora'
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             318770480 bytes
Database Buffers           92274688 bytes
Redo Buffers                4272128 bytes
SQL> create spfile  from pfile;

File created.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.

第四步：修改监听文件

SQL> host
[oracle@chj ~]$ cd $ORACLE_HOME/network/admin
[oracle@chj admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@chj admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/oracle/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.203)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/oracle/app/oracle
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = chjora11g)
      (ORACLE_HOME = /u01/oracle/app/oracle/product/11.2.0)
      (SID_NAME = ora11g)
    )
  )

~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"listener.ora" 21L, 564C written

然后启动一下主库和备库的监听

第五步：在备库ping主库的实例名

[oracle@chj admin]$ tnsping ora11g

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 07-OCT-2014 19:07:37

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora11g)))
OK (0 msec)

在主库ping一下备库的实例名

[oracle@cindy ~]$ tnsping chjora11g

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 09-OCT-2014 17:47:38

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.203)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = chjora11g)))
OK (60 msec)

主库的数据库正常启动

[oracle@cindy ~]$ exit
exit

SQL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             318770480 bytes
Database Buffers           92274688 bytes
Redo Buffers                4272128 bytes
Database mounted.
Database opened.

第六步：备库的启动

[oracle@chj admin]$ exit
exit

SQL> startup nomount
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             318770480 bytes
Database Buffers           92274688 bytes
Redo Buffers                4272128 bytes
SQL> alter database mount standby database;

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.

当这些步骤做完的时候，遇到一些问题，首先一个是alert报密码文件不对即： ORA-16191

究其原因，第一种，是我一开始在primary数据库建的密码文件的SID可能大写了，应该小写

第二种，一旦出现这种错误之后，不要分别在primary数据库和standby数据库分别单独建密码文件，而是要在primary库建完之后，传到standby数据库

然后再重启主库，这样alert不再报错，而是出现如下记录：

Thu Oct 09 19:19:39 2014
db_recovery_file_dest_size of 4122 MB is 3.64% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thread 1 advanced to log sequence 25 (LGWR switch)
  Current log# 1 seq# 25 mem# 0: /u02/oradata/ora11g/redo01.log
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Archived Log entry 102 added for thread 1 sequence 24 ID 0xfd868021 dest 1:
Thu Oct 09 19:19:42 2014
Starting background process CJQ0
Thu Oct 09 19:19:42 2014
CJQ0 started with pid=29, OS id=7431

但是，我接着在primary库做修改之后，在standby数据库上查的时候，发现没有起效果，然后在一顿垂头顿足之后，意识到自己犯了一个严重的错误，就是在打开standby数据库的时候，直接startup了，而不是按照正规步骤（上面的第六步和第七步）

这里还要一个小插曲，因为我做的时候，漏了一步alter database open read only，而直接应用redo，报错

ORA-10456: cannot open standby database; media recovery session may be in

这个问题的解决方法可以看我的上一篇blog的笔记，主要就是，要在数据库read only之后应用redo日志

最终效果如下

主库：

备库：

cindy-berry

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
DG实验

首先确认主库是否开归档
复制链接

扫一扫

专栏目录

DG实验

“相关推荐”对你有帮助么？