• Wallet creation steps in oracle 11g database

    Wallet creation in oracle 11g database $ echo $ORACLE_HOME $ echo $ORACLE_BASE $ echo $DB_UNIQUE_NAME make sure all above environment variables are set properly$ mkdir $ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet$ orapki wallet create -wallet $ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet -auto_login -pwd xxxxxx"-autologin: enables autologin by that we don’t need to enable wallet when we restart$ ls $ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet cwallet.sso ewallet.p12How to set / find wallet path:SQL> SELECT * FROM ...

  • Zero Downtime database upgrade using Oracle Goldengate 12c

    Zero downtime database upgrade using Oracle Goldengate This document discuss about the setup of zero downtime database upgrade or migration using Oracle Goldengate. We will be covering the migration from Oracle Database to another oracle database. But the concept will remain same for heterogeneous RDBMS as well. To simulate database upgrade , in this example,  i am ...

  • Tuning SQL statements using SQL Tuning advisory in oracle database

    Step-1 : Create tuning taskHere you need :1) AWR snap ids begin snap (1001) 2) AWR end snap (1002) during sql execution time 3) sql id of your sql statement (sqlidxxxxxx) from your AWR reportSET SERVEROUTPUT ON -- Tuning task created for specific a statement from the AWR. DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( begin_snap ...

  • Find transactions which are causing high archive log generation in oracle

    To find transactions which are causing high archive log generation in oracle database SELECT s.sid, s.serial#, s.username, s.program, i.block_changes FROM v$session s, v$sess_io i WHERE s.sid = i.sid ORDER BY 5 desc, 1, 2, 3, 4;SELECT s.sid, s.serial#, s.username, s.program,t.used_ublk, t.used_urec FROM v$session s, v$transaction t WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, ...

  • How to find long running queries (long ops) in oracle

    How to find long running operations (long ops) in oracle ?set echo off  linesize 200  pages 1000  head on  feedback on col username format a10 col start_time format a15 col curr_time format a15 col osuser format a10 col opname format a35 col target format a25 col tremain format 999999.99 col elamin format 999999.99 select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,to_char(a.start_time,'dd-mon:hh24:mi:ss') start_time, to_char(sysdate,'dd-mon:hh24:mi:ss') ...

  • How to check session wait events in oracle database

    Script to find  session wait events in oracle databaseset linesize 1000 col p1 format 9999999 col p2 format 9999999 col program format a15 col event format a20 col event form a25 trunc head "Event| Waiting For" col command format a15 col sid format 9999 col machine format a20 col username format a20 select ...

  • How to check all sessions information (active and inactive) in oracle database ?

    How to check all sessions information (active and inactive) in oracle database ?set echo off   linesize 200  pages 1000  head on  feedback on col sid head "Sid" form 9999 trunc col serial# form 99999 trunc head "Ser#" col username form a8 trunc col osuser form a7 trunc col machine form a20 trunc head "Client|Machine" col program form ...

  • Oracle DBA Useful Queries Part-1 – “Basic DBA Queries”

    Dba scripts will help in the easy database administration . In this blog we discuss about some of the useful dba scripts , which helps on day to day database administration

  • How to take table backup in sqlserver using select statement

    Using below T-SQL select statement we can take table backup into new tableSyntax: select * into new_table from old_tableExample: use wysheid_db go SELECT * INTO dbo.wysheid_new from dbo.wysheid go

  • How to verify backups in sql server

    How to verify backups in sql server1) RESTORE VERIFYONLY 2) RESTORE HEADERONLY 3) RESTORE FILELISTONLY 4) RESTORE LABELONLY--Backup is valid or notRESTORE VERIFYONLY FROM DISK = 'E:\SQLBAKUP\backuptest.bak'-- How many backup sets are there in your backup. Suppose if you take backup more than one time into one file this command will show you all backup-sets in that ...

  • How to find your sqlserver IP address and port number in sqlserver cluster

    This TSQL script returns Server Name, Cluster Node, Default Drive Letter, IP address and Port sqlserver cluster environment SELECT SERVERPROPERTY('ServerName') as 'Server\Instance',  SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as 'Node',  (select left(af.filename,3)from sys.sysaltfiles af where af.name like '%MSDB%' and af.fileid = 1) as 'Drive',  ec.local_net_address as 'IP Addr',  ec.local_tcp_port as 'SQL Port'from sys.dm_exec_connections ec where ec.session_id = @@SPID

  • How to get user permissions in sql server database

    This T-SQL script will provide all privileges, roles and permission of particular user name in sql server database Replace your user name with  “YOUR_USER_NAME” in the below script select sys.schemas.name 'Schema' , sys.objects.name Object , sys.database_principals.name username , sys.database_permissions.type permissions_type , sys.database_permissions.permission_name , sys.database_permissions.state permission_state , sys.database_permissions.state_desc , state_desc + ' ' + permission_name + ' ...

  • Working with snapshots in sql server

    A SQL Server database snapshot provides a read-only, static view of a source database as it existed at snapshot creation, minus any uncommitted transactions. Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created (transactions in the database are not affected).”   ...

  • How to move or relocate system database files in sql server

    We can use below steps to relocate data files in sql serverHigh level steps : For model,msdb and tempdb you can run alter statements  For master database change startup parameters  stop sql server move files to desired locations start sql severStep-1)  Run alter statement to relocate filename of model,mdsb and tempdb USE master  GO ALTER DATABASE model MODIFY FILE (NAME = ...

  • How to monitor progress of backup and restore in sql server

    Handy T-SQL script to monitor sql server backup restore progress and percentage of completion

  • How to find SQL Server start time ?

    script for finding sql server start time