Thursday, August 16, 2012

ORA-12162: TNS:net service name is incorrectly specified


oracle@oradev801:/opt/oracle INT$  sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 16 16:57:18 2012

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

ERROR:
ORA-12162: TNS:net service name is incorrectly specified
  
Enter user-name:
  
Strange!!
I thought it is a problem with the TNS entry, but after doing some research I came to know that it was a problem with ORACLE_SID, May be ORACLE_SID is not properly set or it was not exported.

oracle@oradev801:/opt/oracle INT$  export ORACLE_SID=XXXXXX
oracle@oradev801:/opt/oracle INT$  echo $ORACLE_SID
XXXXXX
oracle@oradev801:/opt/oracle INT$  sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 16 16:57:18 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  select instance_name from v$instance;

INSTANCE_NAME
----------------
XXXXXX


On WINDOWS:  set ORACLE_SID=XXXXXX


Wednesday, August 8, 2012

ORA-12906, DROPPING DEFAULT TEMPORARY TABLESPACE


First find the Default temporary tablespace in your database

SQL> select * from v$version;

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

SQL>
SQL > select  property_value  from  database_properties  Where  property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP


Check the size of TEMP tablespace:

SQL > SELECT tablespace_name, file_name, bytes/1024/1024/1024 GB FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

TABLESPACE_NAME         FILE_NAME                                                   GB
--------------------------     --------------------------------                        ------------
  TEMP                        /opt/oracle/oradata/temp01.dbf                        6


For Oracle 8 and above, the following query will return all users and their SIDs which are doing a sort:

 SELECT   b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status  FROM     v$session a,v$sort_usage b  WHERE    a.saddr = b.session_addr  ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;


To see USED and FREE space of it:

SQL> select SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree from  v$temp_space_header;

  GBUSED     GBFREE
------------    ---------
   1.25                 4.75

Now, try to drop the Default temp tablespace

SQL> DROP TABLESPACE temp;
DROP TABLESPACE temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

In order to drop and recreate the default temp tablepsace first thing is to create another temporary tablespace and make that tablespace as default tablespace for the database and then drop the old default tablespace(TEMP)

SQL>   CREATE TEMPORARY TABLESPACE temp_new TEMPFILE  '/opt/oracle/oradata/temp_new01.dbf' size 5G reuse  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new; 
Database altered.

Now drop the TEMP tablespace

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

Monday, August 6, 2012

Find the delay of standby from primary


Here is a quick way to find the current timestamp from the current SCN.

On primary:

SQL> select to_char(CURRENT_SCN) from v$database;

TO_CHAR(CURRENT_SCN)
————————————
12345678909

SQL> select scn_to_timestamp(48133107062) from dual;

SCN_TO_TIMESTAMP(12345678909)
—————————————————
19-JUN-11 08.40.27.000000000 AM

SQL>


And do the same on standby database, then find the difference in time.

If you set the delay parameter then find it in the parameter file

Thanks !!

Thursday, August 2, 2012

import only one table ROWS


TASK :  I want to import 1 schema table rows from a full or full schema dump

SCHEMA: Chandra
Table     :  Demo
Dumpfile : dummy.dmp
Directory = TEST

$ impdp dumpfile=dummy.dmp directory=TEST content=data_only tables=DEMO logfile=impdp_demo.log

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 14:59:52 2012

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.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object SYS.DEMO was not found.

REASON  : Here we are running this import as sysdba user so oracle will find and import the sys.table rows , since it is not found thrown an error above

$ impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=DEMO logfile=impdp_demo.log schemas=chandra

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:00:46 2012

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.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDI-00010: multiple job modes requested, schema and tables

REASON : I tried to import table rows by using both SCHEMAS at the same time


$ impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=DEMO logfile=impdp_demo.log

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:03:50 2012

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

Username: Chandra
Password : *********

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_02":  /******** AS SYSDBA dumpfile=chandra_0731.dmp directory=EXPORT_DIR content=data_only tables=demo logfile=impdp_0802.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CHANDRA"."DEMO"                692.9 KB   15700 rows
Job "SYS"."SYS_IMPORT_TABLE_02" successfully completed at 15:03:58


Here it searched for Chandra.demo table and imported rows

(or)

 impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=chandra.DEMO logfile=impdp_demo.log
 Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:03:50 2012

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.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_02":  /******** AS SYSDBA dumpfile=chandra_0731.dmp directory=TEST content=data_only tables=chandra.demo logfile=impdp_0802.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CHANDRA"."DEMO"                692.9 KB   15700 rows
Job "SYS"."SYS_IMPORT_TABLE_02" successfully completed at 15:03:58

NOTE :   schema “CHANDRA” from which you are importing should have ‘imp_full_database’ role granted, also READ and WRITE permissions on TEST Directory

Thank you !!! :-) 

Auto Scroll Stop Scroll