通过网络复制库并直接转储到ASM上

wonder

A机器：oracle 11.2.0.1.0  rhel 5.4   使用ASM存储   IP 192.168.8.13
B机器：oracle 11.2.0.1.0  rhel 5.4   使用文件系统存储   IP  192.168.8.11

现在的情况是：
A上只安装了ORACLE 软件，没建立库，在grid用户下建立了ASM磁盘组+DATA ；B上安装了ORACLE软件，并建立了g_chris的库，实例名为chris，并开启了归档

目的：
将B机器上的g_chris库迁移到A机器上。

步骤：

A机器上配置：
[oracle@master admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_F =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = master.wonder.com)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER_F = /u01/app
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = g_chris)
      (ORACLE_HOME = /u01/app/oracle)
      (SID_NAME = chris)
    )
  )


[oracle@master admin]$ cat tnsnames.ora
g_chris =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = master.wonder.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = g_chris)
    )
  )
T_filesystem=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = chris)
      (SERVER = DEDICATED)
    )
  )

[oracle@master dbs]$ cat initchris.ora
DB_NAME=g_chris
db_file_name_convert = ('/u01/app/oradata/g_chris/','+DATA')
log_file_name_convert = ('/u01/app/oradata/g_chris/','+DATA')

B机器上：
[oracle@filesystem admin]$ cat tnsnames.ora
g_chris =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = filesystem.wonder.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = g_chris)
    )
  )
T_master=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.13)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = chris)
      (SERVER = DEDICATED)
    )
  )


现在在A机器上通过网络直接复制B机器上g_chris库到A机器上：

[oracle@master ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 25 13:37:52 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/dbs/initchris.ora
ORACLE instance started.
Total System Global Area  146472960 bytes
Fixed Size      1335080 bytes
Variable Size     92274904 bytes
Database Buffers    50331648 bytes
Redo Buffers      2531328 bytes
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@master ~]$ rman target [email=sys/sysoracle@t_filesystem]sys/sysoracle@t_filesystem[/email] auxiliary [email=sys/sysoracle@g_chris]sys/sysoracle@g_chris[/email]
Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 25 13:38:38 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: G_CHRIS (DBID=1374986122)
connected to auxiliary database: G_CHRIS (not mounted)
RMAN> duplicate target database to g_chris from active database;
Starting Duplicate Db at 25-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     146472960 bytes
Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
contents of Memory Script:
{
   sql clone "alter system set  db_name =
''G_CHRIS'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
''G_CHRIS'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/dbs/cntrlchris.dbf';
   alter clone database mount;
}
executing Memory Script
sql statement: alter system set  db_name =  ''G_CHRIS'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set  db_unique_name =  ''G_CHRIS'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area     146472960 bytes
Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
Starting backup at 25-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/dbs/snapcf_chris.f tag=TAG20120525T133917 RECID=6 STAMP=784215558
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-MAY-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/25/2012 13:39:18
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-00205: error in identifying control file, check alert log for more info
RMAN>

告警日志：
................

RECO started with pid=14, OS id=4542
Fri May 25 13:39:16 2012
MMON started with pid=15, OS id=4544
Fri May 25 13:39:16 2012
MMNL started with pid=16, OS id=4546
ORACLE_BASE from environment = /u01/app
Fri May 25 13:39:18 2012
alter database mount
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/dbs/cntrlchris.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: alter database mount...

说明：B机器上确实产生了/u01/app/oracle/dbs/cntrlchris.dbf这个文件，但没传输到A机器上，导致mount时找不到控制文件？请问我上面的步骤问题出在哪里？

[ 本帖最后由 wonder 于 2012-5-25 15:50 编辑 ]

wonder

上面运行
rman target sys/sysoracle@t_filesystem auxiliary [email=sys/sysoracle@g_chris]sys/sysoracle@g_chris[/email] ，此命令是在A机器（空库）上运行的，之后
我在B机器上运行：
[oracle@filesystem admin]$ rman target [email=sys/sysoracle@g_chris]sys/sysoracle@g_chris[/email] auxiliary [email=sys/sysoracle@t_master]sys/sysoracle@t_master[/email]

Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 25 14:32:52 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: G_CHRIS (DBID=1374986122)
connected to auxiliary database: G_CHRIS (not mounted)
RMAN> duplicate target database to g_chris from active database;
Starting Duplicate Db at 25-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     146472960 bytes
Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
contents of Memory Script:
{
   sql clone "alter system set  db_name =
''G_CHRIS'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
''G_CHRIS'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/dbs/cntrlchris.dbf';
   alter clone database mount;
}
executing Memory Script
sql statement: alter system set  db_name =  ''G_CHRIS'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set  db_unique_name =  ''G_CHRIS'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area     146472960 bytes
Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
Starting backup at 25-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/dbs/snapcf_chris.f tag=TAG20120525T143320 RECID=8 STAMP=784218800
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-MAY-12
database mounted
contents of Memory Script:
{
   set newname for datafile  1 to
"+DATA/system01.dbf";
   set newname for datafile  2 to
"+DATA/sysaux01.dbf";
   set newname for datafile  3 to
"+DATA/undotbs01.dbf";
   set newname for datafile  4 to
"+DATA/users01.dbf";
   set newname for datafile  5 to
"+DATA/example01.dbf";
   set newname for datafile  6 to
"+DATA/zaodian01.dbf";
   set newname for datafile  7 to
"+DATA/flink01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
"+DATA/system01.dbf"   datafile
2 auxiliary format
"+DATA/sysaux01.dbf"   datafile
3 auxiliary format
"+DATA/undotbs01.dbf"   datafile
4 auxiliary format
"+DATA/users01.dbf"   datafile
5 auxiliary format
"+DATA/example01.dbf"   datafile
6 auxiliary format
"+DATA/zaodian01.dbf"   datafile
7 auxiliary format
"+DATA/flink01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 25-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oradata/g_chris/zaodian01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/25/2012 14:33:33
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oradata/g_chris/system01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/25/2012 14:33:34
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oradata/g_chris/flink01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/25/2012 14:33:37
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oradata/g_chris/sysaux01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/25/2012 14:33:38
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oradata/g_chris/example01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/25/2012 14:33:40
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oradata/g_chris/undotbs01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/25/2012 14:33:41
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oradata/g_chris/users01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/25/2012 14:33:42
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/25/2012 14:33:42
ORA-17628: Oracle error 19505 returned by remote Oracle server



[ 本帖最后由 wonder 于 2012-5-25 15:49 编辑 ]

wonder

A机器的告警日志：

Fri May 25 14:33:20 2012
alter database mount
Set as converted control file due to db_unique_name mismatch
Changing di2dbun from g_chris to G_CHRIS
Successful mount of redo thread 1, with mount id 1375765616
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
Fri May 25 14:33:25 2012
Starting background process ASMB
Fri May 25 14:33:26 2012
ASMB started with pid=20, OS id=5148
Starting background process RBAL
Fri May 25 14:33:26 2012
RBAL started with pid=21, OS id=5152
NOTE: initiating MARK startup
Starting background process MARK
Fri May 25 14:33:26 2012
MARK started with pid=22, OS id=5154
NOTE: MARK has subscribed
Errors in file /u01/app/diag/rdbms/g_chris/chris/trace/chris_rbal_5152.trc:
ORA-15183: ASMLIB initialization error [driver/agent not installed]
WARNING: FAILED to load library: /opt/oracle/extapi/32/asm/orcl/1/libasm.so
Errors in file /u01/app/diag/rdbms/g_chris/chris/trace/chris_rbal_5152.trc:
ORA-15183: ASMLIB initialization error [driver/agent not installed]
SUCCESS: diskgroup DATA was mounted
Errors in file /u01/app/diag/rdbms/g_chris/chris/trace/chris_ora_5146.trc:
ORA-19505: failed to identify file "+DATA/zaodian01.dbf"
ORA-15012: ASM file 'zaodian01.dbf' does not exist
ORA-17502: ksfdcre:5 Failed to create file +DATA/zaodian01.dbf
ORA-15081: failed to submit an I/O operation to a disk
Fri May 25 14:33:33 2012
Errors in file /u01/app/diag/rdbms/g_chris/chris/trace/chris_ora_5168.trc:
ORA-19505: failed to identify file "+DATA/system01.dbf"
ORA-15012: ASM file 'system01.dbf' does not exist
ORA-17502: ksfdcre:5 Failed to create file +DATA/system01.dbf
ORA-15081: failed to submit an I/O operation to a disk
Fri May 25 14:33:35 2012
Errors in file /u01/app/diag/rdbms/g_chris/chris/trace/chris_ora_5170.trc:
ORA-19505: failed to identify file "+DATA/flink01.dbf"
ORA-15012: ASM file 'flink01.dbf' does not exist
ORA-17502: ksfdcre:5 Failed to create file +DATA/flink01.dbf
ORA-15081: failed to submit an I/O operation to a disk
Fri May 25 14:33:37 2012
Errors in file /u01/app/diag/rdbms/g_chris/chris/trace/chris_ora_5172.trc:
ORA-19505: failed to identify file "+DATA/sysaux01.dbf"
ORA-15012: ASM file 'sysaux01.dbf' does not exist
ORA-17502: ksfdcre:5 Failed to create file +DATA/sysaux01.dbf
ORA-15081: failed to submit an I/O operation to a disk
Fri May 25 14:33:38 2012
Errors in file /u01/app/diag/rdbms/g_chris/chris/trace/chris_ora_5174.trc:
ORA-19505: failed to identify file "+DATA/example01.dbf"
ORA-15012: ASM file 'example01.dbf' does not exist
ORA-17502: ksfdcre:5 Failed to create file +DATA/example01.dbf
ORA-15081: failed to submit an I/O operation to a disk
Fri May 25 14:33:39 2012
Errors in file /u01/app/diag/rdbms/g_chris/chris/trace/chris_ora_5176.trc:
ORA-19505: failed to identify file "+DATA/undotbs01.dbf"
ORA-15012: ASM file 'undotbs01.dbf' does not exist
ORA-17502: ksfdcre:5 Failed to create file +DATA/undotbs01.dbf
ORA-15081: failed to submit an I/O operation to a disk
Fri May 25 14:33:40 2012
Errors in file /u01/app/diag/rdbms/g_chris/chris/trace/chris_ora_5178.trc:
ORA-19505: failed to identify file "+DATA/users01.dbf"
ORA-15012: ASM file 'users01.dbf' does not exist
ORA-17502: ksfdcre:5 Failed to create file +DATA/users01.dbf
ORA-15081: failed to submit an I/O operation to a disk

在A机器的grid用户下查看，有DATA磁盘组：

[grid@master ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 25 14:47:14 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option
SQL> select name from v$asm_diskgroup;
NAME
------------------------------
DATA

SQL> select name from v$asm_disk;      
NAME
------------------------------
VOLB
VOLC
VOLD

但为什么会报错呢？

wonder

在斩月的帮助下问题已经解决：

主要原因是数据库的实例无法在ASM注册导致的，数据库的监听没有配置在grid用户下

[grid@master ~]$ vim /u02/app/grid/network/admin/listener.ora  --添加如下行

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = g_chris)
      (ORACLE_HOME = /u01/app/oracle)
      (SID_NAME = chris)
    )
  )

[grid@master ~]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 25-MAY-2012 18:09:37
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

[grid@master ~]$ lsnrctl services
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 25-MAY-2012 17:43:03
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "g_chris" has 2 instance(s).
  Instance "chris", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:14 refused:0
         LOCAL SERVER
  Instance "chris", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:6 refused:0 state:ready
         LOCAL SERVER

再运行复制命令即可

Maclean Liu(刘相兵

Good article !