Common Error And Solutions

1. Oracle Memory Sizing

RAM = 4GB

Databases =4

-----------For Old Year

Connect to old year database SQLPLUSW sys/oracle@apx0910srv AS SYSDBA

Create pfile from spfile;

show parameter sga

show parameter pga

alter system set sga_target=500M;

alter system set sga_max_size=500M scope=spfile;

alter system set pga_aggregate_target=100M;

shutdown immediate

startup

----------------For Current and Last Year

Connect to old year database SQLPLUSW sys/oracle@apx1213srv AS SYSDBA

SQLPLUSW sys/oracle@apx1112srv AS SYSDBA

Create pfile from spfile;

show parameter sga

show parameter pga

alter system set sga_target=800M;

alter system set sga_max_size=800M scope=spfile;

alter system set pga_aggregate_target=300M;

shutdown immediate

startup

Note1: For 32 bit server, Max Oracle Memory Limit 2*32bits (1.7GB)

Don't give more that 1.5 gb to sga otherwise oracle will not started

Note2: Please disable all OracleJobScheduler, OracleDBConsole services from services.msc

Note3: Stop Archiving for old year databases

2. Change Utl_file_dir location

create pfile from spfile;

Alter system set utl_file_dir='D:\ldoutput' Scope=spfile ;

shut immediate

startup

3. How to set default tablespace as USR ?

select * from database_properties where property_name like '%TABLESPACE';

Alter Database default tablespace "USR" ;

4. At the time of LD Login it shows error message "Oracle Not Available" or Shared Memory Realm does not exist ?

Manually start database

sqlplusw sys@apx1213srv as sysdba

startup

5.for ld digital

sqlplusw sys@apx1213srv as sysdba

CREATE DIRECTORY LDDGITAL AS 'd:\ldoutput\Lddigital';

grant read, write on directory LDDIGITAL to ldbo;

6. If any entry of LISTENER_APX1213 in tnsnames.ora then remove it.

show parameter listener

create pfile from spfile;

alter system set local_listener='' scope=spfile;

shut immediate

start

7. if listener shows error

run listener services from windows command prompt to view right status

lsnrctl status

lsnrctl stop

lsnrctl start

8. Master Transmission Performance

1) delete from CLIENTSCANNEDIMAGE; before master transmission for better performance

2) create table from sql

3) later imp clientscannedimage table

exp ldbo@ldbo@apx1112srv file='d:\expdp1112\tblclientscan.dmp' log='d:\expdp1112\tblclientscan.log' TABLES= CLIENTSCANNEDIMAGE

imp ldbo@ldbo@apx1213srv file='d:\expdp1112\tblclientscan.dmp' log='d:\expdp1112\tblclientscan1.log'

9. if Space Issue, Stop Archiving at time of import or other long insert / update / transmission process. Later enable

Disable Archiving

sqlplusw sys@apx1213srv as sysdba

shut immediate

startup mount

alter database noarchivelog;

alter database open;

Enable Archiving

shut immediate

startup mount

alter database archivelog;

alter database open;

10. unable to extend <> segment by <> in tablespace USR

ALTER TABLESPACE USR

ADD DATAFILE 'D:\APXD1213\USERS2.ORA' SIZE 2048M

AUTOEXTEND ON

NEXT 1024M

MAXSIZE unlimited;

11. ora-01536 space quota exceed

ALTER USER LDBO QUOTA UNLIMITED on USR;

ALTER USER LDBO QUOTA UNLIMITED on INDX;

12. ORA-38029 object statistics are locked

select 'exec DBMS_STATS.UNLOCK_TABLE_STATS('''||owner||''','''||table_name||''');' from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

RUN THE OUTPUT RESULT

13. ORA-25153 Temporary Tablespace is Empty

ALTER TABLESPACE temporary ADD tempfile 'D:\APX1213D\TEMP01.ORA' SIZE 1000M REUSE AUTOEXTEND ON NEXT 500M MAXSIZE unlimited;

14. ORA-20014: %s Brokerage cannot be Run. ~-27477~ORA-27477: "LDBO.C_BSENM1112177_J" already exists

BEGIN

DBMS_SCHEDULER.DROP_JOB (

job_name => 'C_BSENM1112177_J');

END;

/

15. Tablespace, Datafiles Details

select tablespace_name,block_size,INITIAL_EXTENT/1024,MAX_EXTENTS/1024/1024/1024,segment_space_management,bigfile from dba_tablespaces;

select file_name,tablespace_name,bytes/1024/1024,autoextensible,increment_by from dba_temp_files;

select file_name,tablespace_name,bytes/1024/1024,MAXbytes/1024/1024,autoextensible,increment_by from dba_data_files;

16. Partitioned Table Details

select table_name from user_part_tables;

select name,column_name from user_part_key_columns;

select * from user_tab_partitions;

17.

If following ODBC error (Mostly occur at Large DB / Partitioned Table Clients)

Most Critical Error In Oracle ORA-00600

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00600: internal error code, arguments: [15735], [2168], [2152], [], [], [], [], []

solution:

sqlplusw sys@apx1213srv as sysdba

create pfile from spfile;

show parameter parallel_automatic_tuning

alter system set parallel_automatic_tuning=TRUE scope=spfile;

alter system set parallel_execution_message_size=4096 scope=spfile;

shut immediate

startup

18. Enable / Disable Trigger

Alter Trigger Enable ;

Alter Trigger Disable ;

19. Enable / Disable Constraint

Alter Table Enable constraint ;

Alter Table Disable constraint ;

20. ORA-14402 updating partition key column would cause a partition change

ALTER TABLE enable row movement;

21. COMPILE OBJECT ERROR

ORA-02019: connection description for remote database not found

TABLE VIEW DOES NOT EXIST

ADD DATABASE LINK

CREATE PUBLIC DATABASE LINK "LNK_RAKSHAK"

CONNECT TO "LDBO" IDENTIFIED BY password USING 'apx1213srv';

CREATE PUBLIC DATABASE LINK "LNK_CCM"

CONNECT TO "LDBO" IDENTIFIED BY password USING 'apx1213srv';

CREATE DATABASE LINK "LNK_DIGITAL"

CONNECT TO "LDBO" IDENTIFIED BY password USING 'apx1213srv';