data guard 的部署

发布时间：2020-08-10 23:58:29 来源：ITPUB博客阅读：182 作者：zhanghongni 栏目：关系型数据库

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

---

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

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

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

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

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

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

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

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;

向AI问一下细节

data guard 的部署

猜你喜欢

最新资讯

相关推荐

相关标签