11g RMAN新特性 active database duplication createing standby

最新推荐文章于 2022-05-13 16:01:46 发布

cucanqi9387

最新推荐文章于 2022-05-13 16:01:46 发布

阅读量117

点赞数

文章标签：数据库操作系统网络

Network-Enabled Database Duplication Without Backups
Prior to Oracle 11g, an RMAN database duplication to a different host required copies of
the relevant backups and archived redo logs to be manually copied to the remote server.
Oracle 11g allows active database duplication, so there is no need for the presence of pre-existing
database backups and manual copying of files.

Once the DUPLICATE command is initiated, RMAN automatically performs the following steps:

Copies the spfile to the destination server.
Starts the auxiliary instance with the spfile.
Copies the relevant database files and archived redo logs over the network to the destination server.
Recovers the database.
Opens the database with the RESETLOGS option.
This method is suitable for creating a duplicate database or physical standby database.

也就是说oracle 11g 用DUPLICATE命令创建standby database 或 duplicate database 不再需要主库的备份数据文件,
归档日志文件，参数文件,控制文件等
而是直接从主库拷贝到standby库，不需要做copy文件手工操作
需要做手工的地方就是拷贝主库的密码文件PWDprimary.ora 命名为PWDstandby.ora文件
oracle 11g rman新特性 active database duplication
下面是在同一台机利用active database duplication 搭建standby 的操作过程

操作系统环境windows 2003 server +oracle 11.1.0.6
数据库名 primary
主库 sid=primary
存放数据文件路径:d:\app\administrator\oradata\primary\
存放归档文件路径:d:\primary\archivelog
闪回文件路径:d:\app\administrator\flash_recovery_area\primary
备库
sid=primary
存放数据文件路径:d:\app\administrator\oradata\standby\
存放归档文件路径:d:\standby\archivelog
闪回文件路径:d:\app\administrator\flash_recovery_area\standby
建好备库的存放文件相关目录
d:\app\administrator\admin 目录下建立standby 目录
d:\app\administrator\admin\standby 目录下建立以下三个文件夹
adump, dpdump, pfile

操作步骤如下:
1.在windows下新建standby 实例,用oradim工具
C:\Documents and Settings\Administrator>oradim -NEW -SID standby -STARTMODE auto -S
RVCSTART system
Instance created.
The Oracle standby VSS Writer Service service is starting.
The Oracle standby VSS Writer Service service was started successfully.

2.在f:目录下新建个文件init.ora文件用于启动standby 实例
编辑init.ora 输入 db_name=primary 保存退出

3.编辑listener.ora与tnsnames.ora文件使能够与备库相同
listener.ora文件如下:
# listener.ora Network Configuration File: D:\app\Administrator\product\11.1.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = paul2003)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_NAME =primary)
      (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)
      (SID_NAME =primary)
    )
   (SID_DESC =
      (GLOBAL_NAME =primary)
      (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)
      (SID_NAME =standby)
    )
)
tnsnames.ora文件如下:
primary =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.227)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
)
standby =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.227)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =standby)
    )
)
重新启动监听
lsnrctl stop
lsnrctl start

4.启动standby实例到nomount状态
C:\Documents and Settings\Administrator>set oracle_sid=standby

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 31 11:16:20 2008

Connected to an idle instance.

SQL> startup nomount pfile='f:\init.ora';
ORACLE instance started.

Total System Global Area 146472960 bytes
Fixed Size                  1331740 bytes
Variable Size              92278244 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - P
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

5.在执行DUPLICATE之前，首先设置PRIMARY数据库为归档模式,FORCE LOGGING和DATA GUARD相关的初始化参数：
C:\Documents and Settings\Administrator>set oracle_sid=primary

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 31 11:17:30 2008

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            d:\primary\archivelog
Oldest online log sequence     43
Next log sequence to archive   45
Current log sequence           45
--已经是归档模式如果是非归档模式则必须修改为归档模式

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(primary,standby)';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=d:\primary\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' SCOPE = SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='PRIMARY','STANDBY';

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='PRIMARY','STANDBY'; 用于搭建过程中主库和备库目录转换
如果是两台机器相同目录则不需要设置这两个参数
System altered.

6.登录主库执行duplicate命令

C:\Documents and Settings\Administrator>set oracle_sid=primary

C:\Documents and Settings\Administrator>rman target / auxiliary sys/abcdefg@stan
dby log=c:\rman.log
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Jul 31 11:17:54 2008

connected to target database: PRIMARY (DBID=1498839992)
connected to auxiliary database: PRIMARY (not mounted)

RMAN>RMAN> duplicate target database for standby
2> dorecover from active database nofilenamecheck
3> spfile set DB_UNIQUE_NAME 'standby'
4> set FAL_CLIENT 'standby'
5> set FAL_SERVER 'primary'
6> set log_archive_dest_1
7> 'LOCATION=d:\standby\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
8> '
9> set CONTROL_FILES 'd:\app\administrator\oradata\standby\control01.ctl', --注意这是存放备库控制文件的地方
10> 'd:\app\administrator\oradata\standby\control02.ctl', --一定要设置
11> 'd:\app\administrator\oradata\standby\control03.ctl'
12> ;
Starting Duplicate Db at 31-JUL-08
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK

contents of Memory Script.:
{
   backup as copy reuse
   file 'D:\app\Administrator\product\11.1.0\db_1\DATABASE\PWDprimary.ORA' auxiliary format
'D:\app\Administrator\product\11.1.0\db_1\DATABASE\PWDstandby.ORA'   file
'D:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILEPRIMARY.ORA' auxiliary format
'D:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILESTANDBY.ORA'   ;
   sql clone "alter system set spfile= ''D:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILESTANDBY.ORA''";
}
executing Memory Script

Starting backup at 31-JUL-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=139 device type=DISK
Finished backup at 31-JUL-08

sql statement: alter system set spfile= ''D:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILESTANDBY.ORA''

contents of Memory Script.:
{
   sql clone "alter system set db_unique_name =
''standby'' comment=
'''' scope=spfile";
   sql clone "alter system set FAL_CLIENT =
''standby'' comment=
'''' scope=spfile";
   sql clone "alter system set FAL_SERVER =
''primary'' comment=
'''' scope=spfile";
   sql clone "alter system set log_archive_dest_1 =
''LOCATION=d:\standby\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'' comment=
'''' scope=spfile";
   sql clone "alter system set CONTROL_FILES =
''d:\app\administrator\oradata\standby\control01.ctl'', ''d:\app\administrator\oradata\standby\control02.ctl'', ''d:\app\administrator\oradata\standby\control03.ctl'' comment=
'''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount ;
}
executing Memory Script

sql statement: alter system set db_unique_name = ''standby'' comment= '''' scope=spfile

sql statement: alter system set FAL_CLIENT = ''standby'' comment= '''' scope=spfile

sql statement: alter system set FAL_SERVER = ''primary'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''LOCATION=d:\standby\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'' comment= '''' scope=spfile

sql statement: alter system set CONTROL_FILES = ''d:\app\administrator\oradata\standby\control01.ctl'', ''d:\app\administrator\oradata\standby\control02.ctl'', ''d:\app\administrator\oradata\standby\control03.ctl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 431038464 bytes

Fixed Size                     1333676 bytes
Variable Size                255854164 bytes
Database Buffers             167772160 bytes
Redo Buffers                   6078464 bytes

contents of Memory Script.:
{
   backup as copy current controlfile for standby auxiliary format 'D:\APP\ADMINISTRATOR\ORADATA\STANDBY\CONTROL01.CTL';
   restore clone controlfile to 'D:\APP\ADMINISTRATOR\ORADATA\STANDBY\CONTROL02.CTL' from
'D:\APP\ADMINISTRATOR\ORADATA\STANDBY\CONTROL01.CTL';
   restore clone controlfile to 'D:\APP\ADMINISTRATOR\ORADATA\STANDBY\CONTROL03.CTL' from
'D:\APP\ADMINISTRATOR\ORADATA\STANDBY\CONTROL01.CTL';
   sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 31-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=D:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SNCFPRIMARY.ORA tag=TAG20080731T111834 RECID=14 STAMP=661519116
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 31-JUL-08

Starting restore at 31-JUL-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK

channel clone_default: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_DISK_1: copied control file copy
Finished restore at 31-JUL-08

Starting restore at 31-JUL-08
using channel ORA_AUX_DISK_1

channel clone_default: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_DISK_1: copied control file copy
Finished restore at 31-JUL-08

sql statement: alter database mount standby database

contents of Memory Script.:
{
   set newname for tempfile 1 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEMP01.DBF";
   switch clone tempfile all;
   set newname for datafile 1 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSTEM01.DBF";
   set newname for datafile 2 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSAUX01.DBF";
   set newname for datafile 3 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\UNDOTBS01.DBF";
   set newname for datafile 4 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\USERS01.DBF";
   set newname for datafile 5 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEST01_NEW.DBF";
   backup as copy reuse
   datafile 1 auxiliary format
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSTEM01.DBF"   datafile
2 auxiliary format
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSAUX01.DBF"   datafile
3 auxiliary format
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\UNDOTBS01.DBF"   datafile
4 auxiliary format
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\USERS01.DBF"   datafile
5 auxiliary format
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEST01_NEW.DBF"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEMP01.DBF in control file

executing command: SET NEWNAME

Starting backup at 31-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\SYSTEM01.DBF
output file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSTEM01.DBF tag=TAG20080731T111901 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\UNDOTBS01.DBF
output file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\UNDOTBS01.DBF tag=TAG20080731T111901 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\SYSAUX01.DBF
output file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSAUX01.DBF tag=TAG20080731T111901 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\TEST01_NEW.DBF
output file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEST01_NEW.DBF tag=TAG20080731T111901 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\USERS01.DBF
output file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\USERS01.DBF tag=TAG20080731T111901 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 31-JUL-08

sql statement: alter system archive log current

contents of Memory Script.:
{
   backup as copy reuse
   archivelog like "D:\PRIMARY\ARCHIVELOG\ARC00042_0661357304.001" auxiliary format
"D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001"   ;
   catalog clone archivelog "D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 31-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=42 RECID=58 STAMP=661519209
output file name=D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 31-JUL-08

cataloged archived log
archived log file name=D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001 RECID=1 STAMP=661519212

datafile 1 switched to datafile copy
input datafile copy RECID=14 STAMP=661519212 file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=15 STAMP=661519212 file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=16 STAMP=661519212 file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=17 STAMP=661519212 file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=18 STAMP=661519212 file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEST01_RMAN>

Recovery Manager complete.

{
   set until scn 282312;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 31-JUL-08
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 42 is already on disk as file D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001
archived log file name=D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001 thread=1 sequence=42
media recovery complete, elapsed time: 00:00:01
Finished recover at 31-JUL-08
Finished Duplicate Db at 31-JUL-08

7.登陆STANDBY数据库,进入到应用日志模式,并检查主库归档是否正常传到备库：

C:\Documents and Settings\Administrator>set oracle_sid=standby

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 31 11:23:33 2008

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
42 YES
43 YES

主库进行alter system switch logfile;
备库马上可以查询到
SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
        42 YES
        43 YES
        44 YES

SQL>

搭建成功

注意地方：
1.要是没拷贝密码文件
在执行duplicate命令时会报
Starting backup at 31-JUL-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/31/2008 12:28:23
RMAN-03015: error occurred in stored script. Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/31/2008 12:28:
23
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01031: insufficient privileges
ORA-17629: Cannot connect to the remote database server

2.要是执行duplicate 命令时没指定存放standby 参数控制文件存放位置会报以下错误:
contents of Memory Script.:
{
   backup as copy current controlfile for standby auxiliary format 'D:\APP\ADMI
NISTRATOR\ORADATA\PRIMARY\CONTROL01.CTL';
   restore clone controlfile to 'D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\CONTROL02
.CTL' from
'D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\CONTROL01.CTL';
   restore clone controlfile to 'D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\CONTROL03
.CTL' from
'D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\CONTROL01.CTL';
   sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 31-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/31/2008 12:35:22
RMAN-03015: error occurred in stored script. Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/31/2008 12:35:
22
ORA-17628: Oracle error 19505 returned by remote Oracle server

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-416185/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-416185/