ITPub博客

首页 > 数据库 > Oracle > data guard 的部署

data guard 的部署

原创 Oracle 作者:zhanghongni 时间:2019-06-18 23:28:06 0 删除 编辑

tnsname.ora

fal_client

fal_server


data guard 的部署


************************************************************

创建物理standby:

 

一、准备工作:

 

1、确保数据库处于归档模式

SQL> archive log list

Database log mode          No Archive Mode

Automatic archival         Disabled

Archive destination        USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     3

Current log sequence           5

SQL> show parameter recovery

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest            string  /u01/app/oracle/fast_recovery_

                         area

db_recovery_file_dest_size       big integer 4122M

recovery_parallelism             integer     0

SQL> alter system set db_recovery_file_dest_size=1G;

System altered.

SQL> show parameter recovery 

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest            string  /u01/app/oracle/fast_recovery_

                         area

db_recovery_file_dest_size       big integer 1G

recovery_parallelism             integer     0

SQL> 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount   

ORACLE instance started.

Total System Global Area  722366464 bytes

Fixed Size          2231872 bytes

Variable Size         436208064 bytes

Database Buffers      281018368 bytes

Redo Buffers            2908160 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.



2、并打开 Forced Logging 模式

SQL> select force_logging from v$database;

 

FOR

---

NO

SQL> alter database force logging;

 

Database altered.

   

2、创建密码文件(保持两个节点存在,否则集群切换到另一节点就会找不到密码文件)

orapwd file=$ORACLE_HOME/dbs/orapworc1 password=welcome1 force=y ignorecase=y


3、更改数据库的name(db_unique_name)


SQL> show parameter name  

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert             string

db_name                  string  orcl

db_unique_name               string  orcl

global_names                 boolean     FALSE

instance_name                string  orcl

lock_name_space              string

log_file_name_convert            string

processor_group_name             string

service_names                string  orcl

SQL> alter system set db_unique_name=orcl scope=spfile;

System altered.


4、更改standby_file_management

SQL> show parameter standby

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

standby_archive_dest             string  ?/dbs/arch

standby_file_management          string  MANUAL

SQL> alter system set standby_file_management=AUTO;

System altered.

SQL> show parameter standby  

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

standby_archive_dest             string  ?/dbs/arch

standby_file_management          string  AUTO


5、修改fal服务器端和客户端配置

SQL> alter system set fal_client=orcl;

System altered.

SQL> alter system set fal_server=orcldg;

System altered.

SQL> show parameter fal;

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

fal_client               string  ORCL

fal_server               string  ORCLDG


6、修改log_archive_config参数

SQL> show parameter log_archive_config

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

log_archive_config           string

SQL> alter system set log_archive_config='dg_config=(orcl,orcldg)';

System altered.

SQL> show parameter log_archive_config

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

log_archive_config           string  dg_config=(orcl,orcldg)


7、修改log_archive_dest_2参数

SQL> alter system set log_archive_dest_state_2='defer';

System altered.


SQL> show parameter log_archive_dest_2

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2           string

SQL> alter system set log_archive_dest_2='service=orcldg lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';

System altered.

SQL> show parameter log_archive_dest_2

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2           string  service=orcldg lgwr sync affir

                                     m valid_for=(online_logfiles,p

                                     rimary_role) db_unique_name=or

                                     cldg

SQL> 


9,进入rman进行备份

查看数据库的大小

select sum(bytes)/1024/1024/1024 from v$datafile;

备份控制文件

run {

backup database format '/home/oracle/rman_backup/FULL_%U.bak';

backup archivelog all format '/home/oracle/rman_backup/ARC_%U.bak';

backup format '/home/oracle/rman_backup/standby_%U.ctl' current controlfile for standby;

}


backup device type disk format '/rman_backup/standby_%U.ctl' current controlfile for standby;

恢复控制文件的方法

RMAN> restore standby controlfile from '/home/oracle/rman_backup/standby_0tpk53jq_1_1.ctl';      -----路径为控制文件备份片的路径



10,创建orcldg的pfile

SQL> create pfile='/home/oracle/orcldgpfile.ora' from spfile;

File created.

11、传输orcldg的pfile

[oracle@oracle1 ~]$ scp orcldgpfile.ora oracle@192.168.74.21:/home/oracle

oracle@192.168.74.21's password: 

orcldgpfile.ora      

12、修改参数文件(备库)

[oracle@oracle ~]$ vim orcldgpfile.ora 

[oracle@oracle ~]$ cat orcldgpfile.ora 

*.audit_file_dest='/u01/app/oracle/admin/orcldg/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/orcldg/control01.ctl','/u01/app/oracle/fast_recovery_area/orcldg/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=1073741824

*.db_unique_name='ORCLDG'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_client='ORCLDG'

*.fal_server='ORCL'

*.log_archive_config='dg_config=(orcldg,orcl)'

*.log_archive_dest_2='service=orcl lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl'

*.memory_target=725614592

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

[oracle@oracle ~]$ mkdir -p /u01/app/oracle/admin/orcldg/adump

[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/orcldg/

[oracle@oracle ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcldg/

13、修改注册文件

[oracle@oracle ~]$ cat /etc/oratab 

orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N

orcldg:/u01/app/oracle/product/11.2.0/dbhome_1:N


14、尝试启动数据库到nomount状态并创建spfile从pfile中

[oracle@oracle ~]$ . oraenv

ORACLE_SID = [oracle] ? orcldg

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 3 16:56:59 2017

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/orcldgpfile.ora';

ORACLE instance started.

Total System Global Area  722366464 bytes

Fixed Size          2231872 bytes

Variable Size         432013760 bytes

Database Buffers      285212672 bytes

Redo Buffers            2908160 bytes

SQL> create spfile from pfile='/home/oracle/orcldgpfile.ora';

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> 

15、启动数据库到nomount

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  722366464 bytes

Fixed Size          2231872 bytes

Variable Size         432013760 bytes

Database Buffers      285212672 bytes

Redo Buffers            2908160 bytes


16、修改convert参数

SQL> show parameter convert

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert             string

log_file_name_convert            string

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg' scope=spfile;

System altered.

重启实例convert 参数生效

SQL> show parameter convert

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert             string

log_file_name_convert            string

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  722366464 bytes

Fixed Size          2231872 bytes

Variable Size         432013760 bytes

Database Buffers      285212672 bytes

Redo Buffers            2908160 bytes

SQL> show parameter convert

NAME                     TYPE    VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert             string  /u01/app/oracle/oradata/orcl,

                         /u01/app/oracle/oradata/orcldg

log_file_name_convert            string  /u01/app/oracle/oradata/orcl,

                         /u01/app/oracle/oradata/orcldg

[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/orcldg

[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/orcldg


18.配置监听服务名

[oracle@oracle1 ~]$ tnsping orcldg-192.168.74.21

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 04-MAR-2017 17:37:00

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.74.21)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcldg)))

OK (10 msec)

[oracle@oracle1 ~]$ 

[oracle@oracle ~]$ tnsping orcl-192.168.74.22

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2017 17:36:51

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.74.22)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))

OK (0 msec)

配置静态监听

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcldg)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = orcldg)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = Oracle8)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = ORCL)

    )

  )

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))

  )

ADR_BASE_LISTENER = /u01/app/oracle

配置服务名()名称相同

[oracle@oracle admin]$ cat tnsnames.ora 

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.22)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

ORCLDG =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcldg)

    )

  )

[oracle@oracle1 admin]$ cat tnsnames.ora 

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.22)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

ORCLDG =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.21)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcldg)

    )

  )

[oracle@oracle admin]$ vim listener.ora 

[oracle@oracle admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2017 18:42:36

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))

The command completed successfully

[oracle@oracle admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2017 18:42:45

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                03-MAR-2017 18:20:25

Uptime                    0 days 0 hr. 22 min. 19 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))

Services Summary...

Service "Oracle8" has 1 instance(s).

  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...

Service "orcldg" has 2 instance(s).

  Instance "orcldg", status UNKNOWN, has 1 handler(s) for this service...

  Instance "orcldg", status BLOCKED, has 1 handler(s) for this service...

The command completed successfully

[oracle@oracle admin]$ rman target sys/oracle@orcl-192.168.74.22 auxiliary sys/oracle@orcldg nocatalog;

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Mar 3 18:42:56 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1464936028)

using target database control file instead of recovery catalog

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby from active database;

duplicate  target  database  for standby  from active database  nofilenamecheck;


restore standby controlfile from '/home/oracle/rman_backup/standby_05u0o7s4_1_1.ctl'

restore database;


配置redologfile

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

orcldg       MOUNTED

select member from v$logfile;           

SQL> select bytes/1024/1024 from v$log;

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcldg/redo04.log') size 50M;

Database altered.

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcldg/redo05.log') size 50M;     

Database altered.

SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcldg/redo06.log') size 50M;

Database altered.

SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcldg/redo07.log') size 50M;

Database altered.

alter database add standby logfile group 4 size 50M ,group 5 size 50M ,group 6 size 50M, group 7 size 50M;


alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;

alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;

alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;

alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M;


开启日志应用进程:

SQL> alter database recover managed standby database parallel 2 using current logfile disconnect;

Database altered.

开启同步开关配置

      SQL> alter system set log_archive_dest_state_2='enable';

      System altered.       

 查看数据库的主角色(保护级别,模式等)

SQL> 


DATABASE_ROLE    OPEN_MODE        PROTECTION_MODE      PROTECTION_LEVEL

---------------- -------------------- -------------------- --------------------

PHYSICAL STANDBY MOUNTED          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE


查看日志应用


SQL> select max(sequence#),applied from v$archived_log group by applied;

MAX(SEQUENCE#) APPLIED

-------------- ---------

        18 YES

SQL> select max(sequence#),applied from v$archived_log group by applied;

MAX(SEQUENCE#) APPLIED

-------------- ---------

        18 YES

SQL> /                  

MAX(SEQUENCE#) APPLIED

-------------- ---------

        19 IN-MEMORY

        18 YES

SQL> /

MAX(SEQUENCE#) APPLIED

-------------- ---------

        19 YES

打开数据库

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL>

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select max(sequence#),applied from v$archived_log group by applied;

MAX(SEQUENCE#) APPLIED

-------------- ---------

        20 YES

SQL> /

MAX(SEQUENCE#) APPLIED

-------------- ---------

        21 YES

SQL> /

MAX(SEQUENCE#) APPLIED

-------------- ---------

        21 YES

SQL> select * from t1;


查看数据库的状态

SQL> select name,open_mode,protection_level,database_role,switchover#,switchover_status from v$database;

NAME      OPEN_MODE        PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER# SWITCHOVER_STATUS

--------- -------------------- -------------------- ---------------- ----------- --------------------

ORCL      READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY  1464842362 NOT ALLOWED

以上active dataguard部署完毕



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



logcal 数据库(逻辑standby)



3.配置主备监听(必须保证双向监听正常,并且配置RAC集群两个节点)

   

4、设置初始化参数(primary和standby)

 

主库参数

    *.log_archive_config='dg_config=(kc,dg)'

    *.log_archive_dest_2='service=dg db_unique_name=dg lgwr async'

    *.log_archive_dest_state_2='ENABLE'

   

备库参数(config中应该是 net service name,而不是sid或service name)

    *.db_unique_name='dg'

    *.undo_management='AUTO'

    *.log_archive_config='dg_config=(kc,dg)'

    *.db_file_name_convert='+DATA01/kc/datafile','+DATA01/dg/datafile'

    *.log_file_name_convert='+DATA01/kc/onlinelog','DATA01/dg/onlinelog'

    *.standby_file_management=auto

    *.fal_client='kc'

    *.fal_server='kc'

   

 

二、详细步骤:

 

1、主库创建备份(手工复制数据文件或通过 RMAN)

 

2、主库创建控制文件

    通过下列语句为 standby 数据库创建控制文件

    SQL> alter database create standbycontrolfile as 'd:\backup\jsspdg01.ctl';

 

3、主库创建并修改初始化参数文件

    SQL> create pfile='d:\backup\initjsspdg.ora' from spfile;

    注意 primary 和 standby 不同角色的属性配置,注意文件路径。

 

4、复制文件到 standby 服务器

    数据文件,控制文件,修改过的初始化参数文件,注意路径。

   

5、配置 standby 数据库

 

6、启动 standby

    SQL> STARTUP MOUNT;

    需要针对每个thread创建3个standby日志组

    SQL> alter database add standby logfile thread 1

    group 5 ('+DATA01/dg/onlinelog/st_1_5.log')size 50M,

    group 6 ('+DATA01/dg/onlinelog/st_1_6.log')size 50M,

    group 7 ('+DATA01/dg/onlinelog/st_1_7.log')size 50M;

 

    SQL> alter database add standby logfile thread 2

    group 8 ('+DATA01/dg/onlinelog/st_2_8.log')size 50M,

    group 9 ('+DATA01/dg/onlinelog/st_2_9.log')size 50M,

    group 10 ('+DATA01/dg/onlinelog/st_2_10.log')size 50M;

    启动 redo 应用

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

    启动实时应用

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

   

7、停止 standby

    正常情况下,我们停止也应该是先停止 redo 应用,可以通过下列语句:

    SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

    然后再停止 standby 数据库

    SQL> SHUTDOWN IMMEDIATE;

   

8.常用状态查询:

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

    select process,status from v$managed_standby;

    show parameter instance_name;

    select max(sequence#) from v$archived_log;

   

9.准备切换:

    select switchover_status from v$database;

   

    alter database commit to switchover to physical standby;

   

 

    


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31549397/viewspace-2648046/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2018-08-09

  • 博文量
    3
  • 访问量
    1185