Monthly Archives: 四月 2016

12c：将一个CDB的所有PDB，导入到另一个CDB中

By Adamhuan | 2016年4月23日

本文一样是读书时的练习：
exp_4_6_1

开始前，说明下：
在我的环境中有两个CDB：
源：

[oracle@rhel7 trace]$ env | grep SID
ORACLE_SID=conme
[oracle@rhel7 trace]$ 
[oracle@rhel7 trace]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 13:41:52 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,cdb from v$database;

NAME      CDB
--------- ---
CONME     YES

SQL> 
SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDBME                          MOUNTED
         4 PDBSHE                         MOUNTED
         5 PDBSHE2                        MOUNTED
         6 PDBORCL                        MOUNTED

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@rhel7 trace]$

[oracle@rhel7 trace]$ env | grep SID

ORACLE_SID=conme

[oracle@rhel7 trace]$

[oracle@rhel7 trace]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 13:41:52 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,cdb from v$database;

NAME CDB

--------- ---

CONME YES

SQL>

SQL> select con_id,name,open_mode from v$pdbs;

CON_ID NAME OPEN_MODE

---------- ------------------------------ ----------

2 PDB$SEED READ ONLY

3 PDBME MOUNTED

4 PDBSHE MOUNTED

5 PDBSHE2 MOUNTED

6 PDBORCL MOUNTED

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@rhel7 trace]$

目标：

[oracle@rhel7 controlfile]$ env | grep SID
ORACLE_SID=callah
[oracle@rhel7 controlfile]$ 
[oracle@rhel7 controlfile]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 13:42:40 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 
SQL> select name,cdb from v$database;

NAME      CDB
--------- ---
CALLAH    YES

SQL> 
SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@rhel7 controlfile]$

[oracle@rhel7 controlfile]$ env | grep SID

ORACLE_SID=callah

[oracle@rhel7 controlfile]$

[oracle@rhel7 controlfile]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 13:42:40 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

SQL> select name,cdb from v$database;

NAME CDB

--------- ---

CALLAH YES

SQL>

SQL> select con_id,name,open_mode from v$pdbs;

CON_ID NAME OPEN_MODE

---------- ------------------------------ ----------

2 PDB$SEED READ ONLY

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@rhel7 controlfile]$

在源端操作：
Unplug PDB：
[cray[……]

ORA-00202: control file: ‘/u01/app/oracle/product/12/db_1/dbs/cntrlcallah.dbf’

By Adamhuan | 2016年4月23日

0 Comment

对于如题所示的问题，虽然，前面写的NOMOUNT阶段手动创建控制文件，虽然也能解决如题所示的问题，但是还有更简单的做法：
日志文件中的问题详情：

Sat Apr 23 10:40:11 2016
ALTER DATABASE   MOUNT
Sat Apr 23 10:40:11 2016
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/product/12/db_1/dbs/cntrlcallah.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE   MOUNT...

Sat Apr 23 10:40:11 2016

ALTER DATABASE MOUNT

Sat Apr 23 10:40:11 2016

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/product/12/db_1/dbs/cntrlcallah.dbf'

ORA-27037: unable to obtain file status

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

Additional information: 3

ORA-205 signalled during: ALTER DATABASE MOUNT...

将本地已有的OMF管理的控制文件，复制到dbs/cntrlcallah.db[……]

Oracle：ORA-27040: file create error, unable to create file

By Adamhuan | 2016年4月23日

0 Comment

Oracle的“create database”命令不会自动的创建不存在的路径，因此，在执行“create database”之前，你没有创建需要的路径的话，你就会遇到如题所示的错误。
具体如下：

[oracle@rhel7 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 09:36:22 2016

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                  2923096 bytes
Variable Size             192939432 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes
SQL> 
SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
callah           STARTED

SQL> 
SQL> create database callah
  2  user sys identified by oracle
  3  user system identified by oracle
  4  logfile
  5  group 1 ('/u01/app/oracle/oradata/CALLAH/datafile/redo01a.log','/u01/app/oracle/oradata/CALLAH/datafile/redo01b.log') size 100m,
  6  group 2 ('/u01/app/oracle/oradata/CALLAH/datafile/redo02a.log','/u01/app/oracle/oradata/CALLAH/datafile/redo02b.log') size 100m,
  7  group 3 ('/u01/app/oracle/oradata/CALLAH/datafile/redo03a.log','/u01/app/oracle/oradata/CALLAH/datafile/redo03b.log') size 100m
  8  maxloghistory 1
  9  maxlogfiles 16
 10  maxlogmembers 3
 11  maxdatafiles 1024
 12  character set AL32UTF8
 13  national character set AL16UTF16
 14  extent management local
 15  datafile '/u01/app/oracle/oradata/CALLAH/datafile/system01.dbf' size 700m reuse autoextend on next 20m maxsize unlimited
 16  sysaux datafile '/u01/app/oracle/oradata/CALLAH/datafile/sysaux01.dbf' size 550m reuse autoextend on next 20m maxsize unlimited
 17  default tablespace users datafile '/u01/app/oracle/oradata/CALLAH/datafile/users01.dbf' size 500m reuse autoextend on maxsize unlimited
 18  default temporary tablespace temptbs01 tempfile '/u01/app/oracle/oradata/CALLAH/datafile/temptbs01a.dbf' size 200m reuse autoextend on next 20m maxsize unlimited
 19  undo tablespace undotbs01 datafile '/u01/app/oracle/oradata/CALLAH/datafile/undotbs01a.dbf' size 300m reuse autoextend on next 50m maxsize unlimited
 20  enable pluggable database
 21  seed file_name_convert=('/u01/app/oracle/oradata/CALLAH/datafile/','/u01/app/oracle/oradata/CALLAH/datafile/pdb_seed_data/')
 22  ;
create database callah
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dcore.bsq' near line 1079
ORA-01519: error while processing file '?/rdbms/admin/dcore.bsq' near line 88
ORA-00604: error occurred at recursive SQL level 1
ORA-01119: error in creating database file
'/u01/app/oracle/oradata/CALLAH/datafile/pdb_seed_data/system01.dbf'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
Process ID: 13059
Session ID: 1 Serial number: 10888


SQL> !ls -ltr /u01/app/oracle/oradata/CALLAH/datafile/
total 1331232
-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo02a.log
-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo03a.log
-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo02b.log
-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo03b.log
-rw-r----- 1 oracle oinstall 734011392 Apr 23 09:43 system01.dbf
-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo01a.log
-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo01b.log

SQL>

[oracle@rhel7 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 09:36:22 2016

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 251658240 bytes

Fixed Size 2923096 bytes

Variable Size 192939432 bytes

Database Buffers 50331648 bytes

Redo Buffers 5464064 bytes

SQL>

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

callah STARTED

SQL>

SQL> create database callah

2 user sys identified by oracle

3 user system identified by oracle

4 logfile

5 group 1 ('/u01/app/oracle/oradata/CALLAH/datafile/redo01a.log','/u01/app/oracle/oradata/CALLAH/datafile/redo01b.log') size 100m,

6 group 2 ('/u01/app/oracle/oradata/CALLAH/datafile/redo02a.log','/u01/app/oracle/oradata/CALLAH/datafile/redo02b.log') size 100m,

7 group 3 ('/u01/app/oracle/oradata/CALLAH/datafile/redo03a.log','/u01/app/oracle/oradata/CALLAH/datafile/redo03b.log') size 100m

8 maxloghistory 1

9 maxlogfiles 16

10 maxlogmembers 3

11 maxdatafiles 1024

12 character set AL32UTF8

13 national character set AL16UTF16

14 extent management local

15 datafile '/u01/app/oracle/oradata/CALLAH/datafile/system01.dbf' size 700m reuse autoextend on next 20m maxsize unlimited

16 sysaux datafile '/u01/app/oracle/oradata/CALLAH/datafile/sysaux01.dbf' size 550m reuse autoextend on next 20m maxsize unlimited

17 default tablespace users datafile '/u01/app/oracle/oradata/CALLAH/datafile/users01.dbf' size 500m reuse autoextend on maxsize unlimited

18 default temporary tablespace temptbs01 tempfile '/u01/app/oracle/oradata/CALLAH/datafile/temptbs01a.dbf' size 200m reuse autoextend on next 20m maxsize unlimited

19 undo tablespace undotbs01 datafile '/u01/app/oracle/oradata/CALLAH/datafile/undotbs01a.dbf' size 300m reuse autoextend on next 50m maxsize unlimited

20 enable pluggable database

21 seed file_name_convert=('/u01/app/oracle/oradata/CALLAH/datafile/','/u01/app/oracle/oradata/CALLAH/datafile/pdb_seed_data/')

22 ;

create database callah

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01501: CREATE DATABASE failed

ORA-01519: error while processing file '?/rdbms/admin/dcore.bsq' near line 1079

ORA-01519: error while processing file '?/rdbms/admin/dcore.bsq' near line 88

ORA-00604: error occurred at recursive SQL level 1

ORA-01119: error in creating database file

'/u01/app/oracle/oradata/CALLAH/datafile/pdb_seed_data/system01.dbf'

ORA-27040: file create error, unable to create file

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

Additional information: 1

Process ID: 13059

Session ID: 1 Serial number: 10888

SQL> !ls -ltr /u01/app/oracle/oradata/CALLAH/datafile/

total 1331232

-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo02a.log

-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo03a.log

-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo02b.log

-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo03b.log

-rw-r----- 1 oracle oinstall 734011392 Apr 23 09:43 system01.dbf

-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo01a.log

-rw-r----- 1 oracle oinstall 104858112 Apr 23 09:43 redo01b.log

SQL>

这个过程中的后台日志（[……]

Linux：批量移走包含某个字符的所有文件

By Adamhuan | 2016年4月23日

0 Comment

没有太多可以说的，直接看代码吧：

[oracle@rhel7 ~]$ cd $ORACLE_HOME/dbs
[oracle@rhel7 dbs]$ ls
hc_callah.dat  hc_orcl.dat     init.ora  lkCONME  orapwconme  snapcf_callah.f   spfileconme.ora
hc_conme.dat   initcallah.ora  lkCALLAH  lkORCL   orapworcl   spfilecallah.ora  spfileorcl.ora
[oracle@rhel7 dbs]$ 
[oracle@rhel7 dbs]$ ls | grep callah
hc_callah.dat
initcallah.ora
snapcf_callah.f
spfilecallah.ora
[oracle@rhel7 dbs]$ 
[oracle@rhel7 dbs]$ mkdir ~/backup
[oracle@rhel7 dbs]$ mv *callah* ~/backup/
[oracle@rhel7 dbs]$ ls | grep callah
[oracle@rhel7 dbs]$

[oracle@rhel7 ~]$ cd $ORACLE_HOME/dbs

[oracle@rhel7 dbs]$ ls

hc_callah.dat hc_orcl.dat init.ora lkCONME orapwconme snapcf_callah.f spfileconme.ora

hc_conme.dat initcallah.ora lkCALLAH lkORCL orapworcl spfilecallah.ora spfileorcl.ora

[oracle@rhel7 dbs]$

[oracle@rhel7 dbs]$ ls | grep callah

hc_callah.dat

initcallah.ora

snapcf_callah.f

spfilecallah.ora

[oracle@rhel7 dbs]$

[oracle@rhel7 dbs]$ mkdir ~/backup

[oracle@rhel7 dbs]$ mv *callah* ~/backup/

[oracle@rhel7 dbs]$ ls | grep callah

[oracle@rhel7 dbs]$

————————
Done。

12c：ORA-00600: internal error code, arguments: [kgskigetelt_subplan], [INTERNAL_PLAN]

By Adamhuan | 2016年4月23日

1 Comment

今天，在启动我自己的实验环境的时候，遇到了如题所示的异常。
关于该异常的具体呈现如下所示：

启动数据库，并尝试操作：

[oracle@rhel7 ~]$ export ORACLE_SID=callah
[oracle@rhel7 ~]$ env | grep SID
ORACLE_SID=callah
[oracle@rhel7 ~]$ 
[oracle@rhel7 ~]$ ps -ef | grep --color pmon
oracle     5670      1  0 20:59 ?        00:00:00 ora_pmon_conme
oracle     9082   5525  0 22:34 pts/1    00:00:00 grep --color=auto --color pmon
[oracle@rhel7 ~]$ 
[oracle@rhel7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 22 22:34:36 2016

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

Connected to an idle instance.

SQL> startup 
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                  2923096 bytes
Variable Size             192939432 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes
Database mounted.
Database opened.
SQL> 
SQL> select con_id,name,open_mode from v$pdbs;
select con_id,name,open_mode from v$pdbs
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 9139
Session ID: 1 Serial number: 38484


SQL> 
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@rhel7 ~]$ 
[oracle@rhel7 ~]$ ps -ef | grep --color pmon
oracle     5670      1  0 20:59 ?        00:00:00 ora_pmon_conme
oracle     9341   5525  0 22:35 pts/1    00:00:00 grep --color=auto --color pmon
[oracle@rhel7 ~]$

[oracle@rhel7 ~]$ export ORACLE_SID=callah

[oracle@rhel7 ~]$ env | grep SID

ORACLE_SID=callah

[oracle@rhel7 ~]$

[oracle@rhel7 ~]$ ps -ef | grep --color pmon

oracle 5670 1 0 20:59 ? 00:00:00 ora_pmon_conme

oracle 9082 5525 0 22:34 pts/1 00:00:00 grep --color=auto --color pmon

[oracle@rhel7 ~]$

[oracle@rhel7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 22 22:34:36 2016

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 251658240 bytes

Fixed Size 2923096 bytes

Variable Size 192939432 bytes

Database Buffers 50331648 bytes

Redo Buffers 5464064 bytes

Database mounted.

Database opened.

SQL>

SQL> select con_id,name,open_mode from v$pdbs;

select con_id,name,open_mode from v$pdbs

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 9139

Session ID: 1 Serial number: 38484

SQL>

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@rhel7 ~]$

[oracle@rhel7 ~]$ ps -ef | grep --color pmon

oracle 5670 1 0 20:59 ? 00:00:00 ora_pmon_conme

oracle 9341 5525 0 22:35 pts/1 00:00:00 grep --color=auto --color pmon

[oracle@rhel7 ~]$

而这个过程中的后台日志（alert）的异常部分如下：
[crayon-59c7baf7dc098[……]