Surachart Opun's Blog

CLSR-0002 ORA-01003 in IMON logs

2010-09-10T00:46:00.003+07:00

Failed to start the service with "srvctl" command-line.

$ srvctl start service -d db -i db1 -s servicedb
PRKP-1030 : Failed to start the service servicedb.
CRS-0215: Could not start resource 'ora.db.servicedb.db1.srv'.

So, check imon* file.

$ tail -f imon_db.log

2010-09-10 00:16:51.561: [ RACG][1252133216] [29774][1252133216][ora.db.servicedb.db1.srv]: CLSR-0002: Oracle error encountered while executing clsrcsngtsn: open cursor
2010-09-10 00:16:51.561: [ RACG][1252133216] [29774][1252133216][ora.db.servicedb.db1.srv]: ORA-01003: no statement parsed
2010-09-10 00:16:51.566: [ RACG][1252133216] [29774][1252133216][ora.db.servicedb.db1.srv]: CLSR-0002: Oracle error encountered while executing clsrcsngtsn: open cursor
2010-09-10 00:16:51.566: [ RACG][1252133216] [29774][1252133216][ora.db.servicedb.db1.srv]: ORA-01003: no statement parsed
2010-09-10 00:16:52.676: [ RACG][1252133216] [29774][1252133216][ora.db.servicedb.db1.srv]: CLSR-0002: Oracle error encountered while executing clsrcsngtsn: open cursor
2010-09-10 00:16:52.676: [ RACG][1252133216] [29774][1252133216][ora.db.servicedb.db1.srv]: ORA-01003: no statement parsed
2010-09-10 00:19:31.233: [ RACG][1252133216] [29774][1252133216][ora.db.db1.inst]: CLSR-0002: Oracle error encountered while executing clsrcsngtsn: open cursor
2010-09-10 00:19:31.233: [ RACG][1252133216] [29774][1252133216][ora.db.db1.inst]: ORA-01003: no statement parsed

then checked metalink ID 818997.1 : ORA-1003 is caused by unpublished Bug 5556555(fixed 11.1.0.6 and will be fixed in 10.2.0.5) . Idea: • either kill racgimon using kill -9 • restart the rac instance. So, I choose to kill:

[ RACG][1252133216] [29774][1252133216][ora.db.servicedb.db1.srv]: ORA-01003: no statement parsed

$ ps -aef | grep 29774
oracle 29774 1 0 2009 ? 01:56:57 /oracle/10gR2/bin/racgimon startd db

$ kill -9 29774

and then started service again.

$ srvctl start service -d db -i db1 -s servicedb

$ tail -f imon_db.log
2010-09-10 00:22:22.214: [ RACG][2540875008] [2334][2540875008][ora.db.servicedb.db1.srv]: racgimon started

Everything fine. I can start service.

Compare & Synchronize Toad®for Data Analysts

2010-09-05T21:54:00.010+07:00

Toad® for Data Analysts is a great tool from Quest Software. It's helpful to query and report on data; check features and benefits and download trial.
Toad for Data Analysts, we can:
- Gather data from any source: with a single tool that natively supports Oracle, SQL Server, DB2, Sybase, MySQL, Microsoft Access, and Microsoft Excel, and all other platforms, including Teradata and Netezza, through ODBC
- Investigate data: with built-in database browsing, ER diagram, and compare and sync functionality
- Deliver data faster: through one-click data export to Excel, easy report creation and automated distribution
- Improve productivity: by simplifying, automating and scheduling repetitive tasks
then test to compare and synchronize data on Oracle:
use Wizard to help.
then choose Source/Target
then choose objects to compare
after compare then synchronize by using Wizard.
we can sync data source -> target or target -> source.
then generate script -> open script file, run or no action.
we can compare and synchronize different databases.

Convert Ext3 to Btrfs

2010-09-01T17:05:00.008+07:00

After I make kernel 2.6.35.4 on Oracle Enterprise Linux 5.4 support Btrfs.

# cd /usr/src
# tar jxvf linux-2.6.35.4.tar.bz2
# cd linux-2.6.35.4
# cp /boot/config-2.6.18-164.el5 .config
# make && make modules_install headers_install install
# mkinitrd /boot/initrd-2.6.35.4 2.6.35.4
# reboot

then installed btrfs-progs-0.19 program.

# cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5.4 (Carthage)

# rpm -q enterprise-release
enterprise-release-5-0.0.20

# uname -a
Linux oel 2.6.35.4 #1 SMP Wed Sep 1 20:37:04 ICT 2010 x86_64 x86_64 x86_64 GNU/Linux

and then tested to convert ext3 to btrfs (read):

# mount | grep /dev/sda6
/dev/sda6 on /data type ext3 (rw)

# ls /data
linux-2.6.35.4.tar.bz2

# umount /data

# fsck.ext3 /dev/sda6
e2fsck 1.39 (29-May-2006)
/data: ...

# btrfs-convert /dev/sda6
creating btrfs metadata.
creating ext2fs image file.
cleaning up system chunk.
conversion complete.

# mount -t btrfs /dev/sda6 /data

# mount | grep /dev/sda6
/dev/sda6 on /data type btrfs (rw)

# ls /data
ext2_saved linux-2.6.35.4.tar.bz2

we will see ext3/4 snapshot (ext2_saved). we can mount loopback for image in snapshot:

# mount -t ext3 -o loop,ro /data/ext2_saved/image /mnt

# ls /mnt
linux-2.6.35.4.tar.bz2

check some command-line:

# btrfs-show
Label: /data uuid: 7721003c-adcb-4706-8238-68946a5e2547
Total devices 1 FS bytes used 8.73GB
devid 1 size 128.79GB used 128.79GB path /dev/sda6

Btrfs Btrfs v0.19

However, if we need to roll back the conversion(we should backup). we can:

# umount /mnt
# umount /data
# btrfs-convert -r /dev/sda6

dbForge Data Compare for Oracle

2010-08-28T19:14:00.005+07:00

I had a chance to test about dbForge Data Compare for Oracle from Devart (@DevartSoftware). This is a good software for Oracle data comparison and synchronization and free.
Key Features
- Wide support of Oracle versions: 11g, 10g, XE, 9i, 8i, 7.3
- High-speed comparison
- Comparison of all schema tables at once
- Comparing data with different structures
- Custom mapping of tables
- Full or partial Oracle data synchronization
- Generating SQL*Plus-compatible synchronization script
- Saving data comparison settings as comparison projects for the next usage
- Start page with the latest comparisons
- Convenient UI

We can watch "dbForge Data Compare for Oracle Tutorial" for this tool.

Compiled new oci8(1.4.3) in PHP(5.3.3)

2010-08-24T16:28:00.007+07:00

How to compile new oci8 in PHP source?

oci8(PHP) is extension for Oracle Database allows you to access Oracle databases. It can be built with PHP 4.3.9 to 5.x. It can be linked with Oracle 9.2, 10.2, 11.1, or 11.2 client libraries.

When we find new version (oci8) and want to upgrade in PHP, How?
If we want to use oci8 1.4.3. we may wait PHP version 6.0.0 or older (but OCI8 1.4.2 is included in PHP 5.3.3).
So, download oci8 1.4.3 and compile in php 5.3.3:
- check PHP source path.

# pwd
/SRC/php-5.3.3

- check old configuration.

# cat config.nice

#! /bin/sh
#
# Created by configure

'./configure' \
'--with-apxs2=/usr/local/apache/bin/apxs' \
'--with-oci8=instantclient,/u01/app/instantclient_11_2' \
"$@"

- move old oci8 and use new oci8 (1.4.3)

# mv ext/oci8 ext/oci8-org
# ls ../oci8-1.4.3.tgz
../oci8-1.4.3.tgz
# tar zxvf ../oci8-1.4.3.tgz -C ext/
# mv ext/oci8-1.4.3 ext/oci8
# make clean && make
# /usr/local/apache/bin/apachectl stop
# make install
# /usr/local/apache/bin/apachectl start

- check again.
we will see new version(oci8).

How to change Segment Space Management of a tablespace from MANUAL to AUTO?

2010-08-15T18:46:00.004+07:00

That's a good question? If we find out solution. we'll find:

- Backup the tablespace
- Export the tablespace data
- Drop and re-allocate the tablespace
- Import the tablespace

Old Tablespace:

SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name='TBS_TEST';

TABLESPACE_NAME SEGMEN
------------------------------ ------
TBS_TEST MANUAL

SQL> select * from dba_ts_quotas where tablespace_name='TBS_TEST';

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
TBS_TEST SURACHART 9437184 -1 1152 -1 NO

SQL> select owner, table_name, tablespace_name, num_rows from all_tables where tablespace_name='TBS_TEST';

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
SURACHART TB_TEST TBS_TEST 73189

Solution:
- Backup the tablespace

RMAN> backup tablespace TBS_TEST;

Starting backup at 15-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/oracle/oradata/ORCL/datafile/o1_mf_tbs_test_66hmvb4p_.dbf
channel ORA_DISK_1: starting piece 1 at 15-AUG-10
channel ORA_DISK_1: finished piece 1 at 15-AUG-10
piece handle=/oracle/oradata/flashback_area/ORCL/backupset/2010_08_15/o1_mf_nnndf_TAG20100815T183444_66hn6nld_.bkp tag=TAG20100815T183444 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-AUG-10

- Export the tablespace data

$ expdp TABLESPACES=TBS_TEST DIRECTORY=DATA_PUMP_DIR DUMPFILE=tbs_test.dmp LOGFILE=tbs_test.log
Export: Release 11.2.0.1.0 - Production on Sun Aug 15 18:39:42 2010

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01": /******** AS SYSDBA TABLESPACES=TBS_TEST DIRECTORY=DATA_PUMP_DIR DUMPFILE=tbs_test.dmp LOGFILE=tbs_test.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SURACHART"."TB_TEST" 7.074 MB 73189 rows
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
/oracle/admin/orcl/dpdump/tbs_test.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at 18:40:12

- Drop and re-allocate the tablespace

SQL> drop tablespace tbs_test including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name='TBS_TEST';

no rows selected

SQL> create tablespace tbs_test datafile size 10M autoextend on next 1M segment space management auto;

Tablespace created.

SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name='TBS_TEST';

TABLESPACE_NAME SEGMEN
------------------------------ ------
TBS_TEST AUTO

SQL> select owner, table_name, tablespace_name, num_rows from all_tables where tablespace_name='TBS_TEST';

no rows selected

- Import the tablespace

$ impdp TABLESPACES=TBS_TEST DIRECTORY=DATA_PUMP_DIR DUMPFILE=tbs_test.dmp LOGFILE=tbs_test.log

Import: Release 11.2.0.1.0 - Production on Sun Aug 15 18:43:14 2010

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLESPACE_01": /******** AS SYSDBA TABLESPACES=TBS_TEST DIRECTORY=DATA_PUMP_DIR DUMPFILE=tbs_test.dmp LOGFILE=tbs_test.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SURACHART"."TB_TEST" 7.074 MB 73189 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLESPACE_01" successfully completed at 18:43:23

Check:

SQL> select tablespace_name, segment_space_management from dba_tablespaces where tablespace_name='TBS_TEST';

TABLESPACE_NAME SEGMEN
------------------------------ ------
TBS_TEST AUTO

SQL> select * from dba_ts_quotas where tablespace_name='TBS_TEST';

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
TBS_TEST SURACHART 9437184 -1 1152 -1 NO

SQL> select owner, table_name, tablespace_name, num_rows from all_tables where tablespace_name='TBS_TEST';

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
SURACHART TB_TEST TBS_TEST 73189

This is just an example. However... change from UNIFORM SIZE to AUTOALLOCATE, we can use this solution.

ORA-00600: internal error code, arguments: [kck_rls_check must use (11,0,0,0,0) or lower], [kdt.c], [9486], [11.2.0.1.0]

2010-08-16T00:48:00.002+07:00

I tested some about spfile and then found.
ORA-00600: internal error code, arguments: [kck_rls_check must use (11,0,0,0,0) or lower]
found out Oracle Support [ID 1064264.1] and they told it relates to an internal check on the compression compatibility(default -> 11.2.0.1). then test:

SQL> select value from v$parameter where name='_compression_compatibility';

no rows selected

SQL> create pfile='/tmp/pfile' from memory;

File created.

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

SQL> startup pfile='/tmp/pfile';
ORA-00600: internal error code, arguments: [kck_rls_check must use (11,0,0,0,0) or lower], [kdt.c], [9486], [11.2.0.1.0], [], [], [], [], [], [], [], []

checked and solved (_compression_compatibility='11.2.0.1' -> _compression_compatibility='11.2.0')

$ grep compression_compatibility /tmp/pfile
_compression_compatibility='11.2.0.1'

$ grep compression_compatibility /tmp/pfile
_compression_compatibility='11.2.0'

SQL> startup pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 790941696 bytes
Fixed Size 1339428 bytes
Variable Size 222302172 bytes
Database Buffers 557842432 bytes
Redo Buffers 9457664 bytes
Database mounted.
Database opened.

SQL> select value from v$parameter where name='_compression_compatibility';

VALUE
------------------------------
11.2.0

Actually we can remove '_compression_compatibility' from parameter file and start database.

How to find maximum number of third?

2010-08-11T17:18:00.002+07:00

I hope to hear your idea from this question. I learned to find maximum number of third by using ROWNUM.

I learned to use ROW_NUMBER function as well.

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

Create Table:

SQL> create table test as select * from all_objects;

Table created.

1. Use ROWNUM

SQL> select object_name ,object_id from test;
.
.
.
V_1 56532
V_2 56533
V_3 56534
TEST 56535

SQL> select * from (select object_name, object_id from test order by object_id desc) where rownum <=3 ;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
TEST 56535
V_3 56534
V_2 56533

SQL> select * from (select object_name, object_id from test order by object_id desc) where rownum <=3 order by object_id;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
V_2 56533
V_3 56534
TEST 56535

SQL> select * from (select * from (select object_name, object_id from test order by object_id desc) where rownum <=3 order by object_id) where rownum <=1;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
V_2 56533

- Check Execution Plan
----------------------------------------------------------
Plan hash value: 627665718
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | | 852 (2)| 00:00:11 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 3 | 90 | | 852 (2)| 00:00:11 |
|* 3 | SORT ORDER BY STOPKEY | | 3 | 90 | 1648K| 852 (2)| 00:00:11 |
|* 4 | COUNT STOPKEY | | | | | | |
| 5 | VIEW | | 41621 | 1219K| | 507 (2)| 00:00:07 |
|* 6 | SORT ORDER BY STOPKEY| | 41621 | 1219K| 1648K| 507 (2)| 00:00:07 |
| 7 | TABLE ACCESS FULL | TEST | 41621 | 1219K| | 163 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------

2. use ROW_NUMBER function

SQL> select object_name, object_id from (select object_id, object_name, row_number () over (order by object_id desc) id from test) x where id=3;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
V_2 56533

- Check Execution Plan
----------------------------------------------------------
Plan hash value: 1795822849
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41621 | 1747K| | 507 (2)| 00:00:07 |
|* 1 | VIEW | | 41621 | 1747K| | 507 (2)| 00:00:07 |
|* 2 | WINDOW SORT PUSHED RANK| | 41621 | 1219K| 1648K| 507 (2)| 00:00:07 |
| 3 | TABLE ACCESS FULL | TEST | 41621 | 1219K| | 163 (2)| 00:00:02 |
-----------------------------------------------------------------------------------------

These show the same result, but spent the different Cost.

ORA-15041 - ASM Disk Group Unbalanced

2010-03-08T23:07:00.008+07:00

Use ASM Disk Group NORMAL redundancy (11gR2) and found error while create tablespace.

SQL> create tablespace test datafile '+DATA' size 5G;
create tablespace test datafile '+DATA' size 5G
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted

What Wrong??? - I have free size on "DATA", Check ASM Disk Group.

SQL> select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name='DATA';

NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB TYPE
------------------------------ ---------- ---------- ----------------------- -------------- ------
DATA 257807 112233 23437 44398 NORMAL

Check (ASM) alert log file.

kfdpDumpBg()
kfdpDumpBg() - Done

Check on ASM Instance and Investigate the problem.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1017 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23211 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14819 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14829 23437 NORMAL
/dev/sdj 1 23437 NORMAL
/dev/sdd 13839 23437 NORMAL

Disk Group UNBALANCE, then

SQL> alter diskgroup data rebalance power 11;

Diskgroup altered.

Check.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1018 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23209 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14820 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14830 23437 NORMAL
/dev/sdj 0 23437 NORMAL
/dev/sdd 13839 23437 NORMAL

Nothing to resolve... Check on v$operation.

SQL> select group_number, operation, state, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL ERRS ORA-15041

Try to check & repair ASM Disk Group.

SQL> alter diskgroup data check all repair;

Diskgroup altered.

SQL> select group_number, operation, state, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL ERRS ORA-15041

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1018 23437 NORMAL
/dev/sdb 7867 23437 NORMAL
/dev/sdk 7988 23437 NORMAL
/dev/sdq 23209 23437 NORMAL
/dev/sdf 7864 23437 NORMAL
/dev/sdg 14820 23437 NORMAL
/dev/sdc 13827 23437 NORMAL
/dev/sdi 6971 23437 NORMAL
/dev/sde 14830 23437 NORMAL
/dev/sdj 0 23437 NORMAL
/dev/sdd 13839 23437 NORMAL

Nothing to resolve... - "DATA" ASM Disk Group still unbalance, and v$asm_operation show error.
think & make Idea: Move some files or resize some files on "DATA" Disk Group, so login database and make something.
Example:

SQL> alter database tempfile '+DATA/orcl/tempfile/temp.263.712603677' resize 1G;

Database altered.

Check on ASM again.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 1685 23437 NORMAL
/dev/sdb 8322 23437 NORMAL
/dev/sdk 8357 23437 NORMAL
/dev/sdq 22855 23437 NORMAL
/dev/sdf 8324 23437 NORMAL
/dev/sdg 15013 23437 NORMAL
/dev/sdc 14043 23437 NORMAL
/dev/sdi 7386 23437 NORMAL
/dev/sde 15011 23437 NORMAL
/dev/sdj 711 23437 NORMAL
/dev/sdd 14054 23437 NORMAL

Check v$asm_operation.

SQL> select group_number, operation, state, power, error_code from v$asm_operation;

GROUP_NUMBER OPERA STAT ERROR_CODE
------------ ----- ---- --------------------------------------------
1 REBAL RUN

ASM instance... "DATA" Disk Group rebalancing, So Wait... and check

SQL> select group_number, operation, state, power, error_code from v$asm_operation;

no rows selected

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 10524 23437 NORMAL
/dev/sdb 10524 23437 NORMAL
/dev/sdk 10528 23437 NORMAL
/dev/sdq 10532 23437 NORMAL
/dev/sdf 10521 23437 NORMAL
/dev/sdg 10523 23437 NORMAL
/dev/sdc 10522 23437 NORMAL
/dev/sdi 10526 23437 NORMAL
/dev/sde 10522 23437 NORMAL
/dev/sdj 10522 23437 NORMAL
/dev/sdd 10523 23437 NORMAL

SQL> alter diskgroup data rebalance power 1;

Diskgroup altered

Disks rebalanced, Try to create tablespace again.

SQL> select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name='DATA';

NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB TYPE
------------------------------ ---------- ---------- ----------------------- -------------- ------
DATA 257807 115767 12916 51425 NORMAL

SQL> create tablespace test datafile '+DATA' size 30G;

Tablespace created.

SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');

PATH FREE_MB TOTAL_MB STATE
------------------- ---------- ---------- --------
/dev/sdl 4936 23437 NORMAL
/dev/sdb 4931 23437 NORMAL
/dev/sdk 4931 23437 NORMAL
/dev/sdq 4932 23437 NORMAL
/dev/sdf 4922 23437 NORMAL
/dev/sdg 4933 23437 NORMAL
/dev/sdc 4936 23437 NORMAL
/dev/sdi 4940 23437 NORMAL
/dev/sde 4933 23437 NORMAL
/dev/sdj 4928 23437 NORMAL
/dev/sdd 4938 23437 NORMAL

What this told me? Make Sure ASM Disk Group no problem (REBALANCE).

Objects Invalid when Enable Row Movement

2010-04-02T14:44:00.005+07:00

On Oracle 10g, The command "ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT" make the dependent objects be INVALID status. So, we need to recompile the dependent objects or wait for compilation automatic when objects are called.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
PL/SQL Release 10.2.0.4.0
CORE 10.2.0.4.0
TNS for Linux: Version 10.2.0.4.0
NLSRTL Version 10.2.0.4.0

SQL> select object_name, object_type, status from dba_objects where object_name like '%TB01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
V_TB01 VIEW VALID
TB01 TABLE VALID
PRC_TB01 PROCEDURE VALID

SQL> alter table tb01 enable row movement;

Table altered.

SQL> select object_name, object_type, status from dba_objects where object_name like '%TB01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
V_TB01 VIEW INVALID
TB01 TABLE VALID
PRC_TB01 PROCEDURE INVALID

SQL> select * from v_tb01;

SQL> exec PRC_TB01;

SQL> select object_name, object_type, status from dba_objects where object_name like '%TB01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
V_TB01 VIEW VALID
TB01 TABLE VALID
PRC_TB01 PROCEDURE VALID

This is the issue, when we need to use "enable row movement" command, But on Oracle 11g has changed.

SQL> select * from v$version;

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

SQL> select object_name, object_type, status from dba_objects where object_name like '%TB01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
V_TB01 VIEW VALID
TB01 TABLE VALID
PRC_TB01 PROCEDURE VALID

SQL> alter table tb01 enable row movement;

Table altered.

SQL> select object_name, object_type, status from dba_objects where object_name like '%TB01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
V_TB01 VIEW VALID
TB01 TABLE VALID
PRC_TB01 PROCEDURE VALID

FIXING BLOCK CORRUPTION (RMAN 11G)

2010-04-14T02:21:00.006+07:00

How do you fix block corruption? I just tested to fix block corruption on 11g. FIXING BLOCK CORRUPTION (RMAN 11G)

Learn to install SQL*Plus(Instant Client) or Learn More...

2010-04-15T23:59:00.008+07:00

Installation PHP + OCI8, we have to use Instant Client Package(Basic) and Instant Client Package(SDK). but If we need to use SQL*Plus to test something by connection to Oracle Database. We have to use Instant Client Package(Basic) and Instant Client Package(SQL*Plus).

$ unzip oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64.zip
Archive: oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64.zip
inflating: instantclient_11_2/BASIC_README
inflating: instantclient_11_2/adrci
inflating: instantclient_11_2/genezi
inflating: instantclient_11_2/libclntsh.so.11.1
inflating: instantclient_11_2/libnnz11.so
inflating: instantclient_11_2/libocci.so.11.1
inflating: instantclient_11_2/libociei.so
inflating: instantclient_11_2/libocijdbc11.so
inflating: instantclient_11_2/ojdbc5.jar
inflating: instantclient_11_2/ojdbc6.jar
inflating: instantclient_11_2/xstreams.jar

$ unzip oracle-instantclient11.2-sqlplus-11.2.0.1.0-1.x86_64.zip
Archive: oracle-instantclient11.2-sqlplus-11.2.0.1.0-1.x86_64.zip
inflating: instantclient_11_2/SQLPLUS_README
inflating: instantclient_11_2/glogin.sql
inflating: instantclient_11_2/libsqlplus.so
inflating: instantclient_11_2/libsqlplusic.so
inflating: instantclient_11_2/sqlplus

$ cd instantclient_11_2
$ ./sqlplus
./sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

We learned to install SQL*Plus(Instant Client) and we were learning to make it work, then checked "libsqlplus.so" shared library file.

$ ls -l libsqlplus.so
-r-xr-xr-x 1 oracle oinstall 1470768 Aug 15 2009 libsqlplus.so

then used "ldd" to help.
ldd prints the shared libraries required by each program or shared library specified on the command line.

$ ldd sqlplus
libsqlplus.so => not found
libclntsh.so.11.1 => not found
libnnz11.so => not found
libdl.so.2 => /lib64/libdl.so.2 (0x0000003fc1f00000)
libm.so.6 => /lib64/tls/libm.so.6 (0x0000003fc1d00000)
libpthread.so.0 => /lib64/tls/libpthread.so.0 (0x0000003fc2100000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x0000003fc9400000)
libc.so.6 => /lib64/tls/libc.so.6 (0x0000003fc1a00000)
/lib64/ld-linux-x86-64.so.2 (0x0000003fc1600000)

"sqlplus" command could not load shared libraries. checked all shared libraries = "not found"

$ ls -l libsqlplus.so libclntsh.so.11.1 libnnz11.so
-rwxrwxr-x 1 oracle oinstall 48797739 Aug 15 2009 libclntsh.so.11.1
-r-xr-xr-x 1 oracle oinstall 7899997 Aug 15 2009 libnnz11.so
-r-xr-xr-x 1 oracle oinstall 1470768 Aug 15 2009 libsqlplus.so

$ pwd
/opt/instantclient_11_2

then used LD_LIBRARY_PATH environment variable.
The environment variable LD_LIBRARY_PATH is a colon-separated set of directories where libraries should be searched for first, before the standard set of directories.

$ export LD_LIBRARY_PATH=/opt/instantclient_11_2:$LD_LIBRARY_PATH

$ ldd sqlplus
libsqlplus.so => /opt/instantclient_11_2/libsqlplus.so (0x0000002a95557000)
libclntsh.so.11.1 => /opt/instantclient_11_2/libclntsh.so.11.1 (0x0000002a9573f000)
libnnz11.so => /opt/instantclient_11_2/libnnz11.so (0x0000002a97c6f000)
libdl.so.2 => /lib64/libdl.so.2 (0x0000003fc1f00000)
libm.so.6 => /lib64/tls/libm.so.6 (0x0000003fc1d00000)
libpthread.so.0 => /lib64/tls/libpthread.so.0 (0x0000003fc2100000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x0000003fc9400000)
libc.so.6 => /lib64/tls/libc.so.6 (0x0000003fc1a00000)
libaio.so.1 => /usr/lib64/libaio.so.1 (0x0000003fc1800000)
/lib64/ld-linux-x86-64.so.2 (0x0000003fc1600000)

"sqlplus" command could load all shares libraries.

$ ./sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 15 20:58:18 2010

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

Enter user-name:

We can use SQL*Plus(Instant Client), we learned to make it work (learned to fix and use shell command). so We learned more...

However, If we need to make it work faster... and don't necessary to learn more... (just need it work). After unzip file:

$ pwd
/opt/instantclient_11_2
$ export TNS_ADMIN=/opt/instantclient_11_2
$ export LD_LIBRARY_PATH=/opt/instantclient_11_2:$LD_LIBRARY_PATH
$ export PATH=$PATH:/opt/instantclient_11_2
$ sqlplus user/pwd@DB

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 15 21:50:53 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>

TNS_ADMIN environment is the directory containing the tnsnames.ora file.

Reference(To Learn More):
Oracle Database Instant Client
SQL*Plus Instant Client
Linux Shared Libraries
Filesystem Hierarchy Standard

/lib : Essential shared libraries and kernel modules
Purpose:
The /lib directory contains those shared library images needed to boot the system and run the commands in the root filesystem, ie. by binaries in /bin and /sbin.

/usr/lib : Libraries for programming and packages
Purpose:
/usr/lib includes object files, libraries, and internal binaries that are not intended to be executed directly by users or shell scripts.
Applications may use a single subdirectory under /usr/lib. If an application uses a subdirectory, all architecture-dependent data exclusively used by the application must be placed within that subdirectory.

ORDER BY to use Index

2010-05-10T00:03:00.006+07:00

It's difficult thing, If we try to learn something every days, every days. However It's a good thing, If we love and enjoy to learn.

So I planed to learn something (new) every days. I hope so.
I learned... (It's not new... just something we might forgot).

If a user requires the optimizer to consider using an index to drive an ORDER BY statement instead of sorting, then the indexed columns must be NOT NULL as otherwise they are not considered.

SQL> create table a (x VARCHAR2(128), y VARCHAR2(19));

Table created.

SQL> desc A
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
X VARCHAR2(128)
Y VARCHAR2(19)

SQL> insert into a select OBJECT_NAME, OBJECT_TYPE from user_objects;

43 rows created.

SQL> commit;

Commit complete.

SQL> create index a_indx1 on a (x,y);

Index created.

SQL> exec dbms_stats.gather_index_stats(USER,'A_INDX1');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from a order by x,y;

Explained.

SQL> select * from TABLE(dbms_xplan.display(NULL, NULL , 'ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3819873049
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43 | 3311 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 43 | 3311 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| A | 43 | 3311 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Indexes Columns be NULL, then change to be Not NULL.

SQL> alter table a modify (x not null, y not null);

Table altered.

SQL> desc A
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
X NOT NULL VARCHAR2(128)
Y NOT NULL VARCHAR2(19)

SQL> explain plan for select * from a order by x,y;

Explained.

SQL> select * from TABLE(dbms_xplan.display(NULL, NULL , 'ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2241847120
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43 | 3311 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | A_INDX1 | 43 | 3311 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

After changed to be Not NULL, the optimizer consider an index.

Flashback Transaction Backout

2010-05-13T01:11:00.007+07:00

The dbms_flashback.transaction_backout procedure uses logminer data to backout transactions, so your data in that transaction was rolled back.
Requirements:
1. Database must be in Archive Mode

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 48
Next log sequence to archive 50
Current log sequence 50

2. Enable supplemental logging at Database level

SQL> alter database add supplemental log data;

Database altered.

Example:

SQL> delete from tb_test where object_id=11112;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tb_test where object_id=11112;

COUNT(*)
----------
0

SQL> select versions_xid , object_id from tb_test versions between scn minvalue and maxvalue where object_id=11112;

VERSIONS_XID OBJECT_ID
---------------- ----------
08001600CC040000 11112

SQL> declare
v_xid sys.xid_array;
begin
v_xid := sys.xid_array('08001600CC040000');
dbms_flashback.transaction_backout(numtxns=>1,
xids=>v_xid, options=>dbms_flashback.cascade);
end;
/
declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5

Problem:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

Solve:
grant create any table to user_name;

SQL> declare
v_xid sys.xid_array;
begin
v_xid := sys.xid_array('08001600CC040000');
dbms_flashback.transaction_backout(numtxns=>1,
xids=>v_xid, options=>dbms_flashback.cascade);
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from tb_test where object_id=11112;

COUNT(*)
----------
1

Now... data was rolled back. We can review on...
- [DBA, USER]_FLASHBACK_TXN_STATE
- [DBA, USER]_FLASHBACK_TXN_REPORT

SQL> SELECT * FROM USER_FLASHBACK_TXN_STATE;

COMPENSATING_XID XID DEPENDENT_XID BACKOUT_MODE
---------------- ---------------- ---------------- ----------------
09000B00D6040000 08001600CC040000 CASCADE

SQL> SELECT * FROM USER_FLASHBACK_TXN_REPORT;

Granule size

2010-05-18T02:37:00.011+07:00

SGA memory is allocated in unit of contiguous memory chunks called granule. The SGA memory components(shared pool,buffer cache, redo log buffer,java pool,streams pool,large pool) are sized as multiples of granules.

The granule size is determined based on the amount of memory requested at the instance startup. It is based on the SGA_MAX_SIZE. If MEMORY_MAX_TARGET is specified, then SGA_MAX_SIZE defaults to MEMORY_MAX_TARGET for the purpose of sizing the granule. Once set, the granule size does not change for the life of the instance.
However granule maximum size = 16MB on 32-bit platforms.

SQL> show parameter memory_max_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 800M

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
4

RDBMS	SGA_MAX_SIZE (or memory_max_target)	GRANULE SIZE
9.2	<= 128MB	4MB
	> 128MB	16MB
10.2	<= 1GB	4MB
	> 1GB	16MB
11gR1	<= 1GB	4MB
	1Gb - 4GB	16MB
	4Gb - 16GB	64MB
	16Gb - 64GB	256MB
	> 64GB	512MB
11gR2 (and 11gR1 with patch 8813366 applied *)	< 1Gb	4Mb
	1Gb - 8Gb	16Mb
	8Gb - 16Gb	32Mb
	16Gb - 32Gb	64Mb
	32Gb - 64Gb	128Mb
	64Gb - 128Gb	256Mb
	> 128Gb	512Mb

How to test from above table. If I need granule size 16Mb, I have to Memory 1Gb - 4GB for SGA???

SQL> select * from v$version;

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

SQL> !free -m
total used free shared buffers cached
Mem: 888 849 38 0 2 740

I have only 900Mb for Physical Memory. Find Idea to test then created /tmp/pfile file for modified MEMORY_MAX_TARGET parameter.

SQL> create pfile='/tmp/pfile' from spfile;

File created.

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

then faked /dev/shm size (this's just test for granule size to fake memory_max_target parameter)

Filesystem Size Used Avail Use% Mounted on
tmpfs 4.0G 0 4.0G 0% /dev/shm

then modified /tmp/pfile (MEMORY_MAX_TARGET=4G) - If MEMORY_MAX_TARGET is specified, then SGA_MAX_SIZE defaults to MEMORY_MAX_TARGET.

SQL> startup pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 2058981376 bytes
Fixed Size 1300968 bytes
Variable Size 1644168728 bytes
Database Buffers 402653184 bytes
Redo Buffers 10858496 bytes
Database mounted.
Database opened.

SQL> show parameter memory_max_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 4G

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
16

Used granule size 16M.
If we set a value in the spfile that is not a multiple of the granule size, the actual size allocated will be rounded up to the nearest granule.

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
4

SQL> alter system set java_pool_size=4M;

System altered.

SQL> show parameter java_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 4M

SQL> alter system set MEMORY_MAX_TARGET=4G scope=spfile;

System altered.

SQL> shutdown

SQL> startup

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
16

SQL> show parameter java_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 16M

The java_pool_size 4M in the spfile, after increased granule size, then the actual allocation for the java_pool_size will be rounded up to 16M.

Reset password Weblogic Console

2010-06-01T19:46:00.009+07:00

Forgot password to login WebLogic Server Administrator Console, After installed Oracle EM 11g Grid.
* Authentication Denied

some steps to Fun ...

$ export DOMAIN_HOME=/u01/app/Oracle/gc_inst/user_projects/domains/GCDomain
$ cd $DOMAIN_HOME/bin
$ . setDomainEnv.sh
$ cd security/
$ mv DefaultAuthenticatorInit.ldift DefaultAuthenticatorInit.ldift_old
$ java weblogic.security.utils.AdminAccount weblogic password .
$ ls -l DefaultAuthenticatorInit.ldift
-rw-r--r-- 1 oracle oinstall 3301 Jun 1 18:46 DefaultAuthenticatorInit.ldift

$ cd ../servers/EMGC_ADMINSERVER/
$ mv data data_old
$ cd security/
$ mv boot.properties boot.properties_old
$ cd $DOMAIN_HOME
$ ./startWebLogic.sh
.
.
.
***************************************************
* To start WebLogic Server, use a username and *
* password assigned to an admin-level user. For *
* server administration, use the WebLogic Server *
* console at http://hostname:port/console *
***************************************************
starting weblogic with Java version:
java version "1.6.0_20"
Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
Java HotSpot(TM) 64-Bit Server VM (build 16.3-b01, mixed mode)
Starting WLS with line:
.
.
.
Enter username to boot WebLogic server:weblogic
Enter password to boot WebLogic server:password
.
.
.
<Jun 1, 2010 7:12:21 PM ICT> <Notice> <Security> <BEA-090082> <Security initializing using security realm myrealm.>
<Jun 1, 2010 7:12:33 PM ICT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to STANDBY>
<Jun 1, 2010 7:12:33 PM ICT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to STARTING>
Ctrl-C

check boot.properties file

$ ls ./servers/EMGC_ADMINSERVER/security/boot.properties
ls: ./servers/EMGC_ADMINSERVER/security/boot.properties: No such file or directory

then create boot.properties file

$ cat ./servers/EMGC_ADMINSERVER/security/boot.properties
username=weblogic
password=password

and then start WebLogic again.

$ ./startWebLogic.sh &

Checking
- check boot.properties file.

$ cat ./servers/EMGC_ADMINSERVER/security/boot.properties
password={AES}4R+5Tu48BpEKFH9zbrTxvbG5EZywMIpecpNxsV1+YAk\=
username={AES}OvsVXimdPC6X8TrgyzzjC9YT2Z+9I2o3KNvih8vHyWw\

- login WebLogic Server Administrator Console with new password

DDL on Tables Enabled for Flashback Data Archive

2010-06-03T16:53:00.007+07:00

On 11gR2, Flashback Data Archive supports many DDL statements. A Flashback Data Archive is feature on 11g.

SQL> create table tb_recall (id number, name varchar2(50));

Table created.

SQL> alter table tb_recall flashback archive fla1;

Table altered.

SQL> insert into tb_recall values (1,'surachart');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table tb_recall add (address varchar2(100));

Table altered.

SQL> truncate table tb_recall;
truncate table tb_recall
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

SQL> alter table tb_recall rename to tb_recall_new;
alter table tb_recall rename to tb_recall_new
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

can't use "TRUNCATE", "RENAME" table on 11gR1, then on 11gR2:

SQL> create tablespace tbs_fla1 datafile size 1G;

Tablespace created.

SQL> create flashback archive fla1 tablespace tbs_fla1 retention 1 year;

Flashback archive created.

SQL> insert into tb_recall values ('1','surachart');

1 row created.

SQL> commit;

Commit complete.

SQL> !date
Thu Jun 3 15:45:00 ICT 2010

SQL> alter table tb_recall add (address varchar2(100));

Table altered.

SQL> truncate table tb_recall;

Table truncated.

SQL> alter table tb_recall rename to tb_recall_new;

Table altered.

SQL> select count(*) from tb_recall_new;

COUNT(*)
----------
0

SQL> select count(*) from tb_recall_new as of timestamp to_timestamp ('2010-06-03:15:45:00', 'yyyy-mm-dd:hh24:mi:ss');

COUNT(*)
----------
1

We can use truncate and rename table.

SQL> alter table tb_recall_new add (zip varchar2(10));

Table altered.

SQL> insert into tb_recall_new values(1,'surachart','999','10400');

1 row created.

SQL> commit;

Commit complete.

SQL> !date
Thu Jun 3 16:01:41 ICT 2010

SQL> alter table tb_recall_new drop column zip;

Table altered.

SQL> select * from tb_recall_new;

ID NAME ADDRESS
---------- ------------------------------ ---------------
1 surachart 999

didn't see column(ZIP), then checked at '2010-06-03:16:02:00'

SQL> select * from tb_recall_new as of timestamp to_timestamp ('2010-06-03:16:02:00', 'yyyy-mm-dd:hh24:mi:ss');

ID NAME ADDRESS ZIP
---------- ------------------------------ --------------- ----------
1 surachart 999 10400

How? if we re- add column (old name).

SQL> select * from tb_recall_new ;

ID NAME ADDRESS A
---------- ------------------------------ --------------- ----------
1 surachart 999

SQL> update tb_recall_new set A=100;

1 row updated.

SQL> commit;

Commit complete.

SQL> !date
Thu Jun 3 16:17:46 ICT 2010

SQL> alter table tb_recall_new drop column A;

Table altered.

SQL> select * from tb_recall_new;

ID NAME ADDRESS
---------- ------------------------------ ---------------
1 surachart 999

SQL> select * from tb_recall_new as of timestamp to_timestamp ('2010-06-03:16:17:46', 'yyyy-mm-dd:hh24:mi:ss');

ID NAME ADDRESS A ZIP
---------- ------------------------------ --------------- ---------- ----------
1 surachart 999 100

then added old column name.

SQL> alter table tb_recall_new add (a number);

Table altered.

SQL> select * from tb_recall_new;

ID NAME ADDRESS A
---------- ------------------------------ --------------- ----------
1 surachart 999

SQL> select * from tb_recall_new as of timestamp to_timestamp ('2010-06-03:16:17:46', 'yyyy-mm-dd:hh24:mi:ss');

ID NAME ADDRESS A ZIP
---------- ------------------------------ --------------- ---------- ----------
1 surachart 999

After dropped and re- added column (old name), that made us don't see old data... Any Idea?

Some DDL statements cause error ORA-55610:
- ALTER TABLE statement that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
- ALTER TABLE statement that moves or exchanges a partition or subpartition operation
- DROP TABLE statement

SQL> drop table tb_recall_new;
drop table tb_recall_new
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

A result cache + RESULT_CACHE_MAX_SIZE + ...

2010-06-16T21:55:00.010+07:00

A result cache is an area of memory, either in the SGA or client application memory.
Server Result Cache
Initialization Parameters (we should know):
- RESULT_CACHE_MAX_SIZE
This parameter sets the memory allocated to the server result cache.
A result cache is in the shared pool. The RESULT_CACHE_MAX_SIZE initialization parameter is parameter for the maximum size of the result cache component of the SGA, if RESULT_CACHE_MAX_SIZE is 0 upon instance startup, the result cache is diabled.

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 3136 K

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> alter system set result_cache_max_size=0;

System altered.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
BYPASS

A result cache status is "BYPASS".
If STOP/START instance. Then

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 0

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
DISABLED

A result cache status is "DISABLED".
If the result cache is disabled and use an ALTER SYSTEM statement to set RESULT_CACHE_MAX_SIZE to a nonzero value but do not restart the database, querying the value of the RESULT_CACHE_MAX_SIZE parameter returns a nonzero value even though the result cache is still disabled. The value of RESULT_CACHE_MAX_SIZE is therefore not the most reliable way to determine if the result cache is enabled.

SQL> alter system set result_cache_max_size=3M;

System altered.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
DISABLED

SQL> SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM hr.employees GROUP BY department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 77 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------

still... "DISABLED", so STOP/START instance again.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM hr.employees GROUP BY department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 77 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 3w0d0687vphfr3gvhgbcpfg89s | | | | |
| 2 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

A result cache status is "ENABLED", then check:

SQL> SELECT ID, TYPE, CREATION_TIMESTAMP, BLOCK_COUNT, COLUMN_COUNT, PIN_COUNT, ROW_COUNT FROM V$RESULT_CACHE_OBJECTS WHERE CACHE_ID = '3w0d0687vphfr3gvhgbcpfg89s';

ID TYPE CREATION_ BLOCK_COUNT COLUMN_COUNT PIN_COUNT ROW_COUNT
---------- ---------- --------- ----------- ------------ ---------- ----------
1 Result 16-JUN-10 1 2 0 12

That showed some situation of the result cache with RESULT_CACHE_MAX_SIZE initialization parameter.

- RESULT_CACHE_MAX_RESULT
This parameter sets the maximum amount of server result cache memory that can be used for for a single result. The default is 5%, but you can specify any percentage value between 1 and 100. You can set this parameter at the system or session level.

SQL> show parameter RESULT_CACHE_MAX_RESULT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_result integer 5

- RESULT_CACHE_REMOTE_EXPIRATION
This parameter specifies the expiration time for a result in the server result cache that depends on remote database objects. The default value is 0 minutes, which implies that results using remote objects should not be cached.

We can manage memory for the server result cache by using DBMS_RESULT_CACHE package.
Example: Check result cache report and flush

SQL> set serverout on
SQL> exec dbms_result_cache.memory_report;
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 3M bytes (3K blocks)
Maximum Result Size = 153K bytes (153 blocks)
[Memory]
Total Memory = 158360 bytes [0.033% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 147664 bytes [0.030% of the Shared Pool]
....... Overhead = 114896 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)

PL/SQL procedure successfully completed.

SQL> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

SQL> exec dbms_result_cache.memory_report;
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 3M bytes (3K blocks)
Maximum Result Size = 153K bytes (153 blocks)
[Memory]
Total Memory = 10696 bytes [0.002% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

Install APEX 4.0 with Embedded PL/SQL Gateway

2010-06-24T11:53:00.012+07:00

Oracle® Application Express Release 4.0 Now Available
Time to upgrade APEX on my database:

SQL> SELECT COMP_ID, COMP_NAME, VERSION, STATUS from DBA_REGISTRY WHERE COMP_ID='APEX';

COMP_ID COMP_NAME VERSION STATUS
------------------------------ ------------------------------ ------------------------------ ----------
APEX Oracle Application Express 3.2.1.00.10 VALID

- Download and start to APEX 4.0 (documents):
*** created "APEX" tablespace ***

$ unzip apex_4.0.zip
$ cd apex
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> @apexins APEX APEX TEMP /i/

- Change the Password for the ADMIN Account:

SQL> @apxchpwd
Enter a value below for the password for the Application Express ADMIN user.

Enter a password for the ADMIN user []

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.

Commit complete.

*** using Embedded PL/SQL Gateway ***
- Running the apex_epg_config.sql Configuration Script (unzipped source at /tmp ):

SQL> @apex_epg_config /tmp

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Directory created.

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

timing for: Load Images
Elapsed: 00:03:00.52

Session altered.

PL/SQL procedure successfully completed.

Commit complete.

Session altered.

Directory dropped.

- Unlock the ANONYMOUS account:

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

User altered.

- Update the Images Directory When Upgrading from a Previous Release (nzipped source at /tmp ):

SQL> @apxldimg.sql /tmp

PL/SQL procedure successfully completed.

Directory created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

timing for: Load Images
Elapsed: 00:02:09.70

Directory dropped.

- Enable Oracle XML DB HTTP Server:

SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

GETHTTPPORT
-----------
0

SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);

PL/SQL procedure successfully completed.

SQL> !lsnrctl status | grep HTTP
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxtest01)(PORT=8080))(Presentation=HTTP)(Session=RAW))

- Enable Network Services in Oracle Database 11g:
(Grant connect privileges to any host for the APEX_040000 database user)

DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040000
-- the "connect" privilege if APEX_040000 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, ensure that the ACL is valid
-- (for example, does not contain stale references to dropped users).
-- If it does, the following exception will be raised:
--
-- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
--
SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040000', TRUE, 'connect');
END IF;

EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Enable indexing the Oracle Application Express Online Help and could possibly enable email and PDF printing if those servers were also on the local host.

DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to 'localhost' and give APEX_040000
-- the "connect" privilege if APEX_040000 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, ensure that the ACL is valid
-- (for example, does not contain stale references to dropped users).
-- If it does, the following exception will be raised:
--
-- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
--

SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040000', TRUE, 'connect');
END IF;

EXCEPTION
-- When no ACL has been assigned to 'localhost'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/
COMMIT;

- Enable Indexing of Online Help in Oracle Database 11gR2 and Higher:

SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';

PAR_VALUE
----------------------------------------------------------------------------------------------------------------------------------
<Not Found>

SQL> CREATE ROLE APEX_URL_DATASTORE_ROLE;

Role created.

SQL> GRANT APEX_URL_DATASTORE_ROLE to APEX_040000;

Grant succeeded.

SQL> EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');

PL/SQL procedure successfully completed.

SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';

PAR_VALUE
----------------------------------------------------------------------------------------------------------------------------------
APEX_URL_DATASTORE_ROLE

- Task for Upgrade:

SQL> SELECT username
FROM dba_users
WHERE (username LIKE 'FLOWS_%' OR USERNAME LIKE 'APEX_%')
AND USERNAME NOT IN (
SELECT 'FLOWS_FILES'
FROM DUAL
UNION
SELECT 'APEX_PUBLIC_USER' FROM DUAL
UNION
SELECT SCHEMA s
FROM dba_registry
WHERE comp_id = 'APEX');

USERNAME
------------------------------
APEX_030200
FLOWS_030000

SQL> DROP USER FLOWS_030000 cascade;

User dropped.

SQL> DROP USER APEX_030200 cascade;

User dropped.

Fix Invalid ACL in Oracle Database 11g:

EXEC DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('power_users.xml', 'FLOWS_030000');

Time to test...

Check in DBA_REGISTRY:

SQL> SELECT COMP_ID, COMP_NAME, VERSION, STATUS from DBA_REGISTRY WHERE COMP_ID='APEX';

COMP_ID COMP_NAME VERSION STATUS
------------------------------ ------------------------------ ------------------------------ ----------
APEX Oracle Application Express 4.0.0.00.46 VALID

Add Disk with ORA-15054 After ORA-15075

2010-06-29T14:52:00.007+07:00

Tested add new disk to ASM Disk Group on 10gR2 RAC 4 nodes, I assumed I have only one node see ASM new Disk (DATA_NEW).

SQL> ALTER DISKGROUP DATA ADD DISK 'ORCL:DATA_NEW';
ALTER DISKGROUP DATA ADD DISK 'ORCL:DATA_NEW'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15075: disk(s) are not visible cluster-wide

In alert log file:

Tue Jun 29 12:47:24 2010
SQL> ALTER DISKGROUP DATA ADD DISK 'ORCL:DATA_NEW'
Tue Jun 29 12:47:24 2010
NOTE: reconfiguration of group 1/0x933863a4 (DATA), full=1
Tue Jun 29 12:47:24 2010
NOTE: initializing header on grp 1 disk DATA_NEW
NOTE: cache opening disk 1 of grp 1: DATA_NEW label:DATA_NEW
NOTE: PST update: grp = 1
NOTE: requesting all-instance disk validation for group=1
Tue Jun 29 12:47:24 2010
NOTE: disk validation pending for group 1/0x933863a4 (DATA)
SUCCESS: validated disks for 1/0x933863a4 (DATA)
Tue Jun 29 12:47:26 2010
NOTE: requesting all-instance PST refresh for group=1
Tue Jun 29 12:47:26 2010
NOTE: PST refresh pending for group 1/0x933863a4 (DATA)
SUCCESS: refreshed PST for 1/0x933863a4 (DATA)
Tue Jun 29 12:47:29 2010
WARNING: offlining disk 1.3915944791 (DATA_NEW) with mask 0x3
NOTE: PST update: grp = 1, dsk = 1, mode = 0x6
Tue Jun 29 12:47:29 2010
NOTE: PST enabling heartbeating (grp 1)
Tue Jun 29 12:47:29 2010
NOTE: PST update: grp = 1, dsk = 1, mode = 0x4
NOTE: cache closing disk 1 of grp 1: DATA_NEW
Tue Jun 29 12:47:31 2010
Reconfiguration started (old inc 16, new inc 17)
List of nodes:

That made ASM other nodes failed... Oops!!!
Then I made all nodes see ASM new Disk (DATA_NEW) and added...

SQL> ALTER DISKGROUP DATA ADD DISK 'ORCL:DATA_NEW';
ALTER DISKGROUP DATA ADD DISK 'ORCL:DATA_NEW'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15033: disk 'ORCL:DATA_NEW' belongs to diskgroup "DATA"

What's wrong?

SQL> ALTER DISKGROUP DATA drop disk DATA_NEW;
ALTER DISKGROUP DATA drop disk DATA_NEW
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15054: disk "DATA_NEW" does not exist in diskgroup "DATA"

Read on How to Confuse ASM, idea to "dd" disk, Great Idea ^ ^
OK ... about "DATA_NEW" (asmlib) = /dev/emcpowerc1

# dd if=/dev/zero of=/dev/emcpowerc1 bs=4k count=20000

then added Disk again:

SQL> ALTER DISKGROUP DATA ADD DISK 'ORCL:DATA_NEW';

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL RUN 1 1 96 15988 1680 9

SQL> ALTER DISKGROUP DATA REBALANCE POWER 7;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL RUN 7 7 794 15564 2289 6

OK... we should see new Disk from all nodes before add Disk

FLUSH_DATABASE_MONITORING_INFO Procedure

2010-07-14T15:59:00.004+07:00

This is just some procedure, I haven't used it and It's not new... just interest
A FLUSH_DATABASE_MONITORING_INFO is Procedure in DMBS_STATS package. This procedure flush monitoring information for all tables. Corresponding entries in the *_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS views are updated immediately.

SQL> update tb_obj set object_id=100 where rownum <= 50; 50 rows updated. SQL> commit;

Commit complete.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
TB_OBJ 0 0 1

SQL> delete from tb_obj where rownum <= 50; 50 rows deleted. SQL> commit;

Commit complete.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
TB_OBJ 0 0 1

Test to use FLUSH_DATABASE_MONITORING_INFO Procedure:

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
TB_OBJ 0 50 51

That shows monitoring information after it's flushed from memory.

oradebug CLOSE_TRACE

2010-07-14T18:16:00.008+07:00

I read oracle-l - How to free inodes in Linux without killing processes From: Tanel Poder
I learned with oradebug ...
Example:

$ mv orcl_lgwr_14604.trc orcl_lgwr_14604.trc.old
$ ls -la *14604*
-rw-r----- 1 oracle oinstall 18764 Jul 13 22:38 orcl_lgwr_14604.trc.old

SQL> oradebug help
CLOSE_TRACE Close trace file

SQL> oradebug setospid 14604
Oracle pid: 6, Unix process pid: 14604, image: oracle@oratest (LGWR)

SQL> oradebug close_trace
Statement processed.

SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/admin/orcl/bdump/orcl_lgwr_14604.trc

SQL> !ls -la *14604*
-rw-r----- 1 oracle oinstall 679 Jul 14 18:02 orcl_lgwr_14604.trc
-rw-r----- 1 oracle oinstall 18948 Jul 14 18:01 orcl_lgwr_14604.trc.old

Audit SQL with audit_trail db,extended

2010-08-03T00:48:00.007+07:00

Nothing new on this article, I just want to post about audit_trail=db_extended. Because I want to audit SQL statements and find out it.
db,extended - Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.

SQL> alter system set audit_trail=db_extended scope=spfile;

System altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 786759680 bytes
Fixed Size 1339400 bytes
Variable Size 633343992 bytes
Database Buffers 146800640 bytes
Redo Buffers 5275648 bytes
Database mounted.
Database opened.

SQL> show parameter audit_trail

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB_EXTENDED

SQL> AUDIT ALL BY surachart BY ACCESS;

Audit succeeded.

SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY surachart BY ACCESS;

Audit succeeded.

SQL> connect surachart
<Operations>
.
.
.

Then Check...

SQL> show user;
USER is "SYS"

SQL> select sqltext from aud$ where userid='SURACHART';
select * from tab
delete from sdo_geor_ddl__table$$
select count(*) from test
delete from test where rownum <=1

I saw SQL statements from sqltext column in aud$ table.

Error - netca: line 190: 18852 Aborted

2010-08-05T17:27:00.008+07:00

On some test server...
$ netca

Oracle Net Services Configuration:
#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
# SIGSEGV (0xb) at pc=0xf7e76b33, pid=18852, tid=4158679248
#
# Java VM: Java HotSpot(TM) Client VM (1.4.2_14-b05 mixed mode)
# Problematic frame:
# C [libc.so.6+0x6fb33] index+0x63
#
# An error report file with more information is saved as hs_err_pid18852.log
#
# If you would like to submit a bug report, please visit:
# http://java.sun.com/webapps/bugreport/crash.jsp
#
/oracle/product/10.2.0/db_1/bin/netca: line 190: 18852 Aborted $JRE -mx64m $SRVM_PROPERTY_DEFS -Dsun.java2d.font.DisableAlgorithmicStyles=true -classpath $CLASSPATH oracle.net.ca.NetCA $*

Then check "hostname" command-line and /etc/hosts file and add hostname in file.
1. check hostname command-line:

$ hostname
host01.surachartopun.com

2. /etc/hosts file:

127.0.0.1 localhost.localdomain localhost

Then changed:

127.0.0.1 localhost.localdomain localhost
10.10.10.100 host01.surachartopun.com

Try again:
$ netca

Oracle Net Services Configuration:
Configuring Listener:LISTENER
Default local naming configuration complete.
Listener configuration complete.
Oracle Net Listener Startup:
Running Listener Control:
lsnrctl start LISTENER
Listener Control complete.
Listener started successfully.
Oracle Net Services configuration successful. The exit code is 0

OK...

Oracle 11g R1/R2 Real Application Clusters Handbook Book

2010-08-02T23:34:00.004+07:00

Oracle Real Application Cluster(RAC) provides unbeatable fault tolerance, performance, and scalability with no application changes necessary. Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide a highly scalable and available database solution for all our business applications.
I mentions a book... Oracle 11g R1/R2 Real Application Clusters Handbook written by Ben Prusinski (blog), Guenadi Jilevski (blog) and Syed Jaffar Hussain (blog). They have had a lot of experience about Oracle Database, Applications and Oracle Real Application Clusters.

This book is helpful about designation, implementation, administration and management for someone who interest in Oracle RAC DBA as well as It explains new features for Oracle 11g R1/R2 RAC and ASM.

What we will learn from this book :
- Administer, implement, and manage Oracle 11g RAC environments for real-world deployments
- Understand the high availability concepts and solutions that are available for Oracle 11g RAC
- Discover the key architectural design and installation techniques required to successfully deploy Oracle 11g RAC
- Add functionality to your RAC environment by incorporating new RAC features such as Automatic Storage Management
- Effectively manage the complex 11g Clusterware, using key troubleshooting tips and techniques.
- Successfully implement database creation methods, manage the RAC database, and handle workload in your RAC environment efficiently
- Plan your backup and recovery strategy appropriately
- Know when and how to upgrade your RAC environment effectively
- Deploy Oracle 11g RAC with complex standard-off-the-shelf systems like Oracle EBS
- Understand key new features for 11g R1/R2 RAC and ASM

If you have plan to begin Oracle RAC DBA, I believe this book can help you to learn and improve.