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

EASY SIMPLE STEPS TO INSTALL ORACLE11G ON LINUX64 BIT.

EASY SIMPLE STEPS TO INSTALL ORACLE11G ON LINUX64 BIT.

Hardware Requirements

To check out your recommended hardware requirements, login to the Linux server

     How much memory:

$ grep -i memtotal /proc/meminfo

        results: should be at least 1GB

     How much swap:

$ grep -i swaptotal /proc/meminfo

        results:
       
        - should be 1.5 the size of memtotal if memtotal < 2GB

             - should be equal to memtotal if memtotal is between 2GB and 8GB

             - should be .75 the size of memtotal if memtotal > 8GB

    What processor type:

$ grep "model name" /proc/cpuinfo

        results: informational
   
    How much available RAM and swap:

$ free

        results: informational

    How much shared memory available:

$ df -k /dev/shm

        results: informational

        purpose: The shared memory should be sized to be at least the greater of MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer.

    How much disk space in /tmp:

$ df -k /tmp

        results: should have at least 400MB available

    How much disk space for Oracle binaries:

$ df -k

        results: Depending on installation type you should have between 1.5GB and 3.5GB

Software Requirements

To check out your recommended software requirements,login to linux server.

   What distribution and version of Linux is installed:

$ cat /proc/version

        results:  Linux version 2.6.32-220.2.1.el6.x86_64 (mockbuild@c6-x8664-build.centos.org) 

(gcc version 4.4.6 20110731 (Red Hat 4.4.6-3) (GCC) ) 

#1 SMP Fri Dec 23 02:21:33 CST 2011


    What version of the kernel is installed:

$ uname -r

        results: 2.6.32-220.2.1.el6.x86_64

    Ensure that the following packages are installed:

$ rpm -q compat-libstdc++-33.x86_64 binutils elfutils-libelf elfutils-libelf-devel 
$ rpm -q glibc glibc-common glibc-devel glibc-headers gcc gcc-c++ libaio-devel 
$ rpm -q libaio libgcc libstdc++ libstdc++ make sysstat unixODBC unixODBC-devel

    if some of this packages are missing then install them
  

$ sudo yum install compat-libstdc++-33.x86_64 binutils elfutils-libelf elfutils-libelf-devel 
$ sudo yum install glibc glibc-common glibc-devel glibc-headers gcc gcc-c++ libaio-devel 
$ sudo yum install libaio libgcc libstdc++ libstdc++ make sysstat unixODBC unixODBC-devel

Install the following packages if they are not already present.

# From Oracle Linux 6 DVD
cd /media/cdrom/Server/Packages
rpm -Uvh binutils-2*x86_64*
rpm -Uvh glibc-2*x86_64* nss-softokn-freebl-3*x86_64*
rpm -Uvh glibc-2*i686* nss-softokn-freebl-3*i686*
rpm -Uvh compat-libstdc++-33*x86_64*
rpm -Uvh glibc-common-2*x86_64*
rpm -Uvh glibc-devel-2*x86_64*
rpm -Uvh glibc-devel-2*i686*
rpm -Uvh glibc-headers-2*x86_64*
rpm -Uvh elfutils-libelf-0*x86_64*
rpm -Uvh elfutils-libelf-devel-0*x86_64*
rpm -Uvh gcc-4*x86_64*
rpm -Uvh gcc-c++-4*x86_64*
rpm -Uvh ksh-*x86_64*
rpm -Uvh libaio-0*x86_64*
rpm -Uvh libaio-devel-0*x86_64*
rpm -Uvh libaio-0*i686*
rpm -Uvh libaio-devel-0*i686*
rpm -Uvh libgcc-4*x86_64*
rpm -Uvh libgcc-4*i686*
rpm -Uvh libstdc++-4*x86_64*
rpm -Uvh libstdc++-4*i686*
rpm -Uvh libstdc++-devel-4*x86_64*
rpm -Uvh make-3.81*x86_64*
rpm -Uvh numactl-devel-2*x86_64*
rpm -Uvh sysstat-9*x86_64*
rpm -Uvh compat-libstdc++-33*i686*
rpm -Uvh compat-libcap*
cd /
eject

     also install unzip package:

$ sudo yum install unzip 

Install Steps

Hosts File

The "/etc/hosts" file must contain a fully qualified name for the server.

<IP-address>  <fully-qualified-machine-name>  <machine-name>

For example.

127.0.0.1       localhost.localdomain  localhost
192.168.0.181   ol6-112.localdomain    ol6-112

Manual Setup

Oracle recommend the following minimum parameter settings.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

The current values can be tested using the following command.

/sbin/sysctl -a | grep <param-name>

Add or amend the following lines in the "/etc/sysctl.conf" file.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Add the following lines to the "/etc/security/limits.conf" file.

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  4096
oracle              hard    nofile  65536
oracle              soft    stack   10240

 Edit /etc/pam.d/login:

$ sudo nano /etc/pam.d/login
and add the following depending on the architecture:

session required /lib64/security/pam_limits.so
session required pam_limits.so

Create the new groups and users.

groupadd -g 501 oinstall
groupadd -g 502 dba
groupadd -g 503 oper
groupadd -g 504 asmadmin
groupadd -g 506 asmdba
groupadd -g 505 asmoper

useradd -u 502 -g oinstall -G dba,asmdba,oper oracle
passwd oracle

Note. We are not going to use the "asm" groups, since this installation will not use ASM.

Additional Setup

Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=permissive

Once the change is complete, restart the server.
If you have the Linux firewall enabled, you will need to disable or configure it,

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Login as root and issue the following command.

xhost +<machine-name>

Login as the oracle user and add the following lines at the end of the ".bash_profile" file.

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=canopus.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Installation

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

DISPLAY=<machine-name>:0.0; export DISPLAY

Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.

./runInstaller

Note:
Select only database Software installation option ,later you can create the database using DBCA. 

Issues i have faced during installation can be fixed:

During oralle installation kernel parameters error

[root@canopus ~]# /tmp/CVU_11.2.0.1.0_oracle/runfixup.sh
Response file being used is :/tmp/CVU_11.2.0.1.0_oracle/fixup.response
Enable file being used is :/tmp/CVU_11.2.0.1.0_oracle/fixup.enable
Log file location: /tmp/CVU_11.2.0.1.0_oracle/orarun.log
Setting Kernel Parameters...
kernel.shmmax = 536870912






DURING INSTALLATION OF ORACLE LAUNCH SQLPLUS FAILS




[oracle@~]$ sqlplus

-bash: sqlplus: command not found

Solution:

Go to ORACLE_HOME (Path oracle is installed)

[oracle@~]$ cd /opt/oracle/product/11.2.0/dbhome_1/

[oracle@ dbhome_1]$ export ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1

[oracle@dbhome_1]$ cd bin/

[oracle@ bin]$ ./sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 20:53:19 2011

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

To permanent  fix this add following entries into .bash_profile file at /home/oracle location.

ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export PATH



to set the display


So as my_login execute in shell:
xhost +

As oracle execute:
export DISPLAY=:0
./runInstaller

If this don't help then as root execute:
# gdmconfig
Go to "Security" section and there is something like "Always disallow TCP ..." this checkbox must be unchecked.
Sqlplus
sexport ORACLE_BASE=/u01/home
export ORACLE_HOME=/u01/9i
export PATH=$PATH:$ORACLE_HOME/bin

ALternate


ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1;
export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH; 
export PATH

ORACLE_SID=ora11
export ORACLE_SID


xhost +
su - oracle
dbca


Refrence:
http://www.oracle-base.com/articles/11g/oracle-db-11gr2-installation-on-oracle-linux-6.php
http://eduardo-lago.blogspot.in/2012/01/how-to-install-oracle-11g-database.html

 

FIX-SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

FIX-SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory



While connecting to Oracle database using Sql*plus it fails with error SP2-0750 along with Message file sp1<lang>.msb not found.
C:\> sqlplus / as sysdba
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
Cause of the Problem
The problem happened due to incorrect settings of ORACLE_HOME environmental variable.

On Windows you can issue path command to check the environmental variable settings and on Unix issue env command or check the .bash_profile.

C:\Documents and Settings\User>path
PATH=D:\oracle\product\10.2.3\av_agent_1\bin;C:\Program Files\Common Files\NetSarang;
D:\oracle\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;
C:\Program Files\Ringz Studio\Storm Codec\QTSystem\;C:\Program Files\SSH Communications Security\SSH Secure Shell

Solution of the Problem
Note that, ORACLE_HOME environmental variable is up to before bin directory. So correct it. On windows set it using set comamnd and on Unix set it using export command.

C:\Documents and Settings\User>set ORACLE_HOME=D:\oracle

C:\Documents and Settings\User>set PATH=%ORACLE_HOME%;\%ORACLE_HOME%\bin;%PATH%

C:\Documents and Settings\User>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 11 11:23:05 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

FIX-ORA-00257

 FIX-ORA-00257 

[oracle@adcp ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 22 12:28:55 2013

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


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

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1195
Next log sequence to archive   1195
Current log sequence           1197

SQL> sho parameter db_recovery_file;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string     /oracle_data/u01/app/oracle/fl
                         ash_recovery_area
db_recovery_file_dest_size         big integer 10G

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@adcp ~]$ df -h /oracle_data/u01/app/oracle/flash_recovery_area
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup_Oradata-LogVol_oracle
                      900G  110G  746G  13% /oracle_data

[oracle@adcp ~]$ du -sh /oracle_data/u01/app/oracle/flash_recovery_area
8.7G    /oracle_data/u01/app/oracle/flash_recovery_area

[oracle@adcp ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 22 12:31:21 2013

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


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

SQL> alter system set db_recovery_file_dest_size = 20g;

System altered.


OR
 
We can use following steps for this
1. find the location of Archive destination by
 show parameter archive_dest

 lets say it provide  LOCATION=/u10/oradata/mydb/arch

2. move some files to some other location using os command
 cd /u10/oradata/mydb/arch
 mv /u10/oradata/mydb/arch/* /u15/oradata/mydb/arch-bkp/


Or it can be done using RMAN also

rman target /

RMAN> backup archive log all format '/u15/oradata/mydb/arch-bkp';

RMAN> delete archive until time 'trunc(sysdate)';

SCRIPT TO CHECK THE CONCURRENT USERS IN ORACLE:

 Script to check the concurrent users


set linesize 90
set pagesize 60
COLUMN SU FORMAT A8 HEADING 'ORACLE|USER ID' JUSTIFY LEFT
COLUMN OSU FORMAT A8 HEADING 'SYSTEM|USER ID' JUSTIFY LEFT
COLUMN STAT FORMAT A8 HEADING 'SESSION|STATUS' JUSTIFY LEFT
COLUMN SSID FORMAT 999999 HEADING 'ORACLE|SESSION|ID' JUSTIFY RIGHT
COLUMN SSER FORMAT 999999 HEADING 'ORACLE|SERIAL|NO' JUSTIFY RIGHT
COLUMN SPID FORMAT A9 HEADING 'ORACLE|SESSION|ID' JUSTIFY RIGHT
COLUMN TXT FORMAT A25 HEADING 'CURRENT STATEMENT' JUSTIFY CENTER WORD
COLUMN LOGTIME FORMAT A10 HEADING 'LOGIN|TIME' JUSTIFY RIGHT

SELECT
S.USERNAME SU,
S.OSUSER OSU,
to_char(S.LOGON_TIME,'MM-DD-YYYY HH24:MI:SS') LOGTIME,
S.STATUS STAT,
S.SID SSID,
S.SERIAL# SSER,
LPAD(P.SPID,9) SPID,
SUBSTR(SA.SQL_TEXT,1,540) TXT
FROM V$PROCESS P,
V$SESSION S,
V$SQLAREA SA
WHERE P.ADDR=S.PADDR
AND S.USERNAME IS NOT NULL
AND S.SQL_ADDRESS=SA.ADDRESS (+)
AND S.SQL_HASH_VALUE=SA.HASH_VALUE (+)
ORDER BY 1,3,6;


OUTPUT

ORACLE     SYSTEM        LOGIN SESSION  SESSION  SERIAL    SESSION
USER ID  USER ID    TIME STATUS       ID       NO         ID     CURRENT STATEMENT
-------- -------- ---------- -------- ------- ------- --------- -------------------------
VBIP1     surekha. 03-18-2013 INACTIVE       14    18283       9343
     p       10:03:43

VBIP1     ashwini. 03-18-2013 INACTIVE      197     6429       9387
     ks       10:09:51

VBIP1     pruthvi. 03-18-2013 INACTIVE       78      450      10032
     kumar       11:34:03

VBIP1     suhasini 03-18-2013 INACTIVE       13     1107      10143
     .j       11:47:46

FIX-ORA-16038, ORA-19504

 FIX-ORA-16038, ORA-19504




Summary

1. check how much space is used by archiver
2. check the last good backup of the database
3. delete archive log files older than last good backup
4. crosscheck archive log


1. check how much space is used by archiver

  Sql>  select count(*),sum(blocks*block_size) from v$archived_log where    backup_count=0 and      deleted='NO';

   Sql> select * from v$flash_recovery_area_usage;

2. check the last good backup of the database

  set pages 999 lines 120
   col STATUS format a9
   col hrs format 999.99
   col start_time format a15
   col end_time format a15
   col dev format a5
   col inbytes format a10
   col outbytes format a10
   select
       INPUT_TYPE, STATUS,
       to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
       to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
       elapsed_seconds/3600                   hrs,
       output_device_type dev,
       input_bytes_display inbytes,
       output_bytes_display outbytes
    from V$RMAN_BACKUP_JOB_DETAILS
   order by session_key;

   3. delete archive log files older than last good backup

rman target / nocatalog
 allocate channel for maintenance device type disk;
 crosscheck archivelog all;
 delete noprompt archivelog until time 'sysdate - 1';
 delete noprompt expired archivelog all;
 delete noprompt obsolete device type disk;  


4. crosscheck archive log

crosscheck archivelog all;
 release channel;
 exit; 

FIX-ORA-01078 FAILURE IN PROCESSING

 FIX-ORA-01078 FAILURE IN PROCESSING


Enter user-name:sys/sys as sysdba
Connected to an idle instance.

SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/home/oracle/oracle/product/10.2.0/db_1/dbs/initorcl.ora' 
SQL> exit
Disconnected

[oracle@localhost dbs]# ls
  initdw.ora  l  hc_TEST.dat  init.ora    lkTEST  orapwTEST  spfileTEST.ora

Here inittest.ora file is does not exist, so i created pfile from spfile and then It’s working.

SQL> create pfile from spfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/spfileTEST.ora';

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

If I set proper Oracle SID , I can able to start the database.
[oracle@netbackuptest ~]$ export ORACLE_SID=ADP
[oracle@netbackuptest ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 25 08:57:28 2011

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  2020384 bytes
Variable Size             167775200 bytes
Database Buffers         1040187392 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.
SQL>

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