Sharing knowledge is the ultimate key to gaining knowledge…
The only two things that stay with you for life are you & your knowledge...
Live while you can! Teach & inspire while you could & Smile while you have the teeth.
-
ASM
- Creating ASM Instance and diskgroups manually without DBCA
- 20 ASM Realtime | Interview Questions
- How to make a cold backup for database running on ASM ( using RMAN )
- Moving a Single Datafile from File System to ASM System (METHOD 1)
- Moving a Single Datafile from File System to ASM System (METHOD 2)
- Things to consider while adding new disk/ Rebalancing existing Disk Group
-
Dataguard
- Dataguard Modes
- Dataguard Modes - II
- Oracle DataGuard Setup
- invalid archived log deletion policy
- recover from the Loss of a Datafile on a Standby Database ?
- MRP terminated with ORA-00600: internal error code, arguments
- Recover archive gaps in standby database - using 2 methodss
- Gap Resolution in Dataguard
- Convert Physical Standby to Snapshot Standby and Vice Versa
- Re-create Redo-logs on Standby/ Primary
- Pre-check for DataGuard switchover Process
- ORA-19815: WARNING: db_recovery_file_dest_size
- How do you purge old archivelogs which are applied on Standby DB
- Pre-check For SWITCHOVER using DG Broker
- https://oracle-dba-help.blogspot.com/2016/03/data-guard-modes.html
- ORA-01274 : RENAME THE UNKNOW FILE IN THE STANDBY DATABASE
- Database Creation in Easy 6 Steps
- Oracle Networking
- Tablespace management
- Housekeep : Quick Tips for DBA
- Health check for DB
- ORA-12537 While Connecting To Database Via Listener
- How to identify the applied patche-set of your Oracle Home
- Accessing a schema without knowing the password
- ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
- Session Related Scripts
- How to check ORA Error on DB Server – Beginner Tip
- Working with Redo Logs
- Decommission of Oracle Database
- Roles in Oracle - Managing Users
- Oracle Release Number Format
- Troubleshooting Handy-Guide
- Understanding the Upgradation Process
- Query to find the session creating more redo
- Audit in Oracle
- Troubleshooting Internal Errors and Error-Look-up Tool on MOS
- Troubleshooting Issues with Undo Tablespace
- User Management
- User Management - II
- Data Pump Gets Better
- MEMORY_TARGET Parameter
- Installing PostgreSQL Server on CentOS, RHEL 6 / 7 Systems
- Creating a database in Postgres
- Initialize multiple Postgres instance on the same RHEL server
- How to stop starting of clusters while starting up services
- Setup a streaming replication
- Switchover- master server and warm standby (with streaming replication)
- All About Physical Replication and Log shipping
- PostgreSQL Upgrade and troubleshooting
- Timeline Switch issue
- Query to identify the lag in replication
- Drop table : ERROR: must be owner of relation table_name
- PgBadger
- PgCenter
- PgTune
- Internals of Toast Table
- Influencing Query Execution Plan
- Create Extension ERROR
- Find Uptime in PostgreSQL
- Set DB Timezone
- Clear RAM Cache, Buffer
- Read-Only User in Postgres
- Everything you need to know about Streaming Replication
- Ora2Pg Migrate Oracle to Postgres
- Total Page Cost
- PostgreSQL 13 - Everything you need to know
- Startup Failure - .conf file
- All About Postgres Upgrade
- Basic Guide
- Session Related Scripts
- AWR :: Beginners Guide
- All About ASH - Active Session History
- Wait Events : checkpoint busy waits or archiver busy waits
- Find sessions performing sort operations : TEMP Tablespace occupants
- Generate ADDM task and generate its report for Tuning
- Database Performance Tuning Scripts -Keep Handy
- Buffer Busy Wait
- Simulating the Deadlock
- Latches
- Steps to Schedule any RMAN Jobs In Windows Server
- ORA-01194: file 1 needs more recovery to be consistent
- Loss of Current Online Redo-Log
- RMAN "baby Steps"
- Recovery of ControlFiles
- Loss of Datafile
- Recovery from complete loss of all online redo log files
- Block Change Tracking
- RMAN Questions
- Instance Recovery
- Redundancy and Recovery window in RMAN
- Cold Backup
- Database Cloning
- Contact
- Drop Database using RMAN
- PITR to older Incarnation
Monday 25 June 2018
Sunday 10 June 2018
Troubleshooting Issues with Undo Tablespace
Commonly seen problems with the undo
tablespace are of the following nature:
These errors can be caused by many
different issues, such as incorrect sizing of the undo tablespace or poorly
written SQL or PL/SQL code.
• ORA-01555: snapshot too old
• ORA-30036: unable to extend segment
by ... in undo tablespace 'UNDO1'
Causes :
Frequent commits can be the cause of ORA-1555. It's all about read consistency. The time you start a query oracle records a before image. So the result of your query is not altered by DML that takes place in the meantime (your big transaction). The before image uses the rollback segments to get the values of data that is changed after the before image is taken. By committing in your big transaction you tell oracle the rollback data of that transaction can be overwritten. If your query need data from the rollback segments that is overwritten you get this error. The less you commit the less chance you have that the rollback data you need is overwritten. Typically this occurs when users are executing the PL/SQL procedures and code commits inside a cursor.
Actions :
1. Check if Undo Is Correctly Sized:
The below query checks for issues that
have occurred within the last day :
select to_char(begin_time,'MM-DD-YYYY HH24:MI') begin_time ,ssolderrcnt ORA_01555_cnt, nospaceerrcnt no_space_cnt ,txncount max_num_txns, maxquerylen max_query_len ,expiredblks blck_in_expired from v$undostat where begin_time > sysdate - 1 order by begin_time;
Output :
BEGIN_TIME ORA_01555_CNT NO_SPACE_CNT MAX_NUM_TXNS BLCK_IN_EXPIRED
---------------- ------------- ------------ ------------ ---------------
06-10-2018 14:52 0 0 42 0
02-10-2018 07:24 0 0 0 0
If this column reports a non-zero
value, you need to do one or more of the following tasks:
The most effective way is to “Increase
the UNDO_RETENTION initialization parameter”.
2. Below are the resolutions that can be taken hence
forth
• Commit less often, commit at the end only
• Ensure that code does not contain COMMIT statements
within cursor loops.
• Re-schedule long-running queries when the system
has less DML load or Off-peak hours
• Check the SQL’s that are consuming more undo and
try to tune the SQL statement throwing the errors.
• Finally, you may proceed to add extra rollback
segments (undo logs) to make more transaction slots available.
NOTE : A maximum of 4 days’ worth of
information is stored in the V$UNDOSTAT view. The statistics are gathered every
10 minutes, for a maximum of 576 rows in the table. If you’ve stopped and
started your database within the last 4 days, this view will only contain
information from the time you last started your database.
The following query displays the
current undo size and the recommended size for an undo tablespace with
recommended retention in seconds:
select sum(bytes)/1024/1024 cur_mb_size, dbms_undo_adv.required_undo_size(900) req_mb_size from dba_data_files where tablespace_name = (select value from v$parameter where name = 'undo tablespace');
Output:
CUR_MB_SIZE REQ_MB_SIZE
----------- -----------
51200 35840
The output shows that the undo
tablespace currently has size of 50GB allocated to it.
In the prior query, you used 900
seconds as the amount of time to retain information in the undo tablespace. To
retain undo information for 900 seconds, the Oracle Undo Advisor estimates that
the undo tablespace should be around 35G . In this example the
undo tablespace is sized adequately. If it were not sized adequately, you would
have to either add space to an existing data file or add a data file to the
undo tablespace.
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size' /
Output :
ACTUAL UNDO SIZE [MByte]
------------------------
51200
UNDO RETENTION [Sec]
--------------------
10800
OPTIMAL UNDO RETENTION [Sec]
----------------------------
14580
Find the sessions using view - v$session and v$transaction to get sessions consuming UNDO Segments :
select s.sid, s.serial#, s.osuser, s.logon_time ,s.status, s.machine ,t.used_ublk, t.used_ublk*16384/1024/1024 undo_usage_mb from v$session s ,v$transaction t where t.addr = s.taddr;
You can use below query using view –
v$SQL to get SQL statement associated with a user/session consuming undo space.
select s.sid, s.serial#, s.osuser, s.logon_time, s.status ,s.machine, t.used_ublk , t.used_ublk*16384/1024/1024 undo_usage_mb ,q.sql_text from v$session s, v$transaction t ,v$sql q where t.addr = s.taddr and s.sql_id = q.sql_id;
"Do something (anything). If you
don't do anything, you won't get anywhere.
Make it your hobby, not a chore, but above all have fun!" 😊
Make it your hobby, not a chore, but above all have fun!" 😊
Monday 26 March 2018
Wait Events : checkpoint busy waits or archiver busy waits
While
such wait events occur in AlertLog file, one must consider to proceed with ARCHIVER
TUNING
1.
Check the number of Online Redolog Members and size of the online redo logs.
Excessive size and the number of online redo log groups will give archiver more
time to catch up. Hence Adding more online logs does not help a situation where
the archiver cannot keep up with LGWR process.
It can help if there are bursts of redo
generation since it gives ARCH more time to average its processing rate over
time.
2.
In such cases you can add multiple archiver (ARCh) processes
Create
'alter system archive log all'. This will spawn archive processes at some fixed
interval may be required. These processes once spawned will assist archiver in
archiving any un-archived log in that thread of redo. Once it has been
completed, the temporary processes will go away.
3.
Evaluate checkpoint interval and frequency
There
are several possible actions include adding DBWR processes, increasing db_block_checkpoint_batch, reducing db_block_buffers. Turning on or allowing
async IO capabilities definitely helps alleviate most DBWR inefficiencies.
4.
Check OS supportability of asynchronous I/Os
Async
reads should help tremendously. Async writes may help if OS supports
asynchronous I/Os on file systems.
You
can check with your vendor if the current version of your operating system supports
async IO to file systems (ufs).
5.
Check for system or IO contention.
Check
CPU waits and usage, disk level
bottlenecks. Also check operating system manuals for the appropriate commands
to monitor system performance.
For
example, you can use UNIX commands such
as "sar 5 5 5" “sar –d ”or "iostat" to identify
disk bottlenecks.
Sunday 11 March 2018
Accessing a schema without knowing the password
Most
of the times that you may need to logon to a database user / schema owner, to
do so emergency maintenance,, but you
don’t know the password.
There
is an alternative where you can use alter session set current schema
“Schema-Name” ;
Other
than this you can use the below process, where you record the current password
encryption, change the password, logon and do your maintenance.
Let’s
try an example here
Create an account:
Create an account:
SQL> conn / as sysdba Connected. SQL> create user nikhil identified by mypass1 ; User created. SQL> grant connect , resource to nikhil ; Grant succeeded.
Now
if I want to change the password, then I should know the ‘Old Password’
SQL> conn nikhil/mypass1 ; Connected. SQL> password Changing password for NIKHIL Old password:
Oops..!
In
such cases you can use the user$ view under sys user which will give us the
encrypted password so that we will preserve the old password
Let’s
see..
SQL> conn / as sysdba Connected. SQL> select name,'alter user '||name||' identified by values '''||spare4||';'||password||''';' command from sys.user$ where name = 'NIKHIL'; NAME ------------------------------ COMMAND -------------------------------------------------------------------------------- NIKHIL alter user NIKHIL identified by values 'S:2549CDA4335FCEF7814FD9832AD653A937AAE8 7105AB962A873A59A576E8;FD135DE4875002BA';
Now I will set a temporary password for the account to perform the activity.. Later will set the old password using this encrypted values.
SQL> alter user nikhil identified by demopassword ; User altered. SQL> conn nikhil/demopassword ; Connected.
Now
connect as sysdba, and revert the
password using the encrypted values
SQL> conn / as sysdba Connected. SQL> alter user NIKHIL identified by values 'S:2549CDA4335FCEF7814FD9832AD653A937AAE87105AB962A873A59A576E8;FD135DE4875002BA'; User altered. SQL> conn nikhil/mypass1 ; Connected. SQL>
Alternatively,
we can use the DBMS_METADATA package to get the encryption;
SQL> set long 10000 select dbms_metadata.get_ddl('USER','NIKHIL') command from dual;SQL> COMMAND -------------------------------------------------------------------------------- CREATE USER "NIKHIL" IDENTIFIED BY VALUES 'S:2549CDA4335FCEF7814FD9832AD653A9 37AAE87105AB962A873A59A576E8;FD135DE4875002BA' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"
Friday 26 January 2018
Find sessions performing sort operations : TEMP Tablespace occupants
Most of the time, user will ask you to check long
running sessions in database and you will figure out the query which the
session is performing sort/join operation – reading the query you can say the
query is related to sorting. But you will also see the high usage of
Temporary segments or will see wait events such as “Direct Path Temp Read” Or
“Direct Path Temp Write”.
To rectify this you can use views such as
V$SORT_USAGE and V$SORT_SEGMENT to identify the other sessions performing sort
operation
Oracle provides two basic dynamic performance views
for online monitoring of temporary tablespace usage.
These are v$sort_usage to
query online sessions using temporary segments and v$sort_segment to query the
size of the temporary segments in the database.
V$SORT_USAGE has below main columns which can be useful here:
USERNAME
–
Session User name.
SQL_ID –
unique identifier of the SQL that requires the sort.
EXTENTS –
number
of extents in the temporary segment being used by particular sessions.
BLOCKS –
number of blocks in the temporary segment being used by particular
sessions.
SESSION_ADDR – address of the session, can be used to
identify the session in V$SESSION according to the SADDR column.
--Use below query to find the sessions consuming temp
segments
SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT(*) statements FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, P.program, TBS.block_size, T.tablespace ORDER BY sid_serial;
This will give the sid and serial# which be used to
find current query that is taking temp segment using view v$sqltext and passing
sql_id of the sid and serial#
using V$SORT_SEGMENT
SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total-SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;
Saturday 6 January 2018
How to identify the applied patche-set of your Oracle Home
Many
a times you will need to find the patch level of your database.
The
easiest way to get this information is from the OPatch utility. The OPatch utility can be found under
$ORACLE_HOME/OPatch directory.
The
command below will list out all the currently applied patches to your Oracle
environment using the files in the oraInventory.
Example:
$ORACLE_HOME/OPatch/opatch
lsinventory
The
same information can be seen from dynamic views
Example:
SQL>select
* from sys.v$version ;
SQL>select
* from sys.registry$history;
Sunday 31 December 2017
Worth Sharing: Things you should NOT do when you are angry
Things you should NOT do when you are angry 😼
*Hit your phone/gadgets on the wall.
*Punch the door/wall multiple times until your
knuckles start to bleed.
*Verbally abuse
people who love you and care for you, online or offline.
(All
these things will only add up to the pain and will hurt you even more later.)
Things you can do when you get angry 😊
*Punch
a pillow/cushion/a pile of blankets. (Find a punching bag if these are too soft
for you).
*Use
this anger to better yourself and do a ton of crunches/pushups/your fav.
exercise at home/gym.
*Go
on a long drive, preferably alone and notice the beauty of nature that
surrounds you. You can also simply walk or run.
*Grab
a pen and write the hell out of your thoughts. It's not childish. It's one of
the smartest things you can do to yourself. Don't hesitate, no one except you
is going to see that.
(Vent
your anger. Don't contain it. Anger is like poison. You need to let it You
need to let it out and make sure you don't hurt yourself in the process.)
Best
of luck. Share to others in need. 💙 😊
Happy
New Year
May this new year bring all your dreams and happiness come true.. Stay Blessed
May this new year bring all your dreams and happiness come true.. Stay Blessed
Steps to Schedule any RMAN Jobs In Windows Server
This post is about the
backup configuration made in Windows server and what are the backup strategies
mainly used in most of production databases.
Backup strategy followed
in most of production Databases.
1) Weekly RMAN full backup
2) Daily Archive log backup
3) Daily incremental backup
1) Weekly RMAN full backup
2) Daily Archive log backup
3) Daily incremental backup
In windows server
environment, we use “scheduler” (such as cronjob in UNIX) to schedule any job.
The important steps to configure a scheduler are as follows :
Taking Database backup weekly(incremental
level 0):
First create a cmd file (command file ) in windows:
First create a cmd file (command file ) in windows:
Let us create a file name week_backup.cmd using a text file editor(any editor) write the rman script to take the backup.
Assume, I want to take
full backup (i.e, LEVEL 0 Backup)
- Then the script for taking the weekly Level 0 backup would be like
this, (here controlfile autobackup is off(default)).
Step 1.
week_backup.cmd
Step 2.
1. backup_incr_1.cmd
2. backup_incr_1.bat
Now, Configure the scheduler as we did above in Step 3 and simply change the Scheduler option > as ‘daily’ and timings whichever you want.
RMAN>run{
Allocate channel ch1 type disk format '\path of taking backup_\%d_data_%U';
Backup incremental level=0 database tag=’full_backup';
Release channel ch1;
Allocate channel t1 type disk format '\ backup location \%d_ctrl_%U';
Backup current controlfile;
Release channel t1;
}
Step 2.
Create the .bat file (Batch file is for batch
job execution by scheduler)
Let us create a batch file week_backup.bat
week_backup.bat
Let us create a batch file week_backup.bat
The script looks like as below:
week_backup.bat
rman target sys/db1@db1 cmdfile=\Path of cmd file\week_backup.cmd <-------- you need to mention the file location we created at Step 1 above. log=E:\Path of log file\Full_DB_Backup_%date:~4,2%_%date:~7,2%_%date:~10%.log The file will be created as follows Full_DB_Backup_<Current_Date>.log
This batch file is used executing
this file through schedule.
Step 3.
Here comes the
scheduling part. We have to use scheduler for scheduling job:
Goto- Start Menu>
control panel >scheduler tasks >Add
scheduler task >command prompt >Perform this task > weekly >choose
day and timings when you want to run this job weekly >specify your administrator
username and password >finish
Configuring
Database backup Daily(incremental LEVEL
1)
For this activity, all
the above steps remains same, but the scripts and scheduler task option changes
here:
1. backup_incr_1.cmd
run{
allocate channel ch1 type disk format '\path of backup location_\%d_data_%U';
backup incremental level=1 database tag=Level_1_Backup';
release channel ch1;
}
2. backup_incr_1.bat
rman target sys/db1@DBNAME
cmdfile=\Path of cmd file created in Step1 above\ backup_incr_1.cmd
log=E:\Logfile location\Level_1_Backup_%date:~4,2%_%date:~7,2%_%date:~10%.log
Now, Configure the scheduler as we did above in Step 3 and simply change the Scheduler option > as ‘daily’ and timings whichever you want.
-
For information on UNIX System Commands you can check an article here – UNIX for DBA's
Click here for RMAN
recovery scenarios
Everything comes to us
that belongs to us, if we create the capacity to receive it
-Rabindranath Tagore
Subscribe to:
Posts (Atom)