Oracle数据库新版本12c信息汇总

2013/02/26 by Leave a Comment

12c database2

Oracle甲骨文公司的旗舰产品Oracle Database 12c进入release发布的倒计时， 可能在今年7月在上海举行的OOW之前发布。

Oracle Database 12c是甲骨文公司上千名软件工程师耗时7~8年研发的超重量级RDBMS管理型数据库管理系统，可以说是目前世界上技术最为领先的DB产品，业界分析Oracle DB在技术上领先对手5年左右(一家之言)。

 

在这里我们来汇总了解12c的一些新知识！

 

【12c新特性】安装12c Standalone Grid Infrastructure

【12c新特性】EM Database Express

将在版本11.2之后废弃或不再支持的特性

解读Tom介绍的Oracle Database 12c的12个新特性

解读Oracle Database 12.1新特性Pluggable Databases

12c分页查询特性FETCH FIRST ROWS,OFFSET ROWS FETCH NEXT ROW LIMIT Clause子句

【12c新特性】dbms_stats report_gather_auto_stats统计信息报告特性

12c新特性:Recover Table

Oracle Database 12c(12.1) Beta已经开始内部测试

 

 

12c database1

12c 12.1.0.0.2 Beta

 

Filed Under: Oracle, Oracle 12c新特性

【Database 12c】手动创建CDB Container Database容器数据库

2013/02/08 by Leave a Comment

手动建库几乎是每个DBA都需要掌握的技能，而Database 12c中手动创建Container Database容器数据库的过程是如何的呢？

目前12c创建容器数据库Container Database和普通Database存在一点点小的区别，需要指定enable pluggable database，已创建的数据库目前无法转换为容器数据库。

创建必要的目录

mkdir -p /stage/oradata
mkdir -p /stage/fr
mkdir -p /u01/app/oracle/admin/MACLEANCDB/adump

我们创建 实例初始化文件，并创建DB:

 

 

1、 INIT.ORA

##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################

###########################################
# 
###########################################
_enable_pluggable_database=true

###########################################
# Cache and I/O
###########################################
db_block_size=8192

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=""
db_name="MACLEANC"

###########################################
# File Configuration
###########################################
db_create_file_dest="/stage/oradata"
db_recovery_file_dest="/stage/fr"
db_recovery_file_dest_size=5061476352

###########################################
# Miscellaneous
###########################################
compatible=12.0.0.0.0
db_unique_name="MACLEANCDB"
diagnostic_dest=/u01/app/oracle

###########################################
# Network Registration
###########################################
#local_listener=LISTENER_MACLEANCDB

###########################################
# Processes and Sessions
###########################################
processes=300

###########################################
# SGA Memory
###########################################
sga_target=1022361600

###########################################
# Security and Auditing
###########################################
audit_file_dest="/u01/app/oracle/admin/MACLEANCDB/adump"
audit_trail=db
remote_login_passwordfile=EXCLUSIVE

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=MACLEANCDBXDB)"

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=340787200

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1

 

 

 

2、 创建密码文件

 

 

oracle@localhost:~$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/orapwd file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwMACLEANCDB force=y extended=y

Enter password for SYS:

 

 

3、 正式创建DB

 

 

oracle@localhost:~$ export ORACLE_SID=MACLEANCDB
oracle@localhost:~$ sqlplus / as sysdba

SQL> startup nomount pfile='init.ora';
ORACLE instance started.

Total System Global Area 1018830848 bytes
Fixed Size                  2268040 bytes
Variable Size             268436600 bytes
Database Buffers          742391808 bytes
Redo Buffers                5734400 bytes

CREATE DATABASE "MACLEANC"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE SIZE 700M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE SIZE 550M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET UTF8
LOGFILE GROUP 1  SIZE 51200K,
GROUP 2  SIZE 51200K,
GROUP 3  SIZE 51200K
USER SYS IDENTIFIED BY "oracle" USER SYSTEM IDENTIFIED BY "oracle"
enable pluggable database;

Database created.

set linesize 2048;
column ctl_files NEW_VALUE ctl_files;
select concat('control_files=''', concat(replace(value, ', ', ''','''), '''')) ctl_files from v$parameter where name ='control_files';
host echo &ctl_files >> /u01/app/oracle/admin/MACLEANCDB/scripts/init.ora;
spool off

将控制文件信息写入到中==》 echo &ctl_files >> /u01/app/oracle/admin/MACLEANCDB/scripts/init.ora;

 

 

4、创建默认使用的USERS表空间

 

SQL> CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  SIZE 5M AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TABLESPACE "USERS";

Database altered.

 

 

 

5、 执行必要的数据字典创建脚本

 

 

 

alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -l /u01/app/oracle/admin/MACLEANCDB/scripts -b catalog /u01/ap
p/oracle/product/12.1.0/dbhome_1/rdbms/admin/catalog.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -l /u01/app/oracle/admin/MACLEANCDB/scripts -b catblock /u01/a
pp/oracle/product/12.1.0/dbhome_1/rdbms/admin/catblock.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -l /u01/app/oracle/admin/MACLEANCDB/scripts -b catproc /u01/ap
p/oracle/product/12.1.0/dbhome_1/rdbms/admin/catproc.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -l /u01/app/oracle/admin/MACLEANCDB/scripts -b catoctk /u01/ap
p/oracle/product/12.1.0/dbhome_1/rdbms/admin/catoctk.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -l /u01/app/oracle/admin/MACLEANCDB/scripts -b owminst /u01/ap
p/oracle/product/12.1.0/dbhome_1/rdbms/admin/owminst.plb;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -l /u01/app/oracle/admin/MACLEANCDB/scripts -b pupbld -u SYSTE
M/&&systemPassword /u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/admin/MACLEANCDB/scripts/sqlPlusHelp.log append
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl -l /u01/app/oracle/admin/MACLEANCDB/scripts -b hlpbld -u SYSTE
M/&&systemPassword -a 1  /u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin/help/hlpbld.sql 1helpus.sql;
@/u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin/help/hlpbld.sql helpus.sql;

 

 

 

6、 创建一个PDB

 

 

cp init.ora  /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initMACLEANCDB.ora 

startup force;

CREATE PLUGGABLE DATABASE MACLEANCDB ADMIN USER MACadmin IDENTIFIED BY oracle
 FILE_NAME_CONVERT=(
  '/stage/oradata/MACLEANCDB/DC36ED41771D435CE0430100007FA00B/datafile/o1_mf_system_8rns0lxf_.dbf', '/stage/oradata/PDB1/datafile/system01.clone',
  '/stage/oradata/MACLEANCDB/DC36ED41771D435CE0430100007FA00B/datafile/o1_mf_sysaux_8rns13dk_.dbf', '/stage/oradata/PDB1/datafile/sysaux1.dbf.clone',
  '/stage/oradata/MACLEANCDB/DC36ED41771D435CE0430100007FA00B/datafile/o1_mf_temp_8rns1d89_.tmp', '/stage/oradata/PDB1/datafile/temp1.tmp.clone'
  )
 STORAGE UNLIMITED;
Filed Under: Oracle, Oracle 12c新特性 Tagged With: , ,

【Oracle Database 12c新特性】 12c DataPump Expdp/Impdp新特性

2013/01/28 by Leave a Comment

在Oracle Database 12c中加入了一些DataPump Expdp/Impdp的新特性，当然包括对CDB的支持，此外还有部分特性。

 

例如DISABLE_ARCHIVE_LOGGING/RECOVERY_LOGGING 减少impdp导入时 TABLE/INDEX产生的redo，注意这仅仅是减少不是禁绝。

 

基本语法如下：

$ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
$ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE
$ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y TRANSFORM=DISABLE_ARCHIVE_LOGGING:N:INDEX

 

  • 注意 即便你用DISABLE_ARCHIVE_LOGGING:Y 也不代表能完全不产生redo
  • 对于 FORCE LOGGING的数据库 DISABLE_ARCHIVE_LOGGING:Y无效

 

具体使用：

Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 17
Current log sequence 19

 
oracle@localhost:~$ expdp system/oracle dumpfile=temp:ogg_maclean.dmp schemas=ogg_maclean

Export: Release 12.1.0.0.2 – Beta on Sun Apr 28 05:14:00 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_02″: system/******** dumpfile=temp:ogg_maclean.dmp schemas=ogg_maclean
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 30 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS1″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS10″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS2″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS3″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS4″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS5″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS6″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS7″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS8″ 2.298 MB 84000 rows
. . exported “OGG_MACLEAN”.”MACLEAN_PRESS9″ 2.298 MB 84000 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/tmp/ogg_maclean.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully completed at Sun Apr 28 05:15:01 2013 elapsed 0 00:00:57

oracle@localhost:~$ ls -lh /tmp/ogg_maclean.dmp
-rw-r—– 1 oracle oinstall 24M Apr 28 05:15 /tmp/ogg_maclean.dmp

 

 
oracle@localhost:~$ impdp system/oracle dumpfile=temp:ogg_maclean.dmp remap_schema=ogg_maclean:ogg_maclean1

Import: Release 12.1.0.0.2 – Beta on Sun Apr 28 05:18:18 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** dumpfile=temp:ogg_maclean.dmp remap_schema=ogg_maclean:ogg_maclean1
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “OGG_MACLEAN1″.”MACLEAN_PRESS1″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1″.”MACLEAN_PRESS10″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1″.”MACLEAN_PRESS2″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1″.”MACLEAN_PRESS3″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1″.”MACLEAN_PRESS4″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1″.”MACLEAN_PRESS5″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1″.”MACLEAN_PRESS6″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1″.”MACLEAN_PRESS7″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1″.”MACLEAN_PRESS8″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN1″.”MACLEAN_PRESS9″ 2.298 MB 84000 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully completed at Sun Apr 28 05:18:31 2013 elapsed 0 00:00:10

 
DISABLE_ARCHIVE_LOGGING
oracle@localhost:~$ impdp system/oracle dumpfile=temp:ogg_maclean.dmp remap_schema=ogg_maclean:ogg_maclean2 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

Import: Release 12.1.0.0.2 – Beta on Sun Apr 28 05:21:45 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** dumpfile=temp:ogg_maclean.dmp remap_schema=ogg_maclean:ogg_maclean2 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “OGG_MACLEAN2″.”MACLEAN_PRESS1″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2″.”MACLEAN_PRESS10″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2″.”MACLEAN_PRESS2″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2″.”MACLEAN_PRESS3″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2″.”MACLEAN_PRESS4″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2″.”MACLEAN_PRESS5″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2″.”MACLEAN_PRESS6″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2″.”MACLEAN_PRESS7″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2″.”MACLEAN_PRESS8″ 2.298 MB 84000 rows
. . imported “OGG_MACLEAN2″.”MACLEAN_PRESS9″ 2.298 MB 84000 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully completed at Sun Apr 28 05:21:56 2013 elapsed 0 00:00:09

 

如同导出表那样导出视图数据

 Exporting Views as Tables会导出 表的定义和视图数据，而不仅仅是视图定义。以及其依赖的对象，例如约束和授权

 

SQL> create view cnt as select count(*) c1 from MACLEAN_PRESS1;

View created.

oracle@localhost:~$ expdp system/oracle dumpfile=temp:view.dmp views_as_tables=ogg_maclean.cnt

Export: Release 12.1.0.0.2 – Beta on Sun Apr 28 05:52:49 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/******** dumpfile=temp:view.dmp views_as_tables=ogg_maclean.cnt
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported “OGG_MACLEAN”.”CNT” 5.046 KB 1 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/tmp/view.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at Sun Apr 28 05:53:01 2013 elapsed 0 00:00:10

 

Filed Under: Oracle, Oracle 12c新特性

【12c新特性】安装12c Standalone Grid Infrastructure

2013/01/22 by 1 Comment

【12c新特性】安装12c Standalone Grid Infrastructure

 

install 12c grid 1

 

install 12c grid 2

install 12c grid 3

install 12c grid 4

 

install 12c grid 5

 

install 12c grid 6

 

install 12c grid 7

 

install 12c grid 8

 

 

install 12c grid 9

 

install 12c grid 10

install 12c grid 11 install 12c grid 12
install 12c grid asm 13 install 12c grid asm 14









 

 

 

 

 

 

 

[grid@localhost stage]$ unzip grid_12.1BETA2.zip
[root@localhost ~]# /g01/app/grid/product/12.1.0/grid/rootupgrade.sh
[root@localhost ~]# /g01/app/grid/product/12.1.0/grid/rootupgrade.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /g01/app/grid/product/12.1.0/grid
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /g01/app/grid/product/12.1.0/grid/crs/install/crsconfig_params

Error while detecting Oracle Grid Infrastructure. ASMCA needs Oracle Grid Infrastructure to configure ASM.

2013/03/31 02:52:34 CLSRSC-164: ASM upgrade failed

2013/03/31 02:52:34 CLSRSC-304: Failed to upgrade ASM for Oracle Restart configuration

Died at /g01/app/grid/product/12.1.0/grid/crs/install/crsupgrade.pm line 2423.
The command ‘/g01/app/grid/product/12.1.0/grid/perl/bin/perl -I/g01/app/grid/product/12.1.0/grid/perl/lib -I/g01/app/grid/product/12.1.0/grid/crs/install /g01/app/grid/product/12.1.0/grid/crs/install/roothas.pl -upgrade’ execution failed
[root@localhost ~]# /g01/app/grid/product/12.1.0/grid/root.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /g01/app/grid/product/12.1.0/grid
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /g01/app/grid/product/12.1.0/grid/crs/install/crsconfig_params
2013/03/31 02:52:49 CLSRSC-350: Cannot configure two CRS instances on the same cluster

2013/03/31 02:52:49 CLSRSC-352: CRS is already configured on this node for crshome=/g01/app/grid/product/11.2.0/grid

The command ‘/g01/app/grid/product/12.1.0/grid/perl/bin/perl -I/g01/app/grid/product/12.1.0/grid/perl/lib -I/g01/app/grid/product/12.1.0/grid/crs/install /g01/app/grid/product/12.1.0/grid/crs/install/roothas.pl ‘ execution failed
[root@localhost ~]# /g01/app/grid/product/12.1.0/grid/rootupgrade.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /g01/app/grid/product/12.1.0/grid
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /g01/app/grid/product/12.1.0/grid/crs/install/crsconfig_params

Error while detecting Oracle Grid Infrastructure. ASMCA needs Oracle Grid Infrastructure to configure ASM.

2013/03/31 02:53:06 CLSRSC-164: ASM upgrade failed

2013/03/31 02:53:06 CLSRSC-304: Failed to upgrade ASM for Oracle Restart configuration

Died at /g01/app/grid/product/12.1.0/grid/crs/install/crsupgrade.pm line 2423.
The command ‘/g01/app/grid/product/12.1.0/grid/perl/bin/perl -I/g01/app/grid/product/12.1.0/grid/perl/lib -I/g01/app/grid/product/12.1.0/grid/crs/install /g01/app/grid/product/12.1.0/grid/crs/install/roothas.pl -upgrade’ execution failed
[root@localhost ~]# /g01/app/grid/product/12.1.0/grid/rootupgrade.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /g01/app/grid/product/12.1.0/grid
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /g01/app/grid/product/12.1.0/grid/crs/install/crsconfig_params

ASM Configuration upgraded successfully.

Creating OCR keys for user ‘grid’, privgrp ‘oinstall’..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
CRS-4664: Node localhost successfully pinned.
2013/03/31 02:56:41 CLSRSC-329: Replacing Clusterware entries in file ‘/etc/inittab’
2013/03/31 03:00:26 CLSRSC-329: Replacing Clusterware entries in file ‘/etc/inittab’
localhost 2013/03/31 03:04:28 /g01/app/grid/product/12.1.0/grid/cdata/localhost/backup_20130331_030428.olr

localhost 2013/01/29 14:56:43 /g01/app/grid/product/11.2.0/grid/cdata/localhost/backup_20130129_145643.olr
2013/03/31 03:04:28 CLSRSC-327: Successfully configured Oracle Grid Infrastructure for a Standalone Server

 

 

 
[grid@localhost ~]$ pstree -a
init
├─VBoxService
│ ├─{VBoxService}
│ ├─{VBoxService}
│ ├─{VBoxService}
│ ├─{VBoxService}
│ ├─{VBoxService}
│ ├─{VBoxService}
│ └─{VBoxService}
├─acpid
├─anacron -s
├─atd
├─auditd
│ ├─audispd
│ │ └─{audispd}
│ └─{auditd}
├─automount
│ ├─{automount}
│ ├─{automount}
│ ├─{automount}
│ └─{automount}
├─avahi-daemon
│ └─avahi-daemon
├─crond
├─cssdagent
│ ├─{cssdagent}
│ ├─{cssdagent}
│ ├─{cssdagent}
│ ├─{cssdagent}
│ ├─{cssdagent}
│ ├─{cssdagent}
│ ├─{cssdagent}
│ ├─{cssdagent}
│ ├─{cssdagent}
│ └─{cssdagent}
├─cupsd
├─dbus-daemon –system
├─dhclient -1 -q -lf /var/lib/dhclient/dhclient-eth0.leases -pf /var/run/dhclient-eth0.pid eth0
├─evmd.bin
│ ├─evmlogger.bin -o /g01/app/grid/product/12.1.0/grid/log/[HOSTNAME]/evmd/evmlogger.info -l/g01/app/grid/product/12.1.0/grid/log/[H
│ ├─{evmd.bin}
│ ├─{evmd.bin}
│ ├─{evmd.bin}
│ ├─{evmd.bin}
│ ├─{evmd.bin}
│ ├─{evmd.bin}
│ ├─{evmd.bin}
│ ├─{evmd.bin}
│ ├─{evmd.bin}
│ ├─{evmd.bin}
│ └─{evmd.bin}
├─gam_server
├─gdm-binary -nodaemon
│ └─gdm-binary -nodaemon
│ ├─Xorg :0 -br -audit 0 -auth /var/gdm/:0.Xauth -nolisten tcp vt7
│ └─gdmgreeter
├─gdm-rh-security
│ └─{gdm-rh-security}
├─gpm -m /dev/input/mice -t exps2
├─hald
│ └─hald-runner
│ ├─hald-addon-keyb
│ ├─hald-addon-keyb
│ ├─hald-addon-keyb
│ └─hald-addon-stor
├─hcid
├─hidd --server
├─hpiod
├─hpssd.py ./hpssd.py
├─init.ohasd /etc/init.d/init.ohasd run
├─iscsid
├─iscsid
├─iscsiuio
│ ├─{iscsiuio}
│ ├─{iscsiuio}
│ └─{iscsiuio}
├─klogd -x
├─mingetty tty1
├─mingetty tty2
├─mingetty tty3
├─mingetty tty4
├─mingetty tty5
├─mingetty tty6
├─ocssd.bin
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ ├─{ocssd.bin}
│ └─{ocssd.bin}
├─ohasd.bin reboot
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ ├─{ohasd.bin}
│ └─{ohasd.bin}
├─ora_aqpc_cdb1
├─ora_arc0_cdb1
├─ora_arc1_cdb1
├─ora_arc2_cdb1
├─ora_arc3_cdb1
├─ora_cjq0_cdb1
├─ora_ckpt_cdb1
├─ora_d000_cdb1
├─ora_dbrm_cdb1
├─ora_dbw0_cdb1
├─ora_dia0_cdb1
├─ora_diag_cdb1
├─ora_fbda_cdb1
├─ora_gen0_cdb1
├─ora_lgwr_cdb1
├─ora_lreg_cdb1
├─ora_mman_cdb1
├─ora_mmnl_cdb1
├─ora_mmon_cdb1
├─ora_ofsd_cdb1
├─ora_p000_cdb1
├─ora_p001_cdb1
├─ora_p002_cdb1
├─ora_p003_cdb1
├─ora_pmon_cdb1
├─ora_psp0_cdb1
├─ora_q001_cdb1
├─ora_q002_cdb1
├─ora_qm01_cdb1
├─ora_reco_cdb1
├─ora_s000_cdb1
├─ora_smco_cdb1
├─ora_smon_cdb1
├─ora_tmon_cdb1
├─ora_tt00_cdb1
├─ora_vktm_cdb1
├─ora_w000_cdb1
├─oraagent.bin
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ ├─{oraagent.bin}
│ └─{oraagent.bin}
├─pcscd
│ ├─{pcscd}
│ └─{pcscd}
├─portmap
├─rpc.idmapd
├─rpc.statd
├─sdpd
├─sendmail
├─sendmail
├─smartd -q never
├─sshd
│ ├─sshd
│ │ ├─bash
│ │ │ └─su - grid
│ │ │ └─bash
│ │ ├─bash
│ │ │ └─su - grid
│ │ │ └─bash
│ │ │ └─pstree -a
│ │ ├─bash
│ │ │ └─su - grid
│ │ │ └─bash
│ │ │ └─tail -f ohasd.log
│ │ └─sftp-server
│ └─sshd
│ └─sshd
│ └─xterm -ls -display localhost:10.0
│ └─bash
├─syslogd -m 0
├─tnslsnr LISTENER -inherit
├─tnslsnr LISTENER -inherit
├─udevd -d
├─xfs -droppriv -daemon
├─xinetd -stayalive -pidfile /var/run/xinetd.pid
└─yum-updatesd -tt /usr/sbin/yum-updatesd

[grid@localhost ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

# export GRID_HOME=/g01/app/grid/product/11.2.0/grid
export GRID_HOME=/g01/app/grid/product/12.1.0/grid
export PATH=$GRID_HOME/bin:$GRID_HOME/OPatch:/usr/bin:/usr/sbin:/bin:/sbin
export ORACLE_SID=+ASM
# export ORACLE_HOME=/g01/app/grid/product/11.2.0/grid
export ORACLE_HOME=/g01/app/grid/product/12.1.0/grid
export ORACLE_BASE=/g01/app/grid

asmca

[grid@localhost tmp]$ crsctl stat res -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.EXTDG.dg
ONLINE OFFLINE localhost STABLE
ora.LISTENER.lsnr
ONLINE ONLINE localhost STABLE
ora.NORDG.dg
OFFLINE OFFLINE localhost STABLE
ora.SYSTEMDG.dg
ONLINE OFFLINE localhost STABLE
ora.asm
ONLINE OFFLINE localhost STABLE
ora.ons
OFFLINE OFFLINE localhost STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
1 ONLINE ONLINE localhost STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE localhost STABLE
——————————————————————————–

 

 

Filed Under: Oracle, Oracle 12c新特性

【12c新特性】EM Database Express

2012/10/31 by 1 Comment

EM Database Express是Oracle Database 12c 中引入的新特性 ，替代以前版本中的DBCONSOLE，使之EM基于网页管理DB的部署更迅速、方便。

12c EM Database Express Architecture

启动12c EM database express的方式更简便:

 

1. 确认dispatchers参数

SQL> show parameter dispatcher

NAME TYPE VALUE
———————————— ———– ——————————
dispatchers string (PROTOCOL=TCP) (SERVICE=cdb1XD
B)
max_dispatchers integer

 

2.执行 DBMS_XDB.setHTTPPort过程

SQL> exec dbms_XDB.setHttpPort(5500);

PL/SQL procedure successfully completed.

3. 使用浏览器打开页面地址 http://ip地址:5500/em/login 并登陆

 

12c EM Database Express Architecture2

 

 

登陆后的界面：

 

12c EM Database Express Architecture3

Filed Under: Oracle, Oracle 12c新特性

将在版本11.2之后废弃或不再支持的特性

2012/10/26 by Leave a Comment

12c是Oracle未来最重量级的数据库产品，每个新版本的到来都会带来吐故纳新，例如9i的sqlplusw、10g的isqlplus均在其后续版本中废弃；12c中将被废弃或不支持的特性，例如:Database Control DBconsole、OCFS on Windows、CSSCAN、CSALTER、cursor_sharing = ‘SIMILAR’、Oracle Net Connection Pooling feature。  为了照顾旧应用的兼容性，看来在12c中暂时不会彻底废掉RBO。

更多信息可以参考以下NOTE:

Deprecated and Desupported Features after Oracle Database 11.2:

Document 1484775.1 Database Control To Be Desupported in DB Releases after 11.2
Document 1392280.1 Desupport of Oracle Cluster File System (OCFS) on Windows with Oracle DB 12
Document 1175293.1 Obsolescence Notice: Oracle COM Automation
Document 1175303.1 Obsolescence Notice: Oracle Objects for OLE
Document 1175297.1 Obsolescence Notice: Oracle Counters for Windows Performance Monitor
Document 1418321.1 CSSCAN and CSALTER To Be Desupported After DB 11.2
Document 1169017.1 Deprecating the cursor_sharing = ‘SIMILAR’ setting
Document 1469466.1: Deprecation of Oracle Net Connection Pooling feature in Oracle Database 11g Release 2

Filed Under: Oracle, Oracle 12c新特性 Tagged With: ,

解读Tom介绍的Oracle Database 12c的12个新特性

2012/10/10 by 4 Comments

在OOW 2012上Tom kyte介绍了Oracle新一代重量级数据库产品12c 的12个新特性， 目前Open World 2012的主要PDF都可以下载了，传送门在此：Search Content Catalog for Oracle OpenWorld 2012 sessions。

Tom的《12 Things About The Latest Generation of Database Technology》下载地址。

 

这里我们来领略下Tom眼中的12个特性增强：

 

 

 

#1 Even better PL/SQL from SQL， 直接在SQL中嵌入PL/SQL对象并运行，猜测可能优化了SQL engine 和 PL/SQL engine 2种的代码引擎之间的交互，以获得比之前传统的SQL调用函数更少的上下文切换。

 

 

#2 Improved Defaults 增强了DEFAULT, default目前可以直接指代sequence了，同时增强了default充当identity的能力

 

  • Default to a sequence
  • Default when null inserted
  • Identity Type
  • Metadata-only Defaults for NULL columns

 

 

#3 Increased Size Limit for VARCHAR2, NVARCHAR2, and RAW Data Types

Varchar2, NVarchar2 and Raw这些数据类型的大小上限可以扩展到32k，和其在PL/SQL中的变量类型一样了，当然过长可能导致像LOB那样OUT OF LINE存放

 

 

 

#4 Easy Top-N and pagination queries ，更易用的Top-N和页码查询

提供了类似MySQL中limit的语法，Row Limiting Clause

 

 

FETCH FIRST 5 ROWS ONLY; ==》仅fetch前5行
FETCH NEXT 0.01 PERCENT ROWS ONLY;  仅fetch 0.0.1%的行数

 

#5 Row Pattern Matching 增强的行模式匹配

提供了新的模式匹配子句 match_recognize，可以通过match_recognize 定义正则语法

 

#6 Partitioning Improvements 分区特性增强 ,  包括异步维护全局索引的drop和truncate分区操作， 以及Interval + Reference分区方式

  • Asynchronous Global Index Maintenance for DROP and TRUNCATE
  • partition
  •  Cascade Functionality for TRUCATE and EXCHANGE partition
  • Multiple partition operations in a single DDL
  • Online move of a partition (without DBMS_REDEFINITION)
  • Interval + Reference partitioning

 

 

#7 Adaptive  Execution Plans  自适应执行计划， 该特性可以谈得上神奇，最终的执行计划将基于执行中获得的行，列倾斜造成的问题将被克服

 

 

 

#8 Enhanced  Statistics 统计信息增强， 加入了第11级的动态采样， 对于并行查询自动打开11级的动态采样，混合直方图

 

 

#9 Temporary UNDO  临时UNDO撤销，临时表的UNDO数据可以放到TEMP中，有助于减少 UNDO表空间中的undo数据，进一步减少redo的产生， 从而使得Active Data Guard中的临时表允许做DML

设置方法为：ALTER SYSTEM/SESSION SET TEMP_UNDO_ENABLED=true|false

 

 

#10 Data Optimization 数据优化包括自动压缩和分层

 

ALTER TABLE orders
ILM ADD CompressionPolicy
COMPRESS Partitions for Query
AFTER 90 days from creation;

 

ALTER TABLE sales
ILM ADD MovePolicy
TIER Partitions TO ‘Archive_TBS’
ON OrdersClosedPolicy;

 

 

#11 Application Continuity 应用持续性增强,  事务卫士(Transaction Guard)保证了解事务结果:

 

 

#12 Pluggable Databases 可插入数据库:

一个容器数据库(container database)中可以存放多个Pluggable Databases

  • 对于外部应用程序和开发者来说Pluggable Databases看上去就是一个普通的版本12.1之前的数据库
  • 用户连接到Pluggable Databases时看到的是一个单一数据库和从前一样

新的管理模式

  • 数据库管理员DBA可以连接到Pluggable Database并仅仅管理该数据库
  • 超级DBA可以连接到容器数据库并如同管理单系统镜像那样管理
  • RAC中的每一个实例均打开容器数据库并可以选择打开哪些Pluggable Database

 

内建的多分租(Multi-tenancy)

  • 每个Pluggable Database均独立于其他Pluggable Database
  • Resource Manager特性被扩展到Pluggable Database中

 

Pluggable Databases特性可以带来的好处：

  • 加速重新部署现有的数据库到新的平台的速度
  • 加速现有数据库打补丁和升级的速度
  • 从原有的DBA的职责中分离部分责任到应用管理员
  • 集中式管理多个数据库
  • 提升RAC的扩展性和故障隔离
  • 与Oracle SQL Developer 和Oracle Enterprise Manager高度融合

 

Filed Under: Oracle, Oracle 12c新特性

解读Oracle Database 12.1新特性Pluggable Databases

2012/09/28 by 2 Comments

在OOW最新发布的Presentation《Oracle Database Cloud Strategy》中，除了提到Oracle Database for public/private cloud的一些信息外，还提到了12c最重量级特性Pluggable Databases的一些概念：

 

 

 

按照该文档的描述，Pluggable Databases暂译为可插入数据库具有以下特性：

一个容器数据库(container database)中可以存放多个Pluggable Databases

  • 对于外部应用程序和开发者来说Pluggable Databases看上去就是一个普通的版本12.1之前的数据库
  • 用户连接到Pluggable Databases时看到的是一个单一数据库和从前一样

新的管理模式

  • 数据库管理员DBA可以连接到Pluggable Database并仅仅管理该数据库
  • 超级DBA可以连接到容器数据库并如同管理单系统镜像那样管理
  • RAC中的每一个实例均打开容器数据库并可以选择打开哪些Pluggable Database

 

内建的多分租(Multi-tenancy)

  • 每个Pluggable Database均独立于其他Pluggable Database
  • Resource Manager特性被扩展到Pluggable Database中

 

Pluggable Databases特性可以带来的好处：

  • 加速重新部署现有的数据库到新的平台的速度
  • 加速现有数据库打补丁和升级的速度
  • 从原有的DBA的职责中分离部分责任到应用管理员
  • 集中式管理多个数据库
  • 提升RAC的扩展性和故障隔离
  • 与Oracle SQL Developer 和Oracle Enterprise Manager高度融合

 

 

Filed Under: Oracle, Oracle 12c新特性
«Older Posts