10gRac+DG配置1.1
一、使用RMAN对数据库进行备份
backup database format '/app/oracle/rman/pybackup_%U.bak' tag='pybak_bak';
二、创建standby controlfile
alter database create standby controlfile as '/app/oracle/rman/standby.ctl';
三、准备密码文件
四、创建pfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/racdb/spfileracdb.ora
create pfile='/app/oracle/rman/pfile.ora' from spfile='+DATA/racdb/spfileracdb.ora';
五、将以上文件全部拷贝到DG备库
scp * oracle@192.168.4.71:/oracle/oradata/rman/
如果使用nfs分享注意挂载参数
mount -t nfs -o tcp,rsize=32768,wsize=32768,hard 192.168.4.70:/oracle/oradata/rman /oracle/oradata/rman
六、根据实际情况创建oracle相关目录
mkdir -pv /oracle/admin/racdb/adump
mkdir -pv /oracle/admin/racdb/bdump
mkdir -pv /oracle/admin/racdb/cdump
mkdir -pv /oracle/admin/racdb/udump
mkdir -pv /oracle/oradata/archive
mkdir -pv /oracle/oradata/flash_recovery_area
mkdir -pv /oracle/oradata/racdb
七、备库上操作
a、准备密码文件
cp /oracle/oradata/rman/orapwracdb $ORACLE_HOME/dbs/orapwracdb
b、修改pfile
原始配置
[oracle@mydg1 rman]$ cat pfile.ora
racdb2.__db_cache_size=1174405120
racdb1.__db_cache_size=1174405120
racdb1.__java_pool_size=16777216
racdb2.__java_pool_size=16777216
racdb1.__large_pool_size=16777216
racdb2.__large_pool_size=16777216
racdb1.__shared_pool_size=352321536
racdb2.__shared_pool_size=385875968
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=33554432
*.audit_file_dest='/app/oracle/admin/racdb/adump'
*.background_dump_dest='/app/oracle/admin/racdb/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.5.0'
*.control_files='+DATA/racdb/controlfile/current.260.817673681'
*.core_dump_dest='/app/oracle/admin/racdb/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='racdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb1.instance_number=1
racdb2.instance_number=2
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(racdb,pyracdb)'
*.log_archive_dest_1='LOCATION=+DATA/RACDB/ARC VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb'
*.log_archive_dest_2='SERVICE=pyracdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pyracdb'
*.open_cursors=300
*.pga_aggregate_target=826277888
*.processes=150
*.remote_listener=''
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
racdb2.thread=2
racdb1.thread=1
*.undo_management='AUTO'
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/app/oracle/admin/racdb/udump'
修改后的配置（删除了RAC相关的配置以及修改了相关路径）
[oracle@mydg1 rman]$ cat dg.ora
*.audit_file_dest='/oracle/admin/racdb/adump'
*.background_dump_dest='/oracle/admin/racdb/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/oracle/oradata/racdb/controlfile01.ctl'
*.core_dump_dest='/oracle/admin/racdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='racdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/oracle/oradata/archive'
*.open_cursors=300
*.pga_aggregate_target=826277888
*.processes=150
*.remote_listener=''
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/racdb/udump'
c、通过修改后的pfile创建spfile
create spfile from pfile='/oracle/oradata/rman/dg.ora';
d、准备standby database的控制文件
cp standby.ctl /oracle/oradata/racdb/controlfile01.ctl
e、将数据库启动到mount状态
1073741824
268435456
第八步、使用RMAN还原数据库
a、由于目录结构改变，所以先清除无效的备份集和归档备份集
crosscheck backup ;
crosscheck archivelog all;
delete expired backup;
delete expired archivelog all;
b、重新注册catalog
catalog start with '/oracle/oradata/rman';
c、查看注册后的有效备份
list backup;
d、异路径还原
select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
---------- --------------------------------------------------
4 +DATA/racdb/datafile/users.259.817673631
3 +DATA/racdb/datafile/sysaux.257.817673631
2 +DATA/racdb/datafile/undotbs1.258.817673631
1 +DATA/racdb/datafile/system.256.817673631
5 +DATA/racdb/datafile/undotbs2.264.817673705
FILE# NAME
select file#,name from v$tempfile;
---------- --------------------------------------------------
1 +DATA/racdb/tempfile/temp.263.817673689
run {
set newname for datafile 1 to '/oracle/oradata/racdb/system01.dbf';
set newname for datafile 2 to '/oracle/oradata/racdb/undotbs01.dbf';
set newname for datafile 3 to '/oracle/oradata/racdb/sysaux01.dbf';
set newname for datafile 4 to '/oracle/oradata/racdb/users01.dbf';
set newname for datafile 5 to '/oracle/oradata/racdb/undotbs02.dbf';
set newname for tempfile 1 to '/oracle/oradata/racdb/temp_01.dbf';
restore database;
switch datafile all;
}
e、修改redo的路径
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------- ---
2 ONLINE +DATA/racdb/onlinelog/group_2.262.817673685 NO
1 ONLINE +DATA/racdb/onlinelog/group_1.261.817673683 NO
3 ONLINE +DATA/racdb/onlinelog/group_3.265.817673715 NO
4 ONLINE +DATA/racdb/onlinelog/group_4.266.817673717 NO
alter database rename file '+DATA/racdb/onlinelog/group_2.262.817673685' to '/oracle/oradata/racdb/redo02_a.log';
alter database rename file '+DATA/racdb/onlinelog/group_1.261.817673683' to '/oracle/oradata/racdb/redo01_a.log';
alter database rename file '+DATA/racdb/onlinelog/group_3.265.817673715' to '/oracle/oradata/racdb/redo03_a.log';
alter database rename file '+DATA/racdb/onlinelog/group_4.266.817673717' to '/oracle/oradata/racdb/redo04_b.log';
f、修改dg库的db_unique_name(该参数必须重启才能生效)
alter system set db_unique_name='lgracdb' scope=spfile;
alter system set db_unique_name='pyracdb' scope=spfile;
g、修改standby库的service_names
alter system set service_names='racdb';
第九步、配置tnsname.ora,重启listener
主库每个节点
#默认就有
RACDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = jaro-rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = jaro-rac2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
#新增加
LGRACDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.71)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = racdb)
)
)
备库（RACDB1,RACDB2为2个RAC节点，lgracdb为dg自身）
RACDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.50)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = racdb)
)
)
LGRACDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.71)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = racdb)
)
)
RACDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.51)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = racdb)
)
)
第十步、重启备库到mount状态
第十一步、配置DG
a、主库操作
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb,lgracdb)';
alter system set log_archive_dest_1='LOCATION=+DATA/RACDB/ARC VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=lgracdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lgracdb';
b、备库操作
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb,pyracdb,lgracdb,pvracdb)';
DG_CONFIG=(racdb,pyracdb,lgracdb)
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb,pyracdb)';
alter system set log_archive_dest_1='LOCATION=/oracle/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lgracdb';
alter system set db_file_name_convert='+DATA/racdb/datafile/','/oracle/oradata/racdb/','+DATA/racdb/tempfile/','/oracle/oradata/racdb/'scope=spfile;
alter system set log_file_name_convert='+DATA/racdb/onlinelog/','/oracle/oradata/racdb/' scope=spfile;
alter system set standby_file_management='AUTO';
alter system set fal_server='racdb1','racdb2';
alter system set fal_client='pyracdb';
alter system set fal_client='lgracdb';
c、添加standby log（备库操作）
alter database add standby logfile group 9 ('/oracle/oradata/racdb/stdby_redo09_a.log',
'/oracle/oradata/racdb/stdby_redo09_b.log') size 512m,
group 5 ('/oracle/oradata/racdb/stdby_redo05_a.log',
'/oracle/oradata/racdb/stdby_redo05_b.log') size 512m,
group 6 ('/oracle/oradata/racdb/stdby_redo06_a.log'
,'/oracle/oradata/racdb/stdby_redo06_b.log') size 512m,
group 7 ('/oracle/oradata/racdb/stdby_redo07_a.log',
'/oracle/oradata/racdb/stdby_redo07_b.log') size 512m,
group 8 ('/oracle/oradata/racdb/stdby_redo08_a.log',
'/oracle/oradata/racdb/stdby_redo08_b.log') size 512m;
d、重启备库到Mount状态
第十二步、备库开启关闭日志应用
a、开启日志应用
alter database recover managed standby database using current logfile disconnect from session;
查看是否使用Real-Time apply：
Select recovery_mode from v$archive_dest_status;
观察备库的alter 日志
对主库进行手动日志切换，确认日志能够正常传输和接收。
b、关闭日志应用
alter database recover managed standby database cancel;
15、查看日志应用情况
select to_char(START_TIME,'yyyymmdd hh24:mi:ss') start_time ,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') timestamp from v$recovery_progress;
select round((sysdate-max(timestamp))*86400) from v$recovery_progress;
物理备库转逻辑备库
a、物理备库先停止日志应用
alter database recover managed standby database cancel;
b、主库生产logstandby 元数据
EXECUTE DBMS_LOGSTDBY.BUILD;
c、备库转换成逻辑DG
ALTER DATABASE RECOVER TO LOGICAL STANDBY LGDG;
d、关闭备库并且重启到mount状态
shutdown immeidate
startup mount
e、已经resetlogs方式打开逻辑DG
alter database open resetlogs;
f、开启日志应用
alter database start logical standby apply ;
开启实时日志应用
alter database start logical standby apply immediate;
h、在每个RAC节点都手动切换几次日志，确保日志都能够传输到备库
i、关闭日志应用
alter database stop logical standby apply;
16、逻辑dg
select round((sysdate - applied_time) * 86400) from v$logstdby_progress;
select * from dba_logstdby_progress;
17、查看逻辑DG的应用进程
select type, status_code, status from v$logstdby_process;
select * from v$logstdby_process;
SELECT TYPE, STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS;
18、查看逻辑DG的事件
select * from
(SELECT EVENT_TIME,
xidusn||','||xidslt||','||xidsqn as tx,
status,
event
FROM dba_logstdby_events
where event is not null
order by event_time desc) a
where rownum < 4;
19、删除多余的redo 组
alter system archive log all;
alter database disbale thread 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
run {
set archivelog destination to '/oracle/oradata/arcbak';
restore archivelog all;
}
One Response