30分钟创建一个Oracle11g DataGuard物理备库的步骤

最新推荐文章于 2019-06-19 18:36:12 发布

weixin_33994429

最新推荐文章于 2019-06-19 18:36:12 发布

阅读量191

点赞数

文章标签：网络 python 数据库

原文链接：https://my.oschina.net/rootliu/blog/1559964

版权

2019独角兽企业重金招聘Python工程师标准>>>

30分钟创建一个Oracle11g DataGuard物理备库的步骤:

主库主机名：prmy
备库主机名：sby1
主库ip:192.168.1.1
备库ip:192.168.1.2
主库dbname:prmy
备库dbname:sby1
主库db_unique_name=prmy
备库db_unique_name=sby1
主库listener.ora:prmy
备库listener.ora:sby1
主库备库tnsnames.ora:prmy,sby1

概述：

1.准备主库

2.设置物理备库上的参数

3.配置oracle 网络服务

4.启动备份实例

5.执行RMAN 命令：DUPLICATE TARGET DATABASE FOR

STANDBY FROM ACTIVE DATABASE

6.开启redo的传输和应用

准备主库：

在数据库层面启用FORCE LOGGING
如果需要的话创建一个密码文件
创建备份日志组（standby redo log）
设置初始化参数

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
alter database force logging;
select log_mode,force_logging from v$database;

scp orapwprmy oracle@192.168.76.134:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwsby1

ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/prmy/srl01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/prmy/srl02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/prmy/srl03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/prmy/srl04.log') SIZE 50M;

select group#,type,member from v$logfile where type='STANDBY';
select group#,dbid,thread#,sequence#,status from v$standby_log;

（1）alter system set log_archive_config='dg_config=(prmy,sby1)';

show parameter log_ARCHIVE_CONFIG;

select * from v$dataguard_config;

（2）ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prmy'；
show parameter log_archive_dest_1;

（3）ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=sby1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sby1';

show parameter LOG_ARCHIVE_DEST_2;

show parameter log_archive_dest_state_2

（4）alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

show parameter log_archive_format;

（5）alter system set log_archive_max_processes=10;

show parameter log_archive_max_processes;

show parameter remote_login_passwordfile;

（6）alter system set fal_server=sby1;

show parameter fal_server

（7）alter system set standby_file_management=auto;

show parameter standby_file_management

（8）alter system set db_file_name_convert='/u01/app/oracle/oradata/sby1/','/u01/app/oracle/oradata/prmy/' scope=spfile;

（9）alter system set log_file_name_convert='/u01/app/oracle/oradata/sby1/','/u01/app/oracle/oradata/prmy/'scope=spfile;

为物理备库创建一个参数文件

cat dg.ora
DB_NAME=prmy

DB_UNIQUE_NAME=sby1

DB_BLOCK_SIZE=8192

为物理备库创建目录

mkdir -p /u01/app/oracle/oradata/sby1

mkdir -p /u01/app/oracle/fast_recovery_area/sby1

mkdir -p /u01/app/oracle/fast_recovery_area/SBY1

mkdir -p /u01/app/oracle/admin/sby1/adump

启动物理备库

export ORACLE_SID=sby1

sqlplus /nolog
connect /as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/dg.ora';

select instance_name,host_name from v$instance;
select name from v$database;

为主库及物理备库创建网络服务名
prmy:
cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prmy)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = prmy)
)
)

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

ADR_BASE_LISTENER = /u01/app/oracle

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.

PRMY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prmy)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prmy)
)
)

SBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sby1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sby1)
)
)

sby1:

cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sby1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = sby1)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sby1)(PORT = 1521))
)

ADR_BASE_LISTENER = /u01/app/oracle

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.

PRMY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prmy)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prmy)
)
)

SBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sby1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sby1)
)
)

tnsping prmy
tnsping sby1

使用RMAN脚本来创建物理备库

cat du.rmn
run{
allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'prmy','sby1'