eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

May 19, 2017

云和恩墨 Oracle Database 12.2 体系结构图海报发布

在 2017 DTCC - 数据库大会的现场,我们发布了『Oracle Database 12.2 体系结构图』海报,这张体系结构图,涵盖了 Oracle 最新的 12.2 核心技术,大到多租户、In-Memory、Sharding,小到Recovery Buddy和LMS的变化,尽皆涵盖。

1920x1080dpi.jpg

各种大小的桌面图,和原始图,请在「数据和云」公众号回复 122ARCH 获得下载链接:

https://mp.weixin.qq.com/s/JisneEItduIa87gcg4cJdA

如果想获得印刷版本的海报,也请在公众号找到快递方式。

Posted by eygle at 10:40 AM | Permalink | Download (57)

May 18, 2017

在Mac上通过Docker部署Oracle Database 12.2版本

Oracle 已经宣布支持了Docker部署,这也让我们在Mac上部署 Oracle 数据库有了多一个选择,这是我的第一个Docker应用,非常简便快速的就完成了部署,简捷是最直接的体验。

首先要做的是准备工作,备好需要的软件,安装Docker环境,这些极其简单。

下载一个Docker的稳定版本,『Docker Community Edition for Mac』即可:

以下链接:

https://store.docker.com/editions/community/docker-ce-desktop-mac

选择稳定版本:

https://download.docker.com/mac/stable/Docker.dmg

这也是我第一次安装Docker,看着熟悉的小鲸鱼游进文件夹,第一步的准备工作基本就做好了:

DockerInstall.jpg

接下来还需要在Github上下载Oracle的Docker Image文件(下载之后的文件是 docker-images-master.zip ):

https://github.com/oracle/docker-images

OracleOnGitHub.jpg

此外,你还需要最新的Oracle 12.2 数据库安装包,可以从OTN下载(安装文件是 linuxx64_12201_database.zip):

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

在成功安装了Docker之后,就可以部署Oracle数据库了

在Docker中部署Oracle数据库,简化到只需要一个核心命令。

我将整个过程概括为3个环节:

第一步:准备安装程序

主要是 docker-images-master.zip 和 linuxx64_12201_database.zip ,解压Docker的主体文件,然后将Oracle的安装文件加入到OracleDatabase/dockerfiles的相应目录下,准备工作就就绪了。

准备工作基本上就两条命令,把准备好的安装文件解压,最好建一个顶级目录,如Docker:

unzip docker-images-master.zip

mv linuxx64_12201_database.zip docker-images-master/OracleDatabase/dockerfiles/12.2.0.1/

简单点的日志如下:

bogon:~ eygle$ cd Docker/

bogon:Docker eygle$ ls -l

-rw-r--r--@ 1 eygle staff 4291538 May 16 15:52 docker-images-master.zip

-rw-r--r--@ 1 eygle staff 3453696911 Apr 19 12:04 linuxx64_12201_database.zip

bogon:Docker eygle$ unzip docker-images-master.zip

Archive: docker-images-master.zip

e4dbd20bd3ef245767f37641b5c37d6e918a5f97

creating: docker-images-master/

extracting: docker-images-master/.gitattributes

bogon:Docker eygle$ mv linuxx64_12201_database.zip docker-images-master/OracleDatabase/dockerfiles/12.2.0.1/

第二步:构建数据库的Docker镜像

这个环节就只需要一条命令,需要执行 docker-images-master/OracleDatabase/dockerfiles 下的build脚本

./buildDockerImage.sh -v 12.2.0.1 -e

在这个步骤中首先需要安装 OEL的 Slim 版本:oraclelinux:7-slim 。需要从网上下载文件,所以安装过程中必须保持网络的连通。整个过程有16个步骤,简单抽取出来,就是类似如下的过程,全部是自动执行的:

Step 1/16 : FROM oraclelinux:7-slim

Step 2/16 : MAINTAINER Gerald Venzl <gerald.venzl@oracle.com>

Step 3/16 : ENV ORACLE_BASE /opt/oracle ORACLE_HOME /opt/oracle/product/12.2.0.1/dbhome_1 INSTALL_FILE_1 "linuxx64_12201_database.zip" INSTALL_RSP "db_inst.rsp" CONFIG_RSP "dbca.rsp.tmpl" PWD_FILE "setPassword.sh" PERL_INSTALL_FILE "installPerl.sh" RUN_FILE "runOracle.sh" START_FILE "startDB.sh" CREATE_DB_FILE "createDB.sh" SETUP_LINUX_FILE "setupLinuxEnv.sh" CHECK_SPACE_FILE "checkSpace.sh" CHECK_DB_FILE "checkDBStatus.sh" INSTALL_DB_BINARIES_FILE "installDBBinaries.sh"

Step 4/16 : ENV INSTALL_DIR $ORACLE_BASE/install PATH $ORACLE_HOME/bin:$ORACLE_HOME/OPatch/:/usr/sbin:$PATH LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib CLASSPATH $ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Step 5/16 : COPY $INSTALL_FILE_1 $INSTALL_RSP $PERL_INSTALL_FILE $SETUP_LINUX_FILE $CHECK_SPACE_FILE $INSTALL_DB_BINARIES_FILE $INSTALL_DIR/

Step 6/16 : COPY $RUN_FILE $START_FILE $CREATE_DB_FILE $CONFIG_RSP $PWD_FILE $CHECK_DB_FILE $ORACLE_BASE/

Step 7/16 : RUN chmod ug+x $INSTALL_DIR/*.sh && sync && $INSTALL_DIR/$CHECK_SPACE_FILE && $INSTALL_DIR/$SETUP_LINUX_FILE

Step 8/16 : USER oracle

Step 9/16 : RUN $INSTALL_DIR/$INSTALL_DB_BINARIES_FILE EE

Step 10/16 : USER root

Step 11/16 : RUN $ORACLE_BASE/oraInventory/orainstRoot.sh && $ORACLE_HOME/root.sh && rm -rf $INSTALL_DIR

Step 12/16 : USER oracle

Step 13/16 : WORKDIR /home/oracle

Step 14/16 : VOLUME $ORACLE_BASE/oradata

Step 15/16 : EXPOSE 1521 5500

Step 16/16 : CMD exec $ORACLE_BASE/$RUN_FILE

执行的起点类似如下:

bogon:Docker eygle$ cd docker-images-master/OracleDatabase/dockerfiles

bogon:dockerfiles eygle$ ./buildDockerImage.sh -v 12.2.0.1 -e

完成的终点日志类似:

Oracle Database Docker Image for 'ee' version 12.2.0.1 is ready to be extended:
--> oracle/database:12.2.0.1-ee

Build completed in 52755 seconds.

第三步:创建和运行数据库

完成了安装之后,你拥有了两个镜像,一个是OEL 7-slim 版本,大约114MB,一个 oracle/database 大约14.8GB。

bogon:OracleDocker eygle$ docker images

REPOSITORY TAG IMAGE ID CREATED SIZE

oracle/database 12.2.0.1-ee dcfea9f4f145 3 hours ago 14.8 GB

oraclelinux 7-slim 442ebf722584 3 weeks ago 114 MB

启动数据库只需要一个命令:

docker run --name oracle -p 1521:1521 -p 5500:5500 -v /Users/eygle/oradata:/opt/oracle/oradata oracle/database:12.2.0.1-ee

这个命令有几个主要参数:-p 指定端口映射,主机到Docker的端口对应;-v 指定数据库的对应存储路径,我指定了一个Docker之外的本地存储,将数据库独立出来。

完成这个命令,会在本地完成数据库的构建并启动数据库,同时输出告警日志的监控。

注意这一段的日志:

#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:

这里数据库事实上创建完成,显示的是日志的tail信息,中断之后数据库会停止,重启数据库就好了。

由于数据库缺省会指定用户口令,所以我们可以通过如下命令来修改口令:

docker exec oracle ./setPassword.sh youpassword

其他的必备命令是:

docker start oracle

docker stop oracle

docker logs oracle

docker ps

接下来就可以在 SQL Developer 里配置和连接这个数据库了:

DockerOracleConfig.jpg

DockerOracleConnect.jpg

当然如果你不想剥离数据库文件出来,也可以用类似以下命令来构建数据库:

docker run -p 1521:1521 --name eygle oracle/database:12.2.0.1-ee

想一想我们第一次安装数据库时的场景,再看看今天的简易程度,我们必须承认,技术在进步,学习越来越简单。Oracle 的今天越加开放和自由,乐趣会越来越多起来!

补充的知识FAQ

1.Docker 的镜像在哪里

在MAC上,位于以下目录,用户换成你的就找到:

/Users/eygle/Library/Containers/com.docker.docker/Data/com.docker.driver.amd64-linux/Docker.qcow2


附录:以下是最后一个步骤,创建数据库的日志输出:

bogon:dockerfiles eygle$ docker run --name oracle -p 1521:1521 -p 5500:5500 -v /Users/eygle/oradata:/opt/oracle/oradata oracle/database:12.2.0.1-ee
ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: 2fjRrW5zy8Y=1
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-MAY-2017 01:28:54
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /opt/oracle/product/12.2.0.1/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/cfb8b54eab11/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 17-MAY-2017 01:28:56
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/cfb8b54eab11/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully
[WARNING] [DBT-11209] Current available physical memory is less than the required physical memory (2,048MB) for creating the database.
[WARNING] [DBT-10102] The listener configuration is not selected for the database. EM DB Express URL will not be accessible.
CAUSE: The database should be registered with a listener in order to access the EM DB Express URL.
ACTION: Select a listener to be registered or created with the database.
Copying database files
1% complete
13% complete
25% complete
Creating and starting Oracle instance
26% complete
30% complete
31% complete
35% complete
38% complete
39% complete
41% complete
Completing Database Creation
42% complete
43% complete
44% complete
46% complete
47% complete
50% complete
Creating Pluggable Databases
55% complete
75% complete
Executing Post Configuration Actions
100% complete
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.
SQL*Plus: Release 12.2.0.1.0 Production on Wed May 17 01:37: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> 
System altered.
SQL> 
Pluggable database altered.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
Completed: alter pluggable database ORCLPDB1 open
2017-05-17T01:37:42.901045+00:00
ORCLPDB1(3):CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
ORCLPDB1(3):Completed: CREATE SMALLFILE TABLESPACE "USERS" LOGGING
DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
ORCLPDB1(3):ALTER DATABASE DEFAULT TABLESPACE "USERS"
ORCLPDB1(3):Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
2017-05-17T01:37:44.419029+00:00
ALTER SYSTEM SET control_files='/opt/oracle/oradata/ORCLCDB/control01.ctl' SCOPE=SPFILE;
ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE
Completed: ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE
2017-05-17T01:47:17.595752+00:00
ORCLPDB1(3):Resize operation completed for file# 10, old size 337920K, new size 358400K

2017-05-17T02:58:14.118529+00:00
Warning: VKTM detected a backward time drift.
Time drifts can result in unexpected behavior such as time-outs.
Please see the VKTM trace file for more details:
/opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_vktm_1723.trc
2017-05-17T03:07:19.070128+00:00
Resize operation completed for file# 3, old size 471040K, new size 481280K
^CStopping container.
SIGINT received, shutting down database!
SQL*Plus: Release 12.2.0.1.0 Production on Wed May 17 04:06:49 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> 2017-05-17T04:06:49.354297+00:00
Shutting down instance (immediate) (OS id: 3434)
2017-05-17T04:06:51.079839+00:00
Stopping background process SMCO
2017-05-17T04:06:52.262508+00:00
Shutting down instance: further logons disabled
2017-05-17T04:06:52.290158+00:00
Stopping background process CJQ0
Stopping background process MMNL
2017-05-17T04:06:53.432719+00:00
Stopping background process MMON
2017-05-17T04:06:53.671235+00:00
OS process OFSD (ospid 1739) idle for 30 seconds, exiting
2017-05-17T04:06:54.534515+00:00
alter pluggable database all close immediate
2017-05-17T04:06:54.578363+00:00
ORCLPDB1(3):JIT: pid 3434 requesting stop
Pluggable database ORCLPDB1 closed
Completed: alter pluggable database all close immediate
PDB$SEED(2):JIT: pid 3434 requesting stop
License high water mark = 18
Dispatchers and shared servers shutdown
2017-05-17T04:06:56.956473+00:00
ALTER DATABASE CLOSE NORMAL
2017-05-17T04:06:57.038966+00:00
Stopping Emon pool
alter pluggable database all close immediate
Completed: alter pluggable database all close immediate
Stopping Emon pool
2017-05-17T04:06:57.139148+00:00
Shutting down archive processes
2017-05-17T04:06:57.141700+00:00
TT00: Gap Manager exiting (PID:1797)
Database closed.
Database dismounted.
2017-05-17T04:06:58.151422+00:00
Archiving is disabled
2017-05-17T04:06:58.155611+00:00
Thread 1 closed at log sequence 2
Successful close of redo thread 1
2017-05-17T04:06:58.461910+00:00
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
2017-05-17T04:06:59.516713+00:00
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
2017-05-17T04:07:00.533039+00:00
JIT: pid 3434 requesting stop
2017-05-17T04:07:01.593570+00:00
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
JIT: pid 3434 requesting stop
2017-05-17T04:07:01.638769+00:00
Stopping background process VKTM
ORACLE instance shut down.
2017-05-17T04:07:12.390203+00:00
Instance shutdown complete (OS id: 3434)
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-MAY-2017 04:07:15
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully

参考文档:

https://sqlmaria.com/2017/04/27/oracle-database-12c-now-available-on-docker/

https://blogs.oracle.com/developer/creating-an-oracle-database-docker-image

Posted by eygle at 1:17 AM | Permalink | Advanced (88)

May 17, 2017

Oracle数据库中 MISSING 文件名称的出现和处理

在数据库中,异常情况下,你可能会看到某些文件带有MISSING的名称,这是如何出现的?又可以怎样处理呢?

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create tablespace eygle datafile '/u01/app/oracle/oradata/enmot1/eygle01.dbf' size 10M;

Tablespace created.

SQL> create table enmo tablespace eygle as select * from dba_users;

Table created.

生成重建控制文件的脚本:

SQL> alter database backup controlfile to trace;

Database altered.

SQL> select name,value from v$diag_info where name='Default Trace File';

NAME
----------------------------------------------------------------
VALUE
-----------------------------------------------------------------------------
Default Trace File
/u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_8511.trc

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ENMOT1" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4674
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/enmot1/redo01a.log',
    '/u01/app/oracle/oradata/enmot1/redo01b.log'
  ) SIZE 100M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/enmot1/redo02a.log',
    '/u01/app/oracle/oradata/enmot1/redo02b.log'
  ) SIZE 100M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/app/oracle/oradata/enmot1/redo03a.log',
    '/u01/app/oracle/oradata/enmot1/redo03b.log'
  ) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/enmot1/system01.dbf',
  '/u01/app/oracle/oradata/enmot1/sysaux01.dbf',
  '/u01/app/oracle/oradata/enmot1/undotbs01.dbf',
  '/u01/app/oracle/oradata/enmot1/users02.dbf',
  '/u01/app/oracle/oradata/enmot1/rock01.dbf',
  '/u01/app/oracle/oradata/enmot1/xxx.dbf',
  '/u01/app/oracle/oradata/enmot1/minor.01.dbf',
  '/u01/app/oracle/oradata/enmot1/shghserol01.dbf',
  '/u01/app/oracle/oradata/enmot1/zdb.dbf',
  '/u01/app/oracle/oradata/enmot1/appmon.dbf',
  '/u01/app/oracle/oradata/enmot1/dbadata01.dbf',
  '/u01/app/oracle/oradata/enmot1/data01.dbf',
  '/u01/app/oracle/oradata/enmot1/data02.dbf',
  '/u01/app/oracle/oradata/enmot1/data03.dbf',
  '/u01/app/oracle/oradata/enmot1/data04.dbf',
  '/u01/app/oracle/oradata/enmot1/data05.dbf',
  '/u01/app/oracle/oradata/enmot1/data06.dbf',
  '/u01/app/oracle/oradata/enmot1/data07.dbf',
  '/u01/app/oracle/oradata/enmot1/data08.dbf',
  '/u01/app/oracle/oradata/enmot1/users03.dbf',
  '/u01/app/oracle/oradata/enmot1/undotbs02.dbf',
  '/u01/app/oracle/oradata/enmot1/users04.dbf',
  '/u01/app/oracle/oradata/enmot1/eygle01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch2/arch_standby/1_1_791199500.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch2/arch_standby/1_1_912362625.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- No tempfile entries found to add.
--

重建控制文件,如果我们去掉最后一个新增的文件,启动数据库之后:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  830967808 bytes
Fixed Size		    2232840 bytes
Variable Size		  742395384 bytes
Database Buffers	   83886080 bytes
Redo Buffers		    2453504 bytes

CREATE CONTROLFILE REUSE DATABASE "ENMOT1" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 4674
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/enmot1/redo01a.log',
    '/u01/app/oracle/oradata/enmot1/redo01b.log'
  ) SIZE 100M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/enmot1/redo02a.log',
    '/u01/app/oracle/oradata/enmot1/redo02b.log'
  ) SIZE 100M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/app/oracle/oradata/enmot1/redo03a.log',
    '/u01/app/oracle/oradata/enmot1/redo03b.log'
  ) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/enmot1/system01.dbf',
  '/u01/app/oracle/oradata/enmot1/sysaux01.dbf',
  '/u01/app/oracle/oradata/enmot1/undotbs01.dbf',
  '/u01/app/oracle/oradata/enmot1/users02.dbf',
  '/u01/app/oracle/oradata/enmot1/rock01.dbf',
  '/u01/app/oracle/oradata/enmot1/xxx.dbf',
  '/u01/app/oracle/oradata/enmot1/minor.01.dbf',
  '/u01/app/oracle/oradata/enmot1/shghserol01.dbf',
  '/u01/app/oracle/oradata/enmot1/zdb.dbf',
  '/u01/app/oracle/oradata/enmot1/appmon.dbf',
  '/u01/app/oracle/oradata/enmot1/dbadata01.dbf',
  '/u01/app/oracle/oradata/enmot1/data01.dbf',
  '/u01/app/oracle/oradata/enmot1/data02.dbf',
  '/u01/app/oracle/oradata/enmot1/data03.dbf',
  '/u01/app/oracle/oradata/enmot1/data04.dbf',
  '/u01/app/oracle/oradata/enmot1/data05.dbf',
  '/u01/app/oracle/oradata/enmot1/data06.dbf',
  '/u01/app/oracle/oradata/enmot1/data07.dbf',
  '/u01/app/oracle/oradata/enmot1/data08.dbf',
  '/u01/app/oracle/oradata/enmot1/users03.dbf',
  '/u01/app/oracle/oradata/enmot1/undotbs02.dbf',
  '/u01/app/oracle/oradata/enmot1/users04.dbf'
CHARACTER SET ZHS16GBK
;

Control file created.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE
ORA-00279: change 709395945 generated at 05/26/2016 10:36:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch2/arch_standby/1_8_912362625.arc
ORA-00280: change 709395945 for thread 1 is in sequence #8


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/enmot1/system01.dbf'


SQL> select member from v$logfile;

MEMBER
---------------------------------------------
/u01/app/oracle/oradata/enmot1/redo03a.log
/u01/app/oracle/oradata/enmot1/redo03b.log
/u01/app/oracle/oradata/enmot1/redo02a.log
/u01/app/oracle/oradata/enmot1/redo02b.log
/u01/app/oracle/oradata/enmot1/redo01a.log
/u01/app/oracle/oradata/enmot1/redo01b.log

6 rows selected.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE
ORA-00279: change 709395945 generated at 05/26/2016 10:36:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch2/arch_standby/1_8_912362625.arc
ORA-00280: change 709395945 for thread 1 is in sequence #8


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/enmot1/redo03a.log
Log applied.
Media recovery complete.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

这是数据库中会出现一个 MISSING 文件,也就是数据字典中存在,但是控制文件中丢失了,数据库自动增加一个MISSING的命名:

SQL> col name for a120
SQL> select name from v$datafile where name like '%MISSING%';

NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00023

Thu May 26 10:42:15 2016
SMON: enabling cache recovery
Incremental checkpoint up to RBA [0x1.3.0], current log tail at RBA [0x1.3.0]
ARCt: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
[9722] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:2296415402 end:2296416042 diff:640 (6 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP_TEST' #13 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP_TEST02' #14 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP_TEST03' #15 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'EYGLE' #27 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Thu May 26 10:42:21 2016
File #23 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00023' in the controlfile.
This file can no longer be recovered so it must be dropped.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
*********************************************************************SMON: enabling tx recovery

WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
           Empty temporary tablespace: TEMP_TEST
           Empty temporary tablespace: TEMP_TEST02
           Empty temporary tablespace: TEMP_TEST03
*********************************************************************
Database Characterset is ZHS16GBK
Opening with Resource Manager plan: MIXED_WORKLOAD_PLAN 
Thu May 26 10:42:22 2016
Starting background process VKRM
Thu May 26 10:42:22 2016
VKRM started with pid=59, OS id=9875 
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu May 26 10:42:23 2016
QMNC started with pid=60, OS id=9877 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete

通过重命名,恢复,如果日志齐全,可以将文件重新加回到数据库中:

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR			     CHANGE# TIME
---------- ------- ------- ------------------------------ ---------- -----------------------
	23 OFFLINE OFFLINE FILE MISSING 			   0

SQL> alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00023' to '/u01/app/oracle/oradata/enmot1/eygle01.dbf';
dbf';

Database altered.

SQL> recover tablespace eygle;
ORA-00279: change 709395945 generated at 05/26/2016 10:36:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch2/arch_standby/1_8_912362625.arc
ORA-00280: change 709395945 for thread 1 is in sequence #8


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/arch/enmot1/1_8_912362625.arc
Log applied.
Media recovery complete.
SQL> alter tablespace eygle online;

Tablespace altered.

SQL> select count(*) from enmo;

  COUNT(*)
----------
	59

简单的测试,仅供参考。

Posted by eygle at 6:49 PM | Permalink | Backup&Recovery (140)

April 25, 2017

警惕:Oracle中删除的分区不会进入回收站(Recyclebin)

最近在『云和恩墨大讲堂』的微信群讨论中,有朋友提到分区的删除和闪回问题,我注意到很多人可能忽略了这个问题。

在Oracle数据库中,单个删除的分区并不会进入回收站,全表删除的分区才可能和全表一起放入回收站。这是因为单个分区删除之后,是无法通过简单的闪回加入原分区表中,既然无法保证一致性,这个分区就不会进入回收站中。

以下这个测试展示了这个过程:

SQL> select * from v$version;

BANNER CON_ID

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

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

PL/SQL Release 12.2.0.1.0 - Production 0

CORE 12.2.0.1.0 Production 0

TNS for Linux: Version 12.2.0.1.0 - Production 0

NLSRTL Version 12.2.0.1.0 - Production 0

SQL> CREATE TABLE enmotech (

2 PartID integer not null,

3 CretTm date not null,

4 PartCD varchar2(2) not null

5 ) partition by list (partcd) automatic (

6 partition pBJ values ('BJ'),

7 partition pCD values ('CD'),

8 partition pGZ values ('GZ'),

9 partition pSH values ('SH')

10 );

Table created.

SQL> insert into enmotech values (1, sysdate, 'KM');

1 row created.

SQL> select partition_name from user_tab_partitions

2 where table_name = 'ENMOTECH';

PARTITION_NAME

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

PBJ

PCD

PGZ

PSH

SYS_P281

SQL> alter table enmotech drop partition SYS_P281 purge;

alter table enmotech drop partition SYS_P281 purge

*

ERROR at line 1:

ORA-14048: a partition maintenance operation may not be combined with other operations

SQL> alter table enmotech drop partition PSH;

Table altered.

SQL> select * from user_recyclebin;

no rows selected

SQL> drop table enmotech;

Table dropped.

SQL> select object_name,original_name,type from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE

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

BIN$TflQLiTmWX7gUwo4qMBX+A==$0 ENMOTECH TABLE

BIN$TflQLiTmWX7gUwo4qMBX+A==$0 ENMOTECH Table Partition

BIN$TflQLiTmWX7gUwo4qMBX+A==$0 ENMOTECH Table Partition

BIN$TflQLiTmWX7gUwo4qMBX+A==$0 ENMOTECH Table Partition

BIN$TflQLiTmWX7gUwo4qMBX+A==$0 ENMOTECH Table Partition

很多时候,想当然的结果可能并不可信,实践操作方能出真知,多动手,是技术人的王道。

Posted by eygle at 4:07 PM | Permalink | FAQ (246)

如何修改 VirtualBox 虚拟机的磁盘大小

在 MAC 上,VirtualBox 的管理界面居然无法找到直接修改磁盘大小的地方,还好命令行可以。

以下将一个磁盘修改大小为 40 GB:

bogon:sdb0 eygle$ which VBoxManage

/usr/local/bin/VBoxManage

bogon:sdb0 eygle$ VBoxManage modifyhd /Users/eygle/sdb0/sdb0-Disk2.vdi --resize 40960

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

这个空间是后分配的,很快就完成这个命令了。

Posted by eygle at 1:02 PM | Permalink | System (103)

近期发表

  • 【安全警告】Oracle 12c 多租户的SQL注入高危风险防范 - April 18, 2017
  • 不以规矩不成方圆:Digital Ocean也删除了他们的数据库 - April 18, 2017
  • 如何隐藏或加密 Oracle 备份 RMAN脚本中的口令 - April 18, 2017
  • 关于Oracle 12.2 Sharding,你想知道的都在这里 - April 13, 2017
  • 如何转储数据文件和Buffer Cache中的数据块 - April 7, 2017
  • Oracle优化,云上启航 - 2017 RWP中国之旅圆满结束 - April 1, 2017
  • 防范攻击 加强管控 - Oracle数据库安全的16条军规 - March 31, 2017
  • Oracle 11g 密码延迟认证与 library cache lock 等待 - March 30, 2017
  • ADAPTIVE LOG FILE SYNC 引起的高Log File Sync警示 - March 27, 2017
  • 盖氏溯源及山东莱阳凤头村盖姓谱系 - March 11, 2017


  • CopyRight © 2004 ~ 2012 eygle.com, All rights reserved.