Oracle 12CR2 dbca -silent -createDatabase

在Oracle Linux 7.1,数据库为Oracle 12cr2中使用dbca静默方式创建数据库的语法如下:

[oracle@jytest1 ~]$ dbca -silent -createDatabase help
help
        -createDatabase - Command to Create a database.
                -responseFile | (-gdbName,-templateName)
                -responseFile - 
                -gdbName 
                -templateName 
                [-characterSet ]
                [-createAsContainerDatabase ]
                        [-numberOfPDBs ]
                        [-pdbAdminPassword ]
                        [-pdbName ]
                        [-pdbOptions ]
                        [-pdbStorageMAXSizeInMB ]
                        [-pdbStorageMAXTempSizeInMB ]
                        [-useLocalUndoForPDBs   Specify false to disable local undo tablespace for PDBs.]
                [-createListener ]
                [-customScripts ]
                [-databaseConfigType ]
                        [-RACOneNodeServiceName ]
                [-databaseType ]
                [-datafileDestination ]
                [-datafileJarLocation ]
                [-dbOptions ]
                [-dvConfiguration  Specify true to configure and enable database vault.]
                        -dvUserName 
                        -dvUserPassword 
                        [-dvAccountManagerName ]
                        [-dvAccountManagerPassword ]
                [-emConfiguration ]
                        [-dbsnmpPassword ]
                        [-emExpressPort ]
                        [-emPassword ]
                        [-emUser ]
                        [-omsHost ]
                        [-omsPort ]
                [-enableArchive  Specify true to enable archive>]
                        [-archiveLogDest ]
                        [-archiveLogMode ]
                [-initParams ]
                        [-initParamsEscapeChar ]
                [-listeners ]
                [-memoryMgmtType ]
                [-memoryPercentage | -totalMemory]
                [-memoryPercentage ]
                [-totalMemory ]
                [-nationalCharacterSet ]
                [-nodelist ]
                [-olsConfiguration  Specify true to configure and enable Oracle Label Security.]
                        [-configureWithOID This flag configures Oracle Label Security with OID.]
                [-oracleHomeUserName ]
                [-oracleHomeUserPassword ]
                [-policyManaged | -adminManaged]
                [-policyManaged ]
                        -serverPoolName 
                        [-createServerPool ]
                                [-cardinality ]
                                [-force ]
                                [-pqCardinality ]
                                [-pqPoolName ]
                        [-pqPoolName ]
                [-adminManaged ]
                [-recoveryAreaDestination ]
                        [-recoveryAreaSize ]
                [-redoLogFileSize ]
                [-registerWithDirService ]
                        -dirServiceUserName 
                        [-databaseCN ]
                        [-dirServicePassword ]
                        [-walletPassword ]
                [-runCVUChecks <specify "true" in order to run Cluster Verification Utility checks periodically for RAC databases.>]
                [-sampleSchema ]
                [-sid ]
                [-storageType < FS | ASM >]
                        -datafileDestination 
                        [-asmsnmpPassword ]
                [-sysPassword ]
                [-systemPassword ]
                [-useOMF  Specify true to use Oracle-Managed Files.]
                [-useWalletForDBCredentials  Specify true to load database credentials from wallet]
                        -dbCredentialsWalletLocation 
                        [-dbCredentialsWalletPassword ]
                [-variables ]
                [-variablesFile ]

使用dbca -silent方式来创建RAC数据库jycs,节点信息为jytest1,jytest2,磁盘组为test

[oracle@jytest1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName jycs -sid jycs -createAsContainerDatabase true -numberOfPDBs 1 -pdbName jycspdb -sysPassword xxzx7817600 -systemPassword xxzx7817600 -datafileDestination 'test/' -redoLogFileSize 50 -recoveryAreaDestination 'test/' -storageType -responseFile NO_VALUE  -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -initParams log_archive_dest_1='location=+test/'  -sampleSchema true -automaticMemoryManagement false -totalMemory 1024 -databaseType MULTIPURPOSE -nodeinfo jytest1,jytest2
[FATAL] [DBT-06007] The specified location (FRA Location) is invalid.
   CAUSE: The specified location is not found on the system or is detected to be a file.

上面报错的原因是因为如果使用ASM磁盘组来存储数据文件时,指定磁盘组名称时,其格式为+diskgroup_name,而在11g中为diskgroup_name

修磁盘组格式后再次执行

[oracle@jytest1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName jycs -sid jycs -createAsContainerDatabase true -numberOfPDBs 1 -pdbName jycspdb -sysPassword xxzx7817600 -systemPassword xxzx7817600 -datafileDestination '+test/' -redoLogFileSize 50 -recoveryAreaDestination '+test/' -storageType ASM    -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -initParams log_archive_dest_1='location=+test/'  -sampleSchema true -automaticMemoryManagement false -totalMemory 1024 -databaseType MULTIPURPOSE -nodeinfo jytest1,jytest2 
Enter PDBADMIN User Password: 

[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit 

[0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit 

[0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit 

[0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-09102] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. /u01/app/oracle/cfgtoollogs/dbca/trace.log_2017-04-26_09-55-10-AM
   ACTION: Find the appropriate configuration from the log file or from the installation guide to meet the prerequisites and fix this manually.
Copying database files
DBCA Operation failed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/jycs/jycs.log" for further details.

查看具体错误原因,说是检查磁盘组test是否注册到集群中

[root@jytest1 ~]# cat /u01/app/oracle/cfgtoollogs/dbca/jycs/jycs0.log
[ 2017-04-26 10:21:55.793 CST ] Copying database files
[ 2017-04-26 10:21:55.942 CST ] PRCR-1070 : Failed to check if resource ora.TEST/.dg is registered
CRS-5161 : The entity name contains invalid characters.

检查集群资源可以看到磁盘组test状态正常,两个节点都为online状态,而由于12c中的语法多了listeners参数,所以猜测是由于没有指定监听程序的原因

[grid@jytest1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.CRS.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.DATA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.TEST.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.chad
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.net1.network
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.ons
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.proxy_advm
               OFFLINE OFFLINE      jytest1                  STABLE
               OFFLINE OFFLINE      jytest2                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       jytest1                  169.254.123.145 88.8
                                                             8.88.1,STABLE
ora.asm
      1        ONLINE  ONLINE       jytest1                  Started,STABLE
      2        ONLINE  ONLINE       jytest2                  Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jy.db
      1        ONLINE  ONLINE       jytest1                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
      2        ONLINE  ONLINE       jytest2                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
ora.jytest1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jytest2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       jytest1                  Open,STABLE
ora.orcl.db
      1        ONLINE  ONLINE       jytest1                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db/,STABLE
      2        ONLINE  ONLINE       jytest2                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db/,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
--------------------------------------------------------------------------------

增加listeners参数后再次执行

[oracle@jytest1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName jycs -sid jycs -databaseConfigType RAC -createAsContainerDatabase true -numberOfPDBs 1 -pdbName jycspdb -sysPassword xxzx7817600 -systemPassword xxzx7817600  -redoLogFileSize 50   -storageType ASM -datafileDestination '+test/' -
asmsnmpPassword xxzx7817600   -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -initParams log_archive_dest_1='location=+TEST/',sga_target=1G,sga_max_size=1G  -sampleSchema true  -listeners ASMNET1LSNR_ASM,LISTENER,LISTENER_SCAN1 -automaticMemoryManagement false -totalMemory 2048 -databaseType MULTIPURPOSE -nodeinfo jytest1,jytest2 
Enter PDBADMIN User Password: 

[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit 

[0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit 

[0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit 

[0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-09102] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. /u01/app/oracle/cfgtoollogs/dbca/trace.log_2017-04-26_12-34-00-PM
   ACTION: Find the appropriate configuration from the log file or from the installation guide to meet the prerequisites and fix this manually.
Copying database files
1% complete
11% complete
21% complete
Creating and starting Oracle instance
23% complete
25% complete
26% complete
27% complete
30% complete
33% complete
34% complete
35% complete
Creating cluster database views
37% complete
50% complete
Completing Database Creation
51% complete
53% complete
55% complete
57% complete
Creating Pluggable Databases
61% complete
78% complete
Executing Post Configuration Actions
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/jycs/jycs3.log" for further details.

数据库创建成功,检查是否将数据库jycs注册到集群中,从下面的结果可以看到已经注册了

[grid@jytest1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.CRS.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.DATA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.TEST.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.chad
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.net1.network
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.ons
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.proxy_advm
               OFFLINE OFFLINE      jytest1                  STABLE
               OFFLINE OFFLINE      jytest2                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       jytest1                  169.254.123.145 88.8
                                                             8.88.1,STABLE
ora.asm
      1        ONLINE  ONLINE       jytest1                  Started,STABLE
      2        ONLINE  ONLINE       jytest2                  Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jy.db
      1        ONLINE  ONLINE       jytest1                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
      2        ONLINE  ONLINE       jytest2                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
ora.jycs.db
      1        ONLINE  ONLINE       jytest1                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
      2        ONLINE  ONLINE       jytest2                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
ora.jytest1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jytest2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       jytest1                  Open,STABLE
ora.orcl.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.qosmserver
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
--------------------------------------------------------------------------------

小结:
12c中使用dbca -silent方式来创建数据库之前的版本相比,增加了listeners参数,减少了指定ASM磁盘组的diskGroupName参数,并且指定磁盘组名称时需要指定+号,之前版本不需要指定+号。当然还增加了许多其它参数,有兴趣的可以测试一下。

Oracle 12Cr2 Using CloneDB to clone a database

这里将介绍如何使用Oracle 12CR2的CloneDB功能来克隆一个数据库,生产库orcl的Oracle home目录为/u01/app/oracle/product/12.2.0/db,数据库的备份文件存储在/u01/app/oracle/backup/目录中,CloneDB库clonedb的oracle home目录也为/u01/app/oracle/product/12.2.0/db

操作步骤如下:
1.对生产库执行备份,因为生产库jy启用了归档所以执行联机备份,使用backup as copy来备份生产库的数据文件,只备份数据文件。

SQL> col name for a50
SQL> select file#,rfile#,name from v$datafile;

     FILE#     RFILE# NAME
---------- ---------- --------------------------------------------------
         1          1 +DATA/ORCL/DATAFILE/system.280.941831569
         2          2 +DATA/ORCL/DATAFILE/sysaux.281.941831647
         3          3 +DATA/ORCL/DATAFILE/undotbs1.282.941831677
         4          4 +DATA/ORCL/DATAFILE/users.284.941831687

SQL> select name from v$tablespace;

NAME
--------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS



RMAN> backup as copy database format '/u01/app/oracle/backup/%U';

Starting backup at 25-APR-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/ORCL/DATAFILE/system.280.941831569
output file name=/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSTEM_FNO-1_1vs2jnif tag=TAG20170425T210621 RECID=17 STAMP=942267998
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=+DATA/ORCL/DATAFILE/sysaux.281.941831647
output file name=/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSAUX_FNO-2_20s2jnj1 tag=TAG20170425T210621 RECID=18 STAMP=942268013
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/ORCL/DATAFILE/users.284.941831687
output file name=/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-USERS_FNO-4_21s2jnjj tag=TAG20170425T210621 RECID=19 STAMP=942268023
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/ORCL/DATAFILE/undotbs1.282.941831677
output file name=/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-UNDOTBS1_FNO-3_22s2jnjs tag=TAG20170425T210621 RECID=20 STAMP=942268033
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 25-APR-17

Starting Control File and SPFILE Autobackup at 25-APR-17
piece handle=/u01/app/oracle/product/12.2.0/db/dbs/c-1469612247-20170425-03 comment=NONE
Finished Control File and SPFILE Autobackup at 25-APR-17

2.创建pfile参数文件

SQL> create pfile='/u01/app/oracle/product/12.2.0/db/dbs/initclonedb.ora' from spfile;

File created.

3.创建克隆库clonedb
3.1 手动生成创建CloneDB库的SQL脚本
3.1.1 以sysdba或sysbackup管理权限连接到生产库

[oracle@jytest3 ~]$ export ORACLE_SID=orcl
[oracle@jytest3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 24 19:56:40 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

3.1.2 执行以下命令来对生产库的控制文件生成备份

SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug setmypid
Statement processed.

SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22801.trc

3.1.3 将生成的跟踪文件中的startup nomount与create controlfile语句复制到一个新创建的脚本中。

[oracle@jytest3 dbs]$ vi create_clonedb.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/ORCL/ONLINELOG/group_1.278.941831529'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '+DATA/ORCL/ONLINELOG/group_2.279.941831545'  SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/ORCL/DATAFILE/system.280.941831569',
  '+DATA/ORCL/DATAFILE/sysaux.281.941831647',
  '+DATA/ORCL/DATAFILE/undotbs1.282.941831677',
  '+DATA/ORCL/DATAFILE/users.284.941831687'
CHARACTER SET ZHS16GBK
;

3.1.4 统计图脚本create_clonedb.sql,将数据库名从orcl修改为clonedb,将日志文件目录从+data/orcl/onlinelog/修改为+data/clonedb/onlinelog,将数据文件目录从+data/orcl/datafile修改为备份目录/u01/app/oracle/backup/,修改之后的脚本内容如下:

STARTUP NOMOUNT pfile=/u01/app/oracle/product/12.2.0/db/dbs/initclonedb.ora

CREATE CONTROLFILE REUSE SET DATABASE clonedb RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+data/clonedb/onlinelog/redo1.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '+data/clonedb/onlinelog/redo2.log'  SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSTEM_FNO-1_1vs2jnif',
  '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSAUX_FNO-2_20s2jnj1',
  '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-USERS_FNO-4_21s2jnjj',
  '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-UNDOTBS1_FNO-3_22s2jnjs'
CHARACTER SET ZHS16GBK
;

3.1.5 将生成的参数文件pfile复制到clonedb库所存储参数文件的位置,因为这里生产库与clonedb库的Oracle home目录相同,所以不用复制,这里需要参数文件修改为clonedb库所使用的相关参数,并且要增加CLONEDB=TRUE这个参数

[oracle@jytest3 dbs]$ vi initclonedb.ora
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=427819008
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=4194304
orcl.__large_pool_size=20971520
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=432013312
orcl.__sga_target=641728512
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=171966464
orcl.__streams_pool_size=0
*.control_files='+data/clonedb/controlfile/control01.ctl','+data/clonedb/controlfile/control02.ctl'
*.db_create_file_dest='+DATA'
*.db_name=clonedb
*.log_archive_dest_1='location=+data/arch/clonedb/'
*.memory_target=1G
clonedb=true

3.1.6 使用SQL*PLUS并以sysdba管理权限连接到clonedb库

[oracle@jytest3 ~]$ export ORACLE_SID=clonedb
[oracle@jytest3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 24 20:24:52 2017

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

Connected to an idle instance.

3.1.7 运行之前创建的create_clonedb.sql脚本

SQL> @/u01/app/oracle/product/12.2.0/db/dbs/create_clonedb.sql
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             629146936 bytes
Database Buffers          427819008 bytes
Redo Buffers                8146944 bytes

Control file created.

3.1.8 对于在备份目录中的每个数据文件,执行dbms_dnfs.clonedb_renamefile过程,srcfile指定备份文件,destfile指定目标文件

SQL> BEGIN
  2  DBMS_DNFS.CLONEDB_RENAMEFILE(srcfile => '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSTEM_FNO-1_1vs2jnif',destfile => 

'+DATA/clonedb/datafile/system.dbf');
  3  DBMS_DNFS.CLONEDB_RENAMEFILE(srcfile => '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-SYSAUX_FNO-2_20s2jnj1',destfile => 

'+DATA/clonedb/datafile/sysaux.dbf');
  4  DBMS_DNFS.CLONEDB_RENAMEFILE(srcfile => '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-USERS_FNO-4_21s2jnjj',destfile => '+DATA/clonedb/datafile/users.dbf');
  5  DBMS_DNFS.CLONEDB_RENAMEFILE(srcfile => '/u01/app/oracle/backup/data_D-ORCL_I-1469612247_TS-UNDOTBS1_FNO-3_22s2jnjs',destfile => 

'+DATA/clonedb/datafile/undotbs1.dbf');
  6  END;
  7  /

PL/SQL procedure successfully completed.

4.如果使用联机备份创建clonedb库,那么需要对clonedb执行恢复操作。如果是使用的完全脱机备份或使用的是backup as copy备份,则不需要执行恢复操作,因为我这是使用的backup as copy备份。需要执行恢复,执行以下命令:

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 630593 generated at 04/25/2017 21:06:24 needed for thread 1
ORA-00289: suggestion : +DATA/arch/clonedb/1_22_941831511.dbf
ORA-00280: change 630593 for thread 1 is in sequence #22


Specify log: {=suggested | filename | AUTO | CANCEL}

这里需要查询生产库当前正在使用的联机重做日志文件

SQL> set long 300
SQL> set linesize 300
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME        CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------ ----------
         1          1         21  104857600        512          1 YES INACTIVE                627446 25-APR-17          630528 25-APR-17             0
         2          1         22  104857600        512          1 NO  CURRENT                 630528 25-APR-17      1.8447E+19                       0

SQL> col member for a50
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_     CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
         1         ONLINE  +DATA/ORCL/ONLINELOG/group_1.278.941831529         NO           0
         2         ONLINE  +DATA/ORCL/ONLINELOG/group_2.279.941831545         NO           0


+DATA/ORCL/ONLINELOG/group_2.279.941831545
Log applied.
Media recovery complete.
SQL> 

5.执行以下命令open数据库

SQL> alter database open resetlogs;

Database altered.

到此克隆数据库的所要操作完成。

Oracle 12CR2 using create database command for create non-CDB rac database on Oracle Linux

在Oracle Linux7,数据库版本为12.2中使用create database来创建non-CDB数据库
1.设置实例标识符(SID)

[root@jytest1 ~]# su - oracle
Last login: Fri Apr 21 11:24:50 CST 2017
[oracle@jytest1 ~]$ export ORACLE_SID=orcl1

2.创建相关目录

ASMCMD [+data] > mkdir orcl
ASMCMD [+data] > ls
jy/
orcl/
ASMCMD [+data] > mkdir +data/orcl/CONTROLFILE
ASMCMD [+data] > mkdir +data/orcl/DATAFILE
ASMCMD [+data] > mkdir +data/orcl/ONLINELOG
ASMCMD [+data] > mkdir +data/orcl/TEMPFILE
ASMCMD [+data] > mkdir +data/orcl/PASSWORD
ASMCMD [+data] > mkdir +data/orcl/PARAMETERFILE

3.创建密码文件

[oracle@jytest1 dbs]$ orapwd file='/u01/app/oracle/product/12.2.0/db/dbs/orapworcl1'  force=y password=xxzx#7817600

4.创建参数文件,参数文件至少要包含db_name,control_files与memory_target参数

[oracle@jytest1 dbs]$ vi initorcl1.ora
db_name=orcl
control_files='+data/orcl/controlfile/control01.ctl','+data/orcl/controlfile/control02.c tl'
memory_target=1G

5.连接实例

[oracle@jytest1 dbs]$ sqlplus sys/xxzx#7817600 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Apr 19 19:15:50 2017

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

Connected to an idle instance.

6.启动实例

SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0/db/dbs/initorcl1.ora'
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             750781752 bytes
Database Buffers          306184192 bytes
Redo Buffers                8146944 bytes

7.创建服务器参数文件spfile

SQL>create spfile='+DATA/orcl/PARAMETERFILE/spfileorcl.ora' from pfile='/u01/app/oracle/product/12.2.0/db/dbs/initorcl1.ora';

8.执行create database语句

SQL> CREATE DATABASE orcl
  2  USER SYS IDENTIFIED BY xxzx#7817600
  3  USER SYSTEM IDENTIFIED BY xxzx#7817600
  4  LOGFILE GROUP 1 ('+data/orcl/onlinelog/redo01.log') SIZE 100M BLOCKSIZE 512,
  5  GROUP 2 ('+data/orcl/onlinelog/redo02.log') SIZE 100M BLOCKSIZE 512,
  6  GROUP 3 ('+data/orcl/onlinelog/redo03.log') SIZE 100M BLOCKSIZE 512
  7  MAXLOGHISTORY 1
  8  MAXLOGFILES 16
  9  MAXLOGMEMBERS 3
 10  MAXDATAFILES 1024
 11  CHARACTER SET ZHS16GBK
 12  NATIONAL CHARACTER SET AL16UTF16
 13  EXTENT MANAGEMENT LOCAL
 14  DATAFILE '+data/orcl/datafile/system01.dbf'
 15  SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 16  SYSAUX DATAFILE '+data/orcl/datafile/sysaux01.dbf'
 17  SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 18  DEFAULT TABLESPACE users
 19  DATAFILE '+data/orcl/datafile/users01.dbf'
 20  SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
 21  DEFAULT TEMPORARY TABLESPACE tempts1
 22  TEMPFILE '+data/orcl/tempfile/temp01.dbf'
 23  SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
 24  UNDO TABLESPACE undotbs1
 25  DATAFILE '+data/orcl/datafile/undotbs01.dbf'
 26  SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
 27  USER_DATA TABLESPACE usertbs
 28  DATAFILE '+data/orcl/datafile/usertbs01.dbf'
 29  SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Database created.

9.运行脚本来创建数据字典视图
用sys用户执行以下脚本

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

Session altered.

SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

SQL> Rem END catproc.sql

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> Rem END utlrp.sql

使用system用户执行以下脚本

SQL>@$ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL> alter session set "_ORACLE_SCRIPT" = false;

Session altered.

10.将数据库注册为RAC数据库,在节点2配置实例orcl2的参数文件

[oracle@jytest2 dbs]$ vi initorcl2.ora
SPFILE='+DATA/orcl/PARAMETERFILE/spfileorcl.ora'

设置RAC数据库所需要的相关参数

SQL> alter system set thread=1 scope=spfile sid='orcl1';

System altered.

SQL> alter system set thread=2 scope=spfile sid='orcl2';

System altered.

SQL> alter system set instance_number=1 scope=spfile sid='orcl1';

System altered.

SQL> alter system set instance_number=2 scope=spfile sid='orcl2';

System altered.

SQL> alter system set cluster_database=true scope=sfpile sid='*';

System altered.

SQL> alter system set cluster_database_instances=2 scope=sfpile sid='*';

System altered.

添加重做线程

SQL> alter database add logfile thread 2 group 4('+data/orcl/onlinelog/redo04.log') SIZE 100M BLOCKSIZE 512;

Database altered.

SQL> alter database add logfile thread 2 group 5('+data/orcl/onlinelog/redo05.log') SIZE 100M BLOCKSIZE 512;

Database altered.

SQL> alter database add logfile thread 2 group 6('+data/orcl/onlinelog/redo06.log') SIZE 100M BLOCKSIZE 512;

Database altered.

SQL> alter database enable thread 2;

Database altered.

向crs注册数据库

[oracle@jytest1 dbs]$ srvctl add database -db orcl -oraclehome  /u01/app/oracle/product/12.2.0/db/  -dbtype RAC -spfile  +DATA/orcl/PARAMETERFILE/spfileorcl.ora -diskgroup 'data'


[oracle@jytest1 dbs]$ srvctl add instance -db orcl -instance orcl1 -node jytest1
[oracle@jytest1 dbs]$ srvctl add instance -db orcl -instance orcl2 -node jytest2
[oracle@jytest1 dbs]$ srvctl config database -db orcl
Database unique name: orcl
Database name:
Oracle home: /u01/app/oracle/product/12.2.0/db/
Oracle user: oracle
Spfile: +DATA/orcl/PARAMETERFILE/spfileorcl.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: orcl1,orcl2
Configured nodes: jytest1,jytest2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

11.将密码文件存储在ASM磁盘组中

[oracle@jytest1 dbs]$ orapwd file='+data/orcl/password/pwdorcl' dbuniquename='orcl'

Enter password for SYS:

ASMCMD [+data/orcl/password] > ls -lt
Type      Redund  Striped  Time             Sys  Name
PASSWORD  UNPROT  COARSE   APR 21 11:00:00  Y    pwdorcl.294.941886275
PASSWORD  UNPROT  COARSE   APR 21 11:00:00  N    pwdorcl =>  +DATA/orcl/PASSWORD/pwdorcl.294.941886275

Enter password for SYS:
[oracle@jytest1 dbs]$ srvctl config database -db orcl
Database unique name: orcl
Database name:
Oracle home: /u01/app/oracle/product/12.2.0/db/
Oracle user: oracle
Spfile: +DATA/orcl/PARAMETERFILE/spfileorcl.ora
Password file: +DATA/orcl/password/pwdorcl
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: orcl1,orcl2
Configured nodes: jytest1,jytest2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

12.检查crs状态信息

[grid@jytest2 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.CRS.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.DATA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.TEST.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.chad
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.net1.network
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.ons
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.proxy_advm
               OFFLINE OFFLINE      jytest1                  STABLE
               OFFLINE OFFLINE      jytest2                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       jytest1                  169.254.123.145 88.8
                                                             8.88.1,STABLE
ora.asm
      1        ONLINE  ONLINE       jytest1                  Started,STABLE
      2        ONLINE  ONLINE       jytest2                  Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jy.db
      1        ONLINE  ONLINE       jytest1                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
      2        ONLINE  ONLINE       jytest2                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
ora.jytest1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jytest2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       jytest1                  Open,STABLE
ora.orcl.db
      1        ONLINE  ONLINE       jytest1                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db/,STABLE
      2        ONLINE  ONLINE       jytest2                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db/,STABLE
ora.qosmserver
      1        ONLINE  INTERMEDIATE jytest1                  CHECK TIMED OUT,STAB
                                                             LE
ora.scan1.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
--------------------------------------------------------------------------------

到此操作完成!

Oracle 12CR2 RAC ORA-01033

在12C RAC中登录pdb时出现了ORA-01033错误

[oracle@jytest1 ~]$ sqlplus  hr/hr@JYPDB

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 11 18:15:26 2017

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

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

如是查看pdb数据库的状态,从以下信息来看pdb是正常的

[grid@jytest1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 11-APR-2017 18:03:18

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                21-MAR-2017 19:13:39
Uptime                    20 days 22 hr. 49 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jytest1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.171)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.175)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest1.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))

(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_TEST" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "4b2c6373ae2547cce053ab828a0a7ca3" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jy" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jyXDB" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jypdb" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
The command completed successfully



[grid@jytest2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 11-APR-2017 18:16:50

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                21-MAR-2017 19:11:40
Uptime                    20 days 23 hr. 5 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jytest2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.172)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.176)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest2.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))

(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_TEST" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "4b2c6373ae2547cce053ab828a0a7ca3" has 1 instance(s).
  Instance "jy2", status READY, has 1 handler(s) for this service...
Service "jy" has 1 instance(s).
  Instance "jy2", status READY, has 1 handler(s) for this service...
Service "jyXDB" has 1 instance(s).
  Instance "jy2", status READY, has 1 handler(s) for this service...
Service "jypdb" has 1 instance(s).
  Instance "jy2", status READY, has 1 handler(s) for this service...
The command completed successfully



[grid@jytest1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.CRS.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.DATA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.TEST.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.chad
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.net1.network
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.ons
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.proxy_advm
               OFFLINE OFFLINE      jytest1                  STABLE
               OFFLINE OFFLINE      jytest2                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       jytest2                  169.254.237.250 88.8
                                                             8.88.2,STABLE
ora.asm
      1        ONLINE  ONLINE       jytest1                  Started,STABLE
      2        ONLINE  ONLINE       jytest2                  Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.jy.db
      1        ONLINE  ONLINE       jytest1                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
      2        ONLINE  ONLINE       jytest2                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
ora.jytest1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jytest2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       jytest2                  Open,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
--------------------------------------------------------------------------------


[grid@jytest2 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.CRS.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.DATA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.TEST.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.chad
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.net1.network
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.ons
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.proxy_advm
               OFFLINE OFFLINE      jytest1                  STABLE
               OFFLINE OFFLINE      jytest2                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       jytest2                  169.254.237.250 88.8
                                                             8.88.2,STABLE
ora.asm
      1        ONLINE  ONLINE       jytest1                  Started,STABLE
      2        ONLINE  ONLINE       jytest2                  Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.jy.db
      1        ONLINE  ONLINE       jytest1                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
      2        ONLINE  ONLINE       jytest2                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
ora.jytest1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jytest2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       jytest2                  Open,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
--------------------------------------------------------------------------------

虽然,从监听与集群资源来看是正常,但因为这里pdb数据库(jypdb)使用的服务名与pdb数据库名相同,还有就是因为是RAC数据库,因为在RAC环境中,当PDB被mount时,实例将使用pdb数据库名向SCAN与本地监听程序注册PDB,如果使用SCAN IP来登录pdb数据库,就可能将连接请求指派给PDB只被mounted的实例(没有open),如是检查pdb是否open,发现果然pdb在节点2上的实例没有被open,如是执行以下
命令:

[root@jytest2 ~]# su - oracle
Last login: Tue Apr 11 17:37:14 CST 2017
[oracle@jytest2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 11 18:17:44 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=jypdb;

Session altered.

SQL> alter pluggable database jypdb open;

Pluggable database altered.
[oracle@jytest1 ~]$ sqlplus  hr/hr@JYPDB

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 11 18:18:18 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn system/xxzx7817600@JYPDB
Connected.

在RAC所有节点上open pdb数据库jypdb后,登录就恢复正常了。

服务器时间异常造成ORA-00600 [2252]的故障处理

数据库服务器重启之后,检查Oracle集群资源发现四个实例启动了两个,还有两个没有启动(wu,rl)

[grid@db1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.CWDATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.DADATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.DATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.LISTENER.lsnr
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.OCR.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.KDATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.asm
               ONLINE  ONLINE       db1                      Started             
               ONLINE  ONLINE       db2                      Started             
ora.gsd
               OFFLINE OFFLINE      db1                                          
               OFFLINE OFFLINE      db2                                          
ora.net1.network
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.ons
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       db1                                          
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       db2                                          
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       db2                                          
ora.wu.db
      1        ONLINE  OFFLINE                               Instance Shutdown   
      2        ONLINE  ONLINE       db2                      Open                
ora.dyl.db
      1        ONLINE  OFFLINE                                                   
      2        ONLINE  ONLINE       db2                      Open                
ora.cvu
      1        ONLINE  ONLINE       db2                                          
ora.da.db
      1        ONLINE  ONLINE       db1                      Open                
      2        ONLINE  ONLINE       db2                      Open                
ora.db1.vip
      1        ONLINE  ONLINE       db1                                          
ora.db2.vip
      1        ONLINE  ONLINE       db2                                          
ora.oc4j
      1        ONLINE  ONLINE       db2                                          
ora.rl.db
      1        ONLINE  OFFLINE                               Instance Shutdown   
      2        ONLINE  ONLINE       db2                      Open                
ora.scan1.vip
      1        ONLINE  ONLINE       db1                                          
ora.scan2.vip
      1        ONLINE  ONLINE       db2                                          
ora.scan3.vip
      1        ONLINE  ONLINE       db2                               

查看rlzy实例的alert.log文件,可以看到以下错误信息“ORA-00600: internal error code, arguments: [2252], [3418], [573259345], [1594], [50675712]”,关于这个ORA-00600 2252在MOS上有相关bug描述,但我这并不是bug引起的,查看信息时我们也是需要关注时间的,这里时间显示为2001年1月1号了,与当前时间相差了16年2个多月。

Picked broadcast on commit scheme to generate SCNs
Mon Jan 01 08:23:50 2001
Errors in file /u01/app/oracle/diag/rdbms/rl/RL1/trace/RL1_dbw0_19789.trc  (incident=544328):
ORA-00600: internal error code, arguments: [2252], [3418], [573259345], [1594], [50675712], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/rl/RL1/incident/incdir_544216/RL1_diag_19753_i544216.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/rl/RL1/trace/RL1_dbw0_19789.trc:
ORA-01186: file 2 failed verification tests
ORA-00600: internal error code, arguments: [2252], [3418], [573259345], [1594], [50675712], [], [], [], [], [], [], []
DBW0 (ospid: 19789): terminating the instance due to error 1186
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Jan 01 08:23:56 2001
ORA-1092 : opitsk aborting process
Mon Jan 01 08:23:56 2001

查看系统当前时间,居然变成了2001年1月1号,而且奇怪的是并不是重启时时间就被修改了,因为有两个实例在重启之后正常启动了

[root@db1 ~]# date
Mon Jan  1 08:25:34 CST 2001

手工更新为当前时间后并手动启动实例(caiwu,rlzy),就能正常启动

[root@db1 ~]# date
Fri Mar 24 11:26:44 CST 2017

[grid@db1 ~]$ srvctl start database -d caiwu

[grid@db1 ~]$ srvctl start database -d rlzy

[grid@db1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.CWDATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.DADATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.DATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.LISTENER.lsnr
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.OCR.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.KDATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.asm
               ONLINE  ONLINE       db1                      Started             
               ONLINE  ONLINE       db2                      Started             
ora.gsd
               OFFLINE OFFLINE      db1                                          
               OFFLINE OFFLINE      db2                                          
ora.net1.network
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.ons
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       db1                                          
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       db2                                          
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       db2                                          
ora.wu.db
      1        ONLINE  ONLINE       db1                      Open                
      2        ONLINE  ONLINE       db2                      Open                
ora.dyl.db
      1        ONLINE  ONLINE       db1                      Open                
      2        ONLINE  ONLINE       db2                      Open                
ora.cvu
      1        ONLINE  ONLINE       db2                                          
ora.da.db
      1        ONLINE  ONLINE       db1                      Open                
      2        ONLINE  ONLINE       db2                      Open                
ora.db1.vip
      1        ONLINE  ONLINE       db1                                          
ora.db2.vip
      1        ONLINE  ONLINE       db2                                          
ora.oc4j
      1        ONLINE  ONLINE       db2                                          
ora.rl.db
      1        ONLINE  ONLINE       db1                      Open                
      2        ONLINE  ONLINE       db2                      Open                
ora.scan1.vip
      1        ONLINE  ONLINE       db1                                          
ora.scan2.vip
      1        ONLINE  ONLINE       db2                                          
ora.scan3.vip
      1        ONLINE  ONLINE       db2       

问题虽然解决了,但是为什么服务器会在启动两个实例后,系统时间发生改变还是不得而知,需要找浪潮服务器的工程师来检查。