Oracle Database Administration

Hi all, Today i am going to write another one of mine analysis by using My oracle support site document. In one of our client's database, we are getting the following error message continuously. Alert log entry: Wed Jul 23 11:45:43 2014 Errors in file /home/oracle/app/oracle/diag/rdbms/db/db/trace/db_ora_14547.trc  (incident=149824): ORA-00600: internal error code, arguments: [7005], [0], [], [], [], [], [], [], [], [], [], [] SQL_TEXT involved in this error is  select iso_country_code  from countries  where (SDO_RELATE(geom,SDO_GEOMETRY(:"SYS_B_0",:"SYS_B_1"),:"SYS_B_2")=:"SYS_B_3") So one of our dba has reported like below after searching the document for the error. According to Doc ID 138935.1 The error occurred due to Bug 8770557 Description: ORA-600 [7005] from SQL with user defined selectivity function (eg: Text queries) Solution: The issue is fixed in 11.2.0.2, 12.1.0.1 But o...

Hi all, We have received a mail from customer to investigate above error reported in the database's alert log. Please find the details below. Can you please investigate below error against EBSPRD and let me know the next course of action? Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x24] [PC:0x2587EB9, kglIsOwnerVersionable()+123] [flags: 0x0, count: 1] Errors in file /sw/oraebs/EBSPRD/db/tech_st/11.2.0/admin/EBSPRD_ebsprddb01/diag/rdbms/ebsprd/EBSPRD/trace/EBSPRD_ora_17713.trc  (incident=624688): ORA-07445 : exception encountered: core dump [kglIsOwnerVersionable()+123] [SIGSEGV] [ADDR:0x24] [PC:0x2587EB9] [Address not mapped to object] [] Incident details in: /sw/oraebs/EBSPRD/db/tech_st/11.2.0/admin/EBSPRD_ebsprddb01/diag/rdbms/ebsprd/EBSPRD/incident/incdir_624688/EBSPRD_ora_17713_i624688.trc Non critical error ORA-48913 caught while writing to trace file "/sw/oraebs/EBSPRD/db/tech_st/11.2.0/admin/EBSPRD_ebsprddb01/diag/rdbms/ebsprd/EBSP...

We received a mail from customer stating that - Heavy business activity today after 10AM and especially between 16:00 to 17:00 hrs where we see 47 archive log switch and 100GB archive generated today in today after 10AM. Using below sql query you can check in which interval more archive log generated in the database. col day for a12 set lines 1000 set pages 999 col "00" for a3 col "01" for a3 col "02" for a3 col "03" for a3 col "04" for a3 col "05" for a3 col "06" for a3 col "07" for a3 col "08" for a3 col "09" for a3 col "10" for a3 col "11" for a3 col "12" for a3 col "13" for a3 col "14" for a3 col "15" for a3 col "16" for a3 col "17" for a3 col "18" for a3 col "19" for a3 col "20" for a3 col "21" for a3 col "22" for a3 col ...

Today i got to know about legacy mode in data pump utility during export and import instead "exclude=statistics" parameter, i mentioned "statistics=none" parameter with expdp. Syntax: ======= expdp tables=s1.t1,s1.t2,s3.t3,s4.t4 directory=DATA_PUMP_DIR dumpfile=expdp_tables_28052014.dmp logfile=expdp_tables_28052014.log statistics=none export log output: =============== Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ;;; Legacy Mode Active due to the following parameters: ;;; Legacy Mode Parameter: "statistics=none" Location: Command Line, ignored. ;;; Legacy Mode has set reuse_dumpfiles=true parameter. But the export completed successfully. When i googled about Legacy Mode, i came to know the following facts. In 11gR2, Oracle decides to introduce Data Pump Legacy Mode in order to provide backward co...

Why row movement to be enabled before shrinking the segments? The shrinking is accomplished by moving rows between blocks,hence the requirement for row movement to be enabled for the shrink to take place. This can cause problem with ROWID based triggers. The shrinking process is only available for objects in tablespaces with automatic segment-space management enabled. Online Segment Shrink ================== Based on the recommendations from the segment advisor you can recover space from specific objects using one of the variations of the ALTER TABLE ... SHRINK SPACE command. -- Enable row movement. ALTER TABLE scott.emp ENABLE ROW MOVEMENT; -- Recover space and amend the high water mark (HWM). ALTER TABLE scott.emp SHRINK SPACE; -- Recover space, but don't amend the high water mark (HWM). ALTER TABLE scott.emp SHRINK SPACE COMPACT; -- Recover space for the object and all dependant objects. ALTER TABLE scott.emp SHRINK SPACE CASCADE; The shrink is accomplished...

Note: Before getting DDL, set long 50000 in sql prompt. then only you will get full ddl if it has more character To get DDL of the User: Syntax: set long 50000 (It will give you the full output when you set long for higher value) set pages 50000 set lines 300 SELECT dbms_metadata.get_ddl('USER','<schema_name>') FROM dual; To get DDL of the Tablespace: Syntax: select dbms_metadata.get_ddl('TABLESPACE','tablespace_name') from dba_tablespaces; To get DDL of the role granted to user: Syntax: SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema_name>') from dual; Object grants & system grants are important when it gives insufficient privileges error during recompilation of invalid objects after importing the db. You must find the grants for the user who has privilege on some other user's object. To get DDL of the object grants privileges to user: Syntax: SELECT DBMS_METADA...

Use the below sql query to export and import multiple schema: expdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp exclude=statistics logfile=expdpschemas120514.log impdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp logfile=impdpschemas120514.log sql query to export and import a schema: expdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log impdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log Parameter STATISTICS=NONE can either be used in export or import. No need to use the parameter in both. To export meta data only to get ddl of the schemas: expdp schemas=schema1,schema2,schema3 directory=TEST_DIR dumpfile=content.dat content=METADATA_ONLY exclude=statistics To get the DDL in a text file: impdp directory=TEST_DIR sqlfile=sql.dat logfile=sql.log dumpfil...