[Pending] Oracle 10g RAC Reboot Problem

Sometimes one node would be reboot suddenly. Problem not solved…

Reference:

1. [Chinese] http://www.itpub.net/747833.html

CRS Management Commands

To check CRS resource status

$ORA_CRS_HOME/bin/crs_stat –t

For example:

[oracle@rac2 css]$ $ORA_CRS_HOME/bin/crs_stat -t
Name Type Target State Host
————————————————————
ora….CL1.srv application ONLINE ONLINE rac1
ora….CL2.srv application ONLINE ONLINE rac2
ora….TEST.cs application ONLINE ONLINE rac1
ora….L1.inst application ONLINE ONLINE rac1
ora….L2.inst application ONLINE ONLINE rac2
ora.ORCL.db application ONLINE ONLINE rac1
ora….SM1.asm application ONLINE ONLINE rac1
ora….C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….SM2.asm application ONLINE ONLINE rac2
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2

此命令用来启动所有的集群服务，但是启动的前提是资源状态State不能是UNKNOWN

$ORA_CRS_HOME/bin/crs_start –all
$ORA_CRS_HOME/bin/crs_start 集群服务名
（集群服务名可以通过$ORA_CRS_HOME/bin/crs_stat得到）

下面的一组crs命令是用来启动关闭crs服务的

$ORA_CRS_HOME/bin/crs_stop -all
$ORA_CRS_HOME/bin/crs_stop 集群服务名
（集群服务名可以通过$ORA_CRS_HOME/bin/crs_stat得到）

下面通过一个真实的环境，具体讲解这些命令的使用。举例如下：

Oracle RAC在安装完毕后，重启RAC，一些服务不能启动，手动启动也没用。

下面是系统自动启动以后的情况：

[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
————————————————————
ora….CL1.srv application ONLINE UNKNOWN rac1
ora….CL2.srv application ONLINE UNKNOWN rac2
ora….TEST.cs application ONLINE UNKNOWN rac2
ora….L1.inst application ONLINE OFFLINE
ora….L2.inst application ONLINE OFFLINE
ora.ORCL.db application ONLINE UNKNOWN rac1
ora….SM1.asm application ONLINE UNKNOWN rac1
ora….C1.lsnr application ONLINE UNKNOWN rac1
ora.rac1.gsd application ONLINE UNKNOWN rac1
ora.rac1.ons application ONLINE UNKNOWN rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….SM2.asm application ONLINE UNKNOWN rac2
ora….C2.lsnr application ONLINE UNKNOWN rac2
ora.rac2.gsd application ONLINE UNKNOWN rac2
ora.rac2.ons application ONLINE UNKNOWN rac2
ora.rac2.vip application ONLINE ONLINE rac2

可以看到，只有vip是能自动启动的！其它资源都处于UNKNOWN状态下。

手工用srvctl启动试试！

$ srvctl start nodeapps -n rac1
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource ‘ora.rac1.gsd’ has placement error.
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource ‘ora.rac1.vip’ has placement error.
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource ‘ora.rac1.ons’ has placement error.
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource ‘ora.rac1.LISTENER_RAC1.lsnr’ has placement error.

测试crs：

$crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

[oracle@rac1 bin]$ crsctl start resources
Starting resources.
Successfully started CRS resources

[oracle@rac1 bin]$ pwd
/oracle/app/oracle/oracle/product/10.2.0/crs/bin

[oracle@rac1 bin]$ gsdctl stop
[oracle@rac1 bin]$ gsdctl status
[oracle@rac1 bin]$ ./gsdctl status
[oracle@rac1 bin]$ gsdctl start
[oracle@rac1 bin]$ srvctl start nodeapps -n rac1
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource ‘ora.rac1.gsd’ has placement error.
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource ‘ora.rac1.vip’ has placement error.
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource ‘ora.rac1.ons’ has placement error.
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource ‘ora.rac1.LISTENER_RAC1.lsnr’ has placement error.

[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
————————————————————
ora….CL1.srv application ONLINE UNKNOWN rac1
ora….CL2.srv application ONLINE UNKNOWN rac2
ora….TEST.cs application ONLINE UNKNOWN rac2
ora….L1.inst application ONLINE OFFLINE
ora….L2.inst application ONLINE OFFLINE
ora.ORCL.db application ONLINE UNKNOWN rac1
ora….SM1.asm application ONLINE UNKNOWN rac1
ora….C1.lsnr application ONLINE UNKNOWN rac1
ora.rac1.gsd application ONLINE UNKNOWN rac1
ora.rac1.ons application ONLINE UNKNOWN rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….SM2.asm application ONLINE UNKNOWN rac2
ora….C2.lsnr application ONLINE UNKNOWN rac2
ora.rac2.gsd application ONLINE UNKNOWN rac2
ora.rac2.ons application ONLINE UNKNOWN rac2
ora.rac2.vip application ONLINE ONLINE rac2

可以看到，仍然是启动后的原始状态。从上面的显示可以知道，每个资源的State显示为UNKNOWN，应该是由于两个节点时间不同步，进而造成crs在启动的时候无法认到系统已经注册的资源造成的。

解决方法：首先，保证两个节点时间保持同步，ORACLE 10g RAC两个节点之间有10几秒的时间误差，不会对应用造成影响。但是过分大的时间误差可能导致某个节点down机，或者节点不断重启。然后执行如下操作：

[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stop –all
Attempting to stop `ora.rac1.vip` on member `rac1`
Attempting to stop `ora.rac2.vip` on member `rac2`
Stop of `ora.rac2.vip` on member `rac2` succeeded.
Stop of `ora.rac1.vip` on member `rac1` succeeded.

[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stat -t
Name Type Target State Host
————————————————————
ora….CL1.srv application ONLINE UNKNOWN rac1
ora….CL2.srv application ONLINE UNKNOWN rac2
ora….TEST.cs application ONLINE UNKNOWN rac2
ora….L1.inst application OFFLINE OFFLINE
ora….L2.inst application OFFLINE OFFLINE
ora.ORCL.db application OFFLINE UNKNOWN rac1
ora….SM1.asm application OFFLINE UNKNOWN rac1
ora….C1.lsnr application OFFLINE UNKNOWN rac1
ora.rac1.gsd application ONLINE UNKNOWN rac1
ora.rac1.ons application ONLINE UNKNOWN rac1
ora.rac1.vip application OFFLINE OFFLINE
ora….SM2.asm application OFFLINE UNKNOWN rac2
ora….C2.lsnr application OFFLINE UNKNOWN rac2
ora.rac2.gsd application ONLINE UNKNOWN rac2
ora.rac2.ons application ONLINE UNKNOWN rac2
ora.rac2.vip application OFFLINE OFFLINE

[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stat （此命令可以查看各个资源的完整服务名称）
NAME=ora.ORCL.ORATEST.ORCL1.srv
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
NAME=ora.ORCL.ORATEST.ORCL2.srv
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
NAME=ora.ORCL.ORATEST.cs
TYPE=application
TARGET=ONLINE
STATE=UNKNOWN on rac2
NAME=ora.ORCL.ORCL1.inst
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
NAME=ora.ORCL.ORCL2.inst
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
NAME=ora.ORCL.db
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
NAME=ora.rac1.ASM1.asm
TYPE=application
TARGET=OFFLINE
STATE=UNKNOWN on rac1
NAME=ora.rac1.LISTENER_RAC1.lsnr
TYPE=application
TARGET=OFFLINE
STATE=UNKNOWN on rac1
NAME=ora.rac1.gsd
TYPE=application
TARGET=ONLINE
STATE=UNKNOWN on rac1
NAME=ora.rac1.ons
TYPE=application
TARGET=ONLINE
STATE=UNKNOWN on rac1
NAME=ora.rac1.vip
TYPE=application
TARGET=ONLINE
STATE=UNKNOWN on rac1
NAME=ora.rac2.ASM2.asm
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
NAME=ora.rac2.LISTENER_RAC2.lsnr
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
NAME=ora.rac2.gsd
TYPE=application
TARGET=ONLINE
STATE=UNKNOWN on rac2
NAME=ora.rac2.ons
TYPE=application
TARGET=ONLINE
STATE=UNKNOWN on rac2
NAME=ora.rac2.vip
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE

$ORA_CRS_HOME/bin/crs_stop –all只能停掉State为ONLINE的服务，状态为UNKNOWN的资源不能通过这个命令关闭，因此可以通过$ORA_CRS_HOME/bin/crs_stop 服务名 的方式单独停止那些状态为UNKNOWN的资源。下面是通过$ORA_CRS_HOME/bin/crs_stop命令依次停止资源状态State为UNKNOWN的服务。

[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stop ora.ORCL.ORATEST.cs
Attempting to stop `ora.ORCL.ORATEST.cs` on member `rac2`
Stop of `ora.ORCL.ORATEST.cs` on member `rac2` succeeded.
[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stop ora.rac1.ASM1.asm
Attempting to stop `ora.rac1.ASM1.asm` on member `rac1`
Stop of `ora.rac1.ASM1.asm` on member `rac1` succeeded.
[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stop ora.rac1.LISTENER_RAC1.lsnr
Attempting to stop `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Stop of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.
[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stop ora.rac1.ons
Attempting to stop `ora.rac1.ons` on member `rac1`
Stop of `ora.rac1.ons` on member `rac1` succeeded.
[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stop ora.rac1.vip
Attempting to stop `ora.rac1.vip` on member `rac1`
Stop of `ora.rac1.vip` on member `rac1` succeeded.
[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stop ora.rac2.gsd
Attempting to stop `ora.rac2.gsd` on member `rac2`
Stop of `ora.rac2.gsd` on member `rac2` succeeded.
[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stop ora.rac2.ons
Attempting to stop `ora.rac2.ons` on member `rac2`
Stop of `ora.rac2.ons` on member `rac2` succeeded.
[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stop ora.rac1.gsd
Attempting to stop `ora.rac1.gsd` on member `rac1`
Stop of `ora.rac1.gsd` on member `rac1` succeeded.

把所有状态为UNKNOWN的资源全部停止以后，再次查看资源状态：

[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_stat -t
Name Type Target State Host
————————————————————
ora….CL1.srv application OFFLINE OFFLINE
ora….CL2.srv application OFFLINE OFFLINE
ora….TEST.cs application OFFLINE OFFLINE
ora….L1.inst application OFFLINE OFFLINE
ora….L2.inst application OFFLINE OFFLINE
ora.ORCL.db application OFFLINE OFFLINE
ora….SM1.asm application OFFLINE OFFLINE
ora….C1.lsnr application OFFLINE OFFLINE
ora.rac1.gsd application OFFLINE OFFLINE
ora.rac1.ons application OFFLINE OFFLINE
ora.rac1.vip application OFFLINE OFFLINE
ora….SM2.asm application OFFLINE OFFLINE
ora….C2.lsnr application OFFLINE OFFLINE
ora.rac2.gsd application OFFLINE OFFLINE
ora.rac2.ons application OFFLINE OFFLINE
ora.rac2.vip application OFFLINE OFFLINE

此时可以重启所有crs资源了

[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/crs_start –all（注意，这里是在oracle用户下操作）
Attempting to start `ora.rac1.vip` on member `rac1`
Attempting to start `ora.rac2.vip` on member `rac2`
Start of `ora.rac2.vip` on member `rac2` succeeded.
Attempting to start `ora.rac2.ASM2.asm` on member `rac2`
Start of `ora.rac1.vip` on member `rac1` succeeded.
Attempting to start `ora.rac1.ASM1.asm` on member `rac1`
Start of `ora.rac2.ASM2.asm` on member `rac2` succeeded.
Attempting to start `ora.ORCL.ORCL2.inst` on member `rac2`
Start of `ora.rac1.ASM1.asm` on member `rac1` succeeded.
Attempting to start `ora.ORCL.ORCL1.inst` on member `rac1`
Start of `ora.ORCL.ORCL2.inst` on member `rac2` succeeded.
Attempting to start `ora.rac2.LISTENER_RAC2.lsnr` on member `rac2`
Start of `ora.rac2.LISTENER_RAC2.lsnr` on member `rac2` succeeded.
Start of `ora.ORCL.ORCL1.inst` on member `rac1` succeeded.
Attempting to start `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Start of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.
Attempting to start `ora.ORCL.ORATEST.ORCL1.srv` on member `rac1`
CRS-1002: Resource ‘ora.rac1.ons’ is already running on member ‘rac1′
CRS-1002: Resource ‘ora.ORCL.db’ is already running on member ‘rac1′
CRS-1002: Resource ‘ora.rac2.ons’ is already running on member ‘rac2′
Attempting to start `ora.ORCL.ORATEST.ORCL2.srv` on member `rac2`
Attempting to start `ora.ORCL.ORATEST.cs` on member `rac2`
Attempting to start `ora.rac1.gsd` on member `rac1`
Attempting to start `ora.rac2.gsd` on member `rac2`
Start of `ora.ORCL.ORATEST.cs` on member `rac2` succeeded.
Start of `ora.rac2.gsd` on member `rac2` succeeded.
Start of `ora.ORCL.ORATEST.ORCL2.srv` on member `rac2` succeeded.
Start of `ora.rac1.gsd` on member `rac1` succeeded.
Start of `ora.ORCL.ORATEST.ORCL1.srv` on member `rac1` succeeded.
CRS-0223: Resource ‘ora.ORCL.db’ has placement error.
CRS-0223: Resource ‘ora.rac1.ons’ has placement error.
CRS-0223: Resource ‘ora.rac2.ons’ has placement error.

[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
————————————————————
ora….CL1.srv application ONLINE ONLINE rac1
ora….CL2.srv application ONLINE ONLINE rac2
ora….TEST.cs application ONLINE ONLINE rac2
ora….L1.inst application ONLINE ONLINE rac1
ora….L2.inst application ONLINE ONLINE rac2
ora.ORCL.db application ONLINE ONLINE rac1
ora….SM1.asm application ONLINE ONLINE rac1
ora….C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….SM2.asm application ONLINE ONLINE rac2
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2

此时，看到crs所有资源全部启动，问题解决。

其它一些常用命令：

$ORA_CRS_HOME/bin/crsctl check crs 用于检查后台进程状态
$ORA_CRS_HOME/bin/crsctl start resources 启动crs资源
$ORA_CRS_HOME/bin/crsctl start crs 启动crs，需要超级用户操作。
$ORA_CRS_HOME/bin/crsctl stop crs 关闭crs，需要超级用户操作。
$ORA_CRS_HOME/bin/crs_unregister 取消注册crs
$ORA_CRS_HOME/bin/ocrcheck 检查ocr设置信息用ocrcheck
$ORA_CRS_HOME/bin/crsctl query css votedisk 检查表决磁盘信息

For Example:

[oracle@rac2 css]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 104344
Used space (kbytes) : 4568
Available space (kbytes) : 99776
ID : 328160432
Device/File Name : /dev/raw/raw4
Device/File integrity check succeeded
Device/File Name : /dev/raw/raw5
Device/File integrity check succeeded
Cluster registry integrity check succeeded

[oracle@rac2 css]$ $ORA_CRS_HOME/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 104344
Used space (kbytes) : 4568
Available space (kbytes) : 99776
ID : 328160432
Device/File Name : /dev/raw/raw4
Device/File integrity check succeeded
Device/File Name : /dev/raw/raw5
Device/File integrity check succeeded
Cluster registry integrity check succeeded

[oracle@rac2 css]$ crsctl query css votedisk
0. 0 /dev/raw/raw6
1. 0 /dev/raw/raw7
2. 0 /dev/raw/raw8
located 3 votedisk(s).

注意：
执行crsctl命令的可以是root用户，也可以是oracle用户，如果是root用户要指定绝对路径，但是对于crsctl start crs和crsctl stop crs必须是root用户来执行。

crs主要进程

1. crsd
   负责管理ha操作
   管理crs资源，如linstener，vip，ons，gsn等
   由root用户管理、启动
2. ocssd
   管理各节点的关系，用于节点间通信
   由oracle用户运行管理
3. oprocd
   集群进程管理 —Process monitor for the cluster.
   仅在没有使用vendor的集群软件状态下运行
4. evmd
   事件检测进程，由oracle用户运行管理
5. 主要log位置
   $ORA_CRS_HOME/log/节点主机名/racg
   $ORA_CRS_HOME/log/节点主机名/crsd
   $ORA_CRS_HOME/crs/init
   $ORA_CRS_HOME/css/log
   $ORA_CRS_HOME/css/init
   $ORA_CRS_HOME/evm/log
   $ORA_CRS_HOME/evm/init
   $ORA_CRS_HOME/srvm/log

Reference:

1. [Chinese] http://blog.chinaunix.net/u1/52223/showart_486081.html

Hostname Change

In doing RAC Workshop, we use VMware to create a server named “rac1″ and then copy all the files as to another server “rac2″. The two servers are the same just after the copy process, including all the IP setting and hostname. We need to change the 2nd server with different hostname.

Change the hostname on a running machine:

$hostname NEW_NAME

This is active right away and will remain like that until the system will be rebooted. You will most probably need to exit the current shell in order to see the change in your shell prompt.

Change the hostname permanently on Redhat based systems:

$vi /etc/sysconfig/network

edit the new Hostname in this file

Reference:

1. http://www.ducea.com/2006/08/07/how-to-change-the-hostname-of-a-linux-system/

iSQLPlus in Oracle 10g

Start sqlplus.exe or sqlplusw.exe (windows GUI)

$isqlplusctl start

$isqlplusctl stop

In IE or Firefox:

http://<hostname.domain>:port/isqlplus

Port preset: 5560

Important Things When Installing Linux Server on USB Disk

To practice Dataguard on one machine. You can install two linux servers using VMware. The other settings during the Linux installation are the same. Only the IP addresses are different.

You can install one in C disk and another one using your USB disk. Make sure:

1. Your company has enough resources, both the CPU and the memory;
2. Your disk has enough space, both the C disk and the USB disk.
3. Your USB disk always uses fixed driver letter and path.

How to fix the driver letter and path for your USB disk?

1. Right click “My Computer”, choose “Manage” from the menu
2. Computer Management (Local) -> Storage -> Disk Management
3. Right click your USB disk and choose “Change Driver Letter and Paths…”
4. Click “Change …”
5. Assign a drive letter to this USB disk

Data Guard Workshop

The most important things for data guard configuration:

1. Listener control files are correctly set up;
2. Database parameter files are correctly set up;
3. All data files, control files and parameter files from Primary database are correctly transferred to Standby database.

Note:

• It’s not necessary to create a database on Standby server.
• On standby server, it only needs files in oradata and admin folders and parameter files to startup the database.

[To be continued]

xhost setting problem for local users

$su – oracle
$env | grep DISPLAY
DISPLAY=:0.0
$xclock
Xlib: connection to “:0.0″ refused by server
Xlib: No protocol specified

Error: Can’t open display: :0.0

The problem is that the local users were not authorized to access control on xhost. To solve this problem, you need to make the authorization using “root”.

[root@localhost]# xhost
access control enabled, only authorized clients can connect
[root@localhost]# xhost +oracle
xhost:   bad hostname “oracle”
[root@localhost]# xhost +local:oracle
non-network local connections being added to access control list
[root@localhost]# su – oracle
[oracle@localhost] $ xclock

xclock successfully started.

Sometimes, there is communication problem between guest Linux server and Windows XP host server. Applications or tools like OEM or DBCA might have problems or appear freezed. Using xhost to show the GUI on Linux server would be much more stable.

Batch Mode Statspack Installation and Application

To install Statspack in batch mode, you must assign values to the SQL*Plus variables that specify the default and temporary tablespaces before running SPCREATE.SQL. The variables are:

* DEFAULT_TABLESPACE: For the default tablespace
* TEMPORARY_TABLESPACE: For the temporary tablespace
* PERFSTAT_PASSWORD: For the PERFSTAT user

For example, on UNIX:

SQL> CONNECT / AS SYSDBA
SQL> define default_tablespace=’TOOLS’
SQL> define temporary_tablespace=’TEMP’
SQL> define perfstat_password=’my_perfstat_password’
SQL> @?/rdbms/admin/spcreate

When SPCREATE.SQL is run, it does not prompt for the information provided by the variables.

Reference:

1. http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm#21667
2. http://www.akadia.com/services/ora_statspack_survival_guide.html

LogMiner Workshop

Step 0: LogMiner Tools Installation

Log in as sysdba,
1) create dbms_logmnr package to analyze log files;

SQL>@$ORACLE_HOME/rdbms/admin/dbmslm.sql

2) create dbms_logmnr_d package to create dictionary files.

SQL>@$ORACLE_HOME/rdbms/admin/dbmslmd.sql

Step 1: Edit Parameter File

If you startup database using pfile, edit pfile directly;

If you startup database using spfile,

SQL> create pfile from spfile;

Edit init<SID>.ora and put the following:

utl_file_dir=/u01/app/oracle/admin/<SID>/udump

Step 2: Verify utl parameter

SQL> connect / as sysdba
SQL> startup force % OR STARTUP
SQL> show parameter utl

Step 3: Turn on supplemental logging at the database level

In order to use DDL_DICT_TRACKING later, you need to turn on supplemental logging at the database level. For other features, such as logging chain rows, cluster table, it should also be enabled.

SQL> alter database add supplemental log data

Step 4: Create a log miner dictionary file

SQL> execute sys.dbms_logmnr_d.build(-
>Dictionary_filename=>’mylog_dict.dic’,-
>Dictionary_location=>’/u01/app/oracle/admin/<SID>/udump’;

PL/SQL procedure successfully completed.

Step 5: Get current logfile info and current time

SQL> alter system switch logfile;
SQL> select member from v$logfile, v$log
where v$logfile.group# = v$log.group#
and v$log.status = ‘CURRENT’;

SQL>select to_char(current_timestamp, ‘DD-MON-RR HH24:MI:SS’) “TIME” from dual; % Time_1

Step 6: Perform some user activity

SQL> connect hr/hr
SQL> create table emp30 as
select employee_id, last_name, salary
from employees
where department_id=30;
SQL> alter table emp30 add (new_salary number(8,2));
SQL> update emp30 set new_salary=salary*1.5;
SQL> rollback;
SQL> update emp30 set new_salary=salary*1.2;
SQL> commit;
SQL> drop table emp30;
SQL> select to_char(current_timestamp, ‘DD-MON-RR HH24:MI:SS’) “TIME” from dual; % TIME_2

Step 7: Create (and add) the list of logfile

SQL> execute dbms_logmnr.add_logfile(-
> logfilename=>’/u01/app/oracle/oradata/<SID>/logxx.rdo’,-
> options=>dbms_logmnr.new);

Step 8: Start Logminer

SQL> alter session set nls_date_format=’DD-MON-RR HH24:MI:SS’;
SQL> execute dbms_logmnr.start_logmnr(-
dictfilename=>’/u01/app/oracle/admin/<SID>/udump/mylog_dict.dic’,-
starttime=>’&starttime’,-
endtime=>’&endtime’,-
options=>dbms_logmnr.ddl_dic_tracking+dbms_logmnr.committed_data_only);

Enter value for starttime: TIME_1
Enter value for endtim: TIME_2

Step 9: Display the committed changes made by user HR on segment EMP30

SQL> select timestamp, username, operation, sql_redo, sql_undo
from v$logmnr_contents
where username=’HR’ and (seg_name=’EMP30′ or seg_name is null);

Step 10: End the Logminer session and turn off supplemental logging at the database level

SQL> execute dbms_logmnr.end_logmnr;
SQL> alter database drop supplemental log data

Notes:

If supplemental logging is not enabled at Step 3, there would be an ORACLE error info at Step 9:

ORA-01347: Supplemental log data no longer found

References:

1. [Chinese] http://www.oracle.com.cn/redirect.php?tid=2491&goto=lastpost
2. [Chinese] http://bbs.chinaunix.net/viewthread.php?tid=477403
3. [Chinese] http://blog.csdn.net/linhong34/archive/2007/08/23/1756776.aspx

RMAN: restore the spfile

$sqlplus /nolog;
SQL>connect / as sysdba;
SQL>startup nomount;
SQL>exit;

$rman target / nocatalog
RMAN>set dbid=147749264
RMAN>run
{
allocate channel d1 type disk;
set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘$ORACLE_BASE/oradata/rman_backup/control%F’;
Restore spfile from autobackup;
Release channel d1;
}

Here is the error info: RMAN-06564: rman must use the t0 clause when the instance is started with spfile

This error info implies that you were using default SPFILE ($ORACLE_HOME/dbs/spfile<SID>.ora) to startup the database. Database STARTUP NOMOUNT will use the default PFILE or SPFILE.

Problem solving:

Method 1: remove spfile, startup nomount using pfile;

Method 2: from documentation,

If restoring to a nondefault location, then you could run commands as in the following example:
RESTORE SPFILE TO ‘/tmp/spfileTEMP.ora’; # if you are using a catalog
RESTORE SPFILE TO ‘/tmp/spfileTEMP.ora’ FROM AUTOBACKUP; # if in NOCATALOG mode

References:

1. http://www.orafaq.com/forum/t/56572/0/
2. [Chinese] http://space.itpub.net/7199859/viewspace-62315
3. http://forums.oracle.com/forums/thread.jspa?threadID=661030&tstart=45