CIO如何寻找合适的Oracle DBA

from http://tech.it168.com/a2008/1229/261/000000261524_1.shtml

　　好的Oracle DBA该具备的两大要素

　　纵然如上所说，好的Oracle DBA难找，不过，日子总是要过下去，企业总不能因此就不雇佣Oracle DBA?以下，我将就我的经验，与大伙分享“如何找寻适合的Oracle DBA”。

　　怎么选择一个对的Oracle DBA

　
　就像我说的，一个有能力的Oracle DBA，其实已经离主管的位子不远了。所以，大部份的企业很难一下子就找到一个全方位的Oracle
DBA(当然也是有可能，只不过，这比较会有运气的成份)，这代表企业在选择Oracle
DBA的时候，势必要对负责该职位的DBA的职能有所取舍。我通常会建议我的客户在寻找Oracle
DBA时，得先确认企业需要这个人来其处理什么问题?

　　大部份的企业对Oracle DBA的需求，一开始多半是以维持数据库的正常运作以及协助数据库进行数据备份等工作为主，因此，我会建议找有大型主机系统管理经验(有存储管理经验者尤佳)，或者了解操作系统原
理与运作的人来做。因为，Oracle
数据库运作的不正常，经常是因为系统出状况，找具有系统管理经验的人员做DBA，在一定程度上应该已经能比“只”懂数据库的信息人员，还会比较快得找到问
题。此外，也比较能避免数据库出现宕机(一般而言，数据库在管理上最忌讳的就是宕机时间了)等状况。

　　其次，有的企业在找Oracle
DBA的时候，希望找那种了解数据处理流程的人，以便大大翻整一下既有数据库内的数据，以及优化数据传递与分析(例如写一些store
Function、triger、建DB_link……)等工作。在这种状况下，若企业找的DBA是系统管理出身的人，在接手的初期他一定会吐血给你看。
至于在未来，有没有机会掌握甚至是“得心应手”，这就要看那个人的资质了。因此，关于这一方面的人选，我就比较建议找有程序开发经验的人(特别是对数据库
存取上有实际程序开发经验的人)，他们除对数据与数据结构的本身有一定的敏感度，也比较熟悉客户的需求，知道该如何翻整数据库。

　　要特别
注意的是，鱼与熊掌不可兼得，同时兼俱系统管理与程序开发等两种技术能力的人少之又少，因此，在进行DBA的招募前，建议企业先想好自己要的是哪一种人
才。不过，不论你找的是那一种Oracle
DBA，我都不太建议找一个只有OCP认证但无其它工作经验的人来做DBA。因为这意味着，企业要落实数据库管理的阵痛期可能会拉得很长，而且阵痛的过程
中DBA也很是辛苦。

　　运气好的时候，就像今天故事的主角小白一样，在我们的扶持下，撑了一年半，已经可以称得上是称职的DBA。运气不好的时候，像我在教育培训中心的同学，他苦撑了一年后离去，至今他仍不敢跟别人说他有OCP的证照。

　　最后，要另外一提的是“效能调校”。由于Oracle数据库的效能是可以被调整的。因此，也有不少企业在找Oracle DBA时，是以调校数据库效能为主要诉求。这样的想法是很好，只不过，“效能调校”所牵扯到的领域太过广泛。

　　决定数据库效能高低的因素有很多，有时候是单纯的数据库参数没有调整好，有时候则牵扯到整个硬件架构，甚至是程序软件开发规划等问题。光是能做到单点调校的DBA即已十分难得，Oracle DBA若不是以很全面的角度在思考问题来进行调校，也很可能导致数据库系统出现其它面向的问题。

　　发生在企业的真实状况是，一个应用服务能否顺利运维，与服务器、
储存设备、网通设备等硬件，数据库、操作系统、应用系统(复杂点的还牵扯到中间件软件与开发组件，例如ODBC
)等范畴都有关。因此，一个DBA很难在第一时间就面面俱到地思考所有问题，必须慢慢地依经验抽丝剥茧地清楚问题的所在。所以，如果企业目前尚未有专任的
Oracle DBA，我会建议千万不要一开始就想找一个能做好“效能调校”等工作的Oracle
DBA(不过，还是可以去找到协助运维或数据整理的DBA)，否则，可能会导致极心痛的失望。

　　OCP的认证真的没有用吗?

　　说到这里，相信很多人会想问：“那我选人的时候，还要看OCP这张认证吗?这张认证不就跟废物一样?”如果真的造成这样的误会，我一定会被这些教育培训中心及Oracle 原厂给杀了。这里要郑重的重新声明一下：“我本人也是教育培训中心教出来的……”。

　
　教育培训中心的训练课程有其重要性，尤其是数据库基本概念等。老实说，一个Oracle
DBA若没有先好好把OCP的五门课(8i是五门课，9i以后有两门合并，变成四门课)攻读过一遍，了解Oracle
数据库的架构与运作原理的DBA，我不觉得他有资格说他是一个DBA。

　　没错，我刚刚确实提到造成数据库运维不顺的原因，有可能是操作系
统或系统开发等非数据库本身的问题所引发的。但是，愈了解数据库架构与运作原理(真得好好读懂这一些书)的人，才愈有可能厘清上述的“非数据库本身的因素
”来自何处，并进一步地针对数据库的特别来改善问题。而且，他们在管理数据库时，他们的做法也会比较多元化。

　　以数据库备份为例，没有考
过OCP认证的DBA，他们熟悉的数据库备份方式可能就只有一种，而且还有可能是错的备份方式，例如：我之前听过最扯的备份方式就是在Oracle数据还
在运作的状态，直接用OS提供的Ccopy指令将数据库档案复制出来。相对的，考过OCP证照的DBA，他们在备份方式的选择上就比较多元，而且能根据不
同的情境采取不同的备份或备源方式，以确保灾难发生的时候可以无误恢复各种数据。

　　因此，企业在进行Oracle DBA的招募时，DBA是否拥有OCP认证，仍旧是一件很需要被考虑的。但同样需要提醒的是，拥有OCP认证只代表这个DBA对于数据库的熟悉度，不代表他有能力发现问题与解决问题。

　　我个人的想法是，拥有OCP认证，只代表该人员拥有“开始往后的DBA生涯”的权力，至于这个DBA 未来的成长幅度，则取决于他在系统面与程序面的学习能力。

　　拥有10g或11g的OCP认证足够吗?

　
　过去在Oracle数据库 8i、9i的年代，只要考4、5门课(8i 五门、9i
四门)才可以拿到OCP认证，但自从10g以后，只要考两门就可以拿到OCP认证，这样的人是否够格呢?很多CIO都在问我这个问题。我想，并不能用考试
科目的多寡来决定人的素质。不过，说老实话，就目前得知教育培训内容来说，以授课时数来看，10g以后的OCP教育培训的总天数是10天，比8i、9i少
了一半的时间，言尽于此，大家应该心里都有底了。

　　虽然10g以后的版本，数据库在管理与应用等操作上比 8i、9i
容易多了。但就数据库底层的技术架构概念以及运作模式而言，我个人觉得8i以及9i在教材的规划上比较扎实(相对地难懂);而10g以后的教材则比较浅显
易懂(不像以前我们读的8i“天书级”OCP教材)，但也相对地无法太深入地着眼于底层的技术架构与运作等内容。当然，这也是一个福音，因为可以更容易的
跨入这一个领域。

　　人员的本质最为关键

　　我会选择一个什么样的人来做DBA?事实上，我并不会先看他(或她)有没有OCP认证。

　
　“诚信”反而是我第一个考虑的标准。因为数据库的数据不外乎企业或客户的重要数据，如果DBA的功力好，但他却“诚信不足”，那么，上述重要数据很容易
在他“有心”又有“能力”的情况下被利用。第二个评估点是他对系统面的了解程度，以及他解决问题的方法。一个肯学、好学的DBA，在面对问题时，比较不需
要我担心这担心那，我也不用忧烦他是不是有能力熟悉OCP课程等。

　　所以，关于OCP执照，我觉得有是最好，如果没有的话，至少要找到对的人，再来要求、培训(考到OCP)。“对的人”远比拥有OCP证照重要多了，事实上，要“对的人”取得OCP认证一点也不难。

create controlfile to increase maxdatafiles in contro file

setp 1. Before increase it, notice the column RECORDS_TOTAL of REDO LOG and DATAFILE, 16 and 100 before increament.


SQL> select * from v$controlfile_record_section;

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
DATABASE 316 1 1 0
0 0

CKPT PROGRESS 8180 11 0 0
0 0

REDO THREAD 256 8 1 0
0 0

REDO LOG 72 16 16 0
0 12

DATAFILE 428 100 5 0
0 36

FILENAME 524 2298 44 0
0 0

TABLESPACE 68 100 6 0
0 1

TEMPORARY FILENAME 56 100 1 0
0 1

RMAN CONFIGURATION 1108 50 1 0
0 1

LOG HISTORY 56 292 42 1
42 42

OFFLINE RANGE 200 163 0 0
0 0

ARCHIVED LOG 584 308 49 1
49 49

BACKUP SET 40 409 7 1
7 7

BACKUP PIECE 736 200 7 1
7 7

BACKUP DATAFILE 116 282 6 1
6 6

BACKUP REDOLOG 76 215 10 1
10 10

DATAFILE COPY 660 223 16 1
16 16

BACKUP CORRUPTION 44 371 0 0
0 0

COPY CORRUPTION 40 409 0 0
0 0

DELETED OBJECT 20 818 10 1
10 10

PROXY COPY 852 249 0 0
0 0

BACKUP SPFILE 36 454 2 1
2 2

DATABASE INCARNATION 56 292 5 1
5 5

FLASHBACK LOG 84 2048 35 0
0 0

RECOVERY DESTINATION 180 1 1 0
0 0

INSTANCE SPACE RESERVATION 28 1055 1 0
0 0

REMOVABLE RECOVERY FILES 32 1000 7 0
0 0

RMAN STATUS 116 141 40 1
40 40

THREAD INSTANCE NAME MAPPING 80 8 8 0
0 0

MTTR 100 8 1 0
0 0

DATAFILE HISTORY 568 57 0 0
0 0

STANDBY DATABASE MATRIX 400 10 10 0
0 0

GUARANTEED RESTORE POINT 212 2048 0 0
0 0

RESTORE POINT 212 2083 0 0
0 0


34 rows selected.

step 2: generate trace file

SQL> alter database backup controlfile to trace as '/tmp/cntl_recreate.trc' noresetlogs;

Database altered.



[oracle@localhost ~]$ cat /tmp/cntl_recreate.trc
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=OCP10G_%t_%s_%r.arc
--
-- DB_UNIQUE_NAME="OCP10G"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/apps/oracle/oraarch/OCP10G'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OCP10G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/apps/oracle/oradata/OCP10G/redo01.log' SIZE 50M,
GROUP 2 '/apps/oracle/oradata/OCP10G/redo02.log' SIZE 50M,
GROUP 3 '/apps/oracle/oradata/OCP10G/redo03.log' SIZE 50M
-- STANDBY LOGFILE

DATAFILE
'/apps/oracle/oradata/OCP10G/system01.dbf',
'/apps/oracle/oradata/OCP10G/undotbs01.dbf',
'/apps/oracle/oradata/OCP10G/sysaux01.dbf',
'/apps/oracle/oradata/OCP10G/users01.dbf',
'/apps/oracle/oradata/OCP10G/example01.dbf'
CHARACTER SET WE8ISO8859P1
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');
-- 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 '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651015758.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651853690.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651854542.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651856491.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651862296.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/oracle/oradata/OCP10G/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.



step 3: prepare the sql modified from trace file

--my comments
--STARTUP NOMOUNT
--remove below line among "CREATE CONTROLFILE". it seems doesn't support remarks within the CREATE statement



cp ./cntrl_receate.sql cntrl_receate_new.sql

[oracle@localhost create-cntrl]$ cat cntrl_receate_new.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=OCP10G_%t_%s_%r.arc
--
-- DB_UNIQUE_NAME="OCP10G"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/apps/oracle/oraarch/OCP10G'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

--my comments
--STARTUP NOMOUNT
--remove below line among "CREATE CONTROLFILE". it seems doesn't support remarks within the CREATE statement

-- "-- STANDBY LOGFILE"

CREATE CONTROLFILE REUSE DATABASE "OCP10G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/apps/oracle/oradata/OCP10G/redo01.log' SIZE 50M,
GROUP 2 '/apps/oracle/oradata/OCP10G/redo02.log' SIZE 50M,
GROUP 3 '/apps/oracle/oradata/OCP10G/redo03.log' SIZE 50M
DATAFILE
'/apps/oracle/oradata/OCP10G/system01.dbf',
'/apps/oracle/oradata/OCP10G/undotbs01.dbf',
'/apps/oracle/oradata/OCP10G/sysaux01.dbf',
'/apps/oracle/oradata/OCP10G/users01.dbf',
'/apps/oracle/oradata/OCP10G/example01.dbf'
CHARACTER SET WE8ISO8859P1
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');
-- 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 '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651015758.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651853690.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651854542.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651856491.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651862296.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/oracle/oradata/OCP10G/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--



step 4: shutdown current database
step 5: backup existing control file

[oracle@localhost oradata]$ cd OCP10G
[oracle@localhost OCP10G]$ mkdir cntrl_bak
[oracle@localhost OCP10G]$ ls con*
control01.ctl control02.ctl control03.ctl
[oracle@localhost OCP10G]$ mv con*.ctl ./cntrl_bak/
[oracle@localhost OCP10G]$ ls*.ctl ./cntrl_bak/
bash: ls*.ctl: command not found
[oracle@localhost OCP10G]$ ls *.ctl ./cntrl_bak/
ls: *.ctl: No such file or directory
./cntrl_bak/:
control01.ctl control02.ctl control03.ctl


step 6: startup nomount


SQL> spool recreate_cntrl.log
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1593835520 bytes
Fixed Size 2073088 bytes
Variable Size 872418816 bytes
Database Buffers 704643072 bytes
Redo Buffers 14700544 bytes

step 7: invoke the scripts

SQL> @cntrl_recreate_new.sql
SQL> -- The following are current System-scope REDO Log Archival related
SQL> -- parameters and can be included in the database initialization file.
SQL> --
SQL> -- LOG_ARCHIVE_DEST=''
SQL> -- LOG_ARCHIVE_DUPLEX_DEST=''
SQL> --
SQL> -- LOG_ARCHIVE_FORMAT=OCP10G_%t_%s_%r.arc
SQL> --
SQL> -- DB_UNIQUE_NAME="OCP10G"
SQL> --
SQL> -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
SQL> -- LOG_ARCHIVE_MAX_PROCESSES=2
SQL> -- STANDBY_FILE_MANAGEMENT=MANUAL
SQL> -- STANDBY_ARCHIVE_DEST=?/dbs/arch
SQL> -- FAL_CLIENT=''
SQL> -- FAL_SERVER=''
SQL> --
SQL> -- LOG_ARCHIVE_DEST_1='LOCATION=/apps/oracle/oraarch/OCP10G'
SQL> -- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
SQL> -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
SQL> -- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
SQL> -- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
SQL> -- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
SQL> -- LOG_ARCHIVE_DEST_STATE_1=ENABLE
SQL>
SQL> --
SQL> -- The following commands will create a new control file and use it
SQL> -- to open the database.
SQL> -- Data used by Recovery Manager will be lost.
SQL> -- Additional logs may be required for media recovery of offline
SQL> -- Use this only if the current versions of all online logs are
SQL> -- available.
SQL>
SQL> -- After mounting the created controlfile, the following SQL
SQL> -- statement will place the database in the appropriate
SQL> -- protection mode:
SQL> -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
SQL>
SQL> --STARTUP NOMOUNT
SQL> CREATE CONTROLFILE REUSE DATABASE "OCP10G" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 32
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1000
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/apps/oracle/oradata/OCP10G/redo01.log' SIZE 50M,
9 GROUP 2 '/apps/oracle/oradata/OCP10G/redo02.log' SIZE 50M,
10 GROUP 3 '/apps/oracle/oradata/OCP10G/redo03.log' SIZE 50M
11 DATAFILE
12 '/apps/oracle/oradata/OCP10G/system01.dbf',
13 '/apps/oracle/oradata/OCP10G/undotbs01.dbf',
14 '/apps/oracle/oradata/OCP10G/sysaux01.dbf',
15 '/apps/oracle/oradata/OCP10G/users01.dbf',
16 '/apps/oracle/oradata/OCP10G/example01.dbf'
17 CHARACTER SET WE8ISO8859P1
18 ;

Control file created.

SQL>
SQL> -- Configure RMAN configuration record 1
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');

PL/SQL procedure successfully completed.

SQL> -- Commands to re-create incarnation table
SQL> -- Below log names MUST be changed to existing filenames on
SQL> -- disk. Any one log file from each branch can be used to
SQL> -- re-create incarnation records.
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651015758.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651853690.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651854542.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651856491.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651862296.arc';
SQL> -- Recovery is required if any of the datafiles are restored backups,
SQL> -- or if the last shutdown was not normal or immediate.
SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL>
SQL> -- All logs need archiving and a log switch is needed.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL>
SQL> -- Database can now be opened normally.
SQL> ALTER DATABASE OPEN;

Database altered.

SQL>
SQL> -- Commands to add tempfiles to temporary tablespaces.
SQL> -- Online tempfiles have complete space information.
SQL> -- Other tempfiles may require adjustment.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/oracle/oradata/OCP10G/temp01.dbf'
2 SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SQL> -- End of tempfile additions.
SQL> --

step 8: verify the result of REDO LOG and DATAFILE

SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
DATABASE 316 1 1 0
0 0

CKPT PROGRESS 8180 11 0 0
0 0

REDO THREAD 256 8 1 0
0 0

REDO LOG 72 32 3 0
0 0

DATAFILE 428 1000 5 0
0 0

FILENAME 524 4146 9 0
0 0

TABLESPACE 68 1000 6 0
0 1

TEMPORARY FILENAME 56 1000 1 0
0 1

RMAN CONFIGURATION 1108 50 1 0
0 1

LOG HISTORY 56 292 1 1
1 1

OFFLINE RANGE 200 1063 0 0
0 0

ARCHIVED LOG 584 308 3 1
3 3

BACKUP SET 40 1227 0 0
0 0

BACKUP PIECE 736 1000 0 0
0 0

BACKUP DATAFILE 116 1128 0 0
0 0

BACKUP REDOLOG 76 215 0 0
0 0

DATAFILE COPY 660 1016 0 0
0 0

BACKUP CORRUPTION 44 1115 0 0
0 0

COPY CORRUPTION 40 1227 0 0
0 0

DELETED OBJECT 20 818 0 0
0 0

PROXY COPY 852 1017 0 0
0 0

BACKUP SPFILE 36 454 0 0
0 0

DATABASE INCARNATION 56 292 1 1
1 1

FLASHBACK LOG 84 2048 0 0
0 0

RECOVERY DESTINATION 180 1 1 0
0 0

INSTANCE SPACE RESERVATION 28 1055 0 0
0 0

REMOVABLE RECOVERY FILES 32 1000 0 0
0 0

RMAN STATUS 116 141 0 0
0 0

THREAD INSTANCE NAME MAPPING 80 8 8 0
0 0

MTTR 100 8 1 0
0 0

DATAFILE HISTORY 568 57 0 0
0 0

STANDBY DATABASE MATRIX 400 10 10 0
0 0

GUARANTEED RESTORE POINT 212 2048 0 0
0 0

RESTORE POINT 212 2083 0 0
0 0


34 rows selected.

SQL> spool off
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1593835520 bytes
Fixed Size 2073088 bytes
Variable Size 872418816 bytes
Database Buffers 704643072 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.

step 9: note that the size of controlfile is creaased

[oracle@localhost OCP10G]$ ls -lrt
total 1625676
drwxrwxr-x 2 oracle oracle 4096 Oct 28 13:57 cntrl_bak
-rw-r----- 1 oracle oracle 52429312 Oct 28 14:03 redo02.log
-rw-r----- 1 oracle oracle 52429312 Oct 28 14:03 redo01.log
-rw-r----- 1 oracle oracle 30416896 Oct 28 14:03 temp01.dbf
-rw-rw---- 1 oracle oracle 5251072 Oct 28 14:04 users01.dbf
-rw-r----- 1 oracle oracle 492838912 Oct 28 14:04 undotbs01.dbf
-rw-r----- 1 oracle oracle 524296192 Oct 28 14:04 system01.dbf
-rw-r----- 1 oracle oracle 304095232 Oct 28 14:04 sysaux01.dbf
-rw-r----- 1 oracle oracle 52429312 Oct 28 14:04 redo03.log
-rw-r----- 1 oracle oracle 104865792 Oct 28 14:04 example01.dbf
-rw-rw---- 1 oracle oracle 14630912 Oct 28 14:04 control03.ctl
-rw-rw---- 1 oracle oracle 14630912 Oct 28 14:04 control02.ctl
-rw-rw---- 1 oracle oracle 14630912 Oct 28 14:04 control01.ctl

[oracle@localhost OCP10G]$ ll -l cntrl_bak/
total 21696
-rw-rw---- 1 oracle oracle 7389184 Oct 28 13:56 control01.ctl
-rw-rw---- 1 oracle oracle 7389184 Oct 28 13:56 control02.ctl
-rw-rw---- 1 oracle oracle 7389184 Oct 28 13:56 control03.ctl


--fallback

In case of fallback, just
a). shutdown abort the instance
b). cp over the backed control files and startup again

Runing advisor without voilate license agreement.

I like the advisors introudced in 10g, but company always tries to cut cost which affects buying the tuning package license. Luckily there is back door, i.e we can call them from command line. Below is the illustration of using segment advisor.

googled and get the sample template from http://www.oracle-base.com/articles/10g/SpaceObjectTransactionManagement10g.php

cat call_segment_advisor.sql

DECLARE
l_object_id NUMBER;
BEGIN
-- Create a segment advisor task for the SCOTT.EMP table.
-- update your own owner, table name (case-sensitive)
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'EMP_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For EMP');

DBMS_ADVISOR.create_object (
task_name => 'EMP_SEGMENT_ADVISOR',
object_type => 'TABLE',
attr1 => 'DBAM1',
attr2 => 'M1_OUTBOUND_USAGE',
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => 'EMP_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');

DBMS_ADVISOR.execute_task(task_name => 'EMP_SEGMENT_ADVISOR');
END;
/


-- Create a segment advisor task for the USERS tablespace.
-- DBMS_ADVISOR.create_task (
-- advisor_name => 'Segment Advisor',
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- task_desc => 'Segment Advisor For USERS');

-- DBMS_ADVISOR.create_object (
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- object_type => 'TABLESPACE',
-- attr1 => 'USERS',
-- attr2 => NULL,
-- attr3 => NULL,
-- attr4 => 'null',
-- attr5 => NULL,
-- object_id => l_object_id);

-- DBMS_ADVISOR.set_task_parameter (
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- parameter => 'RECOMMEND_ALL',
-- value => 'TRUE');

-- DBMS_ADVISOR.execute_task(task_name => 'USERS_SEGMENT_ADVISOR');
--END;
--/

-- Display the findings.
SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR')
ORDER BY f.task_name, f.impact DESC;

select message from dba_advisor_findings f
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR') ;

select more_info from dba_advisor_findings f
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR') ;

exec dbms_advisor.delete_task('EMP_SEGMENT_ADVISOR');


SQL> @call_segment_advisor.sql


PL/SQL procedure successfully completed.


TASK_NAME IMPACT OBJECT_TYPE SCHEMA OBJECT_NAME MESSAGE MORE_INFO
-------------------- ---------- -------------------- -------------------- ------------------------------ ---------------------------------------- ----------------------------------------
EMP_SEGMENT_ADVISOR TABLE DBAM1 M1_OUTBOUND_USAGE The free space in the object is less tha Allocated Space:15398338560: Used Space:
n the size of the last extent. 14503648178: Reclaimable Space :89469038
2:


SQL> select message from dba_advisor_findings f
2 WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR');

MESSAGE
----------------------------------------
The free space in the object is less tha
n the size of the last extent.


SQL> select more_info from dba_advisor_findings f WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR');

MORE_INFO
----------------------------------------
Allocated Space:15398338560: Used Space:
14503648178: Reclaimable Space :89469038
2:

:
SQL> select more_info FROM dba_advisor_findings f WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR6');

MORE_INFO
----------------------------------------
Allocated Space:21937386023: Used Space:
21863850510: Reclaimable Space :73535513
:

--delete the task if no longer needed, thus the script can be re-used easily without changing task name

SQL> exec dbms_advisor.delete_task('EMP_SEGMENT_ADVISOR2');

PL/SQL procedure successfully completed.


Read more the Oracle document "Oracle® Database PL/SQL Packages and Types Reference", DBMS_ADVISOR allows us to call all seven advisors defined in the view DBA_ADVISOR_DEFINITIONS.

BTW, the speed is surpising with ASSM. For above case, regardless the table size is about 21Gb, it finishes within 5 seconds.

Cheers!

Define your favorite editor for sqlplus

Two way to do so.

1. Inside sqlplus : define _editor=vi
2. Outside sqlplus : export EDITOR=vi

lock and create/drop index

Conclusion:

DDL (CREATE&DROP INDEX) conflict with DML. More explanation can be found below

Locks and ORA-00054 error

SQL> create table t1 (f1 number);

Table created.

SQL> select * from t1;

no rows selected

SQL> create index t1_idx on t1(f1);

Index created.

SQL> drop index t1_idx;
drop index t1_idx
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL> commit;

Commit complete.

SQL> select * from t1;

no rows selected

SQL> select * from t1;

F1
----------
2

SQL> drop index t1_idx;

Index dropped.

SQL> create index t1_idx on t1(f1);
create index t1_idx on t1(f1)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Using gdb for debug

install Oracle XE on Ubuntu x86_64

1. download oracle-xe_10.2.0.1-1.0_i386.deb from otn.oracle.com
2. Double click the deb file, througs you below error message
"Error: wrong architecture i386"
3. Try below command

hmc@hmc-desktop:~/Desktop$ sudo dpkg -i --force-architecture oracle-xe_10.2.0.1-1.0_i386.deb
[sudo] password for hmc:
dpkg - warning, overriding problem because --force enabled:
package architecture (i386) does not match system (amd64)
Selecting previously deselected package oracle-xe.
(Reading database ... 344121 files and directories currently installed.)
Unpacking oracle-xe (from oracle-xe_10.2.0.1-1.0_i386.deb) ...
dpkg: dependency problems prevent configuration of oracle-xe:
oracle-xe depends on libaio (>= 0.3.96) | libaio1 (>= 0.3.96); however:
Package libaio is not installed.
Package libaio1 is not installed.
dpkg: error processing oracle-xe (--install):
dependency problems - leaving unconfigured
Errors were encountered while processing:
oracle-xe

4. Repeat below command to install missing packages

hmc@hmc-desktop:~/Desktop$ sudo apt-get install libaio*
Reading package lists... Done
Building dependency tree
Reading state information... Done
Note, selecting libaiksaurus0c102 for regex 'libaio*'
Note, selecting libaiksaurus-1.2-data for regex 'libaio*'
Note, selecting libaiksaurusgtk-1.2-dev for regex 'libaio*'
Note, selecting libaiksaurusgtk-1.2-0 for regex 'libaio*'
Note, selecting libaio1 for regex 'libaio*'
Note, selecting libaiksaurus-1.2-dev for regex 'libaio*'
Note, selecting libaiksaurus0 for regex 'libaio*'
Note, selecting libaiksaurusgtk0c102 for regex 'libaio*'
Note, selecting libaio-dev for regex 'libaio*'
Note, selecting libaiksaurusgtk-1.2-0c2a for regex 'libaio*'
Note, selecting libaiksaurusgtk0 for regex 'libaio*'
Note, selecting libaio for regex 'libaio*'
Note, selecting libaio1-dbg for regex 'libaio*'
Note, selecting libaiksaurus-1.2-0 for regex 'libaio*'
Note, selecting libaiksaurusgtk-bin for regex 'libaio*'
Note, selecting gaiksaurus instead of libaiksaurusgtk-bin
Note, selecting libaiksaurus-1.2-0c2a for regex 'libaio*'
Note, selecting libaiksaurusgtk-dev for regex 'libaio*'
Note, selecting libaiksaurusgtk-1.2-dev instead of libaiksaurusgtk-dev
Note, selecting libaiksaurus-bin for regex 'libaio*'
Note, selecting aiksaurus instead of libaiksaurus-bin
Note, selecting libaiksaurus-data for regex 'libaio*'
Note, selecting libaiksaurus-1.2-data instead of libaiksaurus-data
Note, selecting libaiksaurus-dev for regex 'libaio*'
Note, selecting libaiksaurus-1.2-dev instead of libaiksaurus-dev
The following packages were automatically installed and are no longer required:
ubuntustudio-icon-theme ubuntustudio-gdm-theme ubuntustudio-theme
ubuntustudio-wallpapers
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
aiksaurus gaiksaurus libaiksaurus-1.2-0c2a libaiksaurus-1.2-data
libaiksaurus-1.2-dev libaiksaurusgtk-1.2-0c2a libaiksaurusgtk-1.2-dev
libaio-dev libaio1 libaio1-dbg
The following NEW packages will be installed:
aiksaurus gaiksaurus libaiksaurus-1.2-0c2a libaiksaurus-1.2-data
libaiksaurus-1.2-dev libaiksaurusgtk-1.2-0c2a libaiksaurusgtk-1.2-dev
libaio-dev libaio1 libaio1-dbg
0 upgraded, 10 newly installed, 0 to remove and 1 not upgraded.
Need to get 513kB of archives.
After this operation, 1642kB of additional disk space will be used.
Do you want to continue [Y/n]? y
Get:1 http://tw.archive.ubuntu.com hardy/main libaiksaurus-1.2-data 1.2.1+dev-0.12-5 [318kB]
Get:2 http://tw.archive.ubuntu.com hardy/main libaiksaurus-1.2-0c2a 1.2.1+dev-0.12-5 [25.2kB]
Get:3 http://tw.archive.ubuntu.com hardy/universe aiksaurus 1.2.1+dev-0.12-5 [11.9kB]
Get:4 http://tw.archive.ubuntu.com hardy/main libaiksaurusgtk-1.2-0c2a 1.2.1+dev-0.12-5 [33.8kB]
Get:5 http://tw.archive.ubuntu.com hardy/universe gaiksaurus 1.2.1+dev-0.12-5 [9524B]
Get:6 http://tw.archive.ubuntu.com hardy/main libaiksaurus-1.2-dev 1.2.1+dev-0.12-5 [28.1kB]
Get:7 http://tw.archive.ubuntu.com hardy/main libaiksaurusgtk-1.2-dev 1.2.1+dev-0.12-5 [35.7kB]
Get:8 http://tw.archive.ubuntu.com hardy/main libaio1 0.3.106-8ubuntu1 [8430B]
Get:9 http://tw.archive.ubuntu.com hardy/main libaio-dev 0.3.106-8ubuntu1 [31.4kB]
Get:10 http://tw.archive.ubuntu.com hardy/main libaio1-dbg 0.3.106-8ubuntu1 [11.2kB]
Fetched 513kB in 4s (112kB/s)
Selecting previously deselected package libaiksaurus-1.2-data.
(Reading database ... 346502 files and directories currently installed.)
Unpacking libaiksaurus-1.2-data (from .../libaiksaurus-1.2-data_1.2.1+dev-0.12-5_all.deb) ...
Selecting previously deselected package libaiksaurus-1.2-0c2a.
Unpacking libaiksaurus-1.2-0c2a (from .../libaiksaurus-1.2-0c2a_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package aiksaurus.
Unpacking aiksaurus (from .../aiksaurus_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package libaiksaurusgtk-1.2-0c2a.
Unpacking libaiksaurusgtk-1.2-0c2a (from .../libaiksaurusgtk-1.2-0c2a_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package gaiksaurus.
Unpacking gaiksaurus (from .../gaiksaurus_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package libaiksaurus-1.2-dev.
Unpacking libaiksaurus-1.2-dev (from .../libaiksaurus-1.2-dev_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package libaiksaurusgtk-1.2-dev.
Unpacking libaiksaurusgtk-1.2-dev (from .../libaiksaurusgtk-1.2-dev_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package libaio1.
Unpacking libaio1 (from .../libaio1_0.3.106-8ubuntu1_amd64.deb) ...
Selecting previously deselected package libaio-dev.
Unpacking libaio-dev (from .../libaio-dev_0.3.106-8ubuntu1_amd64.deb) ...
Selecting previously deselected package libaio1-dbg.
Unpacking libaio1-dbg (from .../libaio1-dbg_0.3.106-8ubuntu1_amd64.deb) ...
Setting up libaiksaurus-1.2-data (1.2.1+dev-0.12-5) ...
Setting up libaiksaurus-1.2-0c2a (1.2.1+dev-0.12-5) ...

Setting up aiksaurus (1.2.1+dev-0.12-5) ...
Setting up libaiksaurusgtk-1.2-0c2a (1.2.1+dev-0.12-5) ...

Setting up gaiksaurus (1.2.1+dev-0.12-5) ...

Setting up libaiksaurus-1.2-dev (1.2.1+dev-0.12-5) ...
Setting up libaiksaurusgtk-1.2-dev (1.2.1+dev-0.12-5) ...
Setting up libaio1 (0.3.106-8ubuntu1) ...

Setting up libaio-dev (0.3.106-8ubuntu1) ...
Setting up libaio1-dbg (0.3.106-8ubuntu1) ...
Processing triggers for libc6 ...
ldconfig deferred processing now taking place

5. Try again

hmc@hmc-desktop:~/Desktop$ sudo dpkg -i --force-architecture oracle-xe_10.2.0.1-1.0_i386.deb
dpkg - warning, overriding problem because --force enabled:
package architecture (i386) does not match system (amd64)
(Reading database ... 346600 files and directories currently installed.)
Preparing to replace oracle-xe 10.2.0.1-1.0 (using oracle-xe_10.2.0.1-1.0_i386.deb) ...
Unpacking replacement oracle-xe ...
Setting up oracle-xe (10.2.0.1-1.0) ...
Executing Post-install steps...
-e You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.


hmc@hmc-desktop:~/Desktop$ sudo /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"

6. and edit your ~/.bashrc of id oracle

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME
export ORACLE_SID=XE

export PATH

7. If you like rlwrap, define the alias for sqlplus in .bashrc

alias sqlplus='rlwrap sqlplus'

hmc@hmc-desktop:~$ sudo su - oracle
oracle@hmc-desktop:~$ pwd
/usr/lib/oracle/xe
oracle@hmc-desktop:~$ sqlplus "/as sysdba"
-su: sqlplus: command not found
oracle@hmc-desktop:~$ . .bashrc
oracle@hmc-desktop:~$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 15 21:31:23 2008

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


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO EDITION
---------- --- ----------------- ------------------ --------- --- -------
1 XE
hmc-desktop
10.2.0.1.0 15-SEP-08 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO XE

Cheers!