3zecisi3: April 2012

Thursday, April 26, 2012

Function-based index using DESC

When using desc in create index statement, Oracle interprets this index as function-based index:

SQL> select i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, e.COLUMN_EXPRESSION, C.COLUMN_NAME, C.DESCEND from dba_indexes i

join dba_ind_columns c on i.index_name=c.index_name

join dba_ind_expressions e on i.index_name=e.index_name

where i.table_owner='&owner';

2 3 4 Enter value for owner: USER

old 4: where i.table_owner='&owner'

new 4: where i.table_owner='USER'

TABLE_NAME INDEX_NAME INDEX_TYPE

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

COLUMN_EXPRESSION

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

COLUMN_NAME

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

DESC

----

TARGET1 IDX_ID_DESC FUNCTION-B

ASED NORMA

"ID"

SYS_NC00018$

DESC

TARGET1 IDX_CREATED_DESC FUNCTION-B

ASED NORMA

"CREATED"

SYS_NC00019$

DESC

Thursday, April 19, 2012

ORA-19573: cannot obtain exclusive enqueue for datafile 10

-bash-3.2$ oerr ora 19573
19573, 00000, "cannot obtain %s enqueue for datafile %s"
// *Cause: The file access enqueue could not be obtained for a file
// specified in a backup, copy or restore operation.
// If the enqueue type shown is 'shared', then the file is the
// input file for a backup or copy. If the type is 'exclusive', then
// the file is the output file for a datafile copy or restore which
// is attempting to overwrite the currently active version of that
// file - in this case, the file must be offline or the database must
// be closed. If the type is 'read-only', then you are attempting
// to back up or copy this file while the database is in NOARCHIVELOG
// mode.
// *Action: Wait until the conflicting operation is complete, then retry
// the copy or backup. If the database is in NOARCHIVELOG mode, then
// all files being backed up must be closed cleanly.
-bash-3.2$

Remove one datafile (just for testing)

-bash-3.2$ ls
idx_big_01.dbf idx_small_01.dbf tab_med_01.dbf
idx_med_01.dbf tab_big_01.dbf tab_small_01.dbf
-bash-3.2$ rm idx_small_01.dbf
-bash-3.2$ ls
idx_big_01.dbf tab_big_01.dbf tab_small_01.dbf
idx_med_01.dbf tab_med_01.dbf
-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 11:57:16 2012

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

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
ORA-01116: error in opening database file 10
ORA-01110: data file 10: '/data/tbs/idx_small_01.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
-bash-3.2$
-bash-3.2$

-bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 19 11:57:51 2012

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

connected to target database: ORATEST (DBID=3309052188)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORATEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 730 SYSTEM *** +DATA/oratest/datafile/system.256.773672945
2 730 SYSAUX *** +DATA/oratest/datafile/sysaux.257.773672955
3 110 UNDOTBS1 *** +DATA/oratest/datafile/undotbs1.258.773672957
4 18 USERS *** +DATA/oratest/datafile/users.259.773672963
5 345 EXAMPLE *** +DATA/oratest/datafile/example.265.773673233
6 100 TAB *** +DATA/oratest/datafile/tab.267.774023547
7 64 TAB_SMALL *** /data/tbs/tab_small_01.dbf
8 64 TAB_MED *** /data/tbs/tab_med_01.dbf
9 256 TAB_BIG *** /data/tbs/tab_big_01.dbf
10 0 IDX_SMALL *** /data/tbs/idx_small_01.dbf
11 64 IDX_MED *** /data/tbs/idx_med_01.dbf
12 256 IDX_BIG *** /data/tbs/idx_big_01.dbf
13 100 RC_TBS *** +DATA/oratest/datafile/rc_tbs.271.781005987

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 +DATA/oratest/tempfile/temp.264.773673181
2 10 TEMP_TEMP 10 +DATA/oratest/tempfile/temp_temp.268.778418247

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
28 HIGH OPEN 19-APR-12 One or more non-system datafiles are missing

RMAN> restore datafile 10;

Starting restore at 19-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /data/tbs/idx_small_01.dbf
channel ORA_DISK_1: reading from backup piece +FRA/oratest/backupset/2012_04_19/nnndf0_tag20120419t102811_0.290.781007293
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/19/2012 11:58:54
ORA-19870: error while restoring backup piece +FRA/oratest/backupset/2012_04_19/nnndf0_tag20120419t102811_0.290.781007293
ORA-19573: cannot obtain exclusive enqueue for datafile 10

RMAN> sql 'alter tablespace IDX_SMALL offline';

sql statement: alter tablespace IDX_SMALL offline
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 04/19/2012 12:01:56
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace IDX_SMALL offline
ORA-01116: error in opening database file 10
ORA-01110: data file 10: '/data/tbs/idx_small_01.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

RMAN> sql 'alter tablespace IDX_SMALL offline immediate';

sql statement: alter tablespace IDX_SMALL offline immediate

RMAN> restore tablespace IDX_SMALL;

Starting restore at 19-APR-12
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /data/tbs/idx_small_01.dbf
channel ORA_DISK_1: reading from backup piece +FRA/oratest/backupset/2012_04_19/nnndf0_tag20120419t102811_0.290.781007293
channel ORA_DISK_1: piece handle=+FRA/oratest/backupset/2012_04_19/nnndf0_tag20120419t102811_0.290.781007293 tag=TAG20120419T102811
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 19-APR-12

RMAN> recover tablespace IDX_SMALL;

Starting recover at 19-APR-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished recover at 19-APR-12

RMAN> sql 'alter tablespace IDX_SMALL online';

sql statement: alter tablespace IDX_SMALL online

RMAN> report schema;

Report of database schema for database with db_unique_name ORATEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 730 SYSTEM *** +DATA/oratest/datafile/system.256.773672945
2 730 SYSAUX *** +DATA/oratest/datafile/sysaux.257.773672955
3 110 UNDOTBS1 *** +DATA/oratest/datafile/undotbs1.258.773672957
4 18 USERS *** +DATA/oratest/datafile/users.259.773672963
5 345 EXAMPLE *** +DATA/oratest/datafile/example.265.773673233
6 100 TAB *** +DATA/oratest/datafile/tab.267.774023547
7 64 TAB_SMALL *** /data/tbs/tab_small_01.dbf
8 64 TAB_MED *** /data/tbs/tab_med_01.dbf
9 256 TAB_BIG *** /data/tbs/tab_big_01.dbf
10 64 IDX_SMALL *** /data/tbs/idx_small_01.dbf
11 64 IDX_MED *** /data/tbs/idx_med_01.dbf
12 256 IDX_BIG *** /data/tbs/idx_big_01.dbf
13 100 RC_TBS *** +DATA/oratest/datafile/rc_tbs.271.781005987

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 +DATA/oratest/tempfile/temp.264.773673181
2 10 TEMP_TEMP 10 +DATA/oratest/tempfile/temp_temp.268.778418247

RMAN>

RMAN vitual private catalog - RMAN-06801: no base catalog found

-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 10:46:45 2012

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

SQL> conn / as sysdba
Connected.

SQL> create user rc_01 identified by rc_011234
2 default tablespace rc_tbs
3 quota unlimited on rc_tbs;

User created.

SQL> grant recovery_catalog_owner to rc_01;

Grant succeeded.

-bash-3.2$ rman target / catalog=rc_01/rc_011234@oratest

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 19 10:49:36 2012

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

connected to target database: ORATEST (DBID=3309052188)
connected to recovery catalog database

RMAN> create virtual catalog;

found ineligible base catalog owned by RC_USER
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06801: no base catalog found

-bash-3.2$ oerr rman 6426

6426, 1, "RECOVERY_CATALOG_OWNER role must be granted to user %s"

// *Cause: The CREATE CATALOG or UPGRADE CATALOG command was used, but the

// USERID that was supplied in the CATALOG connect string does not

// have the RECOVERY_CATALOG_OWNER role granted as a DEFAULT role.

// *Action: Grant the RECOVERY_CATALOG_OWNER role to the recovery catalog

// owner.

-bash-3.2$ rman target / catalog=rc_user/rc_user1234@oratest

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 19 10:50:21 2012

connected to target database: ORATEST (DBID=3309052188)

connected to recovery catalog database

RMAN> grant catalog for database oratest to rc_01;

Grant succeeded.

RMAN> exit

Recovery Manager complete.

-bash-3.2$ rman target / catalog=rc_01/rc_011234@oratest

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 19 11:06:01 2012

connected to target database: ORATEST (DBID=3309052188)

connected to recovery catalog database

RMAN> create virtual catalog;

found eligible base catalog owned by RC_USER

created virtual catalog against base catalog owned by RC_USER

RMAN>

Wednesday, April 18, 2012

ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

You cannot create backups on which you’ve used the keep option in the
FRA. If you want to create backups that have nonstandard retention criteria,
you will have to use a non-FRA location to create them. This often
requires the use of the format parameter, as shown in this example:

backup database format ’/data/backup/%U’ keep until time
”sysdate+300” ;

RMAN> backup database tag=keep_bkp keep until time 'sysdate+300';

Starting backup at 18-APR-12
current log archived

using channel ORA_DISK_1
backup will be obsolete on date 12-FEB-13
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/oratest/datafile/system.256.773672945
input datafile file number=00012 name=/data/tbs/idx_big_01.dbf
input datafile file number=00007 name=/data/tbs/tab_small_01.dbf
input datafile file number=00008 name=/data/tbs/tab_med_01.dbf
input datafile file number=00010 name=/data/tbs/idx_small_01.dbf
input datafile file number=00011 name=/data/tbs/idx_med_01.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-12
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/18/2012 15:12:27
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/oratest/datafile/sysaux.257.773672955
channel ORA_DISK_1: starting piece 1 at 18-APR-12
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/18/2012 15:12:28
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/data/tbs/tab_big_01.dbf
input datafile file number=00003 name=+DATA/oratest/datafile/undotbs1.258.773672957
input datafile file number=00004 name=+DATA/oratest/datafile/users.259.773672963
input datafile file number=00006 name=+DATA/oratest/datafile/tab.267.774023547
channel ORA_DISK_1: starting piece 1 at 18-APR-12
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/18/2012 15:12:28
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/oratest/datafile/example.265.773673233
channel ORA_DISK_1: starting piece 1 at 18-APR-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/18/2012 15:12:28
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

Monday, April 9, 2012

ASM directories

-- DIRECTORIES

SQL> alter diskgroup data_test mount;

Diskgroup altered.

SQL> alter diskgroup data_test add directory '+DATA_TEST/DIR';

Diskgroup altered.

SQL> alter diskgroup data_test rename directory '+DATA_TEST/DIR' to '+data_test/dir_test';

Diskgroup altered.

ASM ORA-15032: not all alterations performed, ORA-15283

SQL> alter diskgroup data_test offline disk disk4;
alter diskgroup data_test offline disk disk4
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.0.0 or higher

SQL> alter diskgroup data_test set attribute 'compatible.asm'='11.2';

Diskgroup altered.

SQL> select * from v$asm_disk where name='DATA_TEST';

no rows selected

SQL> select * from v$asm_diskgroup where name='DATA_TEST';

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
3 DATA_TEST 512 4096
1048576 MOUNTED NORMAL 1800 1694 0
106 0 847 0

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
11.2.0.0.0
10.1.0.0.0 N

SQL> alter diskgroup data_test offline disk disk4;
alter diskgroup data_test offline disk disk4
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.0.0 or higher

SQL> alter diskgroup data_test set attribute 'compatible.rdbms'='11.2';

Diskgroup altered.

SQL> select * from v$asm_diskgroup where name='DATA_TEST';

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
3 DATA_TEST 512 4096
1048576 MOUNTED NORMAL 1800 1694 0
106 0 847 0

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
11.2.0.0.0
11.2.0.0.0 N

SQL> alter diskgroup data_test offline disk disk4;

Diskgroup altered.

SQL> select * from v$asm_operation;

no rows selected

SQL> alter diskgroup data_test online all;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
3 ONLIN RUN 1 0 0 0 0
0

Add ASM disk to Solaris

-- DISCOVER DISKS IN SOLARIS
# devfsadm
#
-- FORMAT DISK PARTITION TABLE
# format
Searching for disks...done

AVAILABLE DISK SELECTIONS:
0. c0d1 <DEFAULT cyl 3913 alt 2 hd 255 sec 63>
/pci@0,0/pci-ide@7,1/ide@1/cmdk@1,0
1. c1t0d0 <DEFAULT cyl 1302 alt 2 hd 255 sec 63>
/pci@0,0/pci15ad,1976@10/sd@0,0
2. c1t1d0 <DEFAULT cyl 2556 alt 2 hd 128 sec 32>
/pci@0,0/pci15ad,1976@10/sd@1,0
3. c1t2d0 <DEFAULT cyl 1021 alt 2 hd 64 sec 32>
/pci@0,0/pci15ad,1976@10/sd@2,0
Specify disk (enter its number): 3
selecting c1t2d0
[disk formatted]

FORMAT MENU:
disk - select a disk
type - select (define) a disk type
partition - select (define) a partition table
current - describe the current disk
format - format and analyze the disk
fdisk - run the fdisk program
repair - repair a defective sector
label - write label to the disk
analyze - surface analysis
defect - defect list management
backup - search for backup labels
verify - read and display labels
save - save new disk/partition definitions
inquiry - show vendor, product and revision
volname - set 8-character volume name
!<cmd> - execute <cmd>, then return
quit

format> fdisk
No fdisk table exists. The default partition for the disk is:

a 100% "SOLARIS System" partition

Type "y" to accept the default partition, otherwise type "n" to edit the
partition table.
y
format>

format> part

PARTITION MENU:
0 - change `0' partition
1 - change `1' partition
2 - change `2' partition
3 - change `3' partition
4 - change `4' partition
5 - change `5' partition
6 - change `6' partition
7 - change `7' partition
select - select a predefined table
modify - modify a predefined partition table
name - name the current table
print - display the current table
label - write partition map and label to the disk
!<cmd> - execute <cmd>, then return
quit
partition> 4
Part Tag Flag Cylinders Size Blocks
4 unassigned wm 0 0 (0/0/0) 0

Enter partition id tag[unassigned]:
Enter partition permission flags[wm]:
Enter new starting cyl[0]:
Enter partition size[0b, 0c, 0e, 0.00mb, 0.00gb]: 900m
partition> print
Current partition table (unnamed):
Total disk cylinders available: 1020 + 2 (reserved cylinders)

Part Tag Flag Cylinders Size Blocks
0 unassigned wm 0 0 (0/0/0) 0
1 unassigned wm 0 0 (0/0/0) 0
2 backup wu 0 - 1019 1020.00MB (1020/0/0) 2088960
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 0 - 899 900.00MB (900/0/0) 1843200
5 unassigned wm 0 0 (0/0/0) 0
6 unassigned wm 0 0 (0/0/0) 0
7 unassigned wm 0 0 (0/0/0) 0
8 boot wu 0 - 0 1.00MB (1/0/0) 2048
9 unassigned wm 0 0 (0/0/0) 0

partition> help
Expecting one of the following: (abbreviations ok):
0 - change `0' partition
1 - change `1' partition
2 - change `2' partition
3 - change `3' partition
4 - change `4' partition
5 - change `5' partition
6 - change `6' partition
7 - change `7' partition
select - select a predefined table
modify - modify a predefined partition table
name - name the current table
print - display the current table
label - write partition map and label to the disk
!<cmd> - execute <cmd>, then return
quit

partition> modify
Select partitioning base:
0. Current partition table (unnamed)
1. All Free Hog
Choose base (enter number) [0]? 1

Part Tag Flag Cylinders Size Blocks
0 root wm 0 0 (0/0/0) 0
1 swap wu 0 0 (0/0/0) 0
2 backup wu 0 - 1019 1020.00MB (1020/0/0) 2088960
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 0 0 (0/0/0) 0
5 unassigned wm 0 0 (0/0/0) 0
6 usr wm 0 0 (0/0/0) 0
7 unassigned wm 0 0 (0/0/0) 0
8 boot wu 0 - 0 1.00MB (1/0/0) 2048
9 alternates wm 0 0 (0/0/0) 0

Do you wish to continue creating a new partition
table based on above table[yes]?
Free Hog partition[6]? 7
Enter size of partition '0' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '1' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '3' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '4' [0b, 0c, 0.00mb, 0.00gb]: 900m
Enter size of partition '5' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '6' [0b, 0c, 0.00mb, 0.00gb]:

Part Tag Flag Cylinders Size Blocks
0 root wm 0 0 (0/0/0) 0
1 swap wu 0 0 (0/0/0) 0
2 backup wu 0 - 1019 1020.00MB (1020/0/0) 2088960
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 1 - 900 900.00MB (900/0/0) 1843200
5 unassigned wm 0 0 (0/0/0) 0
6 usr wm 0 0 (0/0/0) 0
7 unassigned wm 901 - 1019 119.00MB (119/0/0) 243712
8 boot wu 0 - 0 1.00MB (1/0/0) 2048
9 alternates wm 0 0 (0/0/0) 0

Okay to make this the current partition table[yes]?
Enter table name (remember quotes): asm3

Ready to label disk, continue? yes

partition> print
Current partition table (asm3):
Total disk cylinders available: 1020 + 2 (reserved cylinders)

Part Tag Flag Cylinders Size Blocks
0 unassigned wm 0 0 (0/0/0) 0
1 unassigned wm 0 0 (0/0/0) 0
2 backup wu 0 - 1019 1020.00MB (1020/0/0) 2088960
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 1 - 900 900.00MB (900/0/0) 1843200
5 unassigned wm 0 0 (0/0/0) 0
6 unassigned wm 0 0 (0/0/0) 0
7 unassigned wm 901 - 1019 119.00MB (119/0/0) 243712
8 boot wu 0 - 0 1.00MB (1/0/0) 2048
9 unassigned wm 0 0 (0/0/0) 0

partition>
partition> q

FORMAT MENU:
disk - select a disk
type - select (define) a disk type
partition - select (define) a partition table
current - describe the current disk
format - format and analyze the disk
fdisk - run the fdisk program
repair - repair a defective sector
label - write label to the disk
analyze - surface analysis
defect - defect list management
backup - search for backup labels
verify - read and display labels
save - save new disk/partition definitions
inquiry - show vendor, product and revision
volname - set 8-character volume name
!<cmd> - execute <cmd>, then return
quit
format> verify

Primary label contents:

Volume name = < >
ascii name = <DEFAULT cyl 1020 alt 2 hd 64 sec 32>
pcyl = 1022
ncyl = 1020
acyl = 2
bcyl = 0
nhead = 64
nsect = 32
Part Tag Flag Cylinders Size Blocks
0 unassigned wm 0 0 (0/0/0) 0
1 unassigned wm 0 0 (0/0/0) 0
2 backup wu 0 - 1019 1020.00MB (1020/0/0) 2088960
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 1 - 900 900.00MB (900/0/0) 1843200
5 unassigned wm 0 0 (0/0/0) 0
6 unassigned wm 0 0 (0/0/0) 0
7 unassigned wm 901 - 1019 119.00MB (119/0/0) 243712
8 boot wu 0 - 0 1.00MB (1/0/0) 2048
9 unassigned wm 0 0 (0/0/0) 0

format> q

-- CREATE DEVICE FOR ASM PARTITION
# cd asmdisk
# ls
disk1 disk2 disk3
# ls -l
total 0
crw-r--r-- 1 oracle oinstall 30, 68 Apr 9 11:33 disk1
crw-r--r-- 1 oracle oinstall 30, 132 Apr 9 11:33 disk2
#
# mknod disk3 c 30 196
# ls -l
total 0
crw-r--r-- 1 oracle oinstall 30, 68 Apr 9 11:34 disk1
crw-r--r-- 1 oracle oinstall 30, 132 Apr 9 11:34 disk2
crw-r--r-- 1 root root 30, 196 Apr 9 11:34 disk3
#
# chown oracle:oinstall disk3
# ls
disk1 disk2 disk3
#
# ls -l
total 0
crw-r--r-- 1 oracle oinstall 30, 68 Apr 9 11:34 disk1
crw-r--r-- 1 oracle oinstall 30, 132 Apr 9 11:34 disk2
crw-r--r-- 1 oracle oinstall 30, 196 Apr 9 11:34 disk3

# su - oracle

-bash-3.2$ . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app/oracle
-bash-3.2$
-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 9 11:48:55 2012

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

SQL> conn / as sysasm
Connected.
SQL>

SQL> select path, group_number group_#, disk_number disk_#, mount_status,
header_status, state, total_mb, free_mb
from v$asm_disk
order by group_number;

/asmdisk/disk3
0 0 CLOSED CANDIDATE NORMAL 0 0

/asmdisk/disk1
1 0 CACHED MEMBER NORMAL 9216 6879

/asmdisk/disk2
2 0 CACHED MEMBER NORMAL 5018 3480

SQL> show parameter asm_diskstring;

asm_diskstring string /asmdisk/disk*

-- ASM DISKGROUP ATTRIBUTES

SQL> select GROUP_NUMBER,NAME,VALUE from v$asm_attribute

2 where name not like 'template%';

disk_repair_time

3.6h

au_size

1048576

sector_size

512

compatible.asm

11.2.0.0.0

compatible.rdbms

10.1.0.0.0

cell.smart_scan_capable

FALSE

access_control.enabled

FALSE

access_control.umask

066

disk_repair_time

3.6h

au_size

1048576

sector_size

512

compatible.asm

11.2.0.0.0

compatible.rdbms

10.1.0.0.0

cell.smart_scan_capable

FALSE

access_control.enabled

FALSE

access_control.umask

066

16 rows selected.

-- ADD DISK

SQL> alter diskgroup data add disk '/asmdisk/disk3';

Diskgroup altered.

SQL> select * from v$asm_operation;

1 REBAL RUN 1 1 149 209 550

SQL> SELECT

NVL(a.name, '[CANDIDATE]') disk_group_name

, b.path disk_file_path

, b.name disk_file_name

, b.failgroup disk_file_fail_group

FROM

v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)

ORDER BY

a.name;

DISK_GROUP_NAME

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

DISK_FILE_PATH

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

DISK_FILE_NAME DISK_FILE_FAIL_GROUP

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

DATA

/asmdisk/disk1

DATA_0000 DATA_0000

DATA

/asmdisk/disk3

DATA_0001 DATA_0001

DISK_GROUP_NAME

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

DISK_FILE_PATH

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

DISK_FILE_NAME DISK_FILE_FAIL_GROUP

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

FRA

/asmdisk/disk2

FRA_0000 FRA_0000

-- ADD DISK USING NAME

SQL> alter diskgroup data drop disk DATA_0001;

Diskgroup altered.

SQL> alter diskgroup data add disk '/asmdisk/disk3' NAME disk3;

Diskgroup altered.

SQL> select name from v$asm_disk;

NAME
------------------------------
DATA_0000
FRA_0000
DISK3

-- DROP DISKS

SQL> alter diskgroup data drop disk DATA_0001;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE

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

EST_MINUTES ERROR_CODE

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

1 REBAL RUN 1 1 166 209 869

SQL> /

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE

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

EST_MINUTES ERROR_CODE

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

1 REBAL WAIT 1

SQL> /

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE

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

EST_MINUTES ERROR_CODE

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

1 REBAL WAIT 1

SQL> /

no rows selected

SQL> select name, header_status, path from v$asm_disk;

NAME HEADER_STATU
------------------------------ ------------
PATH
--------------------------------------------------------------------------------
FORMER
/asmdisk/disk3

DATA_0000 MEMBER
/asmdisk/disk1

FRA_0000 MEMBER
/asmdisk/disk2

-- USING UNDROP DISKS

select NAME, HEADER_STATUS, MOUNT_STATUS, MODE_STATUS, STATE, REPAIR_TIMER/60 from V$ASM_DISK WHERE GROUP_NUMBER=1;

NAME HEADER_STATU MOUNT_S MODE_ST STATE

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

REPAIR_TIMER/60

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

DATA_0000 MEMBER CACHED ONLINE NORMAL

DATA_0001 MEMBER CACHED ONLINE NORMAL

SQL> alter diskgroup data drop disk DATA_0001;

Diskgroup altered.

SQL> alter diskgroup data undrop disks;

Diskgroup altered.

SQL> select NAME, HEADER_STATUS, MOUNT_STATUS, MODE_STATUS, STATE, REPAIR_TIMER/60 from V$ASM_DISK WHERE GROUP_NUMBER=1;

NAME HEADER_STATU MOUNT_S MODE_ST STATE

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

REPAIR_TIMER/60

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

DATA_0000 MEMBER CACHED ONLINE NORMAL

DATA_0001 MEMBER CACHED ONLINE NORMAL

0

-- DISKGROUP REBALANCE

SQL> alter diskgroup data rebalance;

Diskgroup altered.

select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE

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

EST_MINUTES ERROR_CODE

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

1 REBAL RUN 1 1 0 2 0

-- ASM TEMPLATES

SQL> select * from v$asm_template
2 where group_number=2;

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
2 60 UNPROT COARSE Y PARAMETERFILE COLD
COLD

2 61 UNPROT COARSE Y ASMPARAMETERFILE COLD
COLD

2 63 UNPROT COARSE Y DUMPSET COLD
COLD

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
2 64 UNPROT FINE Y CONTROLFILE COLD
COLD

2 65 UNPROT COARSE Y FLASHFILE COLD
COLD

2 66 UNPROT COARSE Y ARCHIVELOG COLD
COLD

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
2 67 UNPROT COARSE Y ONLINELOG COLD
COLD

2 68 UNPROT COARSE Y DATAFILE COLD
COLD

2 69 UNPROT COARSE Y TEMPFILE COLD
COLD

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
2 170 UNPROT COARSE Y BACKUPSET COLD
COLD

2 171 UNPROT COARSE Y AUTOBACKUP COLD
COLD

2 172 UNPROT COARSE Y XTRANSPORT COLD
COLD

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
2 173 UNPROT COARSE Y CHANGETRACKING COLD
COLD

2 174 UNPROT COARSE Y FLASHBACK COLD
COLD

2 175 UNPROT COARSE Y DATAGUARDCONFIG COLD
COLD

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME PRIM
------------ ------------ ------ ------ - ------------------------------ ----
MIRR
----
2 176 UNPROT COARSE Y OCRFILE COLD
COLD

16 rows selected.

Friday, April 6, 2012

ORA-00380 - cannot specify db_stringk_cache_size since stringK is the standard block size

SQL> show parameter db;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean FALSE
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 8192
db_cache_advice string ON

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0

SQL> alter system set db_8k_cache_size=10m;
alter system set db_8k_cache_size=10m
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size

Tuesday, April 3, 2012

MySQL admin

mysql -u root -p -- Connect to Mysql

show databases; -- view databases
create database db_name; -- create database db_name
create schema db_name; -- create database db_name (schema <=> database)
use db_name; -- change database to db information_schema for all statements
show databases; -- list all databases
select database(); -- show current database
help contents; -- access help path

create user test identified by 'test'; -- create user
grant select on test.table_test to test; -- grant select on table_test to user test
select user, host, password from mysql.user; -- show all users
drop user test; -- drop user
show grants; -- show user grants
show grants for 'user'@'host' -- show grants for a specific user

Tables are fully defined by using db_name.table_name.

desc table_name; -- describe table
show tables; -- show database tables;
show index for table_name; -- show index for table_name
show table status; -- list detailed table info
show create table table_name; -- list DDL for table_name create script
truncate table table_name; -- truncates table
analyze table test2; -- analyze table
explain select * from table_tes where pk_col=1; -- view explain plan

select now(); -- show system time
select @@tx_isolation; -- show transaction isolation level
show character set; -- display a list of available character sets

set autocommit = 1; -- default: set autocommit on

MySql administration (mysqladmin)

mysqladmin status
mysqladmin version

status -- Returns information on server state
password -- Changes a user password
shutdown -- Shuts down the MySQL server
reload -- Reloads the MySQL grant tables
refresh -- Resets all caches and logs
variables -- Returns values of all server variables
version -- Returns the server version
processlist -- Returns a list of all processes active on the server
kill -- Kills an active server process
ping -- Tests if the server is alive

Starting/stopping

/usr/local/mysql/support-files/mysql.server start
/usr/local/mysql/support-files/mysql.server stop
On Windows:
mysqladmin -u root -p shutdown

Monday, April 2, 2012

Enable SQL Plus autotrace

As user from SQL Plus:

SQL> set autotrace on;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

As system from SQL Plus:

SQL> @?/rdbms/admin/utlxplan

Table created.

SQL> grant all on plan_table to public;

Grant succeeded.

As sys from SQL Plus:

SQL> @?/sqlplus/admin/plustrce.sql

SQL> grant plustrace to public;

Grant succeeded.

As user (a reconnect is required):

SQL> set autotrace on;