Query to find PID & SPID of LGWR

SQL> select pid, program from v$process where program like '%LGWR%';

       PID   PROGRAM
----------   ------------------------------------------------
        14    oracle@evXXXXX (LGWR)

SQL>SELECT p.spid, s.program FROM v$session s, v$process p WHERE s.paddr=p.addr  AND s.program LIKE '%LGWR%';


SPID                     PROGRAM
------------------------ ------------------------------------------------
26803                    oracle@evXXXXX (LGWR)

Oracle Home and GRID backup command

Create a directory to the target where you wish to take the backup as 

mkdir abc
cd abc
mkdir backup
cd backup

pwd

/abc/backup

Now set the ASM environment and execute as root user for GRID backup

tar -cvpf /abc/backup/abc_GRIDBKP.tar $ORACLE_HOME 

and for RDBMS set any DB environment and as a oracle user 

tar -cvpf /abc/backup/abc_RDBMSBKP.tar $ORACLE_HOME  $ORACLE_HOME 

How to unregister the database from catalog

Steps - 

1)

SQL> conn rman@RMANCAT (CATALOG SERVER DATABASE) 

Enter password:

Connected.

2)

SQL> select NAME,DBID,DB_KEY from rc_database where name='ABC';

NAME           DBID     DB_KEY

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

ABC      2311686309    5021072

3)

SQL> EXECUTE rman.dbms_rcvcat.unregisterdatabase(5021072,2311686309);

PL/SQL procedure successfully completed.

4)

SQL> select NAME,DBID,DB_KEY from rc_database where name='ABC';

no rows selected

Script to moving objects from one tablespace to another tablespace

There are many occasions where we will move segments from one tablespace to another like reclaim unused space in the segments after deleting rows.
We can create scripts to move different type of segments from one tablespace to another using following SQL commands.
Here the scripts will be created to move objects (actually segments) from the tablespace USERS to USERS_TEMP. 
You can change the tablespace names accordingly.

Moving Tables and Un-partitioned Indexes

set lines 200
set pages 0
set feedback off
spool move.sql
select
'alter ' || segment_type || ' ' || owner || '.' || segment_name || decode( segment_type, 'TABLE', ' move', ' rebuild' ) ||
' tablespace USERS_TEMP' || ';'
from dba_segments
where tablespace_name='USERS' 
and segment_type in('TABLE','INDEX') order by owner, segment_type desc, segment_name;
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move all tables from one tablespace to another and to rebuild all indexes from one partition to another.

@move.sql

Moving index partitions

set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter index '|| owner||'.'||segment_name || ' rebuild partition '|| partition_name ||' tablespace USERS_TEMP;'
from dba_segments where segment_type ='INDEX PARTITION' and tablespace_name='USERS';
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move or rebuild all index partitions from one tablespace to another

@move.sql

Moving index sub-partitions

set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter index '||owner||'.'||segment_name ||' rebuild subpartition ' || partition_name ||' tablespace USERS_TEMP;'      
from dba_segments where tablespace_name='USERS' and segment_type = 'INDEX SUBPARTITION';
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move or rebuild all index subpartition segments from one tablespace to another

@move.sql

Moving IOT index segments (When only IOT indexes left on the tablespace)

set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter table '||owner||'.'|| table_name || ' move tablespace USERS_TEMP;' from dba_indexes where index_name in (
select  segment_name from dba_segments
where tablespace_name='USERS' and segment_type='INDEX');
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move all IOT segments form one tablespace to another

@move.sql

Moving LOB segments

set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter table '|| owner || '.' ||table_name|| ' move LOB ('||COLUMN_NAME ||') STORE AS (tablespace USERS_TEMP);'
from DBA_LOBS where tablespace_name='USERS';
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move all LOB segments from one tablespace to another

@move.sql

------------------------------------------------------------------------------------------------
reference -  http://jporacledb.blogspot.in/2014/12/script-to-moving-objects-from-one.html

Scripts related to TEMP TABLESPACE

To check instance-wise total allocated, total used TEMP for both RAC & NON-RAC

set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;


Total Used and Total Free Blocks

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks  from gv$sort_segment;


Another Query to check TEMP USAGE

col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

Temporary Tablespace groups

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select tablespace_name,contents from dba_tablespaces where tablespace_name like '%TEMP%';

select * from dba_tablespace_groups;

Block wise Check

select TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS, FREE_BLOCKS from V$SORT_SEGMENT;

select sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP';
To Check Percentage Usage of Temp Tablespace

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;

To check Used Extents ,Free Extents available in Temp Tablespace

SELECT tablespace_name, extent_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;

To list all tempfiles of Temp Tablespace

col file_name for a45
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files  order by file_name;

SELECT d.tablespace_name tablespace , d.file_name filename, d.file_id fl_id, d.bytes/1024/1024
size_m
, NVL(t.bytes_cached/1024/1024, 0) used_m, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v
WHERE (t.file_id (+)= d.file_id)
AND (d.file_id = v.file#);

Additional checks

select distinct(temporary_tablespace) from dba_users;

select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Changing the default temporary Tablespace

SQL> alter database default temporary tablespace TEMP;

Database altered.

To add tempfile to Temp Tablespace

alter tablespace  temp  add tempfile '&tempfilepath' size 1800M;

alter tablespace temp add tempfile '/m001/oradata/SID/temp02.dbf' size 1000m;

alter tablespace TEMP add tempfile '/SID/oradata/data02/temp04.dbf' size 1800M autoextend on maxsize 1800M;

To resize the  tempfile in Temp Tablespace

alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M

alter database tempfile '/SID/oradata/data02/temp12.dbf' autoextend on maxsize 1800M;

alter tablespace TEMP add tempfile '/SID/oradata/data02/temp05.dbf' size 1800m reuse;

To find Sort Segment Usage by Users

select username,sum(extents) "Extents",sum(blocks) "Block"
from v$sort_usage
group by username;

To find Sort Segment Usage by a particular User

SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
order by u.blocks desc;

To find Total Free space in Temp Tablespace

select 'FreeSpace  ' || (free_blocks*8)/1024/1024 ||' GB'  from v$sort_segment where tablespace_name='TEMP';

select tablespace_name , (free_blocks*8)/1024/1024  FreeSpaceInGB,
(used_blocks*8)/1024/1024  UsedSpaceInGB,
(total_blocks*8)/1024/1024  TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%'

To find  Total Space Allocated for Temp Tablespace

select 'TotalSpace ' || (sum(blocks)*8)/1024/1024 ||' GB'  from dba_temp_files where tablespace_name='TEMP';

Get 10 sessions with largest temp usage

cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;

Displays the amount of IO for each tempfile

SELECT SUBSTR(t.name,1,50) AS file_name,
f.phyblkrd AS blocks_read,
f.phyblkwrt AS blocks_written,
f.phyblkrd + f.phyblkwrt AS total_io
FROM   v$tempstat f,v$tempfile t
WHERE  t.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;


select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,
i.inst_id,i.host_name
FROM gv$session s, gv$sort_usage u ,gv$instance i
WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id  order by MB DESC) a where rownum<10;

Check for ORA-1652

show parameter background

cd <background dump destination>

ls -ltr|tail

view <alert log file name>

shift + G ---> to get the tail end...

?ORA-1652 ---- to search of the error...

shift + N ---- to step for next reported error...

I used these queries to check some settings:

-- List all database files and their tablespaces:
select  file_name, tablespace_name, status
,bytes   /1000000  as MB
,maxbytes/1000000  as MB_max
from dba_data_files ;

-- What temporary tablespace is each user using?:
select username, temporary_tablespace, default_tablespace from dba_users ;

-- List all tablespaces and some settings:
select tablespace_name, status, contents, extent_management
from dba_tablespaces ;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN STATUS
------------------------------ --------- ---------- ---------
SYSTEM                         PERMANENT DICTIONARY ONLINE
TOOLS                          PERMANENT DICTIONARY ONLINE
TEMP                           TEMPORARY DICTIONARY OFFLINE
TMP                            TEMPORARY LOCAL      ONLINE

Now, the above query and the storage clause of the old 'create tablespace TEMP' command seem to tell us the tablespace only allows temporary objects, so it should be safe to assume that no one created any tables or other permanent objects in TEMP by mistake, as I think Oracle would prevent that. However, just to be absolutely certain, I decided to double-check. Checking for any tables in the tablespace is very easy:

-- Show number of tables in the TEMP tablespace - SHOULD be 0:
select count(*)  from dba_all_tables
where tablespace_name = 'TEMP' ;

Checking for any other objects (views, indexes, triggers, pl/sql, etc.) is trickier, but this query seems to work correctly - note that you'll probably need to connect internal in order to see the sys_objects view:

-- Shows all objects which exist in the TEMP tablespace - should get
-- NO rows for this:
column owner        format a20
column object_type  format a30
column object_name  format a40
select
o.owner  ,o.object_name
,o.object_type
from sys_objects s
,dba_objects o
,dba_data_files df
where df.file_id = s.header_file
and o.object_id = s.object_id
and df.tablespace_name = 'TEMP' ;

Identifying WHO is currently using TEMP Segments

10g onwards

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
     (select block_size from dba_tablespaces where tablespace_name='TEMP') d
    WHERE b.tablespace = 'TEMP'
    and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    AND (b.blocks*d.block_size)/1048576 > 1024
    ORDER BY b.tablespace, 6 desc;

SYS.USER$ table in Oracle

last password change time, last locked, last expired, creation time, failed logon.
Oracle internal table SYS.USER$ has got many interesting columns for DBAs. This article describes some of them.

Internal table SYS.USER$ keeps both users and roles. The table is basic table that enlist database users and is referenced by almost all other dictionary views.

DESC sys.user$

Name         Null     Type           
------------ -------- -------------- 
USER#        NOT NULL NUMBER         
NAME         NOT NULL VARCHAR2(30)   
TYPE#        NOT NULL NUMBER         
PASSWORD              VARCHAR2(30)   
DATATS#      NOT NULL NUMBER         
TEMPTS#      NOT NULL NUMBER         
CTIME        NOT NULL DATE           
PTIME                 DATE           
EXPTIME               DATE           
LTIME                 DATE           
RESOURCE$    NOT NULL NUMBER         
AUDIT$                VARCHAR2(38)   
DEFROLE      NOT NULL NUMBER         
DEFGRP#               NUMBER         
DEFGRP_SEQ#           NUMBER         
ASTATUS      NOT NULL NUMBER         
LCOUNT       NOT NULL NUMBER         
DEFSCHCLASS           VARCHAR2(30)   
EXT_USERNAME          VARCHAR2(4000) 
SPARE1                NUMBER         
SPARE2                NUMBER         
SPARE3                NUMBER         
SPARE4                VARCHAR2(1000) 
SPARE5                VARCHAR2(1000) 
SPARE6                DATE


some of the columns have got following meaning:

NAME – name for user or role
TYPE# – 0 for role or 1 for user
CTIME – the date of creation
PTIME – the date the password was last changed
EXPTIME – the date the password has last expired
LTIME – the date the resource was last locked
LCOUNT – number of failed logon


In addition DBA usually looking for last logon that can be found in SYS.AUD$ table.

Backup and Restore Basic questions in ORACLE 11G

1.What is the difference between cold and hot backup?

COLD backup will be taken by shutting down the database, where as HOT backup will be taken while database is up and running

2.What happens when a database or table space is placed in begin backup mode?

• Datafile header will get freezed i.e CKPT will not update latest SCN
• DBWR still will write data into datafiles
• When end backup, CKPT will update the latest SCN to data file header

3.Why more redo will generate during hot backup?
It is to avoid fractured block as oracle will copy entire block as redo entry

4.What is fractured block?
A block which might contain inconsistent data. This happens because the speed of DBWR is different than OS copy during hot backup.

5.What is the difference between complete and incomplete recovery?
No data loss in complete recovery whereas some data loss is observed in incomplete recovery

6.What will happen if we use resetlogs?

• It will create new redolog files (if not already there)
• It will reset log sequence number to 1,2…etc
• Out of 100 datafiles, I lost 29 files. 

7.How you will identify which files to recover?
a.Using the view v$recovery_file

8.How to check if incomplete recovery is performed in the database?

SQL> select RESETLOGS_TIME,RESETLOGS_CHANGE from v$database;

9.I placed a tablespace in hot backup mode and datafile which is being backup is  lost. How you will recover it?

We can restore from old backup and apply all the archives till now

10.Yesterday night backup is successful. Today morning we added a datafile at 11  AM. After noon 3’o clock the newly added datafile is lost. Can I recover that  datafile? If so, how?

Yes we can recover it. We need to create that datafile using “alter database create datafile ‘path’;” command and then apply all the archives

11.What is the importance of archives during recovery process?
As they will store all the changes happened in the database, always we can do complete recovery if we have a perfect backup

12.How you will recover database when all copies of control file are lost?
We can do a complete recovery if we have a latest trace of it. Or else, we need to do incomplete recovery by restoring controlfile from last successful backup

13.Application team informed that an important table is dropped. How you will  recover it?
We can recover it by doing until time recovery. But this will affect other user transactions, so we need to get approval for this first

14.What are the pre-requisite factors you will consider before performing until time  recovery?
We need to see if other users are not getting any affect by doing this

15.You need to restore the database and then realized there are no control files.  How you will proceed?
Either we can resotre controlfiles first and do recovery (but a data loss is there as its incomplete recovery) or we can create new controlfile if we have a latest trace