Oracle Database RESETLOGS AND NORESETLOGS Options

RESETLOGS:-

alter database open resetlogs;

this option will reset redo logs and start generating redo logs newly so all old redo logs will be discarded. 

NORESETLOGS:-

alter database open noresetlogs;

this option will used pre-existing redo logs and don't generate redo logs newly. 

if test options and it usage In Linux

if test (expression)

if test !(expression)

if test expression1 -a expression2 

if test expression1 -o expression2

if test -z STRING 

if test STRING1 = STRING2 

if test STRING1 != STRING2

if test INTEGER1 -eq INTEGER2 

if test INTEGER1 -ge INTEGER2 

if test INTEGER1 -gt INTEGER2 

if test INTEGER1 -lt INTEGER2

if test INTEGER1 -le INTEGER2 

if test INTEGER1 -ne INTEGER2 

if test file1 -nt file2                    # -nt :- newer than 

if test file1 -ot file2                    # -ot :- older than

if test -f file                                # if file exists 

if test -d directory_name           # if directory exists 

if test -x file                               # if file is executable 

if test -r  file                               # if file is readable 

if test -w file                               # if file is writable 

if test -s "filename.txt"; then

    # Code to execute if the file is not empty

else

    # Code to execute if the file is empty or does not exist

fi

if ! test -s "filename.txt"; then

    # Code to execute if the file is empty or does not exist

else

    # Code to execute if the file is not empty

fi

if test -n "$myString"; then

    # Code to execute if the string is not empty

else

    # Code to execute if the string is empty

fi

if ! test -n "$myString"; then

    # Code to execute if the string is empty

else

    # Code to execute if the string is not empty

fi

Enable Flashback Mode In Oracle Database

 To Enable Flashback Database 

shutdown immediate;

startup nomount;

alter database mount;

archive log list;

alter database archivelog;

change FRA size and then dest :-

alter system set db_flashback_retention_target=2880; ----> 2880 minutes 

alter system set db_recovery_file_dest_size=10G;

alter system set db_recovery_file_dest='/path/to/FRA';

alter system set undo_retention=86400; -----------> 86400 seconds

alter database flashback on;

alter database open;

if not open then do "alter database open resetlogs" or "alter database open noresetlogs" 

SQL >

select name, value from gv$parameter where name in ('db_flashback_retention_target','db_recovery_file_dest_size','db_recovery_file_dest','undo_retention');

To Check Flashback on/off :-

select flashback_on from gv$database;

Enable Or Disable Archive Log Mode In Oracle Database

 Enable Archive Log Mode In Oracle Database 

shutdown immediate;

startup nomount;

alter database mount;

alter database archivelog;

alter database open;

archive log list;

Disable Archive Log Mode In Oracle Database 

shutdown immediate;

startup nomount;

alter database mount;

alter database noarchivelog;

alter database open;

archive log list;

RMAN BACKUP FORMAT and Commands

always do pre-measures:-

cat /etc/oratab or cat /var/opt/oracle/oratab 

export ORACLE_HOME=

export ORACLE_SID=

export PATH=$ORACLE_HOME/bin:$PATH

echo $ORACLE_SID 

rman target /

RMAN> backup database format '/path/to/backup/location/db_backup_%U';

RMAN> backup database format '/path/to/backup/location/%d_%t_%U';

Note:- 

"backup database format '/path/to/backup/location/%d_%t_%U';" will take backup of the entire database including data files, control files, and archived logs. The command is called Backup database.

if you want to speed up the backup you can mention "parallel n"

example :-

RMAN> backup database format '/path/to/backup/location/%d_%t_%U' parallel 4;

this will make four files

some flags used in rman :-

%d :- database name

%t :- backup set 

%s :- backup set number

%p :- piece number

%U :- unique backup piece number

%F :- filename with full name

%T :- backup piece tag 

for example :- %d_%t  means  $ORACLE_SID_$(DATE '+%Y%m%d')  

pune_20230516  ---> this will come after using %d_%t 

RMAN > backup archivelog all format '/path/to/backup/location/arc_%d_%t_%U';

To delete archivelog older :-

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

RMAN> list archivelog until time 'sysdate - n';

RMAN > delete noprompt archivelog until time 'sysdate - n';

RMAN > crosscheck archivelog all;

sqlplus / as sysdba 

SQL> shutdown immediate;

SQL> startup nomount;

SQL> alter database mount;

RMAN > catalog start with '/path/to/backup/location';

RMAN > restore database;

RMAN > recover database;

sqlplus / as sysdba 

SQL> alter database open resetlogs;

Restore and Recovery :-

backup current controlfile format '/path/to/bkp_controlfile.bkp';

alter database nomount;

restore controlfile from '/path/to/bkp_controlfile.bkp';

or 

restore controlfile from autobackup;

alter database mount;

recover database;

alter database open resetlogs;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

++++++++++++++++++ RMAN FILES DETAILS ++++++++++++++++++++++++++++++

select fname,to_char(completion_time,'DD-Mon-YYYY HH:MI:SS') "completion_time" from v$backup_files where completion_time is not null and fname like '%dbf%' order by completion_time desc;

select * from v$rman_configuration;

select * from v$rman_backup_job_details;

select * from v$rman_backup_type;

to_char(date,'DD-MONTH-YYYY HH12:MI:SS') :- 08-September-2023 10:02:32

to_char(date,'DD-MONTH-YYYY HH24:MI:SS') :- 08-September-2023 21:02:32 

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

rman target /

RMAN > run {

delete noprompt archivelog until time 'sysdate-2';

crosscheck archivelog all;

}

Incremental backups :-

Level 0 : full backup

Level 1 : incremental backup

To perform a level 0 backup (full backup), you can use:

backup incremental level 0 database;

To perform a level 1 backup ( incremental backup), you can use:

backup incremental level 1 database;

To perform cumulative incremental backup, you can use:

backup incremental level 1 cumulative database;

Specify the backup location:

backup incremental level 1 database tag 'level1' format '/path/to/backup/%U';

The increment backup in RMAN is done if you specify a LEVEL <n> option, where n can by either 0 or 1

A LEVEL 0 incremental backup means complete backup just like Full backup
A LEVEL 1 incremental backup copies all blocks changed since last LEVEL 0 or LEVEL 1 backup.

A LEVEL 1 COMULATIVE backup copies all blocks changed since last LEVEL 0 backup.

In incremental backup strategy you will first need to take complete backup i.e. LEVEL 0 backup

++++++++++++++++++++++++++++++++++++++++++++++++++++++

Point in time recovery in Oracle using RMAN (recovery manager) allows you to recover your database to a specific point in time, i.e. any time you would like to make the database available present in database backup. 

step 1:- Connect to the RMAN 
echo $ORACLE_SID
rman target /
or 
rman target sys/passwd@database_netservice
step 2:- restore the control file to the location desired

RMAN > 
run {
allocate channel ch1 type disk;
restore controlfile to '/path/to/new/controlfile.ctl';
}

step 3:- Mount the database since backup/restore can only perform in mount mode

run {
allocate channel ch1 type disk;
sql 'alter database mount';
}

step 4:- Specify the point in time i.e. mention the time 

run {
set until time 'to_date('yyyy-mm-dd hh24:mi:ss','yyyy-mm-dd hh24:mi:ss')';
}

step 5:- Do Incomplete Recovery to open database with reset logs option since we don't want to use old redo logs.

run {
allocate channel ch1 type disk;
recover database;
}

step 6:- Open the database with reset logs option.

run {
sql 'alter database open resetlogs';
}

step 7:- Complete the recovery now 

run {
allocate channel ch1 type disk;
backup database format '/path/to/backup/%d_%t.dbf';
}

+++++++++++RMAN DETAIL SCRIPT ++++++++++++++++++++

RMAN BACKUP STATUS DETAILS SCRIPTS

######## RMAN Backup logs details will show which are files completed #############

cd logs/voyager/backups

voyager:/home/oracle/ $ cd logs/voyager/backups

voyager:logs/voyager/backups/ $ ls -lrth

total 3218

-rw-r-----   1 oracle   dba          54K Feb 21 08:17 DB_Full_Backup_202202210158.log

-rw-r-----   1 oracle   dba          54K Feb 21 08:18 DB_Full_Backup_202202210155.log

-rw-r-----   1 oracle   dba         103K Feb 21 23:27 DB_Full_Backup_202202212300.log

-rw-r-----   1 oracle   dba         155K Feb 23 01:09 DB_Full_Backup_202202222300.log

-rw-r-----   1 oracle   dba         132K Feb 24 01:13 DB_Full_Backup_202202232300.log

-rw-r-----   1 oracle   dba          29K Feb 25 00:25 DB_Full_Backup_202202242300.log

voyager:logs/voyager/backups/ $ more DB_Full_Backup_202202242300.log

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

SQL> col STATUS format a9

SQL> col hrs format 999.99

SQL> select SESSION_KEY, 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 from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SQL> /

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

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

 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .00

SQL> /

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

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

 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .01

SQL> /

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

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

 29          DB FULL       COMPLETED 01/07/14 10:28 01/07/14 10:29 .03

============================================================

next script

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

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 

ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

 SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE

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

 18        29         1          9115569    19258880   47.33

===========================================================================

next script

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

You can also check historical backup status with the help of following script:

set linesize 500 pagesize 2000

col Hours format 9999.99

col STATUS format a10

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,

to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,

elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SESSION_KEY INPUT_TYPE    STATUS     RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME  HOURS

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

 137764     DB FULL       COMPLETED  06-06-2017 02:00:32 06-06-2017 04:12:13 2.19

 137770     ARCHIVELOG    COMPLETED  06-06-2017 04:00:29 06-06-2017 04:01:05 .01

 137778     ARCHIVELOG    COMPLETED  06-06-2017 06:00:27 06-06-2017 06:00:35 .00

 137782     ARCHIVELOG    COMPLETED  06-06-2017 08:00:32 06-06-2017 08:03:36 .05

 137786     ARCHIVELOG    COMPLETED  06-06-2017 10:00:30 06-06-2017 10:02:03 .03

 137790     ARCHIVELOG    COMPLETED  06-06-2017 12:00:30 06-06-2017 12:02:34 .03

 137794     ARCHIVELOG    COMPLETED  06-06-2017 14:00:30 06-06-2017 14:02:58 .04

=======================================================

next script 

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

 select OUTPUT from V$RMAN_OUTPUT;

=======================================================

RMAN COMMANDS:---

=======================

To show all configuration details :-  RMAN > show all;

To show backups details:- RMAN > list backup;

To show archivelog details:- RMAN > list archivelog until time 'sysdate - 12';

To show backup summary:- RMAN > list backup summary;

To delete expired backups:- RMAN > delete noprompt expired backup;

To crosscheck backups:- RMAN > crosscheck backup;

To delete obsolute backups:- RMAN > delete noprompt obsolute;

To list backups for sysdate:- RMAN > list backup completed before 'sysdate-7';

To delete backup after time:- RMAN > list backup completed after 'sysdate-8';

To delete backup between time:- 

RMAN > list backup completed between 'sysdate-7' and 'sysdate-3'; 

To delete backup before time:- RMAN > delete backup completed before 'sysdate-7';

To delete backup after time:- RMAN > delete backup completed after 'sysdate-8';

To delete backup between time:- 

RMAN > delete backup completed between 'sysdate-7' and 'sysdate-3'; 

+++++++ RMAN BKUP Location command ++++++++++++++++++

echo $(echo "select fname from v\$backup_files where file_type='PIECE' order by completion_time desc fetch first 1 rows only;" | sqlplus \/ as sysdba | dirname $(grep -e "^/[[:graph:]]*"))

another best script that will work on standalone DB :-

cd $(echo "select name from v\$recovery_file_dest;" | sqlplus \/ as sysdba | grep -E "^/[[:graph:]]*")

another best script that will work on RAC DB :-

echo $(echo "select name from v\$recovery_file_dest;" | sqlplus \/ as sysdba | grep -e "^+")

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[oracle@pm latestFolder]$ cat example.sh

#!/bin/sh

# below command will extract timing of latest rman backup

timing=$(echo "select to_char(newest_backup_time,'YYYY-MM-DD HH:MI:SS') "latest_backup_time" from v\$backup_set_summary order by newest_backup_time desc;" | sqlplus \/ as sysdba | grep -v "[[:alpha:]]" | grep -E "^[[:digit:]]{4}+-+[[:digit:]]{0,2}+-[[:digit:]]{0,2}+[[:space:]]+[[:digit:]]{0,2}+:+[[:digit:]]{0,2}+:+[[:digit:]]{0,2}")

# below command will extract backup location

backup_location=$(echo "select name from v\$recovery_file_dest;" | sqlplus \/ as sysdba | grep -E "^/[[:graph:]]*")

# below command will fine files

find ${backup_location}-maxdepth 1 -type f -not -newermt "${timing}"

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

or we can find backups older than today 

find . -type f -not -newermt "$(date -d '0 days ago' +'%Y-%m-%d %H:%M:%S')"

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

du -b *2023_12_30_dbf_* | awk '{x+=$1};END{print x/(1024*1024*1024),"GB"}' 

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

How to kill ORACLE RMAN backup job:-

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

SQL> 

select b.sid, b.serial#, a.spid, b.client_info from gv$process a, gv$session b
where a.addr=b.paddr and client_info like 'rman%';

SID    SERIAL#    SPID    CLIENT_INFO

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

590    452                888        rman channel=full_chanel

SQL >

 alter system kill session '&sid, &serial' ;

=============================================

-- 1. Full Level 0 Backup with KEEP FOREVER and TAG

BACKUP INCREMENTAL LEVEL 0 

  DATABASE 

  TAG 'PREUPGRADE_LEVEL0_BKP' 

  KEEP FOREVER;

-- 2. Full Backup + Control File + Archivelogs + SPFILE

BACKUP INCREMENTAL LEVEL 0 

  DATABASE 

  INCLUDE CURRENT CONTROLFILE 

  PLUS ARCHIVELOG 

  TAG 'PREUPGRADE_FULL_BKP' 

  KEEP FOREVER;

-- 3. Separate Backup for SPFILE and Control File

BACKUP SPFILE TAG 'PREUPGRADE_SPFILE_BKP';

BACKUP CURRENT CONTROLFILE TAG 'PREUPGRADE_CONTROLFILE_BKP';

-- 4. Mark Existing Backup as KEEP FOREVER

CHANGE BACKUP TAG 'PREUPGRADE_FULL_BKP' KEEP FOREVER;

-- 5. Remove KEEP FOREVER Status

CHANGE BACKUP TAG 'PREUPGRADE_FULL_BKP' NOKEEP;

-- 6. Force Delete Backup (Even with KEEP FOREVER)

DELETE FORCE BACKUP TAG 'PREUPGRADE_FULL_BKP';

-- 7. Crosscheck and Validate Backups

CROSSCHECK BACKUP;

CROSSCHECK ARCHIVELOG ALL;

VALIDATE BACKUPSET TAG 'PREUPGRADE_FULL_BKP';

-- 8. Backup All Datafiles into a Single File

RUN {

  ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;

  BACKUP AS BACKUPSET 

    DATABASE 

    FORMAT '/u01/backup/full_db_single_file.bkp';

  RELEASE CHANNEL ch1;

}

=======================================

How to restore & recover database using tag 

STARTUP NOMOUNT;

-- Restore control file from the tagged backup

RESTORE CONTROLFILE FROM TAG 'FULL_BKP_11Jul2025';

-- Mount the database using restored controlfile

ALTER DATABASE MOUNT;

-- Restore all datafiles from the backup with the given tag

RESTORE DATABASE FROM TAG 'FULL_BKP_11Jul2025';

-- Recover database using available archive logs and redo

RECOVER DATABASE;

-- Open the database

ALTER DATABASE OPEN;

If needs to recover control file also 

Set Unitl uses in Oracle RMAN command:

Incremental backup on standby db:

-- Step 1: Start the standby instance without mounting (control file is missing)

STARTUP NOMOUNT;

-- 🔎 Reason: Required before restoring control file. DB can't mount without it.

-- Step 2: Restore control file from a known TAG or path (taken from primary)

RESTORE CONTROLFILE FROM TAG 'FULL_BKP_11Jul2025';

-- 🔎 Reason: Replaces lost/corrupted standby control file with a consistent one from backup.

-- Step 3: Mount the database to allow access to datafile metadata

ALTER DATABASE MOUNT;

-- 🔎 Reason: Required before restoring or recovering datafiles. Now RMAN can read file structure.

-- Step 4: Catalog the incremental backup if standby doesn't know about it

CATALOG START WITH '/u01/backup/';

-- 🔎 Reason: Tells RMAN about external backup files if they weren’t already registered in the control file.

-- Step 5: Restore the database (optional - only if full restore needed)

RESTORE DATABASE FROM TAG 'INCR_STANDBY_BKP_11Jul2025';

-- 🔎 Reason: Reapplies full or incremental image copies of the datafiles if the standby is outdated.

-- Step 6: Recover the database using archived redo or incremental changes

RECOVER DATABASE;

-- 🔎 Reason: Applies redo to bring the datafiles to a consistent state.

-- Step 7: Restart standby recovery (if part of Data Guard)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- 🔎 Reason: Starts real-time redo apply in standby mode, reconnects to primary for log shipping.

Summary:

Rolling forward Standby db:

-- STEP 1: Get SCN from Standby

-- Run on STANDBY SQL*Plus

sqlplus / as sysdba

SELECT CURRENT_SCN FROM V$DATABASE;

-- Assume output SCN is: 123456789

-- STEP 2: Take Incremental Backup on PRIMARY

-- Run on PRIMARY in RMAN

rman target /

RUN {

  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

  BACKUP INCREMENTAL FROM SCN 123456789 DATABASE FORMAT '/backup/inc_for_stby_%U.bkp' TAG 'INC_FOR_STANDBY';

  BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/standby_ctrl.bkp';

  RELEASE CHANNEL c1;

}

-- STEP 3: Transfer Backup Files to STANDBY

-- Run on OS shell (PRIMARY)

scp /backup/inc_for_stby_*.bkp oracle@standby_host:/backup/

scp /backup/standby_ctrl.bkp oracle@standby_host:/backup/

-- STEP 4: Restore and Apply on STANDBY

-- Run on STANDBY in RMAN

rman target /

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT;

RESTORE CONTROLFILE FROM '/backup/standby_ctrl.bkp';

ALTER DATABASE MOUNT;

-- STEP 5: Catalog and Recover

CATALOG START WITH '/backup/';

RECOVER DATABASE;

-- STEP 6: Start Standby Recovery (MRP)

-- Run on STANDBY SQL*Plus

sqlplus / as sysdba

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Recover spfile from backup:

-- STEP 1: Connect to RMAN

rman target /

-- STEP 2: (Optional) Set DBID if database is down

SET DBID = 123456789;

-- STEP 3: Startup NOMOUNT (use PFILE if needed)

-- If no spfile or pfile exists, create a minimal init file manually:

echo "db_name='ORCL'" > $ORACLE_HOME/dbs/initORCL.ora

-- Start database with temporary PFILE

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initORCL.ora';

-- STEP 4: Restore SPFILE from autobackup

-- Run in RMAN

RESTORE SPFILE FROM AUTOBACKUP;

-- or restore to temporary PFILE location (if you want to edit first)

RESTORE SPFILE TO PFILE '/tmp/init_temp.ora' FROM AUTOBACKUP;

-- STEP 5: (Optional) Edit temp PFILE if needed

vi /tmp/init_temp.ora

-- STEP 6: Create SPFILE from edited PFILE

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='/tmp/init_temp.ora';

CREATE SPFILE FROM PFILE='/tmp/init_temp.ora';

-- STEP 7: Restart Database with SPFILE

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT;

-- ✅ SPFILE is now restored and database is using it

=======================================================

User Profile Manipulation In Oracle

 --- We'll Find Profile for the user from below query 
select username,profile from dba_users where username=upper('&username');

--- Now we'll find profile resource limit for the profile 
select profile,resource_name,limit from dba_profiles where profile=upper('&profile_name');

--- We'll create new profile and then assign to scott user 

CREATE PROFILE c##scott_profile LIMIT
  COMPOSITE_LIMIT UNLIMITED
  SESSIONS_PER_USER UNLIMITED
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL UNLIMITED
  LOGICAL_READS_PER_SESSION UNLIMITED
  LOGICAL_READS_PER_CALL UNLIMITED
  IDLE_TIME UNLIMITED
  CONNECT_TIME UNLIMITED
  PRIVATE_SGA UNLIMITED
  FAILED_LOGIN_ATTEMPTS 10
  PASSWORD_LIFE_TIME 180
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL
  PASSWORD_LOCK_TIME 1
  PASSWORD_GRACE_TIME 7
  INACTIVE_ACCOUNT_TIME UNLIMITED;

--- We'll change profile resoure limits 
ALTER PROFILE c##scott_profile LIMIT
  password_lock_time 2;

--- We'll verify profile resource limit changed or not 
select profile,resource_name,limit from dba_profiles where profile=upper('&profile_name') 
and resource_name=upper('&resource_name');

--- We'll change user profile to new one 
ALTER USER scott PROFILE c##scott_profile;

--- We'll revert back profile 
alter user scott profile default;

--- We can drop the profile 
drop profile profile_name;

++++++++++++++++++++++++ User Details +++++++++++++++++++

SELECT username
	,account_status
	,to_char(created, 'DD/MM/YYYY HH:MI:SS AM') created
	,to_char(lock_date, 'DD/MM/YYYY HH:MI:SS AM') lock_date
	,to_char(expiry_date, 'DD/MM/YYYY HH:MI:SS AM') expiry_date
	,to_char(last_login, 'DD/MM/YYYY HH:MI:SS AM') last_login
	,to_char(password_change_date, 'DD/MM/YYYY HH:MI:SS AM') password_change_date
	,PROFILE
FROM dba_users
WHERE username = '&username';

FLASHBACK_TRANSACTION_QUERY displays information about all flashback transaction queries in the database.

 

FLASHBACK_TRANSACTION_QUERY displays information about all flashback transaction queries in the database.

The database must have at least minimal supplemental logging enabled to avoid unpredictable behavior.

select 

xid,

start_scn,

start_timestamp,

commit_scn,

commit_timestamp,

logon_user,

undo_change#,

operation,

table_name,

table_owner,

row_id,

undo_sql

from 

flashback_transaction_query;

if you want to know column_name,data_type  for a table,view, etc then execute below query:-

select column_name,data_type from all_tab_columns where table_name='&table';

Resolve In-Doubt-Transaction (IDT)

 select LOCAL_TRAN_ID from dba_2pc_pending;
retruns idt Value
rollback force 'IDT Value' ;
commit;
commit;
exec dbms_transaction.purge_lost_db_entry('IDT Value');
commit;

ORACLE SESSIONS MONITORING SQL QUERIES

 SQL>
SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

TO find out sql_id for the above sid:
SQL> select sql_id from gv$session where sid='&SID';
To find sql text for the above sql_id:
SQL> select sql_fulltext from gv$sql where sql_id='&sql_id';
To find wait event of the query for which it is waiting for:
SQL>select sql_id, state, last_call_et, event, program, osuser from gv$session where sql_id='&sql_id';     ---------------------------> best query for getting  os user

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;
if object/table locked ora error :
alter session set ddl_lock_timeout=30;
alter system kill session 'sid, serial#';

=============================================
select a.SAMPLE_TIME, a.SQL_OPNAME, a.SQL_EXEC_START, a.program, a.client_id, b.SQL_TEXT
from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b --v$sqltext b
where a.SQL_ID = b.SQL_ID and sql_id='&sql_id'
order by a.SQL_EXEC_START asc;
=============================
SELECT SESSION_ID, program, module , event,sql_id ,TIME_WAITED,SESSION_STATE 
FROM  gv$active_session_history where sql_id='&sqlid';
=======================================================
select distinct inst_id,sample_time,time_waited, session_id,session_serial#,sql_id from gv$active_session_history where sql_id='&sqlid'  order by 1 desc;
select distinct inst_id,sid,status,sql_id,sql_plan_hash_value,sql_child_address,sql_exec_id
 from gv$sql_plan_monitor where sql_id='&sql_id' and rownum<=5;
1b3mkpnwf6c9c
5968nvz4bcn7n
========================
SELECT distinct sql_text FROM gv$sql WHERE sql_id='&sql_id';
===================================================
oracle_sql_text_spid.sql
----------------------------------
select s.sql_text,s.sql_fulltext from v$sql s 
where sql_id in (SELECT A.SQL_ID FROM V$SESSION A,V$PROCESS B 
WHERE A.PADDR=B.ADDR AND B.SPID='6495');
=================================================================
select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where t.sql_id=s.sql_id and 
s.snap_id between 9349 and 9351;
or  t.sql_id='&sql_id';
====================================================================
 col username for a15
 col machine for a20
 col event for a20
 col program for a20
 col osuser for a20
 set line 3000
 set pagesize 2000
 SELECT
 Inst_id,
    s.username,
    s.blocking_session blk_sess,
    s.blocking_instance blk_inst,
    s.sid, 
    s.serial#, 
    s.sql_id,
    s.status,
    s.seconds_in_wait waittime,
    s.event,
    s.machine 
 FROM
    gv$session s
 WHERE
    blocking_session IS NOT NULL order by SECONDS_IN_WAIT;
    
****************** getting inactive sessions ***********************************
    select sid,serial#,username,status from gv$session where username='NIMBUS' and status='INACTIVE';
    
***************** details of session ***************************
select distinct inst_id,sid,status,sql_id,sql_plan_hash_value,sql_child_address,sql_exec_id
 from gv$sql_plan_monitor where sql_id='&sql_id' and rownum<=5;
 
select inst_id, status,first_change_time,last_change_time,sql_exec_start,plan_operation,
plan_object_owner,plan_object_name,plan_object_type,plan_time, plan_temp_space, starts,output_rows from gv$sql_plan_monitor 
where sql_id='&sql_id' and rownum<=5;
*************** osusername,machine,termial and program lockwait details ************
select sid,serial#,username,lockwait,status,osuser,machine,terminal,program,
server,final_blocking_session from gv$session where username='NIMBUS';
desc all_objects;
****************ddl details for objects ********************************************
select owner,object_name,object_type,last_ddl_time,status,(select host_name from v$instance) "host" from all_objects where object_name='SUBSCRIPTION';
SELECT ORACLE_USERNAME, OS_USER_NAME,LOCKED_MODE,OBJECT_NAME,OBJECT_TYPE
    FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
    WHERE A.OBJECT_ID=B.OBJECT_ID;
    
select * from dba_ddl_locks;


select column_name,data_type,(select host_name from v$instance) as host from all_tab_columns where table_name='UDAS_DTVREQUEST' ;
********************** kill multiple sessions ****************************
begin 
for x in (select sid,serial#,username,status from gv$session where username='NIMBUS' and status='INACTIVE')
loop
dbms_output.put_line('alter system kill session '||''''||x.sid||', '||x.serial#||''''||';');
end loop;
end;
/

LOCKED OBJECTS SQL QUERIES IN ORACLE

 Locks on tables by sessions :

solution: 

1) commit;

2) rollback;

3) kill the session;

4) wait to complete;

===================================================================

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name  

FROM V$Locked_Object A, All_Objects B

WHERE A.Object_ID = B.Object_ID;

=================================================

select

   c.owner,

   c.object_name,

   c.object_type,

   b.sid,

   b.serial#,

   b.status,

   b.osuser,

   b.machine

from

   v$locked_object a ,

   v$session b,

   dba_objects c

where

   b.sid = a.session_id

and

   a.object_id = c.object_id;

==============================================================

column oracle_username format a15;

column os_user_name format a15;

column object_name format a37;

column object_type format a37;

select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from 

(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a, 

(select object_id, owner, object_name,object_type from dba_objects) b

where a.object_id=b.object_id;

==========================================================

TABLESPACE, DATAFILES, ASM DISKGROUP IN ORACLE SQL QUERIES

For checking asm disk group :-

su - oragrid

sqlplus / as sysasm 

 tablespace,datafiles,asm,etc 
gv$filespace_usage RAC view;
v$filespace_usage view;

dba_tablespace_usage_metrics view:- 
dba_hist_tbspc_space_usage AWR tablespace :-

SELECT name ,round(bytes/power(1024,3),2)  size_gb
FROM   v$datafile where name like '+INSTANCE_ORADATA%'   order by size_gb desc;
desc v$datafile;

select count(round(bytes/power(1024,3),2)) count,sum(round(bytes/power(1024,3),2)) size_gb 
from v$datafile 
where 
round(bytes/power(1024,3),2) >=10 and round(bytes/power(1024,3),2) < 32 and name like '+INSTANCE_ORADATA%';

select sum(round(bytes/power(1024,3),2)) size_gb from v$datafile where name like '+INSTANCE_ORADATA%';

select
   tablespace_name,
   round(used_percent,2) used_percent
from
   dba_tablespace_usage_metrics;

   
desc gv$asm_diskgroup;

select name,round(total_mb/power(1023,2),2) from gv$asm_diskgroup;

select distinct name,round(total_mb/power(1024,1)) total_size_GB,round(free_mb/power(1024,1)) free_size_GB,
round(round(free_mb/power(1024,1),2)/round(total_mb/power(1024,1),2)*100) percent_free,state from gv$asm_diskgroup;
--where name like '%INSTANCE_ORADATA%';

--- free space in asm diskgroup ---
select distinct name,round(total_mb/power(1024,1)) total_size_GB,round(free_mb/power(1024,1)) free_size_GB,
round(round(free_mb/power(1024,1),2)/round(total_mb/power(1024,1),2)*100) percent_free,
(round((total_mb - free_mb)/power(1024,1),2)) USED_GB
,state from gv$asm_diskgroup;
   
select ts.tablespace_name,df.name from dba_tablespace_usage_metrics ts inner join  v$datafile df on df.name is not null where df.name like 
'+INSTANCE_ORADATA%';

select distinct
a.name Name,
round(a.bytes/power(1024,3),2) size_GB,
b.phyrds,
b.phywrts
from gv$datafile a, gv$filestat b
where a.file# = b.file#
order by size_GB desc;

Install OEM Oracle Enterprise Manager

1) install oracle db sid/service_name "emcdb" and pdb "emrep"
2) check /tmp space should be >15gb 
3) use normal listener without sid_listener_list 
4) sqlplus / as sysdba  
5) alter pluggable database emrep open read write; alter pluggable database emrep save state;
6) execute following as sysdba on cdb 
7) 
-- Recommended minimum settings.
alter system set "_allow_insert_with_update_check"=true scope=both;
alter system set session_cached_cursors=200 scope=spfile;
alter system set processes=600 scope=spfile;
alter system set pga_aggregate_target=450M scope=spfile;
alter system set sga_target=800M scope=spfile;
alter system set shared_pool_size=600M scope=spfile;
-- Unset any adaptive optimizer settings that were set for the previous installation. They are not needed now.
alter system reset "_optimizer_nlj_hj_adaptive_join" scope=both sid='*';
alter system reset "_optimizer_strans_adaptive_pruning" scope=both sid='*';
alter system reset "_px_adaptive_dist_method" scope=both sid='*';
alter system reset "_sql_plan_directive_mgmt_control" scope=both sid='*';
alter system reset "_optimizer_dsdir_usage_control" scope=both sid='*';
alter system reset "_optimizer_use_feedback" scope=both sid='*';
alter system reset "_optimizer_gather_feedback" scope=both sid='*';
alter system reset "_optimizer_performance_feedback" scope=both sid='*';
8) Restart the repository database.
9) check connecting remotely using sql developer/toad 
10) connectivity for cdb "emcdb" --> host:kayyum port:1521 sid:emcdb 
11) connectivity for pdb "emrep" --> host:kayyum port:1521 service:emrep 
12) copy all oem files *.zip and .bin to /u02 
13) chmog u+x *.bin 
14) ./em13500_linux64.bin 
15) wait for universal installer screen to popup 
vm installation:-
/ ---> 150gb
/tmp-->30gb
/home-->10gb
/var -->5gb
/biosboot ---> 1gb 
network:- 
bridged network automatic dhcp
NAT network assign 192.168.56.71