You may also use a Oracle command oerr to check out what the error is all about. oerr is only available on the Linux and Unix platforms.

You may also use a Oracle command oerr to check out what the error is all about. oerr is only available on the Linux and Unix platforms.

oracle@xps:~$ oerr ora 19809
19809, 00000, "limit exceeded for recovery files"
//*Cause: The limit for recovery files specified by the
//        DB_RECOVERY_FILE_DEST_SIZE was exceeded.
//*Action:The error is accompanied by 19804. See message 19804 for further
//        details.

oracle@xps:~$ oerr ora 19804
19804, 00000, "cannot reclaim %s bytes disk space from %s limit"
// *Cause: Oracle cannot reclaim disk space of specified bytes from the
//         DB_RECOVERY_FILE_DEST_SIZE limit.
// *Action: There are five possible solutions:
//          1) Take frequent backup of recovery area using RMAN.
//          2) Consider changing RMAN retention policy.
//          3) Consider changing RMAN archivelog deletion policy.
//          4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
//          5) Delete files from recovery area using RMAN.

JDK Installation on Linux

JDK Installation on Linux


This document describes  how to install JDK on linux operating system.

Host Name : mwlr.mserver.com
User Name : root

Get the software from  :-
http://www.oracle.com/technetwork/java/javase/downloads/jdk6-downloads-1637591.html

 Select operation system version and download it software to server.  Run as follows.

[root@mw Middleware]# ./jdk-6u33-linux-i586-rpm.bin
Unpacking...

its done.njy

How to Change mount point name in Linux

How to Change mount point name in Linux

Here is steps to change to mount point name /home to /m01.


[root@moshe bea]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_moshe-lv_root
                       50G  9.6G   38G  21% /
tmpfs                 1.9G  288K  1.9G   1% /dev/shm
/dev/sda1             485M   52M  408M  12% /boot
/dev/mapper/vg_moshe-lv_home
                      633G  198M  600G   1% /home



Step 1 :
 [root@moshe ~]# umount /home



Step 2:

[root@moshe ~]# mkdir /m01

Step 3:
 Modify /home to /m01 in the file /etc/fstab.

root@moshe ~]# vi /etc/fstab
[root@moshe ~]# cat /etc/fstab
#
# /etc/fstab
# Created by anaconda on Tue Feb 12 09:27:54 2013
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/vg_moshe-lv_root /                       ext4    defaults        1 1
UUID=93acbca6-0fdf-4798-9312-6303540cb185 /boot                   ext4    defaults        1 2
/dev/mapper/vg_moshe-lv_home /m01                   ext4    defaults        1
2

Drop Database in Oracle 10g

Drop Database in Oracle 10g

It has become easier to drop a database from Oracle 10g. Use the DROP DATABASE command to drop the database. It removes the datafiles, redologs, controlfiles and init parameter files.

$ sqlplus / as sysdba
SQL> Shutdown immediate;
SQL> Startup Mount Exclusive Restrict;
SQL> Drop Database;

RMAN: Archivelogs lost what to do

RMAN: Archivelogs lost

Problem: I have lost some of the archivelog files without taking backup. If I run the rman to backup available archive logs, it throws error that the archivelog_seq# is not available.

Solution: run the following command.

RMAN> change archivelog all validate;

Now you run the backup archivelog command. RMAN will backup the available archivelogs successfully.

FIX- ORA-04042: procedure, function, package, or package body does not exist

 FIX- ORA-04042: procedure, function, package, or package body does not exist


ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

Soln:

SQL> exec dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

How to determine row count for all tables in an Oracle Schema?

How to determine row count for all tables in an Oracle Schema?

Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Column d noprint new_value date_
Column u noprint new_value user_
Spool tmp
Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';',
    to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
from user_tables
order by table_name
/
Spool off
Spool count_&user_._&date_
@tmp.LST
Spool off

Fix-ORA-09817: Write to audit file failed

ORA-09817: Write to audit file failed
Space related issue.
ORA-09817, ORA-09945, Write to audit file failed
=========================================================================================================
=== ORA-09817: Write to audit file failed
=========================================================================================================

---------------------------------------------------------------------------------------------------------
--- ORA-09817: Write to audit file failed.
---------------------------------------------------------------------------------------------------------

The "ORA-09817-Write to audit file failed" error occurred while trying to connect oracle database.

[oracle@demoapp1 ~]$ sqlplus
/as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 1 11:39:16 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: /as sysdba
ERROR:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
ORA-09945: Unable to initialize the audit trail file
Linux-x86_64 Error: 28: No space left on device

Enter user-name:
[oracle@demoapp1 ~]$

---------------------------------------------------------------------------------------------------------
-- Solution:
---------------------------------------------------------------------------------------------------------


1) Check the audit file location in init.ora file.

cd $ORACLE_HOME/dbs

cat initDEMODB1.ora

[oracle@demoapp1 dbs]$ cat initDEMODB1.ora | grep adump
*.audit_file_dest='/u01/app/oracle/product/11.2.0/oradump/DEMODB/adump'
*.core_dump_dest='/u01/app/oracle/product/11.2.0/oradump/DEMODB/cdump'
[oracle@demoapp1 dbs]$

2) Check the free on that mount point.

[oracle@demoapp1 oracle]$ df -h /u01
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdb1              66G   63G     0 100% /u01
[oracle@demoapp1 oracle]$

3) Removed any old unwanted audit files.

4) Remove any old trace files.

5) Check the free space on /u01 mount point.

[oracle@demoapp1 11202]$ df -h /u01
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdb1              66G   58G  5.0G  93% /u01
[oracle@demoapp1 11202]$

6) Now try to connect oracle database as SYS user.

7) You should be able to connect to the database.

=========================================================================================================
=== End of File.
=========================================================================================================

Fix-ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

You will receive an error ORA-01589 when you open database
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> reset database;

if you open database with resetlogs, SCN number will be zero. In this situation
all previous backups will be invalid. You must full backup.

Fix- ORA-01578: ORACLE data block corrupted (file # 8, block # 13) ORA-01110

 If your data block is corrupted you will receive an error below.

Error:
ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
ORA-01110: data file 8: ‘/oracle/oradata/users.dbf’
for recover data block;

$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN>blockrecover datafile 8 block 13;

For Block-Level Media Recovery – Concept & Example To recover, we can give a specific backup set;

# recovery from backupset

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;

# recovery from image copy

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
      FROM DATAFILECOPY;

# recovery from backupset which have "FULL" tag

RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199
       FROM TAG = FULL;

During backup or “Validate Backup” command, RMAN finds corrupted blocks and writes to V$DATABASE_BLOCK_CORRUPTION view. When the RMAN recover the corrupt block then automatically updates this view. List of all the corruption of the past, can be viewed over V$BACKUP_CORRUPTION and V$COPY_CORRUPTION views.  If you run the following command,  RMAN will recover all the corrupted blocks in view V$DATABASE_BLOCK_CORRUPTION.

RMAN>BLOCKRECOVER CORRUPTION LIST
  RESTORE UNTIL TIME 'SYSDATE-10';

RMAN RECOVERIES DATA RECOVERY METHODS

RMAN RECOVERIES DATA RECOVERY METHODS:

1-  If you lost all data files ;
SQL> startup mount;
RMAN> restore database;
RMAN> recover database;
SQL> alter database open;

2- If you lost a tablespace;
 SQL> alter tablespace users offline;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter tablespace users online;

if you can not offline tablespace;
$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter database open;

3- if you lost a datafile;
SQL> alter database datafile '/oracle/oradata/users.dbf' offline;
RMAN> restore datafile '/oracle/oradata/users.dbf'
RMAN> recover datafile '/oracle/oradata/users.dbf'
SQL> alter database datafile '/oracle/oradata/users.dbf' online;

if you cannot offline datafile;
$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore datafile '/oracle/oradata/users.dbf';
RMAN> recover datafile '/oracle/oradata/users.dbf';
SQL> alter database open;

4-  if you lost your controlfiles;
$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> set dbid = 3970640872;
RMAN> restore controlfile;
SQL> alter database mount;
SQL> alter database open;


5- May be a special situation. You need to incomplete recovery
A. Time-Based incomplete recovery;

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
SQL> alter database open resetlogs;

B. SCN-Based incomplete recovery;

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000;
SQL> alter database open resetlogs;

C. Archive log sequence based incomplete recovery;

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until sequence 9923;
RMAN> recover database until sequence 9923;
SQL> alter database open resetlogs;

6-  if you need some archive logs in your backup

$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
OR
RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')
UNTIL TIME 'SYSDATE';


8- if you have a image copy backup and your datafile number 2 has problems then you
can switch datafile number2 to image copy.

RMAN>sql ‘alter database datafile 2 offline’;
RMAN>switch datafile 2 to copy;
RMAN>recover datafile 2;
RMAN>sql ‘alter database datafile 2 online’;

Refer this: this http://taliphakanozturken.wordpress.com/tag/ora-01589/

TO GENERATE AWR AND ADDM REPORTS IN ORACLE:

 TO GENERATE AWR AND ADDM REPORTS IN ORACLE:
To issue AWR scripts:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

OR

@?/rdbms/admin/awrrpt.sql

save it in Enter value for report_name: awr_report.txt

you can check the given file name in cd /$ORACLE_HOME/rdbms/admin
ls -lrt

to move from 1 place to another

mv awr_report.txt /home/oracle

to read
 more awr_report.txt

Same for addm:

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

TO FIND FLASH RECOVERY AREA IN ORACLE

TO FIND FLASH RECOVERY AREA IN ORACLE
select *from v$recovery_file_dest;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area
 1.7180E+10 1.7126E+10               0          11

To check the sga size and pga size

To check the sga size and pga size


SQL> show parameter target;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target             integer     0
db_flashback_retention_target         integer     1440
fast_start_io_target             integer     0
fast_start_mttr_target             integer     0
pga_aggregate_target             big integer 382M
sga_target                 big integer 1152M

SQL> select value from v$pgastat where name='maximum PGA allocated';

     VALUE
----------
 164190208

TO CHECK WHETHER THE DB IS USING SPFILE OR PFILE

TO CHECK WHETHER THE DB IS USING SPFILE OR PFILE

SQL>  SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
       FROM sys.v_$parameter WHERE name = 'spfile';  

Init F
------
SPFILE

EXPORT ONLY TABLES IN ORACLE

EXPORT ONLY TABLES
exp sys/oracle file=home/oracle/CP_own.dmp log=/home/oracle/CP_own.log tables=cp_own.ds_ps_ps_pb,cp_own.ds_ps_pb rows=yes indexes=yes

exp sys/oracle file=home/oracle/CP_own.dmp log=/home/oracle/CP_own.log tables=cp_own.ds_ps_ps_pb,cp_own.ds_ps_pb rows=yes indexes=yes

FIX-ORA-39002: invalid operation ORA-39165: Schema IDIVE was not found.

 FIX-ORA-39002: invalid operation
ORA-39165: Schema IDIVE was not found.

impdp system DUMPFILE=xxx.sql LOGFILE=xxxx.log schemas=YYY remap_schema=XXX:YYY table_exists_action=REPLACE


When there is a new dump from production user needs only schema like ide 
use this command ide is source idive is target

impdp manjit/manjit directory=manjit_dp dumpfile=dp_31Dec121315.dmp logfile=dp_31Dec121315.log remap_schema=idi:idive

OR THE PERFECT1

     impdp directory=dpump file=dp_22Oct121315.dmp log=ms2.log schemas=msmd_ive remap_schema=msmd_ive:rms

SOLUTION: REMAP_SCHEMA=IDI_IVE:IDIIVE

Database import from higher version11g to lower version10g in oracle

Database import from higher version11g to lower version10g in oracle:

 $expdp manjit/manjit directory=marketing_dp dumpfile=marketing.dmp logfile=marketing.log schemas=marketing version=10.2

 this is for verson=10.2 to import to macosx10g

 On target os:


SQL> create or replace directory marketing_dp as '/home/oracle';

Directory created.

SQL> select directory_name from all_directories;

SQL> grant read,write on directory marketing_dp to manjit;

 impdp manjit/manjit directory=marketing dumpfile=marketing.dmp logfile=marketing.log REMAP_SCHEMA=marketing:ra

remap, marketing schema is source os  ra schema is target os

DATA PUMPS-Get start"

Getting Started: data pumps'

CONN / AS SYSDBA

ALTER USER manjit IDENTIFIED BY manjit ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
MARKETING_DP

GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

Table Exports/Imports:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

Schema Exports/Imports

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log find / -name ad_dir

Database Exports/Imports

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

Network Exports/Imports (NETWORK_LINK)

CONN / AS SYSDBA
GRANT CREATE DATABASE LINK TO test;

CONN test/test
CREATE DATABASE LINK remote_scott CONNECT TO scott IDENTIFIED BY tiger USING 'DEV';

IF YOU GET THIS MESSAGE IN TERMINAL ON MACOSX

IF YOU GET THIS MESSAGE IN TERMINAL ON MACOSX

 ManjuS$ ssh root@10.10.11.23
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@    WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!     @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE

Solution:

GO TO

 vi .ssh/known_hosts

AND REMOVE THE IP AND THE DETAILS