3zecisi3

Wednesday, May 23, 2012

UTL_INADDR ORA-29257:host [ip or name] unknown

In Oracle there is UTL_INADDR package that is an API to access host name and ip address of server.
Usage

select UTL_INADDR.get_host_name('host_name') from dual;
select UTL_INADDR.get_address_name('ip_address') from dual;

SET serveroutput on
BEGIN
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME);  -- get local host name
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS);  -- get local IP addr
END;
/

If host_name or ip_address is null, this function returns local host hostname or ip.
If hostname or IP can't be resolved, error ORA-29257:host [ip or name] unknown is returned.

Thursday, May 17, 2012

impdp ORA-39083 ORA-00959

At import time you can get these errors:

ORA-39083: Object type INDEX failed to create with error:
ORA-00959: tablespace 'IDX' does not exist

In this case use REMAP_TABLESPACE=IDX:[destination tablespace]
If you have multiple tablespaces, for each use
REMAP_TABLESPACE=[source tablespace]:[destination tablespace]

Example:

impdp user/passsword@sid directory=dir remap_schema=schema_source_schema:dest_schema remap_tablespace=tab1:tab remap_tablespace=tab2:tab dumpfile=file.dmp logfile=file.log

Monday, May 14, 2012

ORA-01756: quoted string not properly terminated

Obviously, the cause of this error is missing some quotes in writing statement:

oerr ora 1756
01756, 00000, "quoted string not properly terminated"
// *Cause:
// *Action:

example:

SQL> select dbms_stats.create_extended_stats(null,'tab,'(x,y)') from dual;

ERROR:

ORA-01756: quoted string not properly terminated

Cause: string tab is not terminated with quote

Action: put quote to avoid error

11g Extended statistics

To take advantages from statistics on group of columns, you can use extended statistics.

SQL> select count(*) from tab;

COUNT(*)
----------
1003241

SQL> select column_name, num_distinct, num_nulls, histogram from user_tab_col_statistics
2 where table_name='TAB';

COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ---------------
X 5 0 NONE
Y 5 0 NONE

SQL> select dbms_stats.create_extended_stats(null,'tab','(x,y)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'TAB','(X,Y)')
--------------------------------------------------------------------------------
SYS_STUYPW88OE302TFVBNC6$MMQXE

SQL> select column_name, num_distinct, num_nulls, histogram from user_tab_col_statistics
2 where table_name='TAB';

COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ---------------
X 5 0 NONE
Y 5 0 NONE

SQL> select * from user_stat_extensions;

TABLE_NAME EXTENSION_NAME
------------------------------ ------------------------------
EXTENSION
--------------------------------------------------------------------------------
CREATO DRO
------ ---
TAB SYS_STUYPW88OE302TFVBNC6$MMQXE
("X","Y")
USER YES

SQL> exec dbms_stats.gather_table_stats(null,'tab');

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, num_nulls, histogram from user_tab_col_statistics
2 where table_name='TAB';

COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ---------------
SYS_STUYPW88OE302TFVBNC6$MMQXE 5 0 NONE
X 5 0 NONE
Y 5 0 FREQUENCY

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>