源端 : node1
目标端 : node2
配置步骤:
1.监听起来
2.开归档,主库设置为force logging 模式
alter system set log_archive_dest_1='location=/u01/app/arch' scope=spfile;
alert database archivelog
SQL> alter database force logging;
添加standby文件
alter database add standby logfile group 4 ('/oradata/node1/redo04.log') size 20M;
alter database add standby logfile group 5 ('/oradata/node1/redo05.log') size 20M;
alter database add standby logfile group 6 ('/oradata/node1/redo06.log') size 20M;
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
查看所有redo文件列表,select * from v$logfile order by 1;
SQL> select * from v$logfile order by 1;
1 ONLINE /oradata/node1/redo01.log NO
2 ONLINE /oradata/node1/redo02.log NO
3 ONLINE /oradata/node1/redo03.log NO
4 STANDBY /oradata/node1/redo04.log NO
5 STANDBY /oradata/node1/redo05.log NO
6 STANDBY /oradata/node1/redo06.log NO
6 rows selected.
添加standby文件
alter database add standby logfile group 4 ('/oradata/node1/redo04.log') size 20M;
alter database add standby logfile group 5 ('/oradata/node1/redo05.log') size 20M;
alter database add standby logfile group 6 ('/oradata/node1/redo06.log') size 20M;
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
查看所有redo文件列表,select * from v$logfile order by 1;
SQL> select * from v$logfile order by 1;
1 ONLINE /oradata/node1/redo01.log NO
2 ONLINE /oradata/node1/redo02.log NO
3 ONLINE /oradata/node1/redo03.log NO
4 STANDBY /oradata/node1/redo04.log NO
5 STANDBY /oradata/node1/redo05.log NO
6 STANDBY /oradata/node1/redo06.log NO
6 rows selected.
3.源端:rman 备份
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/oradata/backup/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/oradata/backup/arch_%d_%T_%s_%p';
}
4.源端: create pfile ='/tmp/aaa.txt' from spfile;
5. 创建目标端控制文件,也可以用rman备份的
SQL>alter database create standby controlfile as '/oradata/ctl_bf';
rman target /
backup current controlfile for standby format '/oradata/backup/ctl_bf'
6. scp 参数文件到目标端
scp rman备份到目标端
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/oradata/backup/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/oradata/backup/arch_%d_%T_%s_%p';
}
4.源端: create pfile ='/tmp/aaa.txt' from spfile;
5. 创建目标端控制文件,也可以用rman备份的
SQL>alter database create standby controlfile as '/oradata/ctl_bf';
rman target /
backup current controlfile for standby format '/oradata/backup/ctl_bf'
6. scp 参数文件到目标端
scp rman备份到目标端
7. vi 源端 参数文件,控制文件 路径改一下
vi aaa.txt
把这些参数加到参数文件中 删掉原有的归档路径
*.fal_client=node2 -- 目标网络别名
*.fal_server=node1 --源端网络别名
*.fal_server=node1 --源端网络别名
*.log_archive_dest_1='location=/u01/app/arch' --本机归档目录
*.log_archive_dest_2='service=node2' --设置源端归档,通过指向源端的网络别名
*.db_file_name_convert='/oradata/node1','/oradata/node2' --(源端,目标端)控制文件数据文件变了,有这个参数,就不用手动 改过来了
*.log_file_name_convert='/oradata/node1','/oradata/node2' --(源端,目标端)日志文件 多个文件目录用空格隔开,如果用裸设备用 /dev/rhdisk_*
*.standby_archive_dest='location=/oradata/arch' --目标端归档目录,最好跟上面归档目录一样
*.standby_file_management=auto 归档的自动回复功能
注意:把路径改了
这些参数全部用 aler system set
8. 把监听文件, tnsname copy过来 ,注意改监听和tns里面的ip和实例名
Listener.ora 文件:
Listener.ora 文件:
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = orcl)
)
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
注意:SID_LIST_LISTENER 配置的是静态注册,如果没有该参数,而且Data Guard 启动顺序又不正确,那么在主库可能会报 PING[ARC1]: Heartbeat failed to connect to standby 'orcl_st'. Error is 12514. 错误,导致归档无法完成。
配置tnsname 文件
NODE1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = node1)
)
)
NODE2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = node2)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = node1)
)
)
NODE2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = node2)
)
)
9. 创建各种dump目录
-----------------------------------------------------
11g要创建这些目录
rm -rf $ORACLE_BASE/admin/$ORACLE_SID
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/scripts
chmod -R 750 $ORACLE_BASE/admin
rm -rf $ORACLE_BASE/diag/rdbms/$ORACLE_SID
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/alert
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/cdump
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/hm
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/incident
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/incpkg
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/ir
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/lck
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/metadata
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/stage
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/sweep
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
chmod -R 750 $ORACLE_BASE/diag/rdbms/$ORACLE_SID
10. 创建密码文件,直接copy过去也行
/u01/app/oracle/product/11.2.0.3/db_1/dbs
[oracle@localhost dbs]$ orapwd file=/u01/app/oracle/product/11.2.0.3/db_1/dbs/orapworcl password=oracle
[oracle@localhost dbs]$ orapwd file=/u01/app/oracle/product/11.2.0.3/db_1/dbs/orapworcl password=oracle
如果已经存在,就不用创建了。 缺省情况下,win下口令文件的格式是pwdsid.ora,unix下的格式是orapwSID(大小写敏感)
实例名区分大小写,库名不区分大小写
11. 修改listener.ora 和 tnsnames.ora 文件,如果不存在,就从主库上copy 过去。
注意: 参数里面实例名改下,路径名都要看下
*.db_unique_name='node2'
12. 目标端 startup nomount pfile='/oradata/backup/aaa.txt';
ORA-32006: STANDBY_ARCHIVE_DEST initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 494931288 bytes
Database Buffers 339738624 bytes
Redo Buffers 2379776 bytes
这个错说的是指定目录不存在,看了一下有这个目录,有资料说该参数被遗弃了暂时不管啦。
ORA-32006: STANDBY_ARCHIVE_DEST initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 494931288 bytes
Database Buffers 339738624 bytes
Redo Buffers 2379776 bytes
这个错说的是指定目录不存在,看了一下有这个目录,有资料说该参数被遗弃了暂时不管啦。
[oracle@ogg04 ~]$ sqlplus / as sysdba
13.恢复控制文件
RMAN> restore standby controlfile from '/oradata/backup/ctl_bf';
Starting restore at 29-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oradata/ogg02/control01.ctl
output file name=/oradata/ogg02/control02.ctl
Finished restore at 29-DEC-14
Starting restore at 29-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oradata/ogg02/control01.ctl
output file name=/oradata/ogg02/control02.ctl
Finished restore at 29-DEC-14
13. alter database mount;
RMAN> alter database mount ;
database mounted
released channel: ORA_DISK_1
验证有没有生效
database mounted
released channel: ORA_DISK_1
验证有没有生效
select name from v$datafile;
14.rman 恢复
run
{
set newname for datafile '/oradata/node1/system01.dbf' to '/oradata/node2/system01.dbf';
set newname for datafile '/oradata/node1/sysaexux01.dbf'to '/oradata/node2/sysaux01.dbf';
set newname for datafile '/oradata/node1/undotbs01.dbf' to '/oradata/node2/undotbs01.dbf';
set newname for datafile '/oradata/node1/users01.dbf' to '/oradata/node2/users01.dbf';
restore database ;
switch datafile all;
}
run{
allocate channel c1 type disk;
recover database;
}
主库先备份归档:RMAN> backup archivelog all format '/oradata/backup/arch_%d_%T_%s_%p';
将归档备份集Scp到备库;
备库再注册归档备份集:
RMAN> catalog backuppiece '/oradata/backup/arch_OGG02_20150204_20_1';
RMAN> recover database;
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 11 and starting SCN of 964852828
allocate channel c1 type disk;
recover database;
}
主库先备份归档:RMAN> backup archivelog all format '/oradata/backup/arch_%d_%T_%s_%p';
将归档备份集Scp到备库;
备库再注册归档备份集:
RMAN> catalog backuppiece '/oradata/backup/arch_OGG02_20150204_20_1';
RMAN> recover database;
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 11 and starting SCN of 964852828
15.目标端执行
create spfile from pfile='/oradata/backup/aaa.txt';
shutdown immediate;
startup nomount;
alter database mount standby database;
SQL> select controlfile_type from v$database;
CONTROL
-------
STANDBY
16.目标端 :启动进程
16.1以只读方式打开standby
sql> alter database open read only;
16.2 启动redo apply
sql> alter database recover managed standby database using current logfile disconnect from session;
从11gR2开始,物理standby数据库在打开的情况下也能应用日志,成为Active DataGuard