Oracle 12C 跨网络传输数据库

跨网络传输数据库,可以通过使用network_link参数来执行导入操作,导入操作将使用数据库链路,不需要生成dump文件。操作步骤如下:
1.在目标数据库中创建链接到源数据库的数据链路。执行导入操作的用户必须要有datapump_imp_full_database权限,并且连接到源数据库的数据链路也必须连接到一个有datapump_exp_full_database角色的用户。在源数据库中用户不能有sysdba管理权限。

2.在源数据库上将所有用户表空间置为只读模式

3.将源数据库中所有用户表空间相关的数据文件传输到目标数据库。如果源平台与目标平台的字节编码不同,那么查询v$transportable_platform视图来进行查看。并且将可以使用以下一种方法来转换数据文件:
.使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件。这些过程会自动将数据文件的字节编码转换为目标平台的字节编码。

.使用rman的convert命令来将数据文件的字节编码转换为目标平台的字节编码。

4.在目标数据库上执行导入操作。使用Data Pump工具来导入所有用户表空间的元数据与管理表空间的元数据与真实数据。确保以下参数正确设置:
.transportable=always
.transport_datafiles=list_of_datafiles
.full=y
.network_link=database_link
.version=12
如果源数据库为11.2.0.3或11g之后的版本,那么必须设置version=12。如果源数据库与目标数据库都是12c,那么version参数不用设置。

如果源数据库包含任何加密表空间或表空间包含加密列,那么你必须指定encryption_pwd_prompt=yes或指定encryption_password参数。

Data Pump跨网络导入将会复制所有用户表空间所存储对象的元数据与管理表空间中的元与用户对象的真实数据。当导入完成后,用户表空间将会置于读写模式。

5.可选操作将源数据库中的所有用户表空间置为读写模式。

下面的例子是将源数据库jyrac传输到目标数据库jypdb
1.在目标数据库中以sys用户来创建链接到源数据库的数据链路。源数据库中的用户为jy

SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.


SQL> create public database link jyrac_link
  2    connect to jy identified by "jy"
  3    using '(DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVER = DEDICATED)
  9        (SERVICE_NAME =jyrac)
 10      )
 11    )';

Database link created.

2.在源数据库上将所有用户表空间置为只读模式

SQL>  select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE

8 rows selected.

SQL> alter tablespace test read only;

Tablespace altered.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace example read only;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          READ ONLY
UNDOTBS2                       ONLINE
EXAMPLE                        READ ONLY
TEST                           READ ONLY

8 rows selected.

3.在目标数据库中使用dbms_file_transfer包中的get_file过程将源数据库中所有用户表空间相关的数据文件传输到目标数据库上
在源数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中执行dbms_file_transfer.get_file过程将源数据库中所有用户表空间所相关的数据文件传输到目标数据库中

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'test01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'test01.dbf');

PL/SQL procedure successfully completed.

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'example.260.930413057',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'example01.dbf');

PL/SQL procedure successfully completed.

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'users.263.930413057',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'users01.dbf');

PL/SQL procedure successfully completed.
ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  N    testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDO_2.277.939167063
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS2.278.945029905
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS1.273.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    SYSTEM.274.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    SYSAUX.275.939167015

4.在目标数据库上执行导入操作。使用Data Pump工具来导入所有用户表空间的元数据与管理表空间的元数据与真实数据。

[oracle@jytest1 tts]$ impdp system/xxzx7817600@JYPDB_175 full=y network_link=jyrac_link transportable=always transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf' version=12 directory=TTS_DUMP_LOG logfile=import.log

Import: Release 12.2.0.1.0 - Production on Fri Jun 2 16:30:40 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@JYPDB_175 full=y network_link=jyrac_link transportable=always transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf version=12 directory=TTS_DUMP_LOG logfile=import.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
.......
Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
ORA-39082: Object type PROCEDURE:"APEX_030200"."F" created with compilation warnings

ORA-39082: Object type PROCEDURE:"APEX_030200"."APEX_ADMIN" created with compilation warnings

ORA-39082: Object type PROCEDURE:"APEX_030200"."HTMLDB_ADMIN" created with compilation warnings

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1689 error(s) at Fri Jun 2 16:43:47 2017 elapsed 0 00:13:03


传输完成后我们抽查用户jy的dba_tables表的数据在传输后是否与源数据库中的数据一致。
源数据库

SQL> conn sys/xxzx7817600@jyrac as sysdba
Connected.

SQL> select count(*) from jy.dba_tables;

  COUNT(*)
----------
      2141

目标数据库

SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.

SQL> select count(*) from jy.dba_tables;

  COUNT(*)
----------
      2141

查询传输后用户表空间的状态是否为online,可以看到test,example,users表空间状态为online

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
UNDO_2                         ONLINE
USERS                          ONLINE
TESTTB                         ONLINE
TEMP2                          ONLINE
TEMP3                          ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
UNDOTBS2                       ONLINE

12 rows selected.

5.将源数据库中的所有用户表空间设置为读写模式

SQL> alter tablespace test read write;

Tablespace altered.

SQL> alter tablespace example read write;

Tablespace altered.

SQL> alter tablespace users read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE

8 rows selected.

到此通过网络执行完整数据库传输的操作就完成了。

重命名与迁移联机数据文件

重命名与迁移联机数据文件
在12C中可以使用alter database move datafile语句来重命名或迁移联机数据文件。当数据库处于open状态,并且用户正在访问数据文件时仍然能够重命名与迁移联机数据文件。

当你重命名与迁移联机数据文件时,在控制文件中所记录指向数据文件的指针会被改变。数据文件会在操作系统层面被重命名与迁移。

当执行以下任务时因为需要允许用户访问数据文件所以需要对联机数据文件执行重命名与迁移:
.将数据文件从一种类型的存储迁移到另一种类型存储上。
.将不频繁访问的数据文件迁移到低成本存储上。
.将表空间置为只读并将它的数据文件写入存储设备上
.将数据库迁移到Oracle ASM中

当执行alter database move datafile语句时,如果在目标目录中存在相同的文件,可以指定reuse选项来覆盖现有文件。当没有指定reuse选项时,如果在目标目录中存在相同文件时,现有文件不会被覆盖并且语句会返回错误信息。

缺省情况下,当执行alter database move datafile语句并指定新目录时,语句会移动数据文件到新目录中。然而可以指定keep选项来保留旧目录中的数据文件。在这种情况下当语句完成后数据库只会使用新目录中的数据文件。

使用alter database move datafile语句来重命名或迁移数据文件时,Oracle数据库会创建一份数据文件副本。确保对于有足够的空间来执行此操作。

注意:如果指定的数据文件处于脱机状态那么执行alter database move datafile语句将会触发错误,如果配置了备库,那么对于主库和备库所执行的联机数据文件迁移操作是相互独立的。当对主库移动数据文件时备库不受影响,反之一样。闪回操作不会将被移动的数据文件重新迁移回之前的目录中。如果将一个联机数据文件从一个目录移动到另一个目录,之后执行闪回数据库操作将其闪回到数据文件迁移之前的时间点,那么数据文件仍然会存储在新目录中,但数据文件的内容将会回到闪回操作所指定的时间点。当在Windows平台上执行数据文件迁移时,就算没有指定keep选项,原始数据文件也会保留在旧目录中。在这种情况下,当迁移操作完成之后,数据库就只会使用新目录中的数据文件。如果需要这时可以使用手动删了旧数据文件。

重命名联机数据文件

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/system.274.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/sysaux.275.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs1.273.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undo_2.277.939167063
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/users.278.939167083
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb1.dbf

6 rows selected.

SQL> alter database move datafile '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb1.dbf' to '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb01.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/system.274.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/sysaux.275.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs1.273.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undo_2.277.939167063
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/users.278.939167083
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb01.dbf

6 rows selected.

迁移联机数据文件

SQL>ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '/u02/oracle/rbdb1/user1.dbf';

复制联机数据文件

SQL>ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '/u02/oracle/rbdb1/user1.dbf' KEEP;

迁移联机数据文件并覆盖所存在的数据文件

SQL>ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '/u02/oracle/rbdb1/user1.dbf' REUSE;

迁移联机数据文件到ASM磁盘组

SQL>ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '+dgroup_01/data/orcl/datafile/user1.dbf';

将联机数据文件从一个ASM磁盘组迁移到另一个ASM磁盘组中

SQL>ALTER DATABASE MOVE DATAFILE '+dgroup_01/data/orcl/datafile/user1.dbf' TO '+dgroup_02/data/orcl/datafile/user1.dbf';

使用表空间组来管理多个临时表空间

使用表空间组来管理多个临时表空间

表空间组能让用户使用来自多个临时表空间中的临时空间。使用表空间组,而不是单个临时表空间,可以减少由于一个临时表空间没有足够的空间来处理排序而引发的问题。表空间组能让单个并行操作的并行执行服务器进程使用多个临时表空间。表空间组有以下特性:
.它包含至少一个临时表空间。对于一个表空间组可以包含的临时表空间数量没有限制。
.它共享命名空间,所以表空间组的名字不能与任何表空间名相同。
.当对数据库或用户指定缺省临时表空间时可以使用表空间组来代替。

我们不能显示创建一个表空间组,但可以在创建第一临时表空间时隐式来创建表空间组。当表空间组中的最后一个临时表空间被删除后,表空间组也会被删除。查询dba_tablespace_groups可以查看相关表空间组与相关的临时表空间。

创建表空间组
当执行create temporary tablespace或alter tablespace语句时通过指定tablespace group子句来隐式创建表空间组并且所指定的表空间组要在当前数据库中并不存在。例如,如果表空间组group1,group2不存在,那么下面的语句将会创建这两个表空间组,并且每个表空间组只有一个临时表空间存在。

SQL> create temporary tablespace temp2 tempfile '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/TEMPFILE/temp2.dbf' size 50M tablespace group group1;

Tablespace created.

SQL> alter tablespace temp tablespace group group2;

Tablespace altered.


SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2                         TEMP
GROUP1                         TEMP2

更改表空间组成员
可以向现有表空间组增加临时表空间,这可以通过执行包含有tablespace group子句的create temporary tablespace或alter tablespace语句来完成。

下面的语句将向现有表空间组group1中增加一个临时表空间temp3,因些表空间组group1包含临时表空间temp2与temp3。

SQL> create temporary tablespace temp3 tempfile '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/TEMPFILE/temp3.dbf' size 50M tablespace group group1;

Tablespace created.

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2                         TEMP
GROUP1                         TEMP2
GROUP1                         TEMP3

下面的语句将向现有表空间组group2中增加一个临时表空间temp2,但因为临时表空间temp2已经存在,语句只是将临时表空间temp2从表空间组group1移到表空间组group2中。

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2                         TEMP
GROUP2                         TEMP2
GROUP1                         TEMP3

现在表空间组group2包含临时表空间temp,temp2,表空间组group1只包含临时表空间temp3。

下面的语句将从表空间组group1中删除临时表空间temp3

SQL> alter tablespace temp3 tablespace group '';

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2                         TEMP
GROUP2                         TEMP2

临时表空间temp3不再属于任何表空间组。因此表空间组group1不包含任何临时表空间,因此隐式地删除了表空间组group1。

指定表空间组作为数据库的缺省临时表空间
通过执行alter database … default temporary tablespace语句来指定一个表空间组作为数据库的临时表空间。

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                                       PROPERTY_VALUE
--------------------------------------------------  --------------------------------------------------
DEFAULT_TEMP_TABLESPACE                             TEMP



SQL> alter database default temporary tablespace group2;

Database altered.


SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                                       PROPERTY_VALUE
--------------------------------------------------  --------------------------------------------------
DEFAULT_TEMP_TABLESPACE                             GROUP2

任何没有显示指定临时表空间的用户,将可以使用临时表空间temp,temp2中的空间。当表空间组被指定作为数据库的临时表空间时,不能删除表空间组中的任何成员。要删除临时表空间必须首先要删除表空间组。同样不能删除作为数据库的缺省临时表空间。

Oracle 12CR2 Install the Sample Schemas

在Oracle 12CR2中在创建数据库时不能安装sample schemas,Oracle将sample schemas的安装脚本存储在GitHub上了,可以通过以下链接地址进行下载

https://github.com/oracle/db-sample-schemas/releases/latest

下载到的是一个zip文件,例如我下载的文件名为db-sample-schemas-12.2.0.1.zip,将其解压

[oracle@jytest1 schema]$ unzip db-sample-schemas-12.2.0.1.zip

[oracle@jytest1 schema]$ ls -lrt
total 36584
-rw-r--r--  1 oracle oinstall     2322 Apr  3  2009 drop_sch.sql
-rw-r--r--  1 oracle oinstall    16894 Jul  1  2014 sted_mkplug.sql.dbl
-rw-r--r--  1 oracle oinstall    27570 Jul  1  2014 mkplug.sql
-rw-r--r--  1 oracle oinstall     1685 Nov  6  2015 mk_dir.sql.sbs
drwxr-xr-x  2 oracle oinstall        6 Mar 20 19:50 log
-rw-r--r--  1 oracle oinstall     1824 Mar 20 19:51 mk_dir.sql
drwxr-xr-x  2 oracle oinstall     4096 May 18 17:12 human_resources
-rw-r--r--  1 oracle oinstall 37389564 May 18 17:48 db-sample-schemas-12.2.0.1.zip
drwxrwxrwx 10 oracle oinstall     4096 May 18 18:46 db-sample-schemas-12.2.0.1

创建sample schemas只需执行db-sample-schemas-12.2.0.1目录下的mksample.sql脚本,其语法如下:

mksample          EXAMPLE TEMP $ORACLE_HOME/demo/schema/log/ localhost:1521/pdb

其参数分别指system,sys,hr,oe,pm,ix,sh,bi用户的密码,与缺省表空间,临时表空间名,以及存储生成日志文件的目录和连接数据库的连接串

[oracle@jytest1 db-sample-schemas-12.2.0.1]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 18 18:01:54 2017

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

SQL> @mksample.sql xxzx7817600 xxzx7817600 hr oe pm id sh bi users temp /u01/app/oracle/product/12.2.0/db/demo/schema/db-sample-schemas-12.2.0.1/log/ jypdb

specify password for SYSTEM as parameter 1:

specify password for SYS as parameter 2:

specify password for HR as parameter 3:

specify password for OE as parameter 4:

specify password for PM as parameter 5:

specify password for IX as parameter 6:

specify password for  SH as parameter 7:

specify password for  BI as parameter 8:

specify default tablespace as parameter 9:

specify temporary tablespace as parameter 10:

specify log file directory (including trailing delimiter) as parameter 11:

specify connect string as parameter 12:

Sample Schemas are being created ...

mkdir: cannot create directory 鈥u01/app/oracle/product/12.2.0/db/demo/schema/db-sample-schemas-12.2.0.1鈥 File exists

Connected.
DROP USER hr CASCADE
          *
ERROR at line 1:
ORA-01918: user 'HR' does not exist


DROP USER oe CASCADE
          *
ERROR at line 1:
ORA-01918: user 'OE' does not exist


DROP USER pm CASCADE
          *
ERROR at line 1:
ORA-01918: user 'PM' does not exist


DROP USER ix CASCADE
          *
ERROR at line 1:
ORA-01918: user 'IX' does not exist


DROP USER sh CASCADE
          *
ERROR at line 1:
ORA-01918: user 'SH' does not exist


DROP USER bi CASCADE
          *
ERROR at line 1:
ORA-01918: user 'BI' does not exist


Connected.
SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_main.sql"
Connected.
SP2-0310: unable to open file "__SUB__CWD__/order_entry/oe_main.sql"
Connected.
SP2-0310: unable to open file "__SUB__CWD__/product_media/pm_main.sql"
Connected.
SP2-0310: unable to open file "__SUB__CWD__/info_exchange/ix_main.sql"
Connected.
SP2-0310: unable to open file "__SUB__CWD__/sales_history/sh_main.sql"
Connected.
SP2-0310: unable to open file "__SUB__CWD__/bus_intelligence/bi_main.sql"
Connected.
not spooling currently
SP2-0310: unable to open file "__SUB__CWD__/mkverify.sql"

上面的错误显示不能打开__SUB__CWD__/目录下的相关脚本文件,这里我们需要将__SUB__CWD__/目录使用相关脚本存储的目录的绝对路径来替,下面执行替换

[oracle@jytest1 db-sample-schemas-12.2.0.1]$ perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat

重新执行

[oracle@jytest1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 18 19:05:33 2017

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

SQL>@mksample.sql xxzx7817600 xxzx7817600 hr oe pm id sh bi users temp /u01/app/oracle/product/12.2.0/db/demo/schema/db-sample-schemas-12.2.0.1/log/ jypdb

specify password for SYSTEM as parameter 1:

specify password for SYS as parameter 2:

specify password for HR as parameter 3:

specify password for OE as parameter 4:

specify password for PM as parameter 5:

specify password for IX as parameter 6:

specify password for  SH as parameter 7:

specify password for  BI as parameter 8:

specify default tablespace as parameter 9:

specify temporary tablespace as parameter 10:

specify log file directory (including trailing delimiter) as parameter 11:

specify connect string as parameter 12:

Sample Schemas are being created ...
....省略....

Index cardinality (without  LOB indexes)

OWNER  INDEX_NAME                DISTINCT_KEYS   NUM_ROWS
------ ------------------------- ------------- ----------
HR     COUNTRY_C_ID_PK                      25         25
HR     DEPT_ID_PK                           27         27
HR     DEPT_LOCATION_IX                      7         27
HR     EMP_DEPARTMENT_IX                    11        106
HR     EMP_EMAIL_UK                        107        107
HR     EMP_EMP_ID_PK                       107        107
HR     EMP_JOB_IX                           19        107
HR     EMP_MANAGER_IX                       18        106
HR     EMP_NAME_IX                         107        107
HR     JHIST_DEPARTMENT_IX                   6         10
HR     JHIST_EMPLOYEE_IX                     7         10
HR     JHIST_EMP_ID_ST_DATE_PK              10         10
HR     JHIST_JOB_IX                          8         10
HR     JOB_ID_PK                            19         19
HR     LOC_CITY_IX                          23         23
HR     LOC_COUNTRY_IX                       14         23
HR     LOC_ID_PK                            23         23
HR     LOC_STATE_PROVINCE_IX                17         17
HR     REG_ID_PK                             4          4
IX     AQ$_STREAMS_QUEUE_TABLE_Y             0          0
OE     ACTION_TABLE_MEMBERS                132        132
OE     CUSTOMERS_PK                        319        319
OE     CUST_ACCOUNT_MANAGER_IX               4        319
OE     CUST_EMAIL_IX                       319        319
OE     CUST_LNAME_IX                       176        319
OE     CUST_UPPER_NAME_IX                  319        319
OE     INVENTORY_IX                       1112       1112
OE     INV_PRODUCT_IX                      208       1112
OE     ITEM_ORDER_IX                       105        665
OE     ITEM_PRODUCT_IX                     185        665
OE     LINEITEM_TABLE_MEMBERS              132        132
OE     ORDER_ITEMS_PK                      665        665
OE     ORDER_ITEMS_UK                      665        665
OE     ORDER_PK                            105        105
OE     ORD_CUSTOMER_IX                      47        105
OE     ORD_ORDER_DATE_IX                   105        105
OE     ORD_SALES_REP_IX                      9         70
OE     PRD_DESC_PK                        8640       8640
OE     PRODUCT_INFORMATION_PK              288        288
OE     PROD_NAME_IX                       3727       8640
OE     PROD_SUPPLIER_IX                     62        288
OE     PROMO_ID_PK                           2          2
OE     WAREHOUSES_PK                         9          9
OE     WHS_LOCATION_IX                       9          9
PM     ONLINEMEDIA_PK                        9          9
PM     PRINTMEDIA_PK                         4          4
SH     CHANNELS_PK                           5          5
SH     COSTS_PROD_BIX                        0          0
SH     COSTS_TIME_BIX                        0          0
SH     COUNTRIES_PK                         23         23
SH     CUSTOMERS_GENDER_BIX                  2          5
SH     CUSTOMERS_MARITAL_BIX                11         18
SH     CUSTOMERS_PK                      55500      55500
SH     CUSTOMERS_YOB_BIX                    75         75
SH     DR$SUP_TEXT_IDX$RC
SH     DR$SUP_TEXT_IDX$X                     0          0
SH     FW_PSC_S_MV_CHAN_BIX                  4          4
SH     FW_PSC_S_MV_PROMO_BIX                 4          4
SH     FW_PSC_S_MV_SUBCAT_BIX               21         21
SH     FW_PSC_S_MV_WD_BIX                  210        210
SH     PRODUCTS_PK                          72         72
SH     PRODUCTS_PROD_CAT_IX                  5         72
SH     PRODUCTS_PROD_STATUS_BIX              1          1
SH     PRODUCTS_PROD_SUBCAT_IX              21         72
SH     PROMO_PK                            503        503
SH     SALES_CHANNEL_BIX                     4         92
SH     SALES_CUST_BIX                     7059      35808
SH     SALES_PROD_BIX                       72       1074
SH     SALES_PROMO_BIX                       4         54
SH     SALES_TIME_BIX                     1460       1460
SH     SUP_TEXT_IDX
SH     TIMES_PK                           1826       1826

72 rows selected.

SQL> select username from dba_users;

USERNAME
------------------------------------------------------------------------------------------
SYS
SYSTEM
XS$NULL
LBACSYS
OUTLN
DBSNMP
APPQOSSYS
DBSFWUSER
GGSYS
ANONYMOUS
CTXSYS
SI_INFORMTN_SCHEMA
DVSYS
DVF
GSMADMIN_INTERNAL
ORDPLUGINS
MDSYS
OLAPSYS
ORDDATA
XDB
WMSYS
ORDSYS
GSMCATUSER
MDDATA
SYSBACKUP
REMOTE_SCHEDULER_AGENT
PDBADMIN
GSMUSER
SYSRAC
HR
BI
OJVMSYS
AUDSYS
DIP
JY
OE
PM
SYSKM
ORACLE_OCM
SYS$UMF
QS_ADM
IX
SYSDG
SPATIAL_CSW_ADMIN_USR
SH

45 rows selected.

相关sample schemas创建成功。

Oracle 12CR2 Oracle Restart – ASM Startup fails with PRCR-1079

操作系统Oracle Linux 7.1,数据库版本为12.2.0.1 Oracle Restart在重启之后不能正常启动ASM实例

[grid@jytest3 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  OFFLINE      jytest3                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest3                  STABLE
ora.asm
               ONLINE  OFFLINE      jytest3                  STABLE
ora.ons
               OFFLINE OFFLINE      jytest3                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.driver.afd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.evmd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.jy.db
      1        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
--------------------------------------------------------------------------------

尝试手动启动ASM实例,提示无效的用户与密码

[grid@jytest3 ~]$ srvctl start asm -startoption MOUNT -f
PRKO-2002 : Invalid command line option: -f
[grid@jytest3 ~]$ srvctl start asm -startoption MOUNT 
PRCR-1079 : Failed to start resource ora.asm
CRS-5017: The resource action "ora.asm start" encountered the following error: 
ORA-01017: invalid username/password; logon denied
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/jytest3/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.asm' on 'jytest3' failed
ORA-01017: invalid username/password; logon denied

如果尝试使用SQL*PLUS来启动ASM实例,仍然提示无效的用户与密码

[grid@jytest3 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 2 20:30:15 2017

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

ERROR:
ORA-01017: invalid username/password; logon denied

检查错误跟踪文件内容如下:

2017-05-02 21:47:39.181 :    AGFW:380081920: {0:0:169} Agent received the message: RESOURCE_START[ora.asm jytest3 1] ID 4098:1530
2017-05-02 21:47:39.181 :    AGFW:380081920: {0:0:169} Preparing START command for: ora.asm jytest3 1
2017-05-02 21:47:39.181 :    AGFW:380081920: {0:0:169} ora.asm jytest3 1 state changed from: OFFLINE to: STARTING
2017-05-02 21:47:39.181 :    AGFW:380081920: {0:0:169} RECYCLE_AGENT attribute not found
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] (:CLSN00107:) clsn_agent::start {
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::start 000 { 
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::start stopConnection 020
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::stopConnection
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::broadcastEvent 000 entry { OHSid:/u01/app/grid/product/12.2.0/crs+ASM 

s_ohSidEventMapLock:0x139e930 action:2
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::removeConnection connection count 0
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::removeConnection freed 0
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::stopConnection sid +ASM status  1
2017-05-02 21:47:39.182 : USRTHRD:388486912: {0:0:169} ConnectionPool::~ConnectionPool  destructor this:f4061e90 m_oracleHome:/u01/app/grid/product/12.2.0/crs, 

m_oracleSid:+ASM,  m_usrOraEnv:  m_pResState:0x7f10f4049000
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] AsmAgent::refresh
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] AsmAgent::refresh ORACLE_HOME = /u01/app/grid/product/12.2.0/crs
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] clsnUtils::cmdIdIsStart CmdId:257
2017-05-02 21:47:39.182 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] clsnUtils::cmdIdIsStart CmdId:257
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] AsmAgent:getOracleSidAttrib 2 getResAttrib USR_ORA_INST_NAME oracleSid:+ASM
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] AsmAgent:getOracleSidAttrib oracleSid:+ASM
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] AsmAgent::refresh ORACLE_SID = +ASM
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::ConnectionPool 2 constructor this:406d700 

m_oracleHome:/u01/app/grid/product/12.2.0/crs, m_oracleSid:+ASM, m_usrOraEnv: m_instanceType:2 m_instanceVersion:12.2.0.1.0
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::ConnectionPool 2 constructor m_pResState:0x7f1104033540
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] AsmAgent::setOracleSidAttrib updating GEN_USR_ORA_INST_NAME to +ASM
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] clsnUtils::setResAttrib nonPerX current value GEN_USR_ORA_INST_NAME value +ASM
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Utils::setResAttrib clsagfw_modify_attribute attr GEN_USR_ORA_INST_NAME value +ASM retCode 0
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] sModifyConfig entry resversion:12.2.0.1.0 compId:+ASM comment:StartOption[1] {
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] sInitFile entry pathname:/etc finename:oratab
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] sclsnInstAgent::sInitFile pathname:/etc 

backupPath:/u01/app/grid/product/12.2.0/crs/srvm/admin/ filename:oratab pConfigF:0x7f11040752d8
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::ConfigFile constructor name:oratab
2017-05-02 21:47:39.183 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] CssLock::lock s_siha_mtex, got lock CLSN.oratab.jytest3
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::parse parseLine name: value: comment:
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::parse mmap name:+asm nameWithCase:+ASM value:/u01/app/grid/product/12.2.0/crs:N 

comment:
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::parse mmap name:jy nameWithCase:jy value:/u01/app/oracle/product/12.2.0/db:N 

comment:
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] CssLock::unlock s_siha_mtex, released lock CLSN.oratab.jytest3
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::setAltName this:0x7f11040d80e0 altName:+ASM
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::setAltValue altValue:/u01/app/grid/product/12.2.0/crs:N
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] sclsnInstAgent::sInitFile resType:ora.asm.type setAltName(compId):+ASM setAltValue

(oracleHome):/u01/app/grid/product/12.2.0/crs:N
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] clsnUtils::cmdIdIsStart CmdId:257
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] clsnUtils::cmdIdIsStart CmdId:257
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] AsmAgent:getOracleSidAttrib 2 getResAttrib USR_ORA_INST_NAME oracleSid:+ASM
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] AsmAgent:getOracleSidAttrib oracleSid:+ASM
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::getAltName this:0x7f11040d80e0
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile:getAltName altName:+asm
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] sclsnInstAgent::sInitFile dbname:+ASM altName:+asm
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::getComment name:+asm comment:
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::getAltName this:0x7f11040d80e0
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile:getAltName altName:+asm
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::getAltName this:0x7f11040d80e0
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile:getAltName altName:+asm
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::getComment name:+asm comment:
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] sclsnInstAgent::sInitFile exit dbname:+ASM startup comment: startup altName:+asm comment:
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::start pConfigF:40d80e0
2017-05-02 21:47:39.184 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] CssLock::lock s_siha_mtex, got lock CLSN.oratab.jytest3
2017-05-02 21:47:39.185 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::parse parseLine name: value: comment:
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::parse mmap name:+asm nameWithCase:+ASM value:/u01/app/grid/product/12.2.0/crs:N 

comment:
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::parse mmap name:jy nameWithCase:jy value:/u01/app/oracle/product/12.2.0/db:N 

comment:
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] sclsnInstAgent::sCleanEntry - delete alt(sid) entry
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] clsnUtils::cmdIdIsStart CmdId:257
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] clsnUtils::cmdIdIsStart CmdId:257
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] AsmAgent:getOracleSidAttrib 2 getResAttrib USR_ORA_INST_NAME oracleSid:+ASM
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] AsmAgent:getOracleSidAttrib oracleSid:+ASM
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] sclsnInstAgent::sCleanEntry - key = +ASM
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::parse parseLine name: value: comment:
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::parse mmap name:+asm nameWithCase:+ASM value:/u01/app/grid/product/12.2.0/crs:N 

comment:
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::parse mmap name:jy nameWithCase:jy value:/u01/app/oracle/product/12.2.0/db:N 

comment:
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConfigFile::updateInPlace file /etc/oratab is not modified
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] CssLock::unlock s_siha_mtex, released lock CLSN.oratab.jytest3
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] sclsnInstAgent::sCleanEntry - key[+ASM] is cleaned
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] sModifyConfig exit for compId:+ASM }
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Utils::getResAttrib entry attribName:USR_ORA_OPI required:0 loglevel:1
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Utils::getResAttrib: attribname USR_ORA_OPI value false len 5
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Utils::getResAttrib attribname:USR_ORA_OPI value:false exit
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Agent::valueOfAttribIs attrib: REASON compare value: user attribute value: user
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Agent::valueOfAttribIs returns 1
2017-05-02 21:47:39.186 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::start 100 getGenRestart
2017-05-02 21:47:39.187 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::getGenRestart exit GEN_RESTART:StartOption[1] }
2017-05-02 21:47:39.187 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::start 120 comment:StartOption[1]
2017-05-02 21:47:39.187 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Agent::valueOfAttribIs attrib: REASON compare value: failure attribute value: user
2017-05-02 21:47:39.187 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Agent::valueOfAttribIs returns 0
2017-05-02 21:47:39.187 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::checkState 030 new gimh oracleSid:+ASM
2017-05-02 21:47:39.187 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Gimh::constructor ohome:/u01/app/grid/product/12.2.0/crs sid:+ASM
2017-05-02 21:47:39.187 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::resetConnection  s_statusOfConnectionMap:0x139ea20
2017-05-02 21:47:39.187 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::resetConnection sid +ASM status  2
2017-05-02 21:47:39.187 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Gimh::check condition changes to (GIMH_NEXT_NUM) 0(Abnormal Termination) exists
2017-05-02 21:47:39.187 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] CLS_DRUID_REF(CLSN00006) AsmAgent::gimhChecks 100 failed gimh state 0
2017-05-02 21:47:39.188 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] (:CLSN00006:)InstAgent::checkState 110 return unplanned offline
2017-05-02 21:47:39.188 : USRTHRD:388486912: {0:0:169} Gimh::destructor gimh_dest_query_ctx rc=0
2017-05-02 21:47:39.188 : USRTHRD:388486912: {0:0:169} Gimh::destructor gimh_dest_inst_ctx rc=0
2017-05-02 21:47:39.188 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::stopConnection
2017-05-02 21:47:39.188 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::broadcastEvent 000 entry { OHSid:/u01/app/grid/product/12.2.0/crs+ASM 

s_ohSidEventMapLock:0x139e930 action:2
2017-05-02 21:47:39.188 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::removeConnection connection count 0
2017-05-02 21:47:39.188 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::removeConnection freed 0
2017-05-02 21:47:39.188 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ConnectionPool::stopConnection sid +ASM status  1
2017-05-02 21:47:39.188 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::checkState 200 prev clsagfw_res_status 3 current clsagfw_res_status 1
2017-05-02 21:47:39.188 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::start 180 startOption:StartOption[1]
2017-05-02 21:47:39.189 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] clsnUtils::setResAttrib nonPerX current value GEN_RESTART value StartOption[1]
2017-05-02 21:47:39.189 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Utils::setResAttrib clsagfw_modify_attribute attr GEN_RESTART value StartOption[1] retCode 0
2017-05-02 21:47:39.189 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::setGenRestart updating GEN_RESTART to StartOption[1] retcode:0 ohasd resource:1
2017-05-02 21:47:39.189 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::start 200 StartInstance with startoption:1 pfile:null 
2017-05-02 21:47:39.189 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::start 400 startInstance
2017-05-02 21:47:39.189 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::startInstance 000 { startOption:1
2017-05-02 21:47:39.189 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstConnection:InstConnection: init:040d5ef0 oracleHome:/u01/app/grid/product/12.2.0/crs 

oracleSid:+ASM instanceType:2 instanceVersion:12.2.0.1.0 
2017-05-02 21:47:39.189 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] clsnInstConnection::makeConnectStr UsrOraEnv  m_oracleHome /u01/app/grid/product/12.2.0/crs 

Crshome /u01/app/grid/product/12.2.0/crs
2017-05-02 21:47:39.189 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] clsnInstConnection::makeConnectStr = (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)

(PROGRAM=/u01/app/grid/product/12.2.0/crs/bin/oracle)(ARGV0=oracle+ASM)(ENVS='ORACLE_HOME=/u01/app/grid/product/12.2.0/crs,ORACLE_SID=+ASM')(ARGS='(DESCRIPTION=

(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')(CONNECT_DATA=(SID=+ASM))))
2017-05-02 21:47:39.191 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] Container:start oracle home /u01/app/grid/product/12.2.0/crs
2017-05-02 21:47:39.191 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstAgent::startInstance 020 connect logmode:8008
2017-05-02 21:47:39.191 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] InstConnection::connectInt 020 server not attached
2017-05-02 21:47:39.694 :  CLSDMC:357324544: command 0 failed with status 1
2017-05-02 21:47:39.695 :CLSDYNAM:357324544: [ora.evmd]{0:0:2} [check] DaemonAgent::check returned 0
2017-05-02 21:47:39.695 :CLSDYNAM:357324544: [ora.evmd]{0:0:2} [check] Deep check returned 1
2017-05-02 21:47:40.210 :CLSDYNAM:388486912: [ ora.asm]{0:0:169} [start] ORA-01017: invalid username/password; logon denied

关于ASM启动报无效用户名与密码的问题在MOS上有一篇相关文档”ASM not Starting With: ORA-01017: invalid username/password; logon denied (Doc ID 1918617.1)”,说原因是因为修改了sqlnet.ora文件,正确设置应该如下:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

但我这里并不是这种情况,从另一篇文档”Oracle Restart – ASM Startup fails with PRCR-1079 (Doc ID 1904942.1)”找到类似的错误信息,说原因是因为ASM资源的ACL(访问控制列表)的权限发生了改变。

检查asm资源的访问控制列表权限

[grid@jytest3 dbs]$  crsctl stat res ora.asm -p
NAME=ora.asm
TYPE=ora.asm.type
ACL=owner:grid:rwx,pgrp:asmdba:r-x,other::r--
ACTIONS=
ACTION_SCRIPT=
ACTION_TIMEOUT=60
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
ASM_DISKSTRING=AFD:*
AUTO_START=restore
CHECK_INTERVAL=1
CHECK_TIMEOUT=30
CLEAN_TIMEOUT=60
CSS_CRITICAL=no
DEGREE=1
DELETE_TIMEOUT=60
DESCRIPTION=Oracle ASM resource
ENABLED=1
GEN_RESTART=StartOption[1]
GEN_USR_ORA_INST_NAME=+ASM
IGNORE_TARGET_ON_FAILURE=no
INSTANCE_FAILOVER=1
INTERMEDIATE_TIMEOUT=0
LOAD=1
LOGGING_LEVEL=1
MODIFY_TIMEOUT=60
NLS_LANG=
OFFLINE_CHECK_INTERVAL=0
OS_CRASH_THRESHOLD=0
OS_CRASH_UPTIME=0
PRESENCE=standard
PWFILE=+DATA/orapwasm
PWFILE_BACKUP=
REGISTERED_TYPE=srvctl
RESOURCE_GROUP=
RESTART_ATTEMPTS=5
RESTART_DELAY=0
SCRIPT_TIMEOUT=60
SERVER_CATEGORY=
SPFILE=+DATA/ASM/ASMPARAMETERFILE/registry.253.938201547
START_CONCURRENCY=0
START_DEPENDENCIES=hard(ora.cssd) weak(ora.LISTENER.lsnr)
START_DEPENDENCIES_RTE_INTERNAL=
START_TIMEOUT=900
STOP_CONCURRENCY=0
STOP_DEPENDENCIES=hard(ora.cssd)
STOP_DEPENDENCIES_RTE_INTERNAL=
STOP_TIMEOUT=600
TARGET_DEFAULT=default
TYPE_VERSION=1.2
UPTIME_THRESHOLD=1d
USER_WORKLOAD=no
USR_ORA_ENV=
USR_ORA_INST_NAME=+ASM
USR_ORA_OPEN_MODE=mount  
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
WORKLOAD_CPU=0
WORKLOAD_CPU_CAP=0
WORKLOAD_MEMORY_MAX=0
WORKLOAD_MEMORY_TARGET=0

从上面的信息ACL=owner:grid:rwx,pgrp:asmdba:r-x,other::r–,可知组名(pgrp)变为了asmdba组,正常来说应该是grid用户的oinstall组,如果尝试修改:

[grid@jytest3 dbs]$ crsctl setperm resource ora.asm -g oinstall
CRS-4995:  The command 'Setperm  resource' is invalid in crsctl. Use srvctl for this command.

修改时出错了,在12.2.0.1中crsctl setperm命令是一种无效的crsctl,建议使用srvctl命令,但srvctl没有setperm命令,如是加上-unsupported参数再次执行修改:

[grid@jytest3 dbs]$ crsctl setperm resource ora.asm -g oinstall -unsupported

重启Oracle Restart,ASM实例正常启动

[grid@jytest3 lib]$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'jytest3'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'jytest3'
CRS-2673: Attempting to stop 'ora.evmd' on 'jytest3'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'jytest3' succeeded
CRS-2677: Stop of 'ora.evmd' on 'jytest3' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'jytest3'
CRS-2677: Stop of 'ora.cssd' on 'jytest3' succeeded
CRS-2673: Attempting to stop 'ora.driver.afd' on 'jytest3'
CRS-2677: Stop of 'ora.driver.afd' on 'jytest3' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'jytest3' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[grid@jytest3 ~]$ crsctl start has
CRS-4123: Oracle High Availability Services has been started.
[grid@jytest3 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       jytest3                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest3                  STABLE
ora.asm
               ONLINE  ONLINE       jytest3                  Started,STABLE
ora.ons
               OFFLINE OFFLINE      jytest3                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.driver.afd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.evmd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.jy.db
      1        ONLINE  ONLINE       jytest3                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
--------------------------------------------------------------------------------

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       

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

Oracle Linux 7 Configure DNS

Oracle Linux 7.1配置DNS服务
一.安装DNS需要的软件包

# yum install bind-libs bind bind-utils

二.编辑named.conf文件
在编辑前先复制一份named.conf文件

[root@jytest1 ~]# cp /etc/named.conf /etc/named.conf.backup


[root@jytest1 ~]# vi /etc/named.conf
//
// named.conf
//
// Provided by Red Hat bind package to configure the ISC BIND named(8) DNS
// server as a caching only nameserver (as a localhost DNS resolver only).
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//

options {
        listen-on port 53 { any; };--将127.0.0.1修改成any
        listen-on-v6 port 53 { ::1; };
        directory       "/var/named";
        dump-file       "/var/named/data/cache_dump.db";
        statistics-file "/var/named/data/named_stats.txt";
        memstatistics-file "/var/named/data/named_mem_stats.txt";
        allow-query     { any; };--将127.0.0.1修改成any

        /* 
         - If you are building an AUTHORITATIVE DNS server, do NOT enable recursion.
         - If you are building a RECURSIVE (caching) DNS server, you need to enable 
           recursion. 
         - If your recursive DNS server has a public IP address, you MUST enable access 
           control to limit queries to your legitimate users. Failing to do so will
           cause your server to become part of large scale DNS amplification 
           attacks. Implementing BCP38 within your network would greatly
           reduce such attack surface 
        */
        recursion yes;

        dnssec-enable yes;
        dnssec-validation yes;
        dnssec-lookaside auto;

        /* Path to ISC DLV key */
        bindkeys-file "/etc/named.iscdlv.key";

        managed-keys-directory "/var/named/dynamic";

        pid-file "/run/named/named.pid";
        session-keyfile "/run/named/session.key";
};

logging {
        channel default_debug {
                file "data/named.run";
                severity dynamic;
        };
};

zone "." IN {
        type hint;
        file "named.ca";
};
include "/etc/named.rfc1912.zones";
include "/etc/named.root.key";

三.配置host.conf

[root@jytest1 ~]# cat /etc/host.conf
multi on

该文件指定如何解析主机名。Linux通过解析器库来获得主机名对应的IP地址。下面是一个“/etc/host.conf”的示例:
order bind,hosts
multi on
nospoof on
“order bind,hosts”指定主机名查询顺序,这里规定先使用DNS来解析域名,然后再查询“/etc/hosts”文件(也可以相反)。
“multi on”指定是否“/etc/hosts”文件中指定的主机可以有多个地址,拥有多个IP地址的主机一般称为多穴主机。
“nospoof on”指不允许对该服务器进行IP地址欺骗。IP欺骗是一种攻击系统安全的手段,通过把IP地址伪装成别的计算机,来取得其它计算机的信任。

四.修改/etc/named.rfc1912.zones

[root@jytest1 ~]# cp /etc/named.rfc1912.zones /etc/named.rfc1912.zones.backup
[root@jytest1 ~]# vi /etc/named.rfc1912.zones
// named.rfc1912.zones:
//
// Provided by Red Hat caching-nameserver package
//
// ISC BIND named zone configuration for zones recommended by
// RFC 1912 section 4.1 : localhost TLDs and address zones
// and http://www.ietf.org/internet-drafts/draft-ietf-dnsop-default-local-zones-02.txt
// (c)2007 R W Franks
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//

zone "localhost.localdomain" IN {
        type master;
        file "named.localhost";
        allow-update { none; };
};

zone "localhost" IN {
        type master;
        file "named.localhost";
        allow-update { none; };
};

zone "1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.arpa" IN {
        type master;
        file "named.loopback";
        allow-update { none; };
};

zone "1.0.0.127.in-addr.arpa" IN {
        type master;
        file "named.loopback";
        allow-update { none; };
};

zone "0.in-addr.arpa" IN {
        type master;
        file "named.empty";
        allow-update { none; };
};

--下面为增加的内容,jybd.net.forward为正向解析,jydba.net.reverse为反向解析
zone "jydba.net" IN {
        type master;
        file "jydba.net.forward";
        allow-update { none; };
};

zone "130.138.10.in-addr.arpa" IN {
        type master;
        file "jydba.net.reverse";
        allow-update { none; };
};
"/etc/named.rfc1912.zones" 54L, 1171C written

五.修改具体的zone配置文件

[root@jytest1 named]# cd /var/named
[root@jytest1 named]# cp named.localhost jydba.net.forward
[root@jytest1 named]# cp named.loopback jydba.net.reverse

[root@jytest1 named]# vi jydba.net.forward
$TTL 1D
@       IN SOA  @ root.jydba.net. (
                                        0       ; serial
                                        1D      ; refresh
                                        1H      ; retry
                                        1W      ; expire
                                        3H )    ; minimum
        NS      @
        A       10.138.130.171
        AAAA    ::1

jytest1              A            10.138.130.171
jytest2              A            10.138.130.172
jytest1-vip          A            10.138.130.175
jytest2-vip          A            10.138.130.176
jytest-scan          A            10.138.130.177
jytest-scan          A            10.138.130.178
jytest-scan          A            10.138.130.179


[root@jytest1 named]# vi jydba.net.reverse
$TTL 1D
@       IN SOA  @ root.jydba.net. (
                                        0       ; serial
                                        1D      ; refresh
                                        1H      ; retry
                                        1W      ; expire
                                        3H )    ; minimum
        NS      @
        A       10.138.130.171
        AAAA    ::1
        PTR     localhost.

171           PTR     jytest1
172           PTR     jytest2
175           PTR     jytest1-vip
176           PTR     jytest2-vip
177           PTR     jytest-scan
178           PTR     jytest-scan
179           PTR     jytest-scan

六.配置resolv.conf

[root@jytest1 named]# cat /etc/resolv.conf
# Generated by NetworkManager
search jydba.net


# No nameservers found; try putting DNS servers into your
# ifcfg files in /etc/sysconfig/network-scripts like so:
#
# DNS1=xxx.xxx.xxx.xxx
# DNS2=xxx.xxx.xxx.xxx
# DOMAIN=lab.foo.com bar.foo.com
search jydba.net
nameserver       10.138.130.171

七.测试

[root@jytest1 named]# dig -x 10.138.130.172

; < <>> DiG 9.9.4-RedHat-9.9.4-18.el7 < <>> -x 10.138.130.172
;; global options: +cmd
;; Got answer:
;; ->>HEADER< <- opcode: QUERY, status: REFUSED, id: 20870
;; flags: qr rd; QUERY: 1, ANSWER: 0, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;172.130.138.10.in-addr.arpa.   IN      PTR

;; Query time: 6 msec
;; SERVER: 10.138.130.171#53(10.138.130.171)
;; WHEN: Fri Mar 17 19:15:47 CST 2017
;; MSG SIZE  rcvd: 56




[root@jytest1 named]# service named restart
Redirecting to /bin/systemctl restart  named.service
[root@jytest1 named]# nslookup jytest1
Server:         10.138.130.171
Address:        10.138.130.171#53

** server can't find jytest1: SERVFAIL

上面出现错误,server can't find jytest1: SERVFAIL,错误信息是因为之前创建文件时使用的是root用户,将这些创建的文件修改为named用户与组。

[root@jytest1 named]# ls -lrt
total 32
-rw-r-----. 1 root  named  152 Jun 21  2007 named.localhost
-rw-r-----. 1 root  named  168 Dec 15  2009 named.loopback
-rw-r-----. 1 root  named  152 Dec 15  2009 named.empty
-rw-r-----. 1 root  named 2076 Jan 28  2013 named.ca
drwxrwx---. 2 root  named    6 Mar  6  2015 dyndb-ldap
drwxrwx---. 2 named named    6 Mar  6  2015 slaves
drwxr-x---. 7 root  named   56 Nov  5 11:03 chroot
-rw-r-----  1 root  named  728 Mar 17 18:45 named.jydba
-rw-r--r--  1 root  root   829 Mar 17 18:45 jydba.zone
drwxrwx---. 2 named named   22 Mar 17 18:45 data
-rw-r-----  1 root  root   503 Mar 17 19:13 jydba.net.forward
-rw-r-----  1 root  root   406 Mar 17 19:15 jydba.net.reverse
drwxrwx---. 2 named named   58 Mar 17 19:16 dynamic
[root@jytest1 named]# chown -R named:named jydba*
[root@jytest1 named]# ls -lrt
total 32
-rw-r-----. 1 root  named  152 Jun 21  2007 named.localhost
-rw-r-----. 1 root  named  168 Dec 15  2009 named.loopback
-rw-r-----. 1 root  named  152 Dec 15  2009 named.empty
-rw-r-----. 1 root  named 2076 Jan 28  2013 named.ca
drwxrwx---. 2 root  named    6 Mar  6  2015 dyndb-ldap
drwxrwx---. 2 named named    6 Mar  6  2015 slaves
drwxr-x---. 7 root  named   56 Nov  5 11:03 chroot
-rw-r-----  1 root  named  728 Mar 17 18:45 named.jydba
-rw-r--r--  1 named named  829 Mar 17 18:45 jydba.zone
drwxrwx---. 2 named named   22 Mar 17 18:45 data
-rw-r-----  1 named named  503 Mar 17 19:13 jydba.net.forward
-rw-r-----  1 named named  406 Mar 17 19:15 jydba.net.reverse
drwxrwx---. 2 named named   58 Mar 17 19:16 dynamic
[root@jytest1 named]# systemctl restart  named.service
[root@jytest1 named]# nslookup jytest1
Server:         10.138.130.171
Address:        10.138.130.171#53

Name:   jytest1.jydba.net
Address: 10.138.130.171

[root@jytest1 named]# nslookup jytest1.jydba.net
Server:         10.138.130.171
Address:        10.138.130.171#53

Name:   jytest1.jydba.net
Address: 10.138.130.171

[root@jytest1 named]# nslookup jytest2-priv.jydba.net
Server:         10.138.130.171
Address:        10.138.130.171#53

** server can't find jytest2-priv.jydba.net: NXDOMAIN

[root@jytest1 named]# nslookup jytest2-vip.jydba.net
Server:         10.138.130.171
Address:        10.138.130.171#53

Name:   jytest2-vip.jydba.net
Address: 10.138.130.176

[root@jytest1 named]# nslookup jytest-scan.jydba.net
Server:         10.138.130.171
Address:        10.138.130.171#53

Name:   jytest-scan.jydba.net
Address: 10.138.130.178
Name:   jytest-scan.jydba.net
Address: 10.138.130.179
Name:   jytest-scan.jydba.net
Address: 10.138.130.177

[root@jytest1 named]# nslookup 10.138.130.179
Server:         10.138.130.171
Address:        10.138.130.171#53

179.130.138.10.in-addr.arpa     name = jytest-scan.130.138.10.in-addr.arpa.

通过测试可以看到DNS通过正向与反向解析都是正常的,说明配置成功。

注意:
对于Linux 使用NetworkManager来控制网络的操作系统,当主机重启之前/etc/resolv.conf文件可能会被重写。如果发生这种情况,需要对相应的网卡配置文件增加以下记录
对于Oracle Linux 6修改类似文件 /etc/sysconfig/network-scripts/ifcfg-eth0 (ifcfg-eth1 etc.)
对于Oracle Linux 7修改类似文/etc/sysconfig/network-scripts/ifcfg-ens160 (ifcfg-ens34 etc.)
DNS1=10.138.130.171
DOMAIN=jydba.net

Oracle Linux 7.1 增加网卡及IP配置

由于需要给虚拟机(oracle linux 7.1)增加网卡并设置IP操作,在增加网卡后执行ifconfig命令可以看到增加的网卡信息,但没有生成网卡配置文件

[root@jytest1 ~]# ifconfig
ens34: flags=4163  mtu 1500
        ether 00:50:56:a0:7e:4c  txqueuelen 1000  (Ethernet)
        RX packets 14332  bytes 1177864 (1.1 MiB)
        RX errors 0  dropped 25  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160: flags=4163  mtu 1500
        inet 10.138.130.171  netmask 255.255.255.0  broadcast 10.138.130.255
        inet6 fe80::250:56ff:feb1:260  prefixlen 64  scopeid 0x20
        ether 00:50:56:b1:02:60  txqueuelen 1000  (Ethernet)
        RX packets 14524  bytes 1203727 (1.1 MiB)
        RX errors 0  dropped 11  overruns 0  frame 0
        TX packets 349  bytes 31569 (30.8 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 0  (Local Loopback)
        RX packets 1152  bytes 89096 (87.0 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 1152  bytes 89096 (87.0 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0: flags=4099  mtu 1500
        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 52:54:00:88:ce:47  txqueuelen 0  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 6  bytes 1172 (1.1 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
[root@jytest1 ~]# cd /etc/sysconfig/network-scripts
[root@jytest1 network-scripts]# ls -lrt
total 232
-rwxr-xr-x. 1 root root  1876 Nov  6  2014 ifup-TeamPort
-rwxr-xr-x. 1 root root  1755 Nov  6  2014 ifup-Team
-rwxr-xr-x. 1 root root  1556 Nov  6  2014 ifdown-TeamPort
-rwxr-xr-x. 1 root root  1599 Nov  6  2014 ifdown-Team
-rw-r--r--. 1 root root 26134 Jan 15  2015 network-functions-ipv6
-rw-r--r--. 1 root root 15316 Jan 15  2015 network-functions
-rwxr-xr-x. 1 root root  4623 Jan 15  2015 init.ipv6-global
-rwxr-xr-x. 1 root root  1740 Jan 15  2015 ifup-wireless
-rwxr-xr-x. 1 root root  2682 Jan 15  2015 ifup-tunnel
-rwxr-xr-x. 1 root root  3263 Jan 15  2015 ifup-sit
-rwxr-xr-x. 1 root root  1925 Jan 15  2015 ifup-routes
-rwxr-xr-x. 1 root root  4154 Jan 15  2015 ifup-ppp
-rwxr-xr-x. 1 root root  2609 Jan 15  2015 ifup-post
-rwxr-xr-x. 1 root root  1043 Jan 15  2015 ifup-plusb
-rwxr-xr-x. 1 root root   642 Jan 15  2015 ifup-plip
-rwxr-xr-x. 1 root root 10430 Jan 15  2015 ifup-ipv6
-rwxr-xr-x. 1 root root 12039 Jan 15  2015 ifup-ippp
-rwxr-xr-x. 1 root root 11502 Jan 15  2015 ifup-eth
-rwxr-xr-x. 1 root root   859 Jan 15  2015 ifup-bnep
-rwxr-xr-x. 1 root root 12616 Jan 15  2015 ifup-aliases
-rwxr-xr-x. 1 root root  1462 Jan 15  2015 ifdown-tunnel
-rwxr-xr-x. 1 root root  1444 Jan 15  2015 ifdown-sit
-rwxr-xr-x. 1 root root   837 Jan 15  2015 ifdown-routes
-rwxr-xr-x. 1 root root  1068 Jan 15  2015 ifdown-ppp
-rwxr-xr-x. 1 root root  1642 Jan 15  2015 ifdown-post
-rwxr-xr-x. 1 root root  4201 Jan 15  2015 ifdown-ipv6
-rwxr-xr-x. 1 root root   781 Jan 15  2015 ifdown-ippp
-rwxr-xr-x. 1 root root  5817 Jan 15  2015 ifdown-eth
-rwxr-xr-x. 1 root root   627 Jan 15  2015 ifdown-bnep
-rw-r--r--. 1 root root   254 Jan 15  2015 ifcfg-lo
-rwxr-xr-x. 1 root root 10145 Mar  6  2015 ifup-ib
-rwxr-xr-x. 1 root root  6196 Mar  6  2015 ifdown-ib
lrwxrwxrwx. 1 root root    24 Nov  5 10:11 ifdown -> ../../../usr/sbin/ifdown
lrwxrwxrwx. 1 root root    11 Nov  5 10:11 ifdown-isdn -> ifdown-ippp
lrwxrwxrwx. 1 root root    22 Nov  5 10:11 ifup -> ../../../usr/sbin/ifup
lrwxrwxrwx. 1 root root     9 Nov  5 10:11 ifup-isdn -> ifup-ippp
-rw-r--r--. 1 root root   358 Nov  5 12:01 ifcfg-ens160

可以看到并没有生成网卡配置文件ifcfg-ens34,而之前在创建虚拟机时增加的网卡ens160是有相应的配置文件ifcfg-ens160。在oracle linux 7.1中使用nmtui来设置网络,nmtui 属于curses-based text user interface(文本用户界面), 类似Linux7以前的setup工具

[root@jytest1 ~]# nmtui

使用nmtui设置完后可以看到生成了网卡ens34的配置文件ifcfg-ens34

 [root@jytest1 network-scripts]# ls -lrt
total 236
-rwxr-xr-x. 1 root root  1876 Nov  6  2014 ifup-TeamPort
-rwxr-xr-x. 1 root root  1755 Nov  6  2014 ifup-Team
-rwxr-xr-x. 1 root root  1556 Nov  6  2014 ifdown-TeamPort
-rwxr-xr-x. 1 root root  1599 Nov  6  2014 ifdown-Team
-rw-r--r--. 1 root root 26134 Jan 15  2015 network-functions-ipv6
-rw-r--r--. 1 root root 15316 Jan 15  2015 network-functions
-rwxr-xr-x. 1 root root  4623 Jan 15  2015 init.ipv6-global
-rwxr-xr-x. 1 root root  1740 Jan 15  2015 ifup-wireless
-rwxr-xr-x. 1 root root  2682 Jan 15  2015 ifup-tunnel
-rwxr-xr-x. 1 root root  3263 Jan 15  2015 ifup-sit
-rwxr-xr-x. 1 root root  1925 Jan 15  2015 ifup-routes
-rwxr-xr-x. 1 root root  4154 Jan 15  2015 ifup-ppp
-rwxr-xr-x. 1 root root  2609 Jan 15  2015 ifup-post
-rwxr-xr-x. 1 root root  1043 Jan 15  2015 ifup-plusb
-rwxr-xr-x. 1 root root   642 Jan 15  2015 ifup-plip
-rwxr-xr-x. 1 root root 10430 Jan 15  2015 ifup-ipv6
-rwxr-xr-x. 1 root root 12039 Jan 15  2015 ifup-ippp
-rwxr-xr-x. 1 root root 11502 Jan 15  2015 ifup-eth
-rwxr-xr-x. 1 root root   859 Jan 15  2015 ifup-bnep
-rwxr-xr-x. 1 root root 12616 Jan 15  2015 ifup-aliases
-rwxr-xr-x. 1 root root  1462 Jan 15  2015 ifdown-tunnel
-rwxr-xr-x. 1 root root  1444 Jan 15  2015 ifdown-sit
-rwxr-xr-x. 1 root root   837 Jan 15  2015 ifdown-routes
-rwxr-xr-x. 1 root root  1068 Jan 15  2015 ifdown-ppp
-rwxr-xr-x. 1 root root  1642 Jan 15  2015 ifdown-post
-rwxr-xr-x. 1 root root  4201 Jan 15  2015 ifdown-ipv6
-rwxr-xr-x. 1 root root   781 Jan 15  2015 ifdown-ippp
-rwxr-xr-x. 1 root root  5817 Jan 15  2015 ifdown-eth
-rwxr-xr-x. 1 root root   627 Jan 15  2015 ifdown-bnep
-rw-r--r--. 1 root root   254 Jan 15  2015 ifcfg-lo
-rwxr-xr-x. 1 root root 10145 Mar  6  2015 ifup-ib
-rwxr-xr-x. 1 root root  6196 Mar  6  2015 ifdown-ib
lrwxrwxrwx. 1 root root    24 Nov  5 10:11 ifdown -> ../../../usr/sbin/ifdown
lrwxrwxrwx. 1 root root    11 Nov  5 10:11 ifdown-isdn -> ifdown-ippp
lrwxrwxrwx. 1 root root    22 Nov  5 10:11 ifup -> ../../../usr/sbin/ifup
lrwxrwxrwx. 1 root root     9 Nov  5 10:11 ifup-isdn -> ifup-ippp
-rw-r--r--. 1 root root   346 Mar 16 12:36 ifcfg-ens160
-rw-r--r--. 1 root root   181 Mar 16 12:42 ifcfg-ens34

编辑配置文件

[root@jytest1 network-scripts]# vi ifcfg-ens34
TYPE=Ethernet
BOOTPROTO=none
IPADDR=88.88.88.1
NETMASK=255.255.255.0
PREFIX=32
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=no
NAME=ens34
UUID=f0e4fffc-668c-4b3d-b1cf-ab2b0122dd7d
DEVICE=ens34
ONBOOT=yes

重启网络

[root@jytest1 network-scripts]# systemctl restart network
[root@jytest1 network-scripts]# ifconfig
ens34: flags=4163  mtu 1500
        inet 88.88.88.1  netmask 255.255.255.255  broadcast 88.88.88.1
        inet6 fe80::250:56ff:fea0:7e4c  prefixlen 64  scopeid 0x20
        ether 00:50:56:a0:7e:4c  txqueuelen 1000  (Ethernet)
        RX packets 24804  bytes 2064784 (1.9 MiB)
        RX errors 0  dropped 25  overruns 0  frame 0
        TX packets 57  bytes 8254 (8.0 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160: flags=4163  mtu 1500
        inet 10.138.130.171  netmask 255.255.255.0  broadcast 10.138.130.255
        inet6 fe80::250:56ff:feb1:260  prefixlen 64  scopeid 0x20
        ether 00:50:56:b1:02:60  txqueuelen 1000  (Ethernet)
        RX packets 25331  bytes 2120003 (2.0 MiB)
        RX errors 0  dropped 11  overruns 0  frame 0
        TX packets 767  bytes 84120 (82.1 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 0  (Local Loopback)
        RX packets 1480  bytes 113808 (111.1 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 1480  bytes 113808 (111.1 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0: flags=4099  mtu 1500
        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 52:54:00:88:ce:47  txqueuelen 0  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 6  bytes 1172 (1.1 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

 [root@jytest1 network-scripts]# ping jytest1-priv
PING jytest1-priv (88.88.88.1) 56(84) bytes of data.
64 bytes from jytest1-priv (88.88.88.1): icmp_seq=1 ttl=64 time=0.419 ms
64 bytes from jytest1-priv (88.88.88.1): icmp_seq=2 ttl=64 time=0.227 ms
^C
--- jytest1-priv ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1001ms
rtt min/avg/max/mdev = 0.227/0.323/0.419/0.096 ms

配置完成