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.