11GR2下创建dataguard

浏览： ℃

字体：大中小

发布时间：2013-12-12 14:51:03

来源：

环境:

OS:Linux AS 5

DB:11.2.0.1

下面介绍如何在11GR2下创建Dataguard.

--------------------------------------主库上的操作---------------------------------------------------

1.设置主库为force logging模式

SQL> alter database force logging;
Database altered.

2.编辑初始化参数
SQL> create pfile='/u01/export/home/oracle/pfile.txt' from spfile;
File created.

vi pfile.txt 添加红色部分的内容

[oracle@primary ~]$ more pfile.txt
oracl.__db_cache_size=272629760
oracl.__java_pool_size=4194304
oracl.__large_pool_size=4194304
oracl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oracl.__pga_aggregate_target=276824064
oracl.__sga_target=415236096
oracl.__shared_io_pool_size=0
oracl.__shared_pool_size=125829120
oracl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/oracl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/oracl/control01.ctl','/u02/app/oracle/oradata/oracl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='oracl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraclXDB)'
*.memory_target=692060160
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

*.db_unique_name=oracl
*.log_archive_config='dg_config=(oracl,oraclbak)'
*.log_archive_dest_1=
'location=/u01/archive_log/
valid_for=(all_logfiles,all_roles)
db_unique_name=oracl'
*.log_archive_dest_2=
'service=dup_oracl async
valid_for=(online_logfiles,primary_role)
db_unique_name=oraclbak'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management=auto
*.fal_server=dup_oracl
*.fal_client=tar_oracl
*.db_file_name_convert='/u01/app/oracle/oradata/oraclbak/','/u01/app/oracle/oradata/oracl/'
*.log_file_name_convert='/u01/app/oracle/oradata/oraclbak/','/u01/app/oracle/oradata/oracl/'

3.使用步骤2修改的参数启动主库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='/u01/export/home/oracle/pfile.txt';
File created.

SQL> startup
ORACLE instance started.
Total System Global Area 690503680 bytes
Fixed Size 1338756 bytes
Variable Size 411042428 bytes
Database Buffers 272629760 bytes
Redo Buffers 5492736 bytes
Database mounted.
Database opened.

4.添加standby日志文件,大小跟v$log中的日志文件大小保持一致,这里的日志组从4开始,因为主库已经创建了3组online日志组了.

alter database add standby logfile group 4 ('/u02/app/oracle/oradata/oracl/stdbyredo01.log') SIZE 512m;
alter database add standby logfile group 5 ('/u02/app/oracle/oradata/oracl/stdbyredo02.log') SIZE 512m;
alter database add standby logfile group 6 ('/u02/app/oracle/oradata/oracl/stdbyredo03.log') SIZE 512m;
alter database add standby logfile group 7 ('/u02/app/oracle/oradata/oracl/stdbyredo04.log') SIZE 512m;

5.创建standby控制文件

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

Total System Global Area 690503680 bytes
Fixed Size 1338756 bytes
Variable Size 411042428 bytes
Database Buffers 272629760 bytes
Redo Buffers 5492736 bytes
Database mounted.
SQL> alter database create standby controlfile AS '/u01/export/home/oracle/standby.ctl';

Database altered.

SQL> alter database open;

Database altered.

6.配置tnsnames文件

使用netca配置tnsnames,文件内容如下:
[oracle@primary admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/n
etwork/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TAR_ORACL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oracl)
)
)

DUP_ORACL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oraclbak)
)
)

7.查看数据文件和日志文件的目录,在备库上我们需要创建oraclbak目录,如下的文件需要放在备库实例的oraclbak目录下.
SQL> select name from v$datafile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/oracl/system01.dbf
/u01/app/oracle/oradata/oracl/sysaux01.dbf
/u01/app/oracle/oradata/oracl/undotbs01.dbf
/u01/app/oracle/oradata/oracl/users01.dbf
/u01/app/oracle/oradata/oracl/hxl001.dbf
/u01/app/oracle/oradata/oracl/hxl002.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/oracl/redo0301.log
/u01/app/oracle/oradata/oracl/redo0302.log
/u01/app/oracle/oradata/oracl/redo0201.log
/u01/app/oracle/oradata/oracl/redo0202.log
/u01/app/oracle/oradata/oracl/redo0101.log
/u01/app/oracle/oradata/oracl/redo0102.log
/u01/app/oracle/oradata/oracl/stdbyredo01.log
/u01/app/oracle/oradata/oracl/stdbyredo02.log
/u01/app/oracle/oradata/oracl/stdbyredo03.log
/u01/app/oracle/oradata/oracl/stdbyredo04.log

SQL>select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/oracl/control01.ctl
/u01/app/oracle/oradata/oracl/control02.ctl

8.shutdown数据库
将数据文件,日志文件(online日志文件和standby日志文件),standby控制文件(/u01/export/home/oracle/standby.ctl),tnsnames文件,密码文件拷贝到备库的相应目录.

先将需要传输的文件放到指定位置

tar -cvf /u01/soft/oradata.tar ./oradata

cp orapworacl /u01/soft/

cp tnsnames.ora /u01/soft/

cp standby.ctl /u01/soft/

cp pfile.txt /u01/soft/

使用scp将如下文件传输到备库的指定目录

scp ./oradata.tar root@192.168.50.192:/u01/soft/

scp ./orapworacl root@192.168.50.192:/u01/soft/

scp ./tnsnames.ora root@192.168.50.192:/u01/soft/

scp ./standby.ctl root@192.168.50.192:/u01/soft/

scp ./pfile.txt root@192.168.50.192:/u01/soft/

这个时候可以先不启动数据库,等整个过程完成后再启动数据库.

--------------------------------------------备库上的操作--------------------------------------------------

1.创建备库实例对应的目录
mkdir -p /u01/app/oracle/oradata/oraclbak

2.将主库上传过来的相应文件存放到备库的相应目录,除了控制文件需要替换为standby控制文件外,其他的文件不需要做任何改动.这里需要将standby.ctl替换掉主库拷贝过来的control01.ctl,control02.ctl

[oracle@stdby oraclbak]$ cp standby.ctl control01.ctl
[oracle@stdby oraclbak]$ cp standby.ctl control02.ctl

3.配置监听和tnsnames(监听器需要重新配置,tnsnames可以使用从主库拷贝过来的)

使用netca配置监听和tnsnames,tnsnames的内容如下:
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/n
etwork/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TAR_ORACL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oracl)
)
)

DUP_ORACL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oraclbak)
)
)

4.启动备库的监听

[oracle@stdby oraclbak]$ lsnrctl start

5.修改从主库传过来的参数文件
修改后的内容如下,注意红色部分.
[oracle@stdby ftp]$ more pfile.txt
oraclbak.__db_cache_size=272629760
oraclbak.__java_pool_size=4194304
oraclbak.__large_pool_size=4194304
oraclbak.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oraclbak.__pga_aggregate_target=276824064
oraclbak.__sga_target=415236096
oraclbak.__shared_io_pool_size=0
oraclbak.__shared_pool_size=125829120
oraclbak.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/oraclbak/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/oraclbak/control01.ctl','/u02/app/oracle/oradata/oraclbak/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='oracl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraclXDB)'
*.memory_target=692060160
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

*.db_unique_name=oraclbak
*.log_archive_config='dg_config=(oracl,oraclbak)'
*.log_archive_dest_1=
'location=/u01/archive_log/
valid_for=(all_logfiles,all_roles)
db_unique_name=oraclbak'
*.log_archive_dest_2=
'service=tar_oracl async
valid_for=(online_logfiles,primary_role)
db_unique_name=oracl'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management=auto
*.fal_server=tar_oracl
*.fal_client=dup_oracl
*.db_file_name_convert='/u01/app/oracle/oradata/oracl/','/u01/app/oracle/oradata/oraclbak/'
*.log_file_name_convert='/u01/app/oracle/oradata/oracl/','/u01/app/oracle/oradata/oraclbak/'

创建参数文件中定义的目录(仔细检查每个目录,没有创建的话需要创建)
mkdir –p /u01/archive_log/
mkdir –p /u01/app/oracle/admin/oraclbak/adump

6.创建密码文件,创建自己的密码文件好像有问题,备库的密码文件需要跟主库一致,否则导致日志传输不到备库,有待验证.我最后是将主库的日志直接copy到备库,重命名后使用.

cp orapworacl $ORACLE_HOME/dbs

mv orapworacl orapworaclbak

7.确保备库的环境变量ORACLE_SID=oraclbak后,使用步骤5改好的pfile创建spfile

[oracle@stdby archive_log]$ echo $ORACLE_SID
oraclbak

SQL> connect / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile='/u01/ftp/pfile.txt';
File created.

8.mount数据库

SQL> startup mount
ORACLE instance started.
Total System Global Area 690503680 bytes
Fixed Size 1338756 bytes
Variable Size 411042428 bytes
Database Buffers 272629760 bytes
Redo Buffers 5492736 bytes
Database mounted.

9.修改数据文件和日志文件的路径,因为备份控制文件中记录的数据文件和日志文件是在主库实例oracl目录下的,转移到备库后这些文件是存放在备库实例oraclbak目录下,所以需要修改数据文件的路径.

9.1设置standby_file_management=manual

9.2 启动数据库到mount状态,修改文件路径

alter database rename file '/u01/app/oracle/oradata/oracl/sysaux01.dbf' to '/u01/app/oracle/oradata/oraclbak/sysaux01.dbf';

alter database rename file '/u01/app/oracle/oradata/oracl/system01.dbf' to '/u01/app/oracle/oradata/oraclbak/system01.dbf';

alter database rename file '/u01/app/oracle/oradata/oracl/temp01.dbf' to '/u01/app/oracle/oradata/oraclbak/temp01.dbf';

alter database rename file '/u01/app/oracle/oradata/oracl/undotbs01.dbf' to '/u01/app/oracle/oradata/oraclbak/undotbs01.dbf';

alter database rename file '/u01/app/oracle/oradata/oracl/users01.dbf' to '/u01/app/oracle/oradata/oraclbak/users01.dbf';

alter database rename file '/u01/app/oracle/oradata/oracl/redo01.log' to '/u01/app/oracle/oradata/oraclbak/redo01.log';

alter database rename file '/u01/app/oracle/oradata/oracl/redo02.log' to '/u01/app/oracle/oradata/oraclbak/redo02.log';

alter database rename file '/u01/app/oracle/oradata/oracl/redo03.log' to '/u01/app/oracle/oradata/oraclbak/redo03.log';

alter database rename file '/u01/app/oracle/oradata/oracl/stdbyredo01.log' to '/u01/app/oracle/oradata/oraclbak/stdbyredo01.log';

alter database rename file '/u01/app/oracle/oradata/oracl/stdbyredo02.log' to '/u01/app/oracle/oradata/oraclbak/stdbyredo02.log';

alter database rename file '/u01/app/oracle/oradata/oracl/stdbyredo03.log' to '/u01/app/oracle/oradata/oraclbak/stdbyredo03.log';

alter database rename file '/u01/app/oracle/oradata/oracl/stdbyredo04.log' to '/u01/app/oracle/oradata/oraclbak/stdbyredo04.log';

9.3设置standby_file_management=auto

10.应用日志
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

11G以后备库可以在打开的情况下同时应用归档日志.

alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;

这个时候可以启动主库了,然后验证备库上日志的应用情况:

Select Sequence#, Name, Applied From V$archived_Log Order By Sequence#;

SEQUENCE# NAME APPLIED

10 /u01/archive_log/1_10_833405832.dbf YES

11 /u01/archive_log/1_11_833405832.dbf YES

12 /u01/archive_log/1_12_833405832.dbf YES

13 /u01/archive_log/1_13_833405832.dbf YES

14 /u01/archive_log/1_14_833405832.dbf YES

15 /u01/archive_log/1_15_833405832.dbf YES

16 /u01/archive_log/1_16_833405832.dbf YES

17 /u01/archive_log/1_17_833405832.dbf YES

18 /u01/archive_log/1_18_833405832.dbf YES