Posts Tagged ‘DBAimmam’

« Older Entries

RMAN SCRIPT TO MAKE BACKUP WITH DATES

April 12th, 2026, posted in Oracle
Share

*******************************************************************************************
************* Set Date, Log, Folders with Variable Names *****************************
*******************************************************************************************

#!/bin/bash
DD=(date +%d%m%Y)
BACKUP_BASE_DIR=”/backup_immam/RMAN_DB_BACKUP”
BACKUP_DIR=”$BACKUP_BASE_DIR/DB_$DD”
mkdir -p $BACKUP_DIR
date > $BACKUP_DIR/RmanStarttime.txt
LOG_FILE=”$BACKUP_DIR/RMAN_DB_BACKUP_$(date +%d%m%Y).log”

*******************************************************************************************
**************************** Starting Log File ******************************************
*******************************************************************************************

echo ==== started on date ==== >> $LOG_FILE”

*******************************************************************************************
**************************** Starting RMAN BACKUP ***********************************
*******************************************************************************************

rman target / msglog $LOG_FILE << EOF
RUN {
ALLOCATE CHANNEL ch1 DEVICE TYPE disk FORMAT ‘$BACKUP_DIR/backup_%u_%s_%p_%T’;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL ch1;
BACKUP AS COPY CURRENT CONTROLFILE FORMAT ‘$BACKUP_DIR/controlfile_%u_%s_%p_%T’;
}
exit;
EOF

*******************************************************************************************
**************************** Ending RMAN BACKUP ***********************************
*******************************************************************************************

echo ==== Online Backup Ends on date ==== >> $LOG_FILE
date > $BACKUP_DIR/RmanStoptime.txt

*******************************************************************************************
**************************** Ending Log File ********************************************
*******************************************************************************************

exit

Share
read more no comments

ORACLE ERROR:ORA-28000: the account is locked

February 9th, 2026, posted in Oracle Queries
Share

ORACLE ERROR : ORA-28000

The account is locked

From your command prompt, type sqlplus “/ as sysdba”
Once logged in as SYSDBA, you need to unlock the SCOTT account

SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;
Share
read more no comments

Oracle Database 23AI Session Related Scripts.

December 1st, 2025, posted in Oracle Queries
Share

Columns Name Details

sid – session identifier
serial# – session serial number
osuser – operating system client user name
machine – operating system machine name
program – operating system program name
module – name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure

——————————

select * from v$session;

——————————

select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,OSUSER,MACHINE from v$session where USERNAME=’SYSTEM’;

—————

select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,OSUSER,MACHINE from v$session where STATUS=’ACTIVE’;

———————

select sid,serial#,osuser,machine,program,module from v$session ;

———————–

Note : Sometimes need to find session details. 
You can change the last line to search for sessions that are running specific queries. 
Replace ALTER TABLE%SHRINK% with another command that you want to search.

SELECT SES.SID,SES.SERIAL# SER#,SES.PROCESS OS_ID,SES.STATUS,SQL.SQL_FULLTEXT
FROM 
   V$SESSION SES,V$SQL SQL,V$PROCESS PRC
WHERE
   SES.SQL_ID=SQL.SQL_ID AND SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND SES.PADDR=PRC.ADDR AND UPPER(SQL.SQL_FULLTEXT) LIKE UPPER(‘ALTER TABLE%SHRINK%’);

   ————————–

How to find sql text and session information history during a week.

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 order by a.SQL_EXEC_START asc;

Share
read more no comments

Set The Schema Session Limit In Oracle 23 AI Database

November 9th, 2025, posted in Oracle Queries
Share

In Oracle, to set a session limit per schema (user) – i.e., control how many concurrent sessions a specific user can have. You use profiles with the SESSIONS_PER_USER parameter.

Step-by-Step Guide to Set Session Limit per Schema.

1. Create a Profile with a Session Limit

If the profile doesn’t exist already, create one :

SQL > CREATE PROFILE limited_sessions_profile LIMIT SESSIONS_PER_USER 40;

This limits users assigned to this profile to 40 concurrent sessions.

2. Assign the Profile to a User (Schema)

SQL > ALTER USER target_user PROFILE limited_sessions_profile;

Replace target_user with the username (schema) you want to restrict. 

3. Check the Profile Assignment and Limit

To verify the profile assigned to the user :

SQL > SELECT username, profile FROM dba_users WHERE username = ‘TARGET_USER’;

4. To check session limits in the profile:

SQL > SELECT * FROM dba_profiles WHERE profile = ‘LIMITED_SESSIONS_PROFILE’ AND resource_name = ‘SESSIONS_PER_USER’;

5. Optional: Modify an Existing Profile

If the user already has a profile, and you want to add a session limit to it:

SQL > ALTER PROFILE existing_profile LIMIT SESSIONS_PER_USER 3;

Important Notes :

This setting does not immediately terminate sessions if they’re over the limit. It prevents new logins once the session count limit is reached. You can check current session counts with :

SQL > SELECT username,COUNT(*) FROM v$session WHERE username IS NOT NULL GROUP BY username;

Share
read more no comments

Restore The Drop Table In 19c Oracle Autonomous Data Warehouse

October 12th, 2025, posted in Oracle Queries
Share

In Oracle Autonomous Data Warehouse (ADW) 19c, if you have accidentally dropped a table and want to restore it, there are a few recovery options available depending on how the table was dropped and the time elapsed since then.

STEPS : Flashback – Using the Recycle Bin

1. Check the Recycle Bin for the dropped table

QL > SHOW RECYCLEBIN;
SQL > select * from DBA_RECYCLEBIN where ORIGINAL_NAME='TABLE_NAME';

2. Flashback the table (use the original table name)

SQL > FLASHBACK TABLE your_table_name TO BEFORE DROP;
SQL > FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;

Flashback succeeded.

3. Verify the drop table data.

SQL > select * from TABLE_NAME;

Notes :

Recycle Bin is enabled by default in ADW.
You cannot use RMAN or traditional tablespace restore in ADW – it’s fully managed.
ADW automatically takes backups every 60 minutes and retains them for 60 days; however, point-in-time table-level recovery from these backups is not user-managed.

Share
read more no comments
« Older Entries