Export errors EXP-00008: ORA-04067: ORA-06508:ORA-06512:ORA-06512:EXP-00083:

While trying to export the db i received the below error

EXP-00008: ORACLE error 4067 encountered
ORA-04067: not executed, package body "SYS.DBMS_REPCAT_UTL" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REPCAT_UTL"
ORA-06512: at "SYS.DBMS_REPCAT_EXP", line 45
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling

select grantee,owner,table_name,privilege from dba_tab_privs where grantee='EXPORT_USER';

Solution 

The workaround for the problem is to grant the two missing privileges explicitly to the user doing the export:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO ;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO ;


There are two execute privileges missing in the DBA role and  the EXP_FULL_DATABASE role.

There is no fix in this bug because the export utility is not longer 
supported in 11g and should be replaced by the Data Pump Export. 

Enabling archivelog mode

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

show parameter archive 

will tell u the archive destination etc ... 

so , create spfile from pfile incase of the db running onthe pfile means, then shutdown startup mount and follow the steps

SQL> startup mount
ORACLE instance started.

Total System Global Area  184549376 bytes
Fixed Size                  1300928 bytes
Variable Size             157820480 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

ou can see here that we put the database in ARCHIVELOG mode
by using the SQL statement "alter database archivelog", but 
Oracle won't let us do this unless the instance is mounted but
not open.  To make the change we shutdown the instance, and then
startup the instance again but this time with the "mount" option
which will mount the instance but not open it.  Then we can
enable ARCHIVELOG mode and open the database fully with the "alter
database open" statement.

There are several system views that can provide us with information reguarding archives, such as:

V$DATABASE

Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.

V$ARCHIVED_LOG

Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST

Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.

V$ARCHIVE_PROCESSES

Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG

Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.

V$LOG

Displays all redo log groups for the database and indicates which need to be archived.

V$LOG_HISTORY

Contains log history information such as which logs have been archived and the SCN range for each archived log.

Using these tables we can verify that we are infact in ARCHIVELOG mode:
SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG SQL> select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;

Last analyzed dates in oracle

Description

Lists the last analyze date for tables, indexes and partitions. This script should be used to determine is any stats are out of date. 

The Last_analyzed column being NULL indcates no stats are present

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set pages 200

col index_owner form a10
col table_owner form a10
col owner form a10

spool checkstat.lst

PROMPT Regular Tables

select owner,table_name,last_analyzed, global_stats
from dba_tables
where owner not in ('SYS','SYSTEM')
order by owner,table_name
/

PROMPT Partitioned Tables

select table_owner, table_name, partition_name, last_analyzed, global_stats
from dba_tab_partitions
where table_owner not in ('SYS','SYSTEM')
order by table_owner,table_name, partition_name
/

PROMPT Regular Indexes

select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner not in ('SYS','SYSTEM')
order by owner, index_name
/

PROMPT Partitioned Indexes

select index_owner, index_name, partition_name, last_analyzed, global_stats
from dba_ind_partitions
where index_owner not in ('SYS','SYSTEM')
order by index_owner, index_name, partition_name
/

spool off

ORA-12547: TNS:lost contact ( From Rman as well as from Sql plus

Users having dba role in Oracle database

SQL> desc dba_role_privs
Name         Null?    Type
------------ -------- ------------
GRANTEE               VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION          VARCHAR2(3)
DEFAULT_ROLE          VARCHAR2(3)

select * from dba_role_privs where granted_role='DBA';

 GRANTEE   GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS       DBA          YES YES
SYSTEM    DBA          YES YES

Oracle import process hanged !! how to handle

Hi

Some times, i use to do a dev refreshes without checking the pre- requisites like size , object counts etc .. but i will pay my time for the same when i see the import taking long time than i expected.

so the below query can be used to see what actually the import process is currently doing inside the db , like which table is getting loaded how many rows are getting updated till  now and we can calculate how long the processs could take etc .. it is a use full quey which i frequently use


select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;


and basically check if the imp process still exist on the server

ps -ef | grep imp

oracle user password expired locked

In 11 r2 , were facing this user lock and expire issue frequently to override it


select profile from dba_users where username ='xxxx';

alter profile default limit passsword_life_time unlimited;
alter profile default limit failed_login_attempts unlimited;

select resource_name,limit from dba_profiles where profile='';