linux oracle 10g dataguard 实施详细记录-白红宇

一、备份

1、数据备份（主库上执行）

rman>backup full database format '/u01/rmanbak/port_full_backup%U.bk';

2、数据库软件备份（主库上执行）

$ cd /home/oracle/product/10.2.0/

$ tar cvf /u01/oraclehomebak/product.tar db_1

3、数据库参数备份（主库上执行）

sqlplus "/as sysdba"

sql>create pfile='/home/oracle/initport1.ora' from spfile;

二、主库实施（阶段一）

1、参数修改

ALTER DATABASE FORCE LOGGING;

ALTER SYSTEM SET DB_UNIQUE_NAME=port scope=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(port,standby)' scope=both;

--主库归档目的地

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=port' scope=both;

--当该库充当主库角色时，设置物理备库redo data的传输目的地

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;

--许redo传输服务传输数据到目的地，默认是enable

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;

--exclusive or shared，所有库sys密码要一致，默认是exclusive

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 scope=spfile;

--配置网络服务名，fal_server拷贝丢失的归档文件到这里

ALTER SYSTEM SET FAL_CLIENT=port SCOPE=SPFILE;

--配置网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件

ALTER SYSTEM SET FAL_SERVER=STANDBY SCOPE=SPFILE;

--auto后当主库的datafiles增删时备库也同样自动操作，且会把日志传送到备库standby_archive_dest参数指定的目录下，

--确保该目录存在，如果你的存储采用文件系统没有问题，但是如果采用了裸设备，你就必须将该参数设置为manual

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;--前为切换后的主库路径，后为切换后的备库路径，如果主备库目录结构完全一样，则无需设定

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/' SCOPE=SPFILE;

--同上，这两个名字转换参数是主备库的路径映射关系，可能会是路径全名，看情况而定

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/' SCOPE=SPFILE;

--一般和LOG_ARCHIVE_DEST_1的位置一样，如果备库采用ARCH传输方式，那么主库会把归档日志传到该目录下

ALTER SYSTEM SET STANDBY_ARCHIVE_DEST='/home/oracle/archivelog/' scope=spfile;

2、重启主库

shutdown immediate;

startup;

3、主库的tnsnames.ora添加内容

STANDBY =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)

))

4、备份数据库

run{

allocate channel c1 device type disk format '/u01/rmanbak/forstandby/%U' connect sys/password@port;

backup database;

backup archivelog all;

}

5、生成备库控制文件

方法一：

SQL>alter database create standby controlfile as '/u01/rmanbak/forstandby/ctl/controlfile.ctl';

方法二：

run{

allocate channel c1 device type disk format '/u01/rmanbak/forstandby/ctl/CON_%U';

backup current controlfile for standby;

}

6、如果主库没有密码文件则建立密码文件，从而可以OS验证的方式登陆

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=iamwangnc entries=5

三、备库实施（阶段一）

1、获取主库的密码文件

方法一：FTP

cd /home/oracle/product/10.2.0/db_1/dbs

ftp 172.16.3.46

cd /home/oracle/product/10.2.0/db_1/dbs/

bin

get orapwport

方法二：SCP

-bash-3.2$ scp -r root@172.16.3.46:/home/oracle/product/10.2.0/db_1/dbs/orapwport /home/oracle/product/10.2.0/db_1/dbs/orapwport

root@172.16.3.46's password:orapwport 100% 1536 1.5KB/s 00:00 -bash-3.2$

orapwd file=orapwport password=oracle entries=5(未执行)

2、检查相应目录和权限

/home/oracle/admin

/home/oracle/admin/port

/home/oracle/admin/port/adump

/home/oracle/admin/port/bdump

/home/oracle/admin/port/cdump

/home/oracle/admin/port/udump

3、配置tnsnames.ora和listener.ora

############################################# tnsnames.ora文件

#############################################port =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = port)

(INSTANCE_NAME = port)

)

STANDBY =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)

)

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

listener.ora文件

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

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /home/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = standby)

(ORACLE_HOME = /home/oracle/product/10.2.0/db_1)

(SID_NAME = port)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

4、启动监听

lsnrctl start

5、修改参数文件/home/oracle/product/10.2.0/db_1/dbs/initport.ora

create pfile = '/home/oracle/product/10.2.0/db_1/dbs/initport.ora' from spfile;

*.audit_file_dest='/home/oracle/admin/port/adump'

*.background_dump_dest='/home/oracle/admin/port/bdump'

*.core_dump_dest='/home/oracle/admin/port/cdump'

*.user_dump_dest='/home/oracle/admin/port/udump'

*.compatible='10.2.0.4.0'

*.control_files='/u01/oracle/oradata/port/control01.ctl','/u01/oracle/oradata/port/control02.ctl','/u01/oracle/oradata/port/control03.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=32

*.db_name='port'

*.db_unique_name='standby'

*.dispatchers=''

*.fal_client='STANDBY'*.fal_server='port'

*.job_queue_processes=10

*.log_archive_config='DG_CONFIG=(port,standby)'

*.log_archive_dest_1='LOCATION=/home/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=standby'

*.log_archive_dest_2='service=port lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=port'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=4

*.log_file_name_convert='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/'

*.db_file_name_convert='/u01/oracle/oradata/port/','/u01/oracle/oradata/port/'

*.open_cursors=1000

*.pga_aggregate_target=1092957696

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=885

*.sga_max_size=4589934592

*.sga_target=4589934592

*.shared_servers=0

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

6、启动到nomount状体

shutdown immediate;

startup nomount pfile='/home/oracle/product/10.2.0/db_1/dbs/initport.ora';

7、从主库拷贝备份到备库上--注意rman备份的文件在主备库主机上目录要一致

scp -r root@172.16.3.46:/u01/rmanbak/forstandby/ /u01/rmanbak/forstandby/

四、主库实施（阶段二）

1、利用auxiliary恢复备库

rman target / auxiliary sys/password@standby

duplicate target database for standby nofilenamecheck;

RMAN>

run{

allocate channel c1 device type disk format '/u01/rmanbak/forstandby/%U' connect sys/password@port;

allocate auxiliary channel ac1 device type disk format '/u01/rmanbak/forstandby/%U';duplicate target database for standby;

}

如果新创建备库的数据库文件路径名称和主库一样，oracle为了避免错误的覆盖，会抛出RMAN-05001错误，

需要先执行如下语句即可成功，不做数据库文件的名称正确性检查

RMAN>duplicate target database for standby nofilenamecheck;

2、把备库至于恢复状态(备库)

sqlplus / as sysdba

alter database recover managed standby database disconnect from session;

3、在备库添加standby redo log

alter database recover managed standby database cancel;

ALTER DATABASE ADD STANDBY LOGFILE group 4 ('/u01/oracle/oradata/port/redo04.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE group 5 ('/u01/oracle/oradata/port/redo05.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE group 6 ('/u01/oracle/oradata/port/redo06.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE group 7 ('/u01/oracle/oradata/port/redo07.log') SIZE 50M;

standby redolog的组数参考公式：(online redolog组数 + 1) * 数据库线程数；单机线程数为1，RAC一般为2。

standby redolog的组成员数和大小也尽量和online redolog一样。

4、把备库至于恢复状态

alter database recover managed standby database disconnect from session;

5、查看备库日志应用状态

select sequence#,applied from v$archived_log ;

6、测试方案

建表

create table test as select * from dba_users;

插入数据

insert into test select * from test;

commit;

切换日志

alter system switch logfile;

启动备机到只读模式

recover managed standby database cancel;

alter database open;

查看test是否同步

select * from test;

7.回退方案

若在实施过程中出现故障，并会影响到业务的情况，就执行回退机制。具体的操作如下：

去除DataGurd

断开主机传输到DataGuard备机的日志功能

还原force logging模式

停止数据库：

Shutdown immediate

启动数据库到mount状态修改强制日志：

$sqlplus /nolog

startup mount;

alter database archivelog;

alter database no force logging;

alter database open;

本文转自zylhsy 51CTO博客，原文链接：http://blog.51cto.com/yunlongzheng/775195，如需转载请自行联系原作者