duplicate 数据库 from active database [oracle 11.2.0.3 + asm] => [oracle 11.2.0.3 + asm]详解数据库

参考自己博客的这个安装 http://blog.csdn.net/ctypyb2002/article/details/51251217 安装好另一台机器的rhel6.4,gi software,rdbms software。

创建一个 +ASM 实例 用过asmca 创建了一个可用的磁盘组。

不要用DBCA创建数据库,因为要duplicate 数据库。只要安装好 rdbms software 就OK了。

vi /etc/hosts 

10.1.1.35 asmnode

10.1.1.36 asmnodedup

在asmnode 节点 查看initdbasm.ora 文件 

[[email protected] dbs]$ pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

[[email protected] dbs]$ ls -l

total 28

-rw-rw—- 1 oracle asmadmin 1544 Apr 25 21:49 hc_dbasm.dat

-rw-r—– 1 oracle oinstall   40 Apr 25 21:49 initdbasm.ora

-rw-r–r– 1 oracle oinstall 2851 May 15  2009 init.ora

-rw-r—– 1 oracle asmadmin   24 Apr 25 21:06 lkDBASM

-rw-r—– 1 oracle oinstall 1536 Apr 25 21:06 orapwdbasm

[[email protected] dbs]$ cat initdbasm.ora 

SPFILE=’+DG_DATA/dbasm/spfiledbasm.ora’

在asmnode 节点 用oracle 用户创建 参数文件 abc.ora 文件

[[email protected] dbs]$ sqlplus / as sysdba;

sql> create pfile=’?/dbs/abc.ora’ from memory;

在 asmnodedup 节点 创建pfile参数文件

export ORACLE_SID = dbasmd

[[email protected] dbs]$ vi /u01/app/grid/product/11.2.0/grid_1/dbs/initdbasmd.ora

audit_file_dest=’/u01/app/oracle/admin/dbasmd/adump’

audit_trail=’DB’

#background_dump_dest=’/u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace’ #Deprecate parameter

#core_dump_dest=’/u01/app/oracle/diag/rdbms/dbasmd/dbasmd/cdump’

#user_dump_dest=’/u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace’ #Deprecate parameter

#control_files=’+DG_DATA/DBASMD/CONTROLFILE/’

compatible=’11.2.0.0.0′

db_block_size=8192

db_create_file_dest=’+DG_DATA’

db_domain=”

db_name=’dbasmd’

diagnostic_dest=’/u01/app/oracle’

dispatchers='(PROTOCOL=TCP) (SERVICE=dbasmdXDB)’

log_buffer=7020544 # log buffer update

memory_target=1536M

open_cursors=300

optimizer_dynamic_sampling=2

optimizer_mode=’ALL_ROWS’

plsql_warnings=’DISABLE:ALL’ # PL/SQL warnings at init.ora

processes=300

sessions=472

query_rewrite_enabled=’TRUE’

remote_login_passwordfile=’EXCLUSIVE’

resource_manager_plan=”

result_cache_max_size=3936K

skip_unusable_indexes=TRUE

undo_tablespace=’UNDOTBS1′

#db_file_name_convert=(‘+dg_data/dbasm/datafile/’,’+dg_data/dbasmd/datafile/’)
#log_file_name_convert=(‘+dg_data/dbasm/onlinelog/’,’+dg_data/dbasmd/onlinelog/’)

如果
auxiliary 是 asm,只需要修改
db_create_file_dest,不需要修改db_file_name_convert,log_file_name_convert

在 asmnodedup 节点 创建必要的目录

mkdir -p /u01/app/oracle/admin/dbasmd/adump

mkdir -p /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace

mkdir -p /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/cdump

需要保证target和auxiliary 库的sys用户口令相同。

从asmnode 拷贝原数据库的密码文件到 asmnodedup 节点 oracle 用户 $ORACLE_HOME/dbs 目录下,然后重命名

[[email protected] dbs]$
scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwdbasm [email protected]:/u01/app/oracle/product/11.2.0/db_1/dbs 

[[email protected] dbs]$
mv orapwdbasm orapwdbasmd

或者在 asmnodedup 使用 orapw 创建一个和target database 的sys一样密码的密码文件。

在 asmnodedup 节点 grid 用户 用asmcd 查看一下

[[email protected] bin]$
./asmcmd

ASMCMD>
ls -l

State    Type    Rebal  Name

MOUNTED  EXTERN  N      DG_DATA/

静态监听,两个节点都要添加


asmnode 节点  grid 用户下添加
静态监听

[[email protected] admin]$ vi /u01/app/grid/product/11.2.0/grid_1/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

       (SID_DESC =

          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

          (SID_NAME =dbasm)

        )

  )


asmnodedup 节点  grid 用户下添加
静态监听

[[email protected] admin]$ vi /u01/app/grid/product/11.2.0/grid_1/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

        (SID_DESC =

           (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

           (SID_NAME =dbasmd)

         )

  )

在 asmnode, asmnodedup 节点 重启监听,看如输出状态为
UNKNOWN 就表示静态监听已添加成功  

[[email protected] admin]$ lsnrctl stop

[[email protected] admin]$ lsnrctl start

Services Summary…

Service “dbasmd” has 1 instance(s).

  Instance “dbasmd”, status
UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

在 asmnode,asmnodedup 节点, oracle 用户下 添加 tnsnames.ora 文件

tns_dbasm =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = asmnode)(PORT = 1521))

    (CONNECT_DATA =

       (SERVER = DEDICATED)

       (SERVICE_NAME = dbasm)

    )

   )

tns_dbasmd =                 

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = asmnodedup)(PORT = 1521))

    (CONNECT_DATA =

       (SERVER = DEDICATED)

       (SERVICE_NAME = dbasmd)

    )

)  

在 asmnodedup 节点 oracle 用户  

[[email protected] dbs]$
sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 6 15:52:22 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL>
conn / as sysdba;

Connected to an idle instance.

SQL>
startup nomount pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initdbasmd.ora’;

ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated

ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size
   2228784 bytes

Variable Size
 956304848 bytes

Database Buffers
 637534208 bytes

Redo Buffers
   7344128 bytes

前期没有屏蔽掉,就把 BACKGROUND_DUMP_DEST,USER_DUMP_DEST 这两个参数屏蔽掉,重新启动实例到 nomount 状态下

在 asmnodedup 节点 需要创建spfile,否则后面会报错 ORA-32001: write to SPFILE requested but no SPFILE is in use

SQL>
create spfile from  pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initdbasmd.ora’;

在 asmnodedup 节点 开始复制, auxiliary 需要启动到 nomount状态, 否则duplicate时就会报错

MAN-04006: error from auxiliary database: ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

[[email protected] dbs]$
rman target [email protected]_dbasm auxiliary [email protected]_dbasmd

Recovery Manager: Release 11.2.0.3.0 – Production on Fri May 6 16:12:29 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBASM (DBID=2252470157)

connected to auxiliary database: DBASMD (not mounted)

RMAN>
duplicate target database to dbasmd from active database ;

Starting Duplicate Db at 06-MAY-16

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=10 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    1603411968 bytes

Fixed Size                     2228784 bytes

Variable Size                956304848 bytes

Database Buffers             637534208 bytes

Redo Buffers                   7344128 bytes

contents of Memory Script:

{

   sql clone “alter system set  control_files = 

  ”+DG_DATA/dbasmd/controlfile/current.271.911150235” comment=

 ”Set by RMAN” scope=spfile”;

   sql clone “alter system set  db_name = 

 ”DBASM” comment=

 ”Modified by RMAN duplicate” scope=spfile”;

   sql clone “alter system set  db_unique_name = 

 ”DBASMD” comment=

 ”Modified by RMAN duplicate” scope=spfile”;

   shutdown clone immediate;

   startup clone force nomount

   backup as copy current controlfile auxiliary format  ‘+DG_DATA/dbasmd/controlfile/current.270.911150235’;

   sql clone “alter system set  control_files = 

  ”+DG_DATA/dbasmd/controlfile/current.270.911150235” comment=

 ”Set by RMAN” scope=spfile”;

   shutdown clone immediate;

   startup clone nomount;

   alter clone database mount;

}

executing Memory Script

sql statement: alter system set  control_files =   ”+DG_DATA/dbasmd/controlfile/current.271.911150235” comment= ”Set by RMAN” scope=spfile

sql statement: alter system set  db_name =  ”DBASM” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set  db_unique_name =  ”DBASMD” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1603411968 bytes

Fixed Size                     2228784 bytes

Variable Size                956304848 bytes

Database Buffers             637534208 bytes

Redo Buffers                   7344128 bytes

Starting backup at 06-MAY-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=255 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_dbasm.f tag=TAG20160506T171250 RECID=4 STAMP=911149970

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 06-MAY-16

sql statement: alter system set  control_files =   ”+DG_DATA/dbasmd/controlfile/current.270.911150235” comment= ”Set by RMAN” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1603411968 bytes

Fixed Size                     2228784 bytes

Variable Size                956304848 bytes

Database Buffers             637534208 bytes

Redo Buffers                   7344128 bytes

database mounted

contents of Memory Script:

{

   set newname for clone datafile  1 to new;

   set newname for clone datafile  2 to new;

   set newname for clone datafile  3 to new;

   set newname for clone datafile  4 to new;

   backup as copy reuse

   datafile  1 auxiliary format new

   datafile  2 auxiliary format new

   datafile  3 auxiliary format new

   datafile  4 auxiliary format new

   ;

   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

Starting backup at 06-MAY-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DG_DATA/dbasm/datafile/undotbs1.262.910127191

output file name=+DG_DATA/dbasmd/datafile/undotbs1.269.911150263 tag=TAG20160506T171309

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DG_DATA/dbasm/datafile/system.260.910127185

output file name=+DG_DATA/dbasmd/datafile/system.268.911150279 tag=TAG20160506T171309

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DG_DATA/dbasm/datafile/sysaux.261.910127189

output file name=+DG_DATA/dbasmd/datafile/sysaux.267.911150285 tag=TAG20160506T171309

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DG_DATA/dbasm/datafile/users.264.910127201

output file name=+DG_DATA/dbasmd/datafile/users.266.911150293 tag=TAG20160506T171309

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02

Finished backup at 06-MAY-16

sql statement: alter system archive log current

contents of Memory Script:

{

   backup as copy reuse

   archivelog like  “+DG_DATA/dbasm_archivelog/1_83_910127181.dbf” auxiliary format 

 “/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_83_910127181.dbf”   ;

   catalog clone archivelog  “/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_83_910127181.dbf”;

   switch clone datafile all;

}

executing Memory Script

Starting backup at 06-MAY-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=83 RECID=2 STAMP=911150020

output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_83_910127181.dbf RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 06-MAY-16

cataloged archived log

archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_83_910127181.dbf RECID=2 STAMP=911150294

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=911150294 file name=+DG_DATA/dbasmd/datafile/system.268.911150279

datafile 2 switched to datafile copy

input datafile copy RECID=5 STAMP=911150294 file name=+DG_DATA/dbasmd/datafile/sysaux.267.911150285

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=911150294 file name=+DG_DATA/dbasmd/datafile/undotbs1.269.911150263

datafile 4 switched to datafile copy

input datafile copy RECID=7 STAMP=911150294 file name=+DG_DATA/dbasmd/datafile/users.266.911150293

contents of Memory Script:

{

   set until scn  1436619;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 06-MAY-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=13 device type=DISK

starting media recovery

archived log for thread 1 with sequence 83 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_83_910127181.dbf

archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_83_910127181.dbf thread=1 sequence=83

media recovery complete, elapsed time: 00:00:00

Finished recover at 06-MAY-16

Oracle instance started

Total System Global Area    1603411968 bytes

Fixed Size                     2228784 bytes

Variable Size                956304848 bytes

Database Buffers             637534208 bytes

Redo Buffers                   7344128 bytes

contents of Memory Script:

{

   sql clone “alter system set  db_name = 

 ”DBASMD” comment=

 ”Reset to original value by RMAN” scope=spfile”;

   sql clone “alter system reset  db_unique_name scope=spfile”;

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

sql statement: alter system set  db_name =  ”DBASMD” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1603411968 bytes

Fixed Size                     2228784 bytes

Variable Size                956304848 bytes

Database Buffers             637534208 bytes

Redo Buffers                   7344128 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE “DBASMD” RESETLOGS ARCHIVELOG 

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP   1  SIZE 50 M ,

  GROUP   2  SIZE 50 M ,

  GROUP   3  SIZE 50 M 

 DATAFILE

  ‘+DG_DATA/dbasmd/datafile/system.268.911150279’

 CHARACTER SET ZHS16GBK

contents of Memory Script:

{

   set newname for clone tempfile  1 to new;

   switch clone tempfile all;

   catalog clone datafilecopy  “+DG_DATA/dbasmd/datafile/sysaux.267.911150285”, 

 “+DG_DATA/dbasmd/datafile/undotbs1.269.911150263”, 

 “+DG_DATA/dbasmd/datafile/users.266.911150293”;

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DG_DATA in control file

cataloged datafile copy

datafile copy file name=+DG_DATA/dbasmd/datafile/sysaux.267.911150285 RECID=1 STAMP=911150313

cataloged datafile copy

datafile copy file name=+DG_DATA/dbasmd/datafile/undotbs1.269.911150263 RECID=2 STAMP=911150313

cataloged datafile copy

datafile copy file name=+DG_DATA/dbasmd/datafile/users.266.911150293 RECID=3 STAMP=911150313

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=911150313 file name=+DG_DATA/dbasmd/datafile/sysaux.267.911150285

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=911150313 file name=+DG_DATA/dbasmd/datafile/undotbs1.269.911150263

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=911150313 file name=+DG_DATA/dbasmd/datafile/users.266.911150293

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 06-MAY-16

至此,复制完成,用sqlplus 登陆 

[[email protected] bin]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 9 16:55:37 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL>
select sysdate from dual;


SYSDATE
————
09-MAY-16

至此 duplicate 数据库完成

其实 duplicate 还可以有一些灵活的格式,但是from active database 必需是 auxiliary database 在 nomount 状态。

RMAN>
duplicate target database to dbasmd from active database ;

RMAN> RUN{   

 DUPLICATE TARGET DATABASE TO dbasmd


PFILE ?/dbs/initdbasmd.ora
from active database;


}

/*********************************************************************************/

下面记录的是一些错误及处理方法

第一次duplicate 完成后用sqlplus 验证

[[email protected] ~]$
sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 9 16:00:18 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn / as sysdba;

ERROR:

ORA-01075: you are currently logged on

SQL> 

查看alert 日志

ORA-01565: Unable to open Spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfiledbasmd.ora.

Errors in file /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace/dbasmd_mmon_13799.trc:

ORA-01565: error in identifying file ‘/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledbasmd.ora’

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-01565: Unable to open Spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfiledbasmd.ora.

Errors in file /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace/dbasmd_mmon_13799.trc:

ORA-01565: error in identifying file ‘/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledbasmd.ora’

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

查看 dbasmd_mmon_13799.trc 文件

[[email protected] trace]$ cat /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace/dbasmd_mmon_13799.trc

*** 2016-05-09 12:39:03.508

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)

—– Error Stack Dump —–

ORA-01565: error in identifying file ‘/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledbasmd.ora’

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Info for error 1565 while doing parameter updates.

貌似是 $ORACLE_HOME/dbs/spfiledbasmd.ora 文件不存在,参考这个哥们的文章删掉实例 http://blog.itpub.net/519536/viewspace-669393/ 

启动实例到nomount状态

[[email protected] dbs]$
sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 9 16:19:00 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL>
conn / as sysdba;

Connected to an idle instance.

SQL>
startup nomount pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initdbasmd.ora’;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size
   2228784 bytes

Variable Size
 956304848 bytes

Database Buffers
 637534208 bytes

Redo Buffers
   7344128 bytes

SQL>
alter database mount;

alter database mount

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 16035

Session ID: 246 Serial number: 1

查看 alert 日志

Mon May 09 16:19:51 2016

alter database mount

Mon May 09 16:19:51 2016

NOTE: Loaded library: System 

ORA-15025: could not open disk “/dev/asm-diskb1”

ORA-27041: unable to open file

Linux-x86_64 Error: 13: Permission denied

Additional information: 9

ORA-15025: could not open disk “/dev/asm-diskb3”

ORA-27041: unable to open file

Linux-x86_64 Error: 13: Permission denied

Additional information: 9

Mon May 09 16:19:51 2016

SUCCESS: diskgroup DG_DATA was mounted

Mon May 09 16:19:51 2016

ERROR: failed to establish dependency between database dbasmd and diskgroup resource ora.DG_DATA.dg

Errors in file /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace/dbasmd_ckpt_16011.trc  (incident=144111):

ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/incident/incdir_144111/dbasmd_ckpt_16011_i144111.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace/dbasmd_ckpt_16011.trc  (incident=144112):

ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/incident/incdir_144112/dbasmd_ckpt_16011_i144112.trc

Mon May 09 16:19:53 2016

Dumping diagnostic data in directory=[cdmp_20160509161953], requested by (instance=1, osid=16011 (CKPT)), summary=[incident=144111].

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process 16011 

Dumping diagnostic data in directory=[cdmp_20160509161954], requested by (instance=1, osid=16011 (CKPT)), summary=[incident=144112].

Mon May 09 16:19:54 2016

PMON (ospid: 15989): terminating the instance due to error 469

System state dump requested by (instance=1, osid=15989 (PMON)), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace/dbasmd_diag_15999.trc

Dumping diagnostic data in directory=[cdmp_20160509161955], requested by (instance=1, osid=15989 (PMON)), summary=[abnormal instance termination].

Instance terminated by PMON, pid = 15989

关键错误

ORA-15025: could not open disk “/dev/asm-diskb1”

ORA-27041: unable to open file

Linux-x86_64 Error: 13: Permission denied

在网上搜索说是oracle 用户下的这个文件 $ORACLE_HOME/bin/oracle 的权限不对导致的

[[email protected] bin]$ ls -l 

-rwxr-x–x 1 oracle oinstall 232399123 Apr 25 18:37 oracle

oracle 用户 $ORACLE_HOME/bin/oracle 这个文件的权限应该是这样

[[email protected] bin]$ ls -l | grep -i asmadmin

-rwsr-s–x 1 oracle asmadmin 232399123 Apr 25 18:37 oracle

在root 用户下修改相应的权限。

语法:chmod [who] [+ | – | =] [mode] 文件名

命令中各选项的含义为:

操作对象who可是下述字母中的任一个或者它们的组合:

  u 表示“用户(user)”,即文件或目录的所有者。

  g 表示“同组(group)用户”,即与文件属主有相同组ID的所有用户。

  o 表示“其他(others)用户”。

  a 表示“所有(all)用户”。它是系统默认值。

操作符号可以是:

  + 添加某个权限。

  - 取消某个权限。

  = 赋予给定权限并取消其他所有权限(如果有的话)。

设置 mode 所表示的权限可用下述字母的任意组合:

  r 可读。

  w 可写。

   x 可执行。

  X 只有目标文件对某些用户是可执行的或该目标文件是目录时才追加x 属性。

  s 在文件执行时把进程的属主或组ID置为该文件的文件属主。

      方式“u+s”设置文件的用户ID位,“g+s”设置组ID位。

  t 保存程序的文本到交换设备上。

  u 与文件属主拥有一样的权限。

  g 与和文件属主同组的用户拥有一样的权限。

  o 与其他用户拥有一样的权限。

文件名:以空格分开的要改变权限的文件列表,支持通配符。

/*********************************************************************************/

把duplicate 数据库删除,想重新操作一遍时又碰到如下这些错误

1) 

[[email protected] dbs]$
rman target [email protected]_dbasm auxiliary [email protected]_dbasmd

Recovery Manager: Release 11.2.0.3.0 – Production on Sat May 7 11:13:32 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBASM (DBID=2252470157)

connected to auxiliary database: DBASMD (not mounted)

RMAN>
duplicate target database to dbasmd from active database ;

Starting Duplicate Db at 07-MAY-16

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=12 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 05/07/2016 11:13:52

RMAN-05501: aborting duplication of target database

RMAN-06136: ORACLE error from auxiliary database: ORA-03113: end-of-file on communication channel

Process ID: 30689

Session ID: 10 Serial number: 15

查看数据库的alert 日志,发下如下错误

Sat May 07 11:30:05 2016

Sweep [inc][98572]: completed

Sweep [inc][98571]: completed

Sweep [inc2][98572]: completed

Sweep [inc2][98571]: completed

Sat May 07 11:30:10 2016

SUCCESS: diskgroup DG_DATA was dismounted

Sat May 07 11:33:05 2016

ORA-15025: could not open disk “/dev/asm-diskb1”

ORA-27041: unable to open file

Linux-x86_64 Error: 13: Permission denied

Additional information: 9

ORA-15025: could not open disk “/dev/asm-diskb3”

ORA-27041: unable to open file

Linux-x86_64 Error: 13: Permission denied

Additional information: 9

Sat May 07 11:33:05 2016

SUCCESS: diskgroup DG_DATA was mounted

Errors in file /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace/dbasmd_ora_4735.trc  (incident=98573):

ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/incident/incdir_98573/dbasmd_ora_4735_i98573.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace/dbasmd_ora_4735.trc  (incident=98574):

ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/incident/incdir_98574/dbasmd_ora_4735_i98574.trc

Sat May 07 11:33:07 2016

Dumping diagnostic data in directory=[cdmp_20160507113307], requested by (instance=1, osid=4735), summary=[incident=98573].

Sat May 07 11:33:08 2016

Sweep [inc][98574]: completed

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Dumping diagnostic data in directory=[cdmp_20160507113308], requested by (instance=1, osid=4735), summary=[incident=98574].

Sweep [inc][98573]: completed

Sweep [inc2][98574]: completed

Sweep [inc2][98573]: completed

ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process 4735 

Sat May 07 11:33:19 2016

SUCCESS: diskgroup DG_DATA was dismounted

也是 oracle 用户下$ORACLE_HOME/bin/oracle  的授权问题。

[[email protected] ~]# chown
oracle:asmadmin /u01/app/oracle/product/11.2.0/db_1/bin/
oracle

[[email protected] ~]# chown
u+s /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[[email protected] ~]# chown
g+s /u01/app/oracle/product/11.2.0/db_1/bin/oracle

2)

[[email protected] ~]$ rman target [email protected]_dbasm auxiliary [email protected]_dbasmd

Recovery Manager: Release 11.2.0.3.0 – Production on Sat May 7 12:14:23 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBASM (DBID=2252470157)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04006: error from auxiliary database: ORA-12537: TNS:connection closed

看错误提示应该是auxiliary database的问题,检查了 静态监听和tns,没发现什么问题

用rman 的 debug 来看一下是哪出错了。

rman target [email protected]_dbasm auxiliary [email protected]_dbasmd debug

DBGRCV:     EXITED krmkgetdb with status 1

RMAN-06005: connected to target database: DBASM (DBID=2252470157)

DBGRPC:     krmxt – terminating krmx layer

DBGRPC:     krmxt – destroying context for channel default

DBGRPC:     krmxcd – removing context for channel default, keep=0

DBGRPC:     krmxcd – closing target default connection

DBGMISC:    ENTERED krmkmrsr [12:32:54.731]

DBGSQL:      ENTERED krmkosqlerr

测试分开连接,发现到 auxiliary 的数据库连不上 

[[email protected] dbs]$ rman target [email protected]_dbasm 

Recovery Manager: Release 11.2.0.3.0 – Production on Sat May 7 13:05:32 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBASM (DBID=2252470157)

RMAN> exit

Recovery Manager complete.

[[email protected] dbs]$ rman target [email protected]_dbasmd 

Recovery Manager: Release 11.2.0.3.0 – Production on Sat May 7 13:05:40 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04005: error from target database: 

ORA-12537: TNS:connection closed

那么问题就定位到 auxiliary database 的 ORA-12537: TNS:connection closed 上了

查看监听日志时发现 

<msg time=’2016-05-07T13:19:10.194+08:00′ org_id=’oracle’ comp_id=’tnslsnr’

 type=’UNKNOWN’ level=’16’ host_id=’asmnodedup’

 host_addr=’10.1.1.36′>

 <txt>07-MAY-2016 13:19:10 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dbasmd)(CID=([email protected])(HOST=asmnodedup)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.36)(PORT=54429)) * establish * dbasmd * 12518

 </txt>

</msg>

<msg time=’2016-05-07T13:19:10.194+08:00′ org_id=’oracle’ comp_id=’tnslsnr’

 type=’UNKNOWN’ level=’16’ host_id=’asmnodedup’

 host_addr=’10.1.1.36′>

 <txt>
TNS-12518: TNS:listener could not hand off client connection

 TNS-12547: TNS:lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contact

   
Linux Error: 32: Broken pipe

 </txt>

</msg>

同时看 lsnrctl servives

Service “dbasmd” has 2 instance(s).

  Instance “dbasmd”, status UNKNOWN, has 1 handler(s) for this service…

    Handler(s):

      “DEDICATED” established:0
refused:8

         LOCAL SERVER

尝试下删除监听,创建新监听看看,依旧不行。

参考 MOS 的文章

‘ORA-12537: TNS:connection closed’ Errors Connecting To Oracle11g R1 on Linux via Oracle Net (文档 ID 733737.1)

ORA-12537 / ORA-12547 or TNS-12518 if Listener (including SCAN Listener) and Database are Owned by Different OS User (文档 ID 1069517.1)

Local SQL*Plus Connection and DBCA Fails With: ORA-12547: TNS:Lost Contact (文档 ID 422173.1)



/**********************************/

The output should show the correct permission which is:

-rwsr-s–x 1 oracle asmadmin

If not, then please execute the following to correct the permissions:

$ cd $ORACLE_HOME/bin

# chmod 6751 oracle

$ ls -l oracle

/**********************************/

在root下执行了 chmod 6751 oracle 终于解决了 Linux Error: 32: Broken pipe 问题。

第二个问题是第一个问题的延续

正确的文件权限如下:

[[email protected] ~]$ ls -l $ORACLE_HOME/bin/oracle

-rwsr-s–x 1 grid oinstall 203972157 Apr 25 18:28 /u01/app/grid/product/11.2.0/grid_1/bin/oracle

[[email protected] ~]$ ls -l $ORACLE_HOME/bin/oracle

-rwsr-s–x 1 oracle asmadmin 232399123 Apr 25 18:37 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

期间又发现日志里有这个warning信息

<msg time=’2016-05-07T14:56:12.547+08:00′ org_id=’oracle’ comp_id=’tnslsnr’

 type=’UNKNOWN’ level=’16’ host_id=’asmnodedup’

 host_addr=’10.1.1.36′>

 <txt>
WARNING: Subscription for node down event still pending

 </txt>

</msg>

在listener.ora里关掉

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=
OFF

参考资料:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=185921272425489&parent=DOCUMENT&sourceId=1069517.1&id=422173.1&_afrWindowMode=0&_adf.ctrl-state=65f0ft0n8_159

http://blog.itpub.net/23135684/viewspace-675750

http://www.xifenfei.com/2012/11/subscription-for-node-down-event-still-pending.html

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=125862473784830&parent=DOCUMENT&sourceId=557416.1&id=785742.1&_afrWindowMode=0&_adf.ctrl-state=14d5znhipy_267#SYMPTOM

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=126099153728478&id=550859.1&_afrWindowMode=0&_adf.ctrl-state=14d5znhipy_329

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=167079149612905&id=549932.1&_afrWindowMode=0&_adf.ctrl-state=yk1fw9zy0_134

原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/3908.html

(0)
上一篇 2021年7月16日
下一篇 2021年7月16日

相关推荐

发表回复

登录后才能评论