on it road.com
步骤
- 启用强制日志记录：
SQL> ALTER DATABASE FORCE LOGGING; Database altered.
- 在主节点上，创建一个临时目录。
例如：
[oracle@prim ~]$ mkdir –p /home/oracle/stage
- 在备用主机上创建完全相同的路径：
[oracle@stdby ~]$ mkdir -p /home/oracle/stage
- 在主节点上，连接到主数据库并从暂存目录中的 SPFILE 创建一个 PFILE。
例如：
sql> create pfile='/home/oracle/stage/boston.ora' from spfile;
- 在主节点上，执行主数据库的 RMAN 备份，将备份片段放入暂存目录。
例如：
RMAN> run{
2> backup device type disk format '/home/oracle/stage/%U' database;
3> backup device type disk format '/home/oracle/stage/%U' current controlfile for standby;
4> }
Starting backup at 24-FEB-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=30 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DATA/chicago/datafile/system.271.679675991
input datafile fno=00003 name=+DATA/chicago/datafile/sysaux.273.679676023
input datafile fno=00002 name=+DATA/chicago/datafile/undo1.272.679676015
channel ORA_DISK_1: starting piece 1 at 24-FEB-09
channel ORA_DISK_1: finished piece 1 at 24-FEB-09
piece handle=/home/oracle/stage/01k8650r_1_1 tag=TAG20090224T154323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 24-FEB-09
channel ORA_DISK_1: finished piece 1 at 24-FEB-09
piece handle=/home/oracle/stage/02k8651v_1_1 tag=TAG20090224T154323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-FEB-09
Starting backup at 24-FEB-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 24-FEB-09
channel ORA_DISK_1: finished piece 1 at 24-FEB-09
piece handle=/home/oracle/stage/03k86525_1_1 tag=TAG20090224T154405 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-FEB-09
RMAN>
- 将临时目录的内容从主服务器复制到备用服务器的临时位置。
例如
[oracle@prim stage]$ scp /home/oracle/stage/* oracle@stdby.example.com:/home/oracle/stage/ oracle@stdby.example.com's password: 01k8650r_1_1 100% 236MB 10.7MB/s 00:22 02k8651v_1_1 100% 6016KB 5.9MB/s 00:00 03k86525_1_1 100% 5984KB 5.8MB/s 00:01 boston.ora 100% 458 0.5KB/s 00:00 [oracle@raca prim]$
- 准备备用实例的初始化文件：
[oracle@stdby ~]$ mkdir -p /u01/app/oracle/product/10.2/oradata [oracle@stdby ~]$ cd /u01/app/oracle/product/10.2/oradata [oracle@stdby oradata]$ mkdir redo data ctrl bdump udump srl arc1 [oracle@stdby oradata]$
在standby的init.ora中设置如下参数，
*.db_name='chicago' *.db_create_file_dest='/u01/app/oracle/product/10.2/oradata/boston/data/' *.db_create_online_log_dest_1='/u01/app/oracle/product/10.2/oradata/boston/redo/' *.db_file_name_convert='+DATA/chicago/datafile/','/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/tempfile/','/u01/app/oracle/product/10.2/oradata/boston/data/' *.log_file_name_convert='+DATA/chicago/onlinelog/','/u01/app/oracle/product/10.2/oradata/boston/redo/' *.db_unique_name=boston
注意：对于 RAC 主到非 RAC 备用，在备用 init.ora 中包含以下行，
cluster_database=false
- 为备库创建密码文件：
[oracle@stdby boston]$ export ORACLE_SID=boston [oracle@stdby dbs]$orapwd file=orapwboston password=oracle
[oracle@stdby dbs]$ ls -ltr *boston* -rw-r----- 1 oracle oinstall 1536 Mar 3 15:55 orapwboston
- 在备用服务器上编写一个 tnsnames 或者连接字符串：
chicago = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =prim.example.com )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = chicago) ) ) boston = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =stdby.example.com )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = boston) ) )
注意：芝加哥将连接到主服务器，而波士顿连接到备用服务器本身。
- 创建备库
[oracle@stdby boston]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 3 16:55:19 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance.
SQL> create spfile from pfile='/u01/app/oracle/product/10.2/oradata/boston/initboston.ora'; File created.
SQL> startup nomount ORACLE instance started. Total System Global Area 327155712 bytes Fixed Size 1218844 bytes Variable Size 150996708 bytes Database Buffers 104857600 bytes Redo Buffers 70082560 bytes SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
[oracle@stdby boston]$ /u01/app/oracle/product/10.2/bin/rman target sys/oracle@chicago auxiliary /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 3 16:56:09 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: CHICAGO (DBID=1289394690)
connected to auxiliary database: CHICAGO (not mounted)
RMAN> duplicate target database for standby;
Starting Duplicate Db at 03-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK
contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 03-MAR-09
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/stage/03k86525_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/stage/03k86525_1_1 tag=TAG20090224T154405
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/u01/app/oracle/product/10.2/oradata/boston/ctrl/ctrl01.ctl
Finished restore at 03-MAR-09
sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/product/10.2/oradata/boston/data/tmp1.274.679676043";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991";
set newname for datafile 2 to
"/u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015";
set newname for datafile 3 to
"/u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /u01/app/oracle/product/10.2/oradata/boston/data/tmp1.274.679676043 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 03-MAR-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991
restoring datafile 00002 to /u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015
restoring datafile 00003 to /u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/stage/01k8650r_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/stage/01k8650r_1_1 tag=TAG20090224T154323
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 03-MAR-09
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=4 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991
datafile 2 switched to datafile copy
input datafile copy recid=5 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015
datafile 3 switched to datafile copy
input datafile copy recid=6 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023
Finished Duplicate Db at 03-MAR-09
RMAN>
- 将备用重做日志添加到备用数据库
SQL> alter database add standby logfile group 3 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl3a.log' size 150m; Database altered.
SQL> alter database add standby logfile group 4 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl4a.log' size 150m; Database altered.
SQL> alter database add standby logfile group 5 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl5a.log' size 150m; Database altered.
SQL> alter database recover managed standby database using current logfile disconnect; Database altered.
- 建立从备用到主的通信：
SQL> select name,database_role from v$database; NAME DATABASE_ROLE --------- ---------------- CHICAGO PHYSICAL STANDBY
SQL> alter system set standby_archive_dest='/u01/app/oracle/product/10.2/oradata/boston/arc1/'; System altered. SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/product/10.2/oradata/boston/arc1/ valid_for=(all_logfiles,all_roles) db_unique_name=boston'; SQL> alter system set log_archive_config='dg_config=(chicago,boston)'; System altered. SQL> alter system set log_archive_dest_2='service=chicago lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=chicago'; System altered. SQL> alter system set fal_client=boston; System altered. SQL> alter system set fal_server=chicago; System altered. SQL> alter system set standby_file_management=auto; System altered.
- 在主服务器上编写 tnsnames 或者连接字符串
chicago = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =prim.example.com )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = chicago) ) ) boston = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =stdby.example.com )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = boston) ) )
注意：boston将连接到备用stdby，而chicago连接到prim本身。
从 11g 开始，我们可以使用 RMAN>DUPLICATE FROM ..ACTIVE DATABASE，
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT '', '' SET DB_FILE_NAME_CONVERT '', '' SET LOG_FILE_NAME_CONVERT '', '' SET SGA_MAX_SIZE 200M SET SGA_TARGET 125M;
- 建立从primary到standby的通信
SQL> alter system set log_archive_config='dg_config=(chicago,boston)'; System altered. SQL> alter system set log_archive_dest_2='service=boston lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=boston'; System altered. SQL> alter system set fal_client=chicago; System altered. SQL> alter system set fal_server=boston; System altered. SQL> select name,database_role from v$database; NAME DATABASE_ROLE --------- ---------------- CHICAGO PRIMARY
- 为当前主设置角色转换特定参数：
SQL> alter system set db_file_name_convert='/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/datafile/','/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/tempfile/' scope=spfile; System altered. SQL> alter system set log_file_name_convert='/u01/app/oracle/product/10.2/oradata/boston/redo/','+DATA/chicago/onlinelog/' scope=spfile; System altered. SQL> alter system set standby_file_management=auto; System altered.
- 配置备用重做日志，
SQL> alter database add standby logfile group 3 size 150m; Database altered. SQL>alter database add standby logfile group 4 size 150m Database altered. SQL>alter database add standby logfile group 5 size 150m Database altered.
注意：要检查 SRL 的数量：
(maximum number of logfiles for each thread + 1) * maximum number of threads
例如，如果主数据库每个线程有3个日志文件和2个线程，那么备用数据库上需要8个备用重做日志文件组。
验证已创建备用重做日志文件组。
目标
这篇文章提供了从 ASM 主数据库创建物理备用数据库的分步过程。
Database Name: chicago Primary db_unique_name: chicago standby db_unique_name: boston
Primary Hostname: prim.example.com standby Hostname: stdby.example.com
日期：2020-09-17 00:11:39 来源：oir作者：oir