文档课题：运用rman备份集搭建物理备库.
主库：192.168.133.110 + oracle 11.2.0.4 + centos 7.9 主机名：leo-oracle-11g
备库：192.168.133.113 + oracle 11.2.0.4 + centos 7.9 主机名：leo-oracle-11gdg
场景描述：最初的备库环境为主库的克隆.
1、环境处理
--对克隆的备库进行相关处理，如删监听文件、tnsnames.ora等文件.
[oracle@leo-oracle-11g admin]$ rm -rf tnsnames.ora
[oracle@leo-oracle-11g admin]$ rm -rf listener.ora
--修改ip、网卡uuid、mac地址，删数据文件、归档文件.
--修改/etc/oratab参数.
[oracle@leo-oracle-11g ~]$ vi /etc/oratab
orcldg:/u01/app/oracle/product/11.2.0/db:Y
说明：将此前orcl修改为orcldg.
--修改.bash_profile文件
[oracle@leo-oracle-11g ~]$ vi .bash_profile
……
export ORACLE_SID=orcldg
说明：将oracle_sid由orcl修改为orcldg.
--修改主机名
[root@leo-oracle-11g:~]# vi /etc/hostname
leo-oracle-11gdg
--重启系统生效.
[root@leo-oracle-11g:~]# reboot
2、主库配置
2.1、确认主库参数
SYS@orcl> select name,open_mode,database_role,log_mode,force_logging from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE FOR
--------- -------------------- ---------------- ------------ ---
ORCL READ WRITE PRIMARY ARCHIVELOG NO
SYS@orcl> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcl
SYS@orcl> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/archivelog
Oldest online log sequence 262
Next log sequence to archive 269
Current log sequence 269
如果主库未开启归档，则执行以下操作开启归档：
shutdown immediate；
alter database mount;
alter database archive log；
alter database open；
2.2、开启force logging
--若主库未开启force logging，则将其开启.
SYS@orcl> select force_logging from v$database;
FOR
---
NO
SYS@orcl> alter database force logging;
Database altered.
SYS@orcl> select force_logging from v$database;
FOR
---
YES
SYS@orcl> select name,db_unique_name,database_role,log_mode,force_logging from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE LOG_MODE FOR
--------- ------------------------------ ---------------- ------------ ---
ORCL orcl PRIMARY ARCHIVELOG YES
2.3、配置tnsnames.ora
[oracle@leo-oracle-11g admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = leo-oracle-11g)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = leo-oracle-11gdg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)
)
说明：蓝颜色为新添加内容.
2.4、修改参数文件
--修改主库参数文件.
SYS@orcl> alter system set log_archive_config='dg_config=(orcl,orcldg)';
System altered.
SYS@orcl> alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=orcl';
System altered.
SYS@orcl> alter system set log_archive_dest_2='service=orcldg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';
System altered.
SYS@orcl> alter system set log_archive_dest_state_1='enable';
System altered.
SYS@orcl> alter system set log_archive_dest_state_2='defer'; --配置从库后再开启
System altered.
SYS@orcl> alter system set standby_file_management='auto';
System altered.
SYS@orcl> alter system set fal_server=orcldg;
System altered.
SYS@orcl> alter system set fal_client=orcl;
System altered.
2.5、传输文件
--主库生成参数文件，并将其传输到备库.
SYS@orcl> create pfile from spfile;
File created.
--将参数文件、tnsnames.ora文件、监听文件、密码文件传递到备库.
$ pwd
/u01/app/oracle/product/11.2.0/db/dbs
$ scp initorcl.ora oracle@192.168.133.113:/u01/app/oracle/product/11.2.0/db/dbs
$ scp orapworcl oracle@192.168.133.113:/u01/app/oracle/product/11.2.0/db/dbs
$ pwd
/u01/app/oracle/product/11.2.0/db/network/admin
$ scp tnsnames.ora oracle@192.168.133.113:/u01/app/oracle/product/11.2.0/db/network/admin
$ scp listener.ora oracle@192.168.133.113:/u01/app/oracle/product/11.2.0/db/network/admin
2.6、域名解析
--添加域名解析.
[root@leo-oracle-11g:~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
## OracleBegin
## Public IP
192.168.133.110 leo-oracle-11g
192.168.133.113 leo-oracle-11gdg
说明：蓝颜色为添加的域名解析.
3、备库配置
3.1、修改监听
--配置静态监听，以及修改host参数.
[oracle@leo-oracle-11gdg admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcldg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
(SID_NAME = orcldg)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = leo-oracle-11gdg)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
说明：蓝色为修改后的监听文件.
--修改后重启监听.
[oracle@leo-oracle-11gdg admin]$ lsnrctl stop
[oracle@leo-oracle-11gdg admin]$ lsnrctl start
3.2、添加域名解析
--备库添加域名解析.
[root@leo-oracle-11gdg:~]# vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
## OracleBegin
## Public IP
192.168.133.110 leo-oracle-11g
192.168.133.113 leo-oracle-11gdg
说明：蓝颜色为添加的域名解析.
3.3、修改文件
--重命名密码文件、参数文件.
[oracle@leo-oracle-11gdg dbs]$ mv orapworcl orapworcldg
[oracle@leo-oracle-11gdg dbs]$ mv initorcl.ora initorcldg.ora
[oracle@leo-oracle-11gdg dbs]$ vi initorcldg.ora
--按如下内容修改参数文件.
*._b_tree_bitmap_plans=FALSE
*._cleanup_rollback_entries=2000
*._datafile_write_errors_crash_instance=FALSE
*._index_partition_large_extents='FALSE'
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/u01/app/oracle/admin/orcldg/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.db_block_size=8192
*.db_domain=''
*.db_files=5000
*.db_name='orcl'
*.db_unique_name='orcldg'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_ddl_logging=TRUE
*.fal_client='ORCLDG'
*.fal_server='ORCL'
*.log_archive_config='dg_config=(orcl,orcldg)'
*.log_archive_dest_1='location=/u01/app/oracle/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
*.log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=1000
*.parallel_max_servers=64
*.pga_aggregate_target=1107103908
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=300
*.sga_max_size=1660655862
*.sga_target=1660655862
*.standby_file_management='auto'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.control_files='/u01/app/oracle/oradata/orcldg/control01.ctl'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/fast_recovery_area/orcl/','/u01/app/oracle/fast_recovery_area/orcldg/','/u01/app/oracle/oradata/ORCL/onlinelog/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/','/u01/app/oracle/oradata/orcldg/'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldg/', '/u02/app/oracle/oradata/orcl/','/u02/app/oracle/oradata/orcldg/'
说明：参数文件的修改根据实际环境进行修改.
3.4、创建文件目录
[oracle@leo-oracle-11gdg dbs]$ mkdir -p /u01/app/oracle/admin/orcldg/adump
[oracle@leo-oracle-11gdg dbs]$ mkdir -p /u01/app/oracle/oradata/orcldg/
[oracle@leo-oracle-11gdg dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcldg/
[oracle@leo-oracle-11gdg dbs]$ mkdir -p /u01/app/oracle/archivelog/
[oracle@leo-oracle-11gdg dbs]$ mkdir -p /u02/app/oracle/oradata/orcldg
3.5、测试互通性
--将备库以修改后的参数文件启动到nomount阶段进行互通性的测试.
SYS@orcldg> startup nomount pfile='/u01/app/oracle/product/11.2.0/db/dbs/initorcldg.ora';
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1241517096 bytes
Database Buffers 402653184 bytes
Redo Buffers 7094272 bytes
SYS@orcldg> create spfile from pfile='/u01/app/oracle/product/11.2.0/db/dbs/initorcldg.ora';
File created.
主备测试连通性
tnsping orcldg
tnsping orcl
sqlplus sys/oracle@orcl as sysdba
sqlplus sys/oracle@orcldg as sysdba
4、主库备份集
4.1、备份
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
sql' alter system archive log current';
backup as compressed backupset database format '/home/oracle/backup/rman/full_db_%U';
sql' alter system archive log current';
backup archivelog all format '/home/oracle/backup/rman/archlog_%U';
backup current controlfile format '/home/oracle/backup/rman/controlfile_%U';
backup spfile format '/home/oracle/backup/rman/spfile_%U';
release channel c1;
release channel c2;
}
4.2、传递备库
[oracle@leo-oracle-11g rman]$ pwd
/home/oracle/backup/rman
[oracle@leo-oracle-11g rman]$ scp * oracle@192.168.133.113:/home/oracle/backup
5、备库恢复
[oracle@leo-oracle-11gdg ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 29 17:35:18 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> restore standby controlfile from '/home/oracle/backup/controlfile_151qr1id_1_1';
Starting restore at 29-APR-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcldg/control01.ctl
Finished restore at 29-APR-23
RMAN> alter database mount;
RMAN> catalog start with '/home/oracle/backup';
RMAN> restore database;
Starting restore at 29-APR-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcldg/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcldg/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_db_101qr1gd_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_db_101qr1gd_1_1 tag=TAG20230429T173156
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcldg/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcldg/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/orcldg/users02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_db_0v1qr1gd_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_db_0v1qr1gd_1_1 tag=TAG20230429T173156
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 29-APR-23
RMAN> recover database;
Starting recover at 29-APR-23
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=276
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=277
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/archlog_141qr1ib_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/archlog_141qr1ib_1_1 tag=TAG20230429T173259
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/archivelog/1_276_1130877590.dbf thread=1 sequence=276
archived log file name=/u01/app/oracle/archivelog/1_277_1130877590.dbf thread=1 sequence=277
unable to find archived log
archived log thread=1 sequence=278
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/29/2023 20:37:34
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 278 and starting SCN of 2631818
RMAN> recover database until scn 2631818;
Starting recover at 29-APR-23
using channel ORA_DISK_1
starting media recovery
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcldg/system01.dbf'
media recovery complete, elapsed time: 00:00:00
Finished recover at 29-APR-23
6、恢复后操作
--主库开启log_archive_dest_state_2.
SYS@orcl> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE';
System altered.
--备库open数据库.
SYS@orcldg> alter database open;
Database altered.
7、添加standby redo log
--主备添加
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '/u01/app/oracle/oradata/orcl/standby_redo11.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '/u01/app/oracle/oradata/orcl/standby_redo12.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '/u01/app/oracle/oradata/orcl/standby_redo13.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '/u01/app/oracle/oradata/orcl/standby_redo14.log' size 50M;
备库增加
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '/u01/app/oracle/oradata/orcldg/standby_redo11.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '/u01/app/oracle/oradata/orcldg/standby_redo12.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '/u01/app/oracle/oradata/orcldg/standby_redo13.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '/u01/app/oracle/oradata/orcldg/standby_redo14.log' size 50M;
8、开启实时同步
--备库开启实时同步.
SYS@orcldg> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SYS@orcldg> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';
PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
13283 RFS IDLE 0 0 0 0
13256 RFS IDLE 0 0 0 0
13258 RFS IDLE 1 280 1257 1
17515 MRP0 WAIT_FOR_LOG 1 280 0 0
参考网址：https://www.cnblogs.com/lijiaman/p/11404413.html服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.e1idc.net
