UNDO TABLESPACE RECOVERY IN CASE OF NO BACKUPS.

This method to recover Undo tablespace is not a substitute for recovery procedures

through backups as mentioned in the official Oracle documentation.

But if you get stuck in a situation where you don’t have any backups and need to

recover your Undo tablespace , then use this method as a last resort.

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes                                       

Fixed Size                  1218992 bytes                                       

Variable Size              96470608 bytes                                       

Database Buffers          184549376 bytes                                       

Redo Buffers                2973696 bytes                                       

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file 

ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/undotbs01.dbf' 

SQL> show parameter undo

NAME                                 TYPE        VALUE                          

------------------------------------ ----------- ------------------------------ 

undo_management                      string      AUTO                           

undo_retention                       integer     900                            

undo_tablespace                      string      UNDOTBS1   

                    

SQL> alter system set undo_management = manual scope=spfile;

System altered.

SQL> shutdown immediate;

ORA-01109: database not open 

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes                                       

Fixed Size                  1218992 bytes                                       

Variable Size              96470608 bytes                                       

Database Buffers          184549376 bytes                                       

Redo Buffers                2973696 bytes                                       

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file 

ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/undotbs01.dbf' 

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf'

  2  offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> create undo tablespace undotbs1

  2  datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 50m

  3  autoextend on next 10m maxsize 1024m;

Tablespace created.

SQL> alter system set undo_management = auto scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  285212672 bytes                                       

Fixed Size                  1218992 bytes                                       

Variable Size              96470608 bytes                                       

Database Buffers          184549376 bytes                                       

Redo Buffers                2973696 bytes                                       

Database mounted.

Database opened.

SQL> show parameter undo

NAME                                 TYPE        VALUE                          

------------------------------------ ----------- ------------------------------ 

undo_management                      string      AUTO                           

undo_retention                       integer     900                            

undo_tablespace                      string      UNDOTBS1                       

How to Manage Oracle Locks.

How to Manage Oracle Locks. 1) First find the sessions holding locks, and objects that are currently being locked. The following SQL prompts you for a schema/owner name and lists all currently lock objects and sessions locking them: select session_id, oracle_username locker, o.OBJECT_ID,object_name,object_type,process,DECODE (l.locked_mode , 0, ‘None’ , 1, ‘Null’ , 2, ‘Row-S’ , 3, ‘Row-X’ , 4, ‘Share’ , 5, ‘S/Row-X’ , 6, ‘Exclusive’) lock_mode from v$locked_object l,dba_objects o where l.object_id=o.object_id and o.owner like upper(‘&SCHEMANAME’) order by 2; Or use the following SQL if you already know the name of the object that is being locked and you want to know the locking session: select session_id, oracle_username locker, o.OBJECT_ID,object_name,object_type,process,DECODE (l.locked_mode , 0, ‘None’ , 1, ‘Null’ , 2, ‘Row-S’ , 3, ‘Row-X’ , 4, ‘Share’ , 5, ‘S/Row-X’ , 6, ‘Exclusive’) lock_mode from v$locked_object l,dba_objects o where l.object_id=o.object_id and o.owner like upper(‘&SCHEMANAME’) and o.object_name like upper(‘&object_name’) order by 2; 2) Lookup detail about the sessions holding locks. Now use the following SQL to see more detail about the locking session using session ID or SID you had listed from previous lock finding SQL: select s.sid,p.spid os_pid,s.status, s.osuser,s.username,s.COMMAND, s.MACHINE,s.MODULE,s.SERIAL#, s.SCHEMANAME, s.LOCKWAIT,s.action from v$session s, v$process p WHERE s.paddr = p.addr and s.sid = &SESSION_ID; 3) Lookup the session’s current SQL. Use the following SQL to the associated SQL using the SID you have found earilier: select sa.sql_text txt from v$session s, v$sqlarea sa where s.sql_address=sa.address(+) and s.sql_hashvalue=sa.hash_value(+) and s.sid = &SESSION_ID;

How to Mirror control files/redolog files in oracle.

How to Mirror control files/redolog files in oracle. Mirror control files in oracle  In this document we will mirror control file 01, thus keeping two                  copies of this files in two different locations. When using spfile: 1) create pfile from spfile so you have the latest version of the spfile. create pfile from spfile; 2) graceful shutdown the database (immediate, startup restrict open, shutdown normal) 3) copy the controlfiles to new location cp /u01/oradata/emrep/control01.ctl /u02/oradata/emrep/control01.ctl 4) make changes in the pfile updating the control_files parameter. 5) startup the database. startup pfile=’/u01/app/oracle/product/9.0.1/dbs/initEMREP.ora’ 6) recreate the spfile using the command create spfile from pfile create spfile from pfile shutdown immediate 7) startup the database. show parameter spfile and make sure it used the spfile  show parameter control to verify control file location. When not using spfile: 1) graceful shutdown the database (immediate, startup restrict open, shutdown normal) 2) copy the controlfiles to new location cp /u01/oradata/emrep/control01.ctl /u02/oradata/emrep/control01.ctl 3) make changes in the pfile (initSID.ora) updating the control_files parameter. 4) startup the database. startup

Mirror Oracle database redo logs files. Assuming that you currently have redo logs group 1, 2 and 3. Verify by using the following query: set linesize 130 select * from v$log; 1) alter database backup controlfile to trace; ================================= 2) Create new groups: ===================== alter database add logfile group 4 (‘/u01/oradata/emrep/redo04.log’, ‘/u02/oradata/emrep/redo04.log’) size 100M; alter system switch logfile; –>until you get to group 4. set linesize 130 select * from v$log; –> Make sutre group 1,2 and 3 are not active before you drop them 3)Drop current redo: ==================== alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; Go to OS and delete corresponding files to log group 1, 2 and 3. Be extremely carful not to delete the wrong files. 4) Add mirrored groups: ==================== alter database add logfile group 1 (‘/u01/oradata/emrep/redo01.log’,'/u02/oradata/emrep/redo01.log’) size 100 M; alter database add logfile group 2 (‘/u01/oradata/emrep/redo02.log’,'/u02/oradata/emrep/redo02.log’) size 100 M; alter database add logfile group 3 (‘/u01/oradata/emrep/redo03.log’,'/u02/oradata/emrep/redo03.log’) size 100 M; alter system switch logfile; select * from v$log; –>Make sure all groups are there and mirrored.

How to Monitor Oracle database performance and sessions monitor

 How to Monitor Oracle database performance and sessions monitor

1) First find the sessions holding locks, and objects that are currently being locked.

The following SQL prompts you for a schema/owner name and lists all currently lock objects and idle sessions locking them:

select s.username locker,s.status,s.sid,s.serial#,

to_char((s.last_call_et/60), ‘99999D99′) idle_mins,

object_name,object_type,s.PROCESS, DECODE (l.locked_mode

, 0, ‘None’

, 1, ‘Null’

, 2, ‘Row-S’

, 3, ‘Row-X’

, 4, ‘Share’

, 5, ‘S/Row-X’

, 6, ‘Exclusive’) lock_mode

from v$locked_object l,dba_objects o, v$session s

where l.object_id=o.object_id

and s.sid=l.session_id

and s.username=l.oracle_username

and s.status in (‘INACTIVE’,'KILLED’)

and OWNER like upper(‘&SCHEMA’)

order by 6;

2) Lookup detail about the sessions holding locks.

Now use the following SQL to see more detail about the locking session using session ID or SID you had listed from previous lock finding SQL:

select s.sid,p.spid os_pid,s.status, s.osuser,s.username,s.COMMAND,

s.MACHINE,s.MODULE,s.SERIAL#, s.SCHEMANAME, s.LOCKWAIT,s.action

from v$session s, v$process p

WHERE s.paddr = p.addr

and s.sid = &SESSION_ID;

3) Lookup the session’s current SQL.

Use the following SQL to the associated SQL using the SID you have found earilier:

select sa.sql_text txt

from v$session s,

v$sqlarea sa

where s.sql_address=sa.address(+)

and s.sql_hashvalue=sa.hash_value(+)

and s.sid = &SESSION_ID;

How to check Oracle: Total Size of The Database

 How to check Oracle: Total Size of The Database

An oracle database consists of data files, redo log files, control files, temporary files. 

The size of the database actually means the total size of all these files.

select 

( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +

( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +

( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +

( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"

from

dual

How to change oracle 10g DB name in linux

How to change oracle 10g DB name in linux

1. First of all backup the database.
2. Then login as SYSDBA to check out the DB ID and DB Name by using the following statement, using SQLPLUS or your favourite SQL Client.
3. select dbid, name from v$database
4. Shutdown the database and startup it in mount state (SQLPLUS).
5. shutdown immediate

1. startup mount
2. Use the DBNEWID command to change the DB ID, you need to login as user with privilege SYSDBA (Terminal).
3. nid target=username/password@servicename dbname=newdbname
4. Shutdown database again (SQLPLUS).
5. shutdown immediate
6. Change db_name initialization parameter in the initialization file, normally inside database folder/admin/pfile (Terminal).
7. Create a new password file using ORAPWD utility, the file usually in Oracle folder/dbs (Terminal).
8. orapwd file=orapwSID password=password entries= 
9. Start database in mount state (SQLPLUS).
10. startup mount
11. Open database with RESETLOGS option (SQLPLUS).
12. alter database open resetlogs
13. Verify the database id and name using following statement (SQLPLUS).
14. select dbid, name from v$database
15. Don’t forget to change ORACLE_SID parameter in .bash_profile and database name in /etc/oratab
16. (Terminal).

Fix - Ora -01653

Fix - Ora- 01653

Solution:

Use Alter table space add datafile statement to add one or more files to the mentioned table space.

How to do Super fast Database Copying/Cloning

Super fast Database Copying/Cloning:

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another.  It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

    STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
    NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 240
    MAXINSTANCES 1
    MAXLOGHISTORY 113
    LOGFILE
    GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
    '/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
    GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
    '/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
    DATAFILE
    '/u01/oradata/oldlsq/system01.dbf',
    '/u01/oradata/oldlsq/mydatabase.dbf'
    ;
    # Recovery is required if any of the datafiles are restored
    # backups, or if the last shutdown was not normal or immediate.
    RECOVER DATABASE
    # Database can now be opened normally.
    ALTER DATABASE OPEN;
     

STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

    rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
    rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
    rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
    rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
     

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

    CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

New:

    CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax

    # Recovery is required if any of the datafiles are restored
    # backups, or if the last shutdown was not normal or immediate.
    RECOVER DATABASE
    # Database can now be opened normally.
    ALTER DATABASE OPEN;
     

STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

Old:

    DATAFILE
    '/u01/oradata/oldlsq/system01.dbf',
    '/u01/oradata/oldlsq/mydatabase.dbf'

New:

    DATAFILE
    '/u01/oradata/newlsq/system01.dbf',
    '/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

    cd $DBA/admin
    mkdir newlsq
    cd newlsq
    mkdir bdump
    mkdir udump
    mkdir cdump
    mkdir pfile


STEP 8: Copy-over the old init.ora file

    rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9: Start the new database

    @db_create_controlfile.sql

STEP 10: Place the new database in archivelog model




1 Put your source database in backup mode
2 Force a checkpoint in your source database
3 FTP all datafiles to your target box
4 Take your source database out of backup mode
5 Force a new checkpoint in your source database
6 FTP all relevant archive logs to target box
7 Create a control file on your source box
8 Move your newly created control file to the target box
9 On target box Edit your new control file as needed
10 Create/recover your new database using CREATE CONTROLFILE REUSE SET DATABASE "newname" RESETLOGS ARCHIVELOG until cancel.

Hope this helps. 

Thank you -Mr George Picoaga.

Identify the Oracle schema Session to be Killed

Identify the Session to be Killed

Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.

Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows.

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

       s.sid,

       s.serial#,

       p.spid,

       s.username,

       s.program

FROM   gv$session s

       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND';

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM

---------- ---------- ---------- ---------- ---------- ---------------------------------------------

         1         30         15 3859       TEST       sqlplus@oel5-11gr2.localdomain (TNS V1-V3)

         1         23        287 3834       SYS        sqlplus@oel5-11gr2.localdomain (TNS V1-V3)

         1         40        387 4663                  oracle@oel5-11gr2.localdomain (J000)

         1         38        125 4665                  oracle@oel5-11gr2.localdomain (J001)

SQL>

The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.

ALTER SYSTEM KILL SESSION

The basic syntax for killing a session is shown below.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.

In addition to the syntax described above, you can add the IMMEDIATE clause.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it's worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.

ALTER SYSTEM DISCONNECT SESSION

The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are rolled back immediately.

The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.

SQL> alter system disconnect session '30,7';

alter system disconnect session '30,7'

                                     *

ERROR at line 1:

ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword

SQL>

This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.

The Windows Approach

To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.

C:> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources released.

The UNIX Approach

To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.

% kill spid

If after a few minutes the process hasn't stopped, terminate the session using the following.

% kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using.

% ps -ef | grep ora

The session thread should be killed immediately and all resources released.

SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';

   INST_ID        SID    SERIAL#
---------- ---------- ----------
         1        130        620

SQL>  alter system kill session '130,620,1';
 alter system kill session '130,620,1'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID

Now, it works:

SQL>  alter system kill session '130,620,@1';

System altered.

OR 

SQL>select sid,serial# from v$session where machine='GPTWORKGROUP';

SID SERIAL#

--------- ---------

9 171

1 row selected

SQL> alter system kill session '9,171';

System altered

OR

SQL>alter system kill session '9,171' immediate;

System altered.

How to stop or kill data pump jobs in Oracle

 It’s a two step process.

 

1. Get the list of datapump jobs:

SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

-- locate Data Pump jobs:

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

The output might look something like this:

OWNER_NAME JOB_NAME             OPERATION   JOB_MODE    STATE       ATTACHED_SESSIONS
---------- -------------------- ----------- ----------- ----------- -----------------
SCHEMA_USER SYS_IMPORT_SCHEMA_01 IMPORT      SCHEMA      EXECUTING  1

There are two things needed to perform the kill:
1. OWNER_NAME (Which is SCHEMA_USER)
2. JOB_NAME (Which is SYS_IMPORT_SCHEMA_01)

With that information, we can now stop and kill the job:

SET serveroutput on
SET lines 100
DECLARE
   h1 NUMBER;
BEGIN
  -- Format: DBMS_DATAPUMP.ATTACH('[job_name]','[owner_name]');
   h1 := DBMS_DATAPUMP.ATTACH('SYS_IMPORT_SCHEMA_01','SCHEMA_USER');
   DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

Check that the job has stopped:

SQL> SET lines 200
SQL> COL owner_name FORMAT a10;
SQL> COL job_name FORMAT a20
SQL> COL state FORMAT a11 
SQL> COL operation LIKE state
SQL> COL job_mode LIKE state
SQL> 
SQL> -- locate Data Pump jobs:
SQL> 
SQL> SELECT owner_name, job_name, operation, job_mode,
  2  state, attached_sessions
  3  FROM dba_datapump_jobs
  4  WHERE job_name NOT LIKE 'BIN$%'
  5  ORDER BY 1,2;

no rows selected

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';