配置清单
| 任务 | 数据库 |
|---|---|
|
主 |
|
|
主 |
|
|
主 |
|
|
主 |
|
|
备库 |
|
|
备库 |
|
|
备库 |
一,主库配置
(1)归档模式
作为为备用数据库创建准备主数据库的一部分，必须启用与计划使用数据保护配置的方式相适应的日志模式。不属于数据保护配置的数据库的默认日志记录模式允许以非日志方式执行某些数据加载操作。这种默认模式不适用于具有备用的数据库，因为它会导致从备用数据库中丢失加载的数据，这需要手动干预才能修复。除了默认的日志记录模式之外，还有其他三种模式是
01, 启用强制归档
FORCE LOGGING模式可防止以非记录方式执行任何加载操作。这会降低加载过程的速度，因为必须将加载的数据复制到重做日志中。FORCE LOGGING使用以下命令启用模式：
SQL> ALTER DATABASE FORCE LOGGING;
02, STANDBY NOLOGGING FOR DATA AVAILABILITY
模式使加载操作通过其自身到备用服务器的连接将加载的数据发送到每个备用服务器。提交将被延迟，直到所有备用应用数据作为在活动数据保护环境中运行托管恢复的一部分。
使用以下命令启用:SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;
03, STANDBY NOLOGGING FOR LOAD PERFORMANCE
备用挂载性能类似于前一种模式，但如果网络无法跟上将数据加载到主服务器的速度，则加载过程可以停止向备用服务器发送数据。在这种模式下，备用服务器可能有丢失的数据，
但是作为在活动数据保护环境中运行托管恢复的正常部分，每个备用服务器自动从主服务器获取数据。使用以下命令启用:
SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;
(2)配置主数据库接受重做数据
一般比原来的redo多一组 查询系统的redo SQL> select MEMBER from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ORCL/redo03.log /u01/app/oracle/oradata/ORCL/redo02.log /u01/app/oracle/oradata/ORCL/redo01.log SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/slog1.rdo') SIZE 500M; SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/slog2.rdo') SIZE 500M; SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/slog3.rdo') SIZE 500M; SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/slog4.rdo') SIZE 500M;
(3) 配置pfile
DB_NAME=orcl DB_UNIQUE_NAME=orcl LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,standby)' CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl' LOG_ARCHIVE_DEST_1= 'LOCATION=/oradata/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' LOG_ARCHIVE_DEST_2= 'SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=standby FAL_CLIENT=orcl STANDBY_FILE_MANAGEMENT=AUTO
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,standby)' 2 scope=both 3 ; System altered. SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oradata/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both; System altered. SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=both; System altered. SQL> SQL> alter system set FAL_SERVER=standby 2 scope=both; System altered. SQL> alter system set FAL_CLIENT=orcl scope=both; System altered. SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both; System altered. SQL>
| 参数 | 推荐设置 |
|---|---|
|
在主数据库上，指定创建数据库时使用的名称。在物理备用数据库上，使用 |
|
|
为每个数据库指定唯一的名称。即使主数据库和备用数据库反向角色，此名称仍保留在数据库中，并且不会更改。 |
|
|
|
|
|
指定主数据库上控制文件的路径名。建议控制文件的第二个副本可用，以便在将良好的控制文件复制到错误控制文件的位置后可以轻松地重新启动实例。 |
|
|
指定在主系统和备用系统上归档重做数据的位置。
注意：如果配置了快速恢复区域（使用 |
|
|
必须将此参数设置为 |
|
|
使用线程（％t），序列号（％s）和resetlogs ID（％r）指定归档重做日志文件的格式。 |
|
|
指定FAL服务器的Oracle Net服务名称（通常这是以主角色运行的数据库）。当Chicago数据库以备用角色运行时，如果Boston无法自动发送丢失的日志文件，它将使用Boston数据库作为FAL服务器从中获取（请求）丢失的归档重做日志文件。 |
|
|
指定备用数据库数据文件的路径名和文件名位置，后跟主要位置。此参数将主数据库数据文件的路径名转换为备用数据文件路径名。此参数仅用于转换物理备用数据库的路径名。此参数可以指定多对路径。 |
|
|
指定备用数据库联机重做日志文件的位置，后跟主要位置。此参数将主数据库日志文件的路径名转换为备用数据库上的路径名。此参数可以指定多对路径。 |
|
|
设置为 |
(4) 创建备用数据库需要的文件,并拷贝
创建备库的控制文件 ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/standby.ctl'; 创建备库的参数文件 CREATE PFILE ='/tmp/initstandby.ora' FROM SPFILE;
[oracle@oracle18c dbs]$ scp /tmp/initstandby.ora 192.168.0.163:/u01/app/oracle/product/18.3.0/dbhome_1/dbs oracle@192.168.0.163's password: initstandby.ora 100% 1392 1.4KB/s 00:00 [oracle@oracle18c dbs]$ scp orapworcl 192.168.0.163:/u01/app/oracle/product/18.3.0/dbhome_1/dbs The authenticity of host '192.168.0.163 (192.168.0.163)' can't be established. ECDSA key fingerprint is 37:33:3c:b8:42:24:45:9d:6e:a1:6a:ba:f3:c8:1d:d9. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.0.163' (ECDSA) to the list of known hosts. oracle@192.168.0.163's password: orapworcl 100% 3584 3.5KB/s 00:00
再备库上操作:
更改密码文件名字 [oracle@oracle18c2 dbs]$ mv orapworcl orapwstandby 修改pfile
需要修改的地方
| 参数 | 推荐设置 |
|---|---|
|
为此数据库指定唯一名称。此名称保留在数据库中，即使主数据库和备用数据库反向角色也不会更改。 |
|
|
在备用数据库上指定控制文件的路径名。例3-1显示了如何为两个控制文件执行此操作。建议控制文件的第二个副本可用，以便在将良好的控制文件复制到错误控制文件的位置后可以轻松地重新启动实例。 |
|
|
指定主数据库数据文件的路径名和文件名位置，后跟备用位置。此参数将主数据库数据文件的路径名转换为备用数据文件路径名。 |
|
|
指定主数据库联机重做日志文件的位置，后跟备用位置。此参数将主数据库日志文件的路径名转换为备用数据库上的路径名。 |
|
|
指定要归档重做数据的位置。在例3-1中：
注意：如果配置了快速恢复区域（使用 |
|
|
指定FAL服务器的Oracle Net服务名称（通常这是以主角色运行的数据库）。当Boston数据库以备用角色运行时，它使用Chicago数据库作为FAL服务器，如果Chicago无法自动发送丢失的日志文件，则从该服务器获取（请求）丢失的归档重做日志文件。 |
(5) 主库备份文件
RMAN> backup as compressed backupset database; Starting backup at 2019:08:3015:52:27 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=87 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf channel ORA_DISK_1: starting piece 1 at 2019:08:3015:52:27 channel ORA_DISK_1: finished piece 1 at 2019:08:3015:53:22 piece handle=/oradata/data/02uah0hr_1_1_ORCL tag=TAG20190830T155227 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/kingle/sysaux01.dbf input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/kingle/system01.dbf input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/kingle/undotbs01.dbf input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/kingle/users01.dbf channel ORA_DISK_1: starting piece 1 at 2019:08:3015:53:22 channel ORA_DISK_1: finished piece 1 at 2019:08:3015:53:57 piece handle=/oradata/data/03uah0ji_1_1_ORCL tag=TAG20190830T155227 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 2019:08:3015:53:58 channel ORA_DISK_1: finished piece 1 at 2019:08:3015:54:33 piece handle=/oradata/data/04uah0km_1_1_ORCL tag=TAG20190830T155227 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 Finished backup at 2019:08:3015:54:33 Starting Control File and SPFILE Autobackup at 2019:08:3015:54:33 piece handle=/u01/app/oracle/product/18.3.0/dbhome_1/dbs/c-1545453018-20190830-01 comment=NONE Finished Control File and SPFILE Autobackup at 2019:08:3015:54:34
RMAN> backup current controlfile for standby 2> ; Starting backup at 2019:08:3015:57:19 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including standby control file in backup set channel ORA_DISK_1: starting piece 1 at 2019:08:3015:57:20 channel ORA_DISK_1: finished piece 1 at 2019:08:3015:57:21 piece handle=/oradata/data/06uah0qv_1_1_ORCL tag=TAG20190830T155719 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2019:08:3015:57:21 Starting Control File and SPFILE Autobackup at 2019:08:3015:57:21 piece handle=/u01/app/oracle/product/18.3.0/dbhome_1/dbs/c-1545453018-20190830-02 comment=NONE Finished Control File and SPFILE Autobackup at 2019:08:3015:57:23
拷贝到备库
(6) 备库启动
用pfile 启动数据库
SQL> startup nomount pfile='/u01/app/oracle/product/18.3.0/dbhome_1/dbs/initstandby.ora'; ORACLE instance started. Total System Global Area 2516580872 bytes Fixed Size 8899080 bytes Variable Size 671088640 bytes Database Buffers 1828716544 bytes Redo Buffers 7876608 bytes SQL>
rman 恢复
恢复控制文件
RMAN> restore standby controlfile from '/oradata/data/db_08uah0tg_1_1.ctl'; Starting restore at 2019:08:3016:04:30 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 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/ORCL/control01.ctl output file name=/u01/app/oracle/oradata/ORCL/control02.ctl Finished restore at 2019:08:3016:04:32
恢复数据文件
指定目录
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> catalog start with '/oradata/data/';
searching for all files that match the pattern /oradata/data/
List of Files Unknown to the Database
=====================================
File Name: /oradata/data/db_08uah0tg_1_1.ctl;;
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oradata/data/db_08uah0tg_1_1.ctl;;
恢复
RMAN> restore database;
Starting restore at 2019:08:3016:06:38
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
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/ORCL/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /oradata/data/02uah0hr_1_1_ORCL
channel ORA_DISK_1: piece handle=/oradata/data/02uah0hr_1_1_ORCL tag=TAG20190830T155227
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
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 00009 to /u01/app/oracle/oradata/ORCL/kingle/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ORCL/kingle/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ORCL/kingle/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCL/kingle/users01.dbf
channel ORA_DISK_1: reading from backup piece /oradata/data/03uah0ji_1_1_ORCL
channel ORA_DISK_1: piece handle=/oradata/data/03uah0ji_1_1_ORCL tag=TAG20190830T155227
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
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 00005 to /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /oradata/data/04uah0km_1_1_ORCL
channel ORA_DISK_1: piece handle=/oradata/data/04uah0km_1_1_ORCL tag=TAG20190830T155227
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2019:08:3016:08:54
RMAN>
备库行同步
alter database recover managed standby database using current logfile disconnect from session;