select t1.sid "SID", s.username, s.osuser, s.program, s.machine, o.object_name, t1.ctime from v$lock t1, v$session s, dba_objects o where t1.type='TM' and t1.sid = s.sid and t1.id1 = o.object_id order by t1.sid, t1.ctime; Reply With Quote
Thursday, August 14, 2014
sql script to check from DML lock
Wednesday, July 23, 2014
Find the enabled events using ORADEBUG EVENTDUMP and disable it
+ASM1 AS SYSDBA> oradebug setmypid Statement processed. +ASM1 AS SYSDBA> oradebug eventdump system 27090 trace name errorstack level 3 +ASM1 AS SYSDBA> alter system set events '27090 trace name errorstack off'; System altered. +ASM1 AS SYSDBA> oradebug setmypid Statement processed. +ASM1 AS SYSDBA> oradebug eventdump system; Statement processed
Monday, April 28, 2014
SQL failed when field exceed 255 character.
Problem description: SQL load failed with following error: Field in data file exceeds maximum length Cause: By default, when you mention “columnname char” without the size in the control file, sql loader will load maximum of 255 characters long to that particular column, even your column size is more than 255 characters in the table. In this situation, if we want to load the column data more than 255 characters data, explicitly we need to mention the size in the control file within the datatype. Solution: In the sqlldr control file, change column: description to: description char(500)
Monday, October 14, 2013
Use DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO to flush monitoring data
Problem description: Oracle automatic stats collection job did not collect stats on a high modified table Cause: The monitoring data did not get flushed. "PL/SQL Packages and Types Reference": =================================== "GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale." "Oracle Database Reference" ============================== "DBA_TAB_MODIFICATIONS describes modifications to all tables in the database that have been modified since the last time statistics were gathered on the tables." "Note: This view is populated only for tables with the MONITORING attribute. It is intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DBMS_ STATS PL/SQL package to populate this view with the latest information. The ANALYZE_ANY system privilege is required to run this procedure." Solution: Schedule a job to execute FLUSH_DATABASE_MONITORING_INFO procedure before automatic stats collection job start.
Friday, August 30, 2013
How to check database PSU version number
You can check database PSU version number using following 2 ways :
1, SQL > select comments, action_time from sys.registry$history; COMMENTS ACTION_TIME -------------------------------------------------- --------------------------------------------------------------------------- PSU 11.1.0.7.6 10-SEP-11 09.15.23.962385 PM view recompilation 10-SEP-11 09.21.53.526892 PM PSU 11.1.0.7.11 22-SEP-12 11.59.38.688006 PM 2, $ opatch lsinventory -bugs_fixed | grep -i -E "(DATABASE PSU|DATABASE PATCH SET UPDATE)" 8833297 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009) 9209238 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010) 9352179 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010) 9654987 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.4 (INCLUDES CPUJUL2010) 9952228 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.5 (INCLUDES CPUOCT2010) 10248531 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.6 (INCLUDES CPUJAN2011) 11724936 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.7 (INCLUDES CPUAPR2011) 12419384 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.8 (INCLUDES CPUJUL2011) 12827740 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.9 (INCLUDES CPUOCT2011) 13343461 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PATCH SET UPDATE 11.1.0.7.10 (INCLUDES 13621679 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PATCH SET UPDATE 11.1.0.7.11 (INCLUDES
Wednesday, August 21, 2013
Datapump export failed with ORA-01555 on table with LOB column.
Problem description:
Datapump export failed with ORA-01555 on table with LOB column. and there is no LOB corruption.
ORA-31693: Table data object "OWNER"."MY_LOB_TABLE" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_1307891672$"
Cause:
The LOB Retention is not defined properly.
This is confirmed by the queries:
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select max(maxquerylen) from gv$undostat;
MAX(MAXQUERYLEN)
----------------
2279
SQL> select retention from dba_lobs where table_name='OWNER.MY_LOB_TABLE';
RETENTION
----------
900
Solution:
SQL> alter system set undo_retention = 2400 scope = BOTH;
System altered.
SQL> alter table OWNER.MY_LOB_TABLE modify lob(LOB_COLUMN) (pctversion 5);
Table altered.
SQL> alter table OWNER.MY_LOB_TABLE modify lob(LOB_COLUMN) (retention);
Table altered.
SQL> select retention from dba_lobs where table_name='OWNER.MY_LOB_TABLE';
RETENTION
----------
2400
Tuesday, August 20, 2013
12C: Convert non-cdb into pdb and plug into cdb
1. After upgarde from 11.2.0.3 to 12.1.0.1, database(TESTRAC) is NON-CDB:
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string TESTRAC
SQL> select name, cdb from v$database;
NAME CDB
--------- ---
TESTRAC NO
SQL> show con_name;
CON_NAME
------------------------------
Non Consolidated
2. Verify all datafile locations
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/testrac/datafile/system.313.822996543
+DATA/testrac/datafile/sysaux.314.822996543
+DATA/testrac/datafile/undotbs1.315.822996543
+DATA/testrac/datafile/users.316.822996543
+DATA/testrac/datafile/example.327.822996619
+DATA/testrac/datafile/undotbs2.328.822996729
3.Shutdown and Open read only, generate XML pdb description file on source non-cdb(TESTRAC)
srvctl stop database -d TESTRAC
sqlplus "/as sysdba"
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 968885504 bytes
Database Buffers 92274688 bytes
Redo Buffers 5480448 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> exec dbms_pdb.describe(PDB_DESCR_FILE=>'/dba/testrac.xml'); ---> make sure created in shared directory
PL/SQL procedure successfully completed.
4.Shutdown the source non-CDB (TESTRAC)
SQL> shutdown immediate;
exit
5. Connect to target CDB(TEST1CDB) and check whether non-cdb (TESTRAC) can be plugged into it.
SQL> select name, cdb from v$database;
NAME CDB
--------- ---
TEST1CDB YES
SQL>
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/dba/testrac.xml',
pdb_name => 'TESTRAC')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
NO
6. If the scripts returns NO, then check PDB_PLUG_IN_VIOLATIONS view
SQL> col cause for a10
SQL> col name for a10
SQL> col message for a35 word_wrapped
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TESTRAC';
NAME CAUSE TYPE MESSAGE STATUS
---------- ---------- --------- ----------------------------------- ---------
TESTRAC OPTION WARNING Database option DV mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
TESTRAC OPTION WARNING Database option OLS mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
TESTRAC Non-CDB to WARNING PDB plugged in is a non-CDB, PENDING
PDB requires noncdb_to_pdb.sql be run.
NAME CAUSE TYPE MESSAGE STATUS
---------- ---------- --------- ----------------------------------- ---------
TESTRAC Parameter WARNING CDB parameter memory_target PENDING
mismatch: Previous 1073741824
Current 0
TESTRAC Parameter WARNING CDB parameter compatible mismatch: PENDING
Previous 11.2.0.0.0 Current
12.1.0.0.0
There are WARNING’s only. We can continue.
7. Plug-in Non-CDB (TESTRAC) as PDB (TESTRAC) into CDB(TEST1CDB):
SQL> CREATE PLUGGABLE DATABASE TESTRAC USING '/dba/testrac.xml' NOCOPY;
Pluggable database created.
8. Check the newly create pdb(TESTRAC)
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
TEST1PDB READ WRITE
TESTRAC MOUNTED
SQL> col pdb_name for a15
SQL> select pdb_name, status from dba_pdbs where pdb_name = 'TESTRAC';
PDB_NAME STATUS
--------------- -------------
TESTRAC NEW
SQL> alter session set container=TESTRAC;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
TESTRAC
SQL> show con_id;
CON_ID
------------------------------
4
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/TEST1CDB/DATAFILE/undotbs2.344.823881825
+DATA/testrac/datafile/system.313.822996543
+DATA/testrac/datafile/sysaux.314.822996543
+DATA/testrac/datafile/users.316.822996543
+DATA/testrac/datafile/example.327.822996619
9. Run the noncdb_to_pdb.sql script:
SQL>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
10. Open PDB(TESTRAC) verify that warnings was resolved.
SQL> alter session set container= CDB$ROOT;
Session altered.
SQL> col cause for a10
SQL> col name for a10
SQL> col message for a35 word_wrapped
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TESTRAC';
NAME CAUSE TYPE MESSAGE STATUS
---------- ---------- --------- ----------------------------------- ---------
TESTRAC Parameter WARNING CDB parameter memory_target RESOLVED
mismatch: Previous 1073741824
Current 0
TESTRAC Parameter WARNING CDB parameter compatible mismatch: RESOLVED
Previous 11.2.0.0.0 Current
12.1.0.0.0
TESTRAC OPTION WARNING Database option DV mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
TESTRAC OPTION WARNING Database option OLS mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
TESTRAC Non-CDB to ERROR PDB plugged in is a non-CDB, PENDING
PDB requires noncdb_to_pdb.sql be run.
SQL> alter pluggable database TESTRAC open;
Pluggable database altered.
SQL> select pdb_name, status from dba_pdbs;
PDB_NAME STATUS
--------------- -------------
TEST1PDB NORMAL
PDB$SEED NORMAL
TESTRAC NORMAL
3 rows selected.
SQL> col cause for a10
SQL> col name for a10
SQL> col message for a35 word_wrapped
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TESTRAC';
NAME CAUSE TYPE MESSAGE STATUS
---------- ---------- --------- ----------------------------------- ---------
TESTRAC Parameter WARNING CDB parameter memory_target RESOLVED
mismatch: Previous 1073741824
Current 0
TESTRAC Parameter WARNING CDB parameter compatible mismatch: RESOLVED
Previous 11.2.0.0.0 Current
12.1.0.0.0
TESTRAC Non-CDB to ERROR PDB plugged in is a non-CDB, RESOLVED
PDB requires noncdb_to_pdb.sql be run.
TESTRAC OPTION WARNING Database option DV mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
TESTRAC OPTION WARNING Database option OLS mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
Subscribe to:
Posts (Atom)