mark We can monitor the progress of an undo operation by running the query shown below: select session.username , substr(session.program, 1, 19) command , transaction.used_ublk , from v$session session , v$transaction transaction where session.saddr = transaction.ses_addr; From the first session connected as SH we issue a DELETE statement SQL> conn customer/customer123 Connected. SQL> delete * from … Read more
The cluster endpoint connects you to the primary instance for the DB cluster. You can perform both read and write operations using the cluster endpoint. The DB cluster can also have up to 15 Aurora Replicas that support read-only access to the data in the DB cluster. The primary instance and each Aurora Replica has … Read more
A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds. A deadlock can occur when transactions lock rows in multiple tables (through statements such as UPDATE … Read more
This is some code how to correctly shrink a datafile. set linesize 1000 pagesize 0 feedback off trimspool on with hwm as ( — get highest block id from each datafiles ( from x$ktfbue as we don’t need all joins from dba_extents ) select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by … Read more
Solution Kill user sessions in RAC 10g Goal The 10g version of Oracle RAC only allows ALTER SYSTEM KILL SESSION (SID, Kill_Session_User(p_username VARCHAR2) – This takes the schema/user name and will loop through all sessions for this user in gv$sessions — Then uses supplies this SID, SERIAL#, INST_ID to Kill_Session_Job Kill_Session_Job(p_sid NUMBER, p_serial NUMBER, p_inst_id … Read more
Here is a script I’ve put together to backup both cluster and non clustered databases in AWS. (Still in progress). import boto3 import datetime import sys # Author: Mark Young # Date: 3rd October 2017 # Detail: Automatic database snapshots are restricted to 35 days # This script enables us to keep snapshots for extended … Read more
Recently AWS released their version of MySQL called Aurora, they claim it delivers up to five times the throughput of standard MySQL. This article explores some of the depths of this database technology, especially coming from an Oracle Database background. Character Sets In an Oracle system, you can define 2 character sets, one is used … Read more
AWS Aurora Point in time recovery Restore AWS Aurora from Point in time snapshot (go back to a specific point in time). These are the Steps Restoring to point in time snapshot of AWS RDS Aurora (to any time with-in the backup retention period irrespective of the frequency of automated backups). 1. In AWS console, … Read more
Restore from a snapshot (either manual or automated). NOTE: To restore the new instance with same as the original one, first the old/original instance must be renamed to something else then the new instance from snapshot can be restored with the required name (can be same as old). Restoring from manual/automated snapshot of AWS RDS … Read more
I was asked to check that we have a backup of database prior to decommissioning it and found the following which was interesting. As we have implemented a backup strategy at the company I work, I was a little confused since we just introduced a new backup method with Netbackup that now uses an intelligent … Read more