RMAN-03009: ORA-19502: ORA-27063:

We got the following error when backing up an 11.2.0.4 database to an external disk. From the below error it is clearly showing there is no space left on the disk to hold new backup pieces.

Cause:1. The cleanup jobs is not running which deleted the old backups pieces.
           2. Some one increase the value for control file recovery window .
           3.Alert mechanism is not properly set for the file system .

Solution:1. connect to rman
         $rman target /
         rman> report obsolete;
if there is any obsolete backup found as a output of the above command .Run the below command to delete those backup pieces.
rman > delete obsolete;
This are the backups whose information are no longer available in controlfile.

2.If by deleting obsolete backup also space is not released than delete some old backup pieces but make sure those backup pieces are copied to tape .
3. Or move some backup pieces to other location where you have space.

Impdp ORA-39111 dependent object type object_grant skipped

During a Data Pump import job, a dependent object is being skipped because its base object already existed. Use import parameter in parfile
    
        table_exists_action=replace

So it replace only the tables not the other  independent objects. 

Note: Table exists action parameter work without any issue only when there is no foreign key constraints in the import tables.

Solution:
To run a clean import drop all the objects of the schema and start import again.To drop all the objects at a time and for  complete import  steps  please refer the links.

http://swadhinur.blogspot.com/2015/12/schema-refresh-step-by-step.html

ORA-00020 maximum number of processes exceeded

We may come across this kind of situation in production when there is no other session are allowed to create in the database.Because database already reached to its maximum limits of process thresholds value.

error:ORA-00020: maximum number of processes (1100) exceeded
        ERROR at line 1:
        ORA-01012: not logged on

There are two possible reason behind ora-00020 error.
1.If process limit is set to few value and total active connection reach the thresholds value than we received the same error .
2 .Zombie process , process those are created from a parent process and not releasing his resources after execution also. In linux there are no child process(PID) is created from parent process(PPID) and after executed those child not informed to his parent process about his task execution. So parent process is keep waiting for there acknowledged .Can we kill those zombie process by KILL -9 PID. The answer is no because process are already dead. You need to kill the parent process to kill all the zombie process.

Here is the example of zombie process , where 8559 is the parent process and waiting for acknowledgement from the other child process under the column PID.

               PID   PPID
   oracle  6363  8559   0 12:19:20 ?           0:01 oracleislam02 (LOCAL=NO)
  oracle 12829  8559   0 12:36:09 ?           0:34 oracleislam02 (LOCAL=NO)
  oracle 21644  8559   0 11:38:25 ?           0:02 oracleislam02 (LOCAL=NO)
  oracle  6308  8559   0 12:19:20 ?           0:01 oracleislam02 (LOCAL=NO)
  oracle  6295  8559   0 12:19:20 ?           0:01 oracleislam02 (LOCAL=NO)
  oracle 12764  8559   0 12:36:09 ?           0:02 oracleislam02 (LOCAL=NO)
  oracle    81  8559   0 10:38:22 ?           0:06 oracleislam02 (LOCAL=NO)
  oracle  6341  8559   0 12:19:20 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle  1960  8559   0 07:55:51 ?           0:14 oracleislam02 (LOCAL=NO)
  oracle  6347  8559   0 12:19:20 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle  6408  8559   0 12:19:21 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle 12810  8559   0 12:36:09 ?           0:02 oracleislam02 (LOCAL=NO)
  oracle 12333  8559   0 12:35:22 ?           0:01 oracleislam02 (LOCAL=NO)
  oracle 19031  8559   0 10:06:35 ?           0:09 oracleislam02 (LOCAL=NO)
  oracle  6286  8559   0 12:19:19 ?           0:01 oracleislam02 (LOCAL=NO)
  oracle  8461  8559   0 08:14:38 ?           2:51 oracleislam02 (LOCAL=NO)
  oracle  6389  8559   0 12:19:21 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle  6423  8559   0 12:19:21 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle 27610  8559   0 09:07:24 ?           4:21 oracleislam02 (LOCAL=NO)
  oracle 12801  8559   0 12:36:09 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle 12772  8559   0 12:36:09 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle 12279  8559   0 12:35:21 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle  6379  8559   0 12:19:20 ?           0:02 oracleislam02 (LOCAL=NO)
  oracle 12311  8559   0 12:35:21 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle 12823  8559   0 12:36:09 ?           0:15 oracleislam02 (LOCAL=NO)
  oracle 21618  8559   0 10:14:42 ?           1:40 oracleislam02 (LOCAL=NO)
  oracle 12302  8559   0 12:35:21 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle 12285  8559   0 12:35:21 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle 12806  8559   0 12:36:09 ?           0:04 oracleislam02 (LOCAL=NO)
  oracle  6454  8559   0 12:19:21 ?           0:02 oracleislam02 (LOCAL=NO)
  oracle  6335  8559   0 12:19:20 ?           0:00 oracleislam02 (LOCAL=NO)
  oracle 12339  8559   0 12:35:22 ?           0:02 oracleislam02 (LOCAL=NO)
  oracle  6372  8559   0 12:19:20 ?           0:35 oracleislam02 (LOCAL=NO)
  oracle  6315  8559   0 12:19:20 ?           0:00 oracleislam02 (LOCAL=NO)


But we are DBA and we are working in database . So when we executing a SQL script and after the execution got complete the session become inactive due to some  reason . This Inactive session is a count of no session connecting to the database and there is a corresponding process is created in the OS level that is also keep waiting ....this cause the problem of no session exceeded in the database.

Solution: As we can't  connect to the database because its already reached its maximum limits. So only way to connect the DB is connect without creating a session and kill all the inactive session .


sqlplus /nolog
@> set _prelim on
@> conn  /  as sysdba

Set  _prelim on , will connect to the database without creating a session.

: Kill all the inactive session at a time
sql> select 'alter system kill session '''||sid||','||serial#||''' IMMEDIATE;' from v$session where sTatus= 'INACTIVE' and USERNAME NOT IN ('SYS','SYSTEM');

Schema Refresh step by step

Below are the details step for schema refresh . The below steps are very helpful when you want to refresh schemas in QA/DEV and want to keep all the previous grants and privileges after refresh .

1. Take the backup of the schema in target database.


expdp directory=<directory_name> dumpfile=backup_%U.dmp logfile=Backup.log parllel=<n> compression=all
schemas=Schema_name

2. sys_privs.sql :
---------------

set echo off
set head off
set pagesize 0
set feedback off
set linesize 200
spool sys_privs.sql
select 'grant '||privilege||' to '||grantee||' ; '  from dba_sys_privs where grantee in ('schema_name');
spool off

3.owner.sql :
-----------

spool owner.sql
select 'grant '||privilege||' on '||OWNER||'.'||table_name||' to  '||grantee||' ; ' from dba_tab_privs WHERE OWNER in ('schema_name');
spool off

4.pre_refresh_role_privs.sql :
----------------------------

set echo off
set head off
set pagesize 0
set feedback off
set linesize 200
spool pre_refresh_role_privs.sql
select 'grant '||granted_role||' to '||grantee||' ; '  from dba_role_privs where grantee in ('schema_name');
spool off

5.Check the object count in the database under that schema
 SELECT object_type,COUNT(*) FROM DBA_OBJECTS WHERE OWNER in ('schema_name') group by object_type ;

6. check the table count
SELECT COUNT(*) FROM DBA_tables WHERE OWNER in ('schema_name');

7.drop all the object under that schema
select 'drop '||object_type||' '||owner||'."'||object_name||'";' from dba_objects where owner in ('schema_names');

8.drop all the tables under that schema
select 'drop table  '||owner||'.'||table_name||' cascade constraints;' from dba_tables where owner in('schema_names');

9.purge the recyclebin
select 'purge table '||owner||'."'||object_name||'";' FROM dba_RECYCLEBIN where owner='schema_name' ;

10.now import data into target database from source dumpfile.
 impdp dumpfile=<dumpfile_name> logfile=import.log directory=<directory_name> schemas=schema_name parallel=<n>

11.check the count of objects after refresh for each schema
SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER in ('schema_name');

12.check the table count
SELECT COUNT(*) FROM DBA_tables WHERE OWNER='schema_name';

13.run the spool file
@sys_privs.sql

14. run the spool file
@owner.sql

15.run the spool file
pre_refresh_role_privs.sql

-------------- Schema refresh is complete ----------------------

Table Refresh step by step

Below are details step by step for table refresh . Points to be remember
a. This steps is very useful when we are refreshing tables that has referential constraints.
b. If there is no referential constraints we can use table_exists_action=replace parameter during import.


1. Check the size of the table in the source and target.
And that much space is available in the mount point or not.

sql> select segment_name,segment_type,bytes/1024/1024
 from dba_segments where segment_type='TABLE' and segment_name='<table_name>';

2.Take the backup of the table in the target database.
 $expdp dumpfile=backup_Table_%U.dmp logfile=backup_Table.log directory=<directory name> tables=schema.tablename parallel=<n> compression=all

3.create spool file for Disable the contraints for the tables.
sql>spool disable_contraints.sql

sql> set lines 200 pages 300
   select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where table_name in('tables_name');
sql> spool off

4.create spool for enable the constraints.

sql>spool enable_contraints.sql

sql> set lines 200 pages 300
   select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where table_name in('tables_name');

 sql> spool off;

5.alter session set current_schema=schema_name

6. run @disable_contraints.sql

7.Either Drop the table in the target database or use TABLE_EXISTS_ACTION=REPLACE  parameter during import 

sql> Drop table schema.table_name;

8.Import the dump taken from source.

$impdp dumfile=export_%U.dmp logfile=import.log directory=<directory_name> tables=schema.tablename parallel=<n>

 --monitor the  log--

9.check the row count in the target after refresh.

sql> Select count(*) from schema.tablename;

10.check the row count in the source database . 

sql> Select count(*) from schema.tablename;

-- count should be same.---

11.alter session set current_schema=schema
run @enable_contraints.sql


------------------------------ table refresh is complete------------------------