Monday, 14 August 2017

cpu usage per session

-
-- Show CPU Usage for Active Sessions
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)"  FORMAT 999,999,999.0000
 
SELECT
   s.username,
   t.sid,
   s.serial#,
   SUM(VALUE/100) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND
   t.SID = s.SID
AND
   s.status='ACTIVE'
AND
   s.username is not null
GROUP BY username,t.sid,s.serial#
/

check the database is consistent after incomplete recovery before open resetlogs

For cold/offline backups, no archivelogs/recovery is necessary. You can simply open the database with resetlogs.

However for HOT/ONLINE backups, ALL archivelogs from backup start to backup end must be applied before the database can be opened this is the MINIMUM amount of recovery needed.

To determine which log was current at the time the backup completed, note the COMPLETION time of the database backup take this from the backup log.

If this is an RMAN backup you can also query the RMAN metadata. Ensure that the environment variable NLS_DATE_FORMAT is set before invoking rman so that timestamps as well as date are returned:

For unix:

export NLS_DATE_FORMAT='dd-mon-rr hh24:mi:ss'

rman target /

For windows:

set nls_date_format='dd-mon-rr hh24:mi:ss'

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;

check 1. Checkpoint Time and Fuzziness

SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZZY STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)

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

NO ONLINE 5311260 31-AUG-2011 23:10:14 6

YES ONLINE 5311260 31-AUG-2011 23:10:14 1

a) Verify that the checkpoint_time / checkpoint_change# is in line with your intended UNTIL TIME/SCN. If not, recover the database further if you have more archived logs available.

b) If FUZZY=YES for some datafiles, it means more recovery is required.

Above check can be considered PASSED when :

a) Verified that all the datafiles are at the some checkpoint_time, and this is your intended Point in time.

b) Fuzzy=NO for SYSTEM, UNDO and all intended datafiles. For datafiles with Fuzzy=YES, either recover them further or bring them OFFLINE if no further archived logs are available.

Check 2: Absolute Fuzzy

Occasionally, it is possible to see Fuzzy=NO and same checkpoint_change# for all the intended datafiles but OPEN RESETLOGS still fails

select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZZY STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)

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

NO ONLINE 5311260 31-AUG-2011 23:10:14 7

SQL> ALTER DATABASE OPEN RESETLOGS ;

ORA01194:file 4 needs more recovery to be consistent

ORA01110:data file 3: '/u01/app/oracle/oradata/prod111/undotbs02.dbf'

Hence, we should perform additional fuzzy check known as Absolute Fuzzy Check:

SQL> select hxfil file#, substr(hxfnm, 1, 50) name, fhscn checkpoint_change#, fhafs Absolute_Fuzzy_SCN, max(fhafs) over () Min_PIT_SCN from x$kcvfh where fhafs!=0 ;

FILE# NAME CHECKPOINT_CHANGE ABSOLUTE_FUZZY_SCN MIN_PIT_SCN

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

4 /u01/app/oracle/oradata/prod111/undotbs01.dbf 5311260 5311524 5311524

6 /u01/app/oracle/oradata/prod111/system01.dbf 5311260 5311379 5311524

Note: Column Min_PIT_SCN will return same value even for multiple rows as we have applied ANALYTICAL "MAX() OVER ()" function on it.

Above query indicates that the recovery must be performed at least UNTIL SCN 5311524 to make datafiles consistent and ready to OPEN. Since the checkpoint_change# is smaller than Min_PIT_SCN, the datafiles will ask for more recovery.

Above check can be considered PASSED when:

a) No rows selected from above query (i.e. Min_PIT_SCN is 0 (Zero) for all the datafiles)

b) Min_PIT_SCN is returned less than Checkpoint_Change#

Check 3: Archive Logs Required:

Query the controlfile to find the latest archivelog required for recovery. Lets say the backup completed at 31-AUG-2011 23:20:14:

SQL> V$ARCHIVED_LOG

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';

SQL> SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG WHERE '31-AUG-11 23:20:14' BETWEEN FIRST_TIME AND NEXT_TIME;

If the above query does not return any rows, it may be that the information has aged out of the controlfile run

the following query against v$log_history.

SQL> V$ LOG_HISTORY view does not have a column NEXT_TIME

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';

SQL> select a.THREAD#, a.SEQUENCE#, a.FIRST_TIME from V$LOG_HISTORY a

where FIRST_TIME =

( SELECT MAX(b.FIRST_TIME) FROM V$LOG_HISTORY b

WHERE b.FIRST_TIME < to_date('31-AUG-11 23:20:14', 'DD-MON-RR HH24:MI:SS')

) ;

The sequence# returned by the above query is the log sequence current at the time the backup ended let say 530 thread 1.

For minimum recovery use: (Sequence# as returned +1 ) :

RMAN> RUN

{

SET UNTIL SEQUENCE 531 THREAD 1;

RECOVER DATABASE;

}

If this is a RAC implementation the use this SQL instead to query the controlfile:

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG WHERE '31-AUG-11 23:20:14' BETWEEN FIRST_TIME AND NEXT_TIME;

For minimum recovery use the log sequence and thread that has the lowest NEXT_CHANGE# returned by the above query.

Above check can be considered PASSED when:

All archivelogs from the time of the backup to the end of the backup is available for use during recovery

Friday, 28 July 2017

Day to Day commands for sql server

For Full Backup

BACKUP DATABASE [RavisDatabase] TO DISK = N'C:\Ravis\Backup Files\RavisDatabase_Full.bak' WITH NOFORMAT, NOINIT, NAME = N'RavisDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

split backup

BACKUP DATABASE
[Xnet_content_NAEU1] TO
DISK = N'L:\Bkup01\MSSQL11.SQLX\MSSQL\Backup\ManualBackups\Xnet_content_NAEU1_1.bak',
DISK = N'L:\ARCH01\Manual_Backup\Xnet_content_NAEU1_2.bak',
DISK = N'L:\ARCH01\Manual_Backup\Xnet_content_NAEU1_3.bak'
WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'Xnet_content_NAEU1-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

For Differential Backup

BACKUP DATABASE [RavisDatabase] TO DISK = N'C:\Ravis\Backup Files\RavisDatabase_Diff.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'RavisDatabase-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

For Transactional Log Backup

BACKUP LOG [RavisDatabase] TO DISK = N'C:\Ravis\Backup Files\ RavisDatabase_Log.trn' WITH NOFORMAT, NOINIT, NAME = N'RavisDatabase-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

For Restore Database

RESTORE DATABASE [RavisDatabase] FROM DISK = N'C:\Ravis\Backup Files\RavisDataFull.bak' WITH FILE = 1, MOVE N'RavisDatabase_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_80.TEST\MSSQL\DATA\RavisDatabase.mdf', MOVE N'RavisDatabase_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_80.TEST\MSSQL\DATA\RavisDatabase_1.LDF', NOUNLOAD, STATS = 10

Restoration Database with Recovery

RESTORE DATABASE [RavisDatabase] WITH RECOVERY (for bring online database)
SP_HELP_REVLOGIN

Restore Database up to Specific Time only

RESTORE DATABASE [RavisDatabase] FROM DISK = [N'C:\Ravis\Backup Files\RavisDataFull.bak'] WITH NORECOVERY
GO
RESTORE LOG [RavisDatabase] FROM DISK = N'C:\Ravis\Backup Files\ RavisDatabase_Log.trn ' WITH RECOVERY,
STOPAT= ‘MARCH 23, 2009 05:31:00 PM’
GO

Checking to make sure a SQL Server backup is useable

RESTORE VERIFYONLY FROM DISK = C:\RavisData.BAK
GO

For used space on the particular database
SP_SPACEUSED

Check the update statistics if SQL Server
SP_UPDATESTATS

Display the Configuration Setting of the Current Server
SP_CONFIGURE

Check the Log Space of All Databases of Server
DBCC SQLPERF(LOGSPACE)

Check for the Drive Size of the Physical Server
XP_FIXEDDRIVES

For Check the Error Log
SP_READERRORLOG

Data File and Log File Location for All Server

SELECT DB_NAME (DATABASE_ID), NAME, TYPE_DESC, PHYSICAL_NAME, [SIZE(MB)] = (SIZE * 8) / 1024.0 FROM SYS.MASTER_FILES

For Missing Index Details
SELECT *FROM SYS.DM_DB_MISSING_INDEX_DETAILS (MISSING INDEX)

Check Recovery Model
SELECT NAME, RECOVERY_MODEL_DESC FROM SYS.DATABASES
WHERE NAME = ['DB_NAME']

Getting All Table Name from particular database
USE [DB_NAME]
GO
SELECT * FROM SYS.TABLES
GO
Number of Cores on the Server
SELECT ( cpu_count / hyperthread_ratio )AS NumberOfPhysicalCPUs
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS NumberOfCoresInEachCPU
, CASE
WHEN hyperthread_ratio = cpu_count THEN cpu_count
ELSE ( cpu_count / hyperthread_ratio ) * ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS TotalNumberOfCores
, cpu_count AS NumberOfLogicalCPUs
, CONVERT(MONEY, Round(physical_memory_in_bytes / 1073741824.0, 0)) AS TotalRAMInGB
FROM
sys.dm_os_sys_info

Check Dead Lock on Server
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id

Checking Locking Process
EXEC SP_LOCK
EXEC SP_WHO2
USE [DB_NAME]
GO
SELECT * FROM SYS.DM_TRAN_LOCKS
GO

Bring Database Offline
ALTER DATABASE [DB_Name] SET OFFLINE WITH
ROLLBACK IMMEDIATE

Bring Database Online
ALTER DATABASE [DB_Name] SET ONLINE

Check which Backup Failed on when

EXEC sp_readerrorlog 0, 1, 'BACKUP failed'; -- current
EXEC sp_readerrorlog 1, 1, 'BACKUP failed'; -- .1 (previous)
EXEC sp_readerrorlog 2, 1, 'BACKUP failed'; -- .2 (the one before that)

Most Recent Database Backup for Each Database
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name

Database Backups for all databases For Previous Week
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

Check Database ISOLATION Level
DBCC USEROPTIONS

Buffer Cache Hit Ratio
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'

Who is disable the job on sql server
EXEC msdb.dbo.sp_helpjob

Physical Location for the database files (data file and log file)
SELECT NAME, PHYSICAL_NAME AS CURRENT_FILE_LOCATION FROM SYS.MASTER_FILES

Checking the Log shipping Last Backup LSN Number for backups of primary and secondary server.
SELECT SECONDARY_DATABASE, LAST_COPIED_FILE, LAST_RESTORED_FILE FROM MSDB..LOG_SHIPPING_MONITOR_SECONDARY

Find SQL Server Startup Time.
SELECT SQLSERVER_START_TIME FROM SYS.DM_OS_SYS_INFO

Find SQL Server Database Size.

USE DATABASE [DATABASE_NAME]

GO;

SELECT

DBNAME,

NAME,

[FILENAME],

SIZE AS 'SIZE(MB)',

USEDSPACE AS 'USEDSPACE(MB)',

(SIZE - USEDSPACE) AS 'AVAILABLEFREESPACE(MB)'

FROM

(

SELECT

DB_NAME(S.DATABASE_ID) AS DBNAME,

S.NAME AS [NAME],

S.PHYSICAL_NAME AS [FILENAME],

(S.SIZE * CONVERT(FLOAT,8))/1024 AS [SIZE],

(CAST(CASE S.TYPE WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(S.NAME, 'SPACEUSED') AS FLOAT)* CONVERT(FLOAT,8) END AS FLOAT))/1024 AS [USEDSPACE],

S.FILE_ID AS [ID]

FROM

SYS.FILEGROUPS AS G

INNER JOIN SYS.MASTER_FILES AS S ON ((S.TYPE = 2 OR S.TYPE = 0) AND S.DATABASE_ID = DB_ID() AND (S.DROP_LSN IS NULL)) AND (S.DATA_SPACE_ID=G.DATA_SPACE_ID)

) DBFILESIZEINFO

Wednesday, 26 July 2017

DBA Day to Day commands

FRa size db
______

set lines 100
col name format a60
select name,
floor(space_limit / 1024 / 1024) "Size MB",
ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/

NAME Size MB Used MB
------------------------------------------------------------ ---------- ----------
+FLASH01 102400 102337

ALTER SYSTEM SET db_recovery_file_dest_size = 105g SCOPE = BOTH;

alter system set db_recovery_file_dest=

alter system set DB_RECOVERY_FILE_DEST='+FLASH01' SID='*';

ALTER SYSTEM SET db_recovery_file_dest_size = 53g; SCOPE = BOTH;

Use this query to find the session information from OS Process id
----------------------------------------------------------------------------------------------------------------------

select sid,serial#,status,machine,program,sql_hash_value,to_char(sysdate,'DD/MM/YYYY HH24:MI') from v$session
where paddr in (select addr from v$process where spid=&spid)
/

select spid from v$process where addr =(select paddr from v$session
where sid=&sid)
/

====================================================================================================
find . -name "030609_GSM.txt" -print (for searching a file from the filesystem)
find . -print|xargs grep -i v\$session
find . -mtime -1 -print (To find Recently created files)
find . -size +10000 -print (find largest files on the server)
find . -mtime +7 (Delete files in bulk)
find . -size +100000 -exec ls -alrt {} \;
find . -size +10000 -exec ls -alrt {} \;
find / -name Chapter1 -type f -print
find /usr -name Chapter1 -type f -print
find /usr -name "Chapter*" -type f -print
find /usr/local -name "*.html" -type f -print
find /usr/local -name "*.html" -type f -exec chmod 644 {} \;
find . -type f -exec ls -l {} \;
find . -type d -exec ls -ld {} \;
find . -type f -name "Poop*" -exec rm {} \;
find . -type d -name CVS -exec rm -r {} \;
find . -type f -name -exec chmod 644 {} \;
find . -type d -name -exec chmod 755 {} \;
locate lost-file
====================================================================================================
Memory Check

$prtconf | grep "Mem" (Solaris)
$prtconf|grep -i mem (Solaris)
$lsdev -C|grep mem (AIX)
$ipcs -pmb (Allocated memory Segments)
$lsdev -C|grep Process|wc -l (Dispays the number of CPU's)
$psrinfo -v|grep "Status of processor"|wc -l (Dispays the number of CPU's in Solaris)
$kmtune|grep sem (Displays values for semaphores)
$grep error /var/adm/syslog/syslog.log|more (Show server log in HP-UX)
$errpt -a|more (Show server log in AIX)
$lsps -a (Displays swap usage in AIX)
$swapinfo -tam (Displays swap usage in HP-UX)
====================================================================================================
Script to create DB link

select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by d3vf0rsys# using '''|| HOST||''';' from dba_db_links
where DB_LINK IN (SELECT SID||'_'||SERVER FROM database_list where USAGE='PROD');

select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by ' ||
substr(a.SERVER,0,1)||substr(a.server,(length(a.SERVER)),1)||'br0adc#st using '''|| HOST||''';' from dba_db_links,ops$oracle.database_list a
where DB_LINK IN (SELECT SID||'_'||SERVER FROM ops$oracle.database_list where USAGE='PROD')
and substr(DB_LINK,1,instr(DB_LINK,'_')-1)=a.sid

Script to Drop DB link

select 'drop database link '||DB_LINK||';' from dba_db_links
where DB_LINK IN (SELECT SID||'_'||SERVER FROM database_list where USAGE='DEV');

select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by ' ||
substr(a.SERVER,0,1)||substr(a.server,(length(a.SERVER)),1)||'d3cis1#n using '''|| HOST||''';' from dba_db_links,ops$oracle.database_list a
where DB_LINK IN (SELECT SID||'_'||SERVER FROM ops$oracle.database_list where USAGE='DEV')
and substr(DB_LINK,1,instr(DB_LINK,'_')-1)=a.sid
===================================================================================================
SNAPSHOT_FEATURE PROCEDURE (REBUILD)

select OWNER,NAME,MASTER_OWNER,MASTER,to_char(LAST_REFRESH,'dd/mm/yyyyhh24:mi') from DBA_SNAPSHOT_REFRESH_TIMES where name like '%SERVICE_FEATURE%'

select host,OWNER from dba_db_links where db_link='CUSTDB.WORLD';

select OWNER,NAME,RNAME,REFGROUP,JOB,NEXT_DATE from DBA_REFRESH_CHILDREN;

nohup ./run.sh @snapshot_rebuild.sql &

BEGIN
DBMS_REFRESH.ADD(
name => '"DBLUSGO"."MPSGUIDE_SNAPS"',
list => '"DBLUSGO"."SERVICE_FEATURE_SNP"',
lax => TRUE);
END;

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DBLUSGO', tabname => 'SERVICE_FEATURE_SNP', cascade => TRUE, estimate_percent => 5, method_opt => 'FOR ALL COLUMNS SIZE 1');
====================================================================================================
This script provides the backup status of the datafiles in the database.
This is a good check to perform on an oracle database before the start of every business day to make sure that none of the oracle data files are left in a backup mode.

set lines 132
col name format a50
select * from (
select f.name, to_char(b.time,'mm/dd hh24:mi'), b.status
from v$backup b, v$datafile f
where b.file# = f.file#
order by b.time desc)
where rownum < 11
/
====================================================================================================
set pages 300
set linesize 3000
select rpad(a.tablespace_name,20,'.')tablespace_name,
sum(a.bytes)/(1024*1024) Allocated,
sum(a.bytes)/(1024*1024) - max(nvl(b.space,0)) Used,
max(nvl(b.space,0)) Free,
max(nvl(b.cont,0))/(1024*1024) Contiguous
from dba_data_files a,
(select tablespace_name,sum(bytes)/(1024*1024) space,max(bytes) cont
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
group by a.tablespace_name order by TABLESPACE_NAME
/
====================================================================================================
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB",
decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB",
decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by tablespace_name
/
====================================================================================================
select index_name ,column_position colp,rtrim( column_name) colu
from user_ind_columns
where index_name in (
select index_name from user_indexes
where table_name = '&tname')
order by index_name,column_position
/

select index_type from user_indexes where index_name in ('IX_sms_content_1','IX_sms_content_2');

drop index "IX_sms_content_1";

create index "IX_sms_content_1" on "sms_content" (CUSTOMERID,FOLDERID,MESSAGECONTENTID) online compute statistics tablespace O2OM_DATA_INDEX_01;
====================================================================================================
execute dbms_stats.gather_table_stats (ownname => 'TAP_USER',tabname=>'TAPOUT',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8,cascade => TRUE);

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DBLUSGO', tabname => 'SERVICE_FEATURE_SNP', cascade => TRUE, estimate_percent => 5, method_opt => 'FOR ALL COLUMNS SIZE 1');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'EPIMM',METHOD_OPT=>'FOR ALL INDEXED COLUMNS ',DEGREE=>8,CASCADE=>TRUE);

exec DBMS_JOB.BROKEN( job=>192, broken=>FALSE );

exec dbms_system.SET_SQL_TRACE_IN_SESSION('&SID','&SR',&TF);

exec statspack.snap (i_snap_level=>7)

exec DBMS_JOB.remove (212);

exec DBMS.JOB.RUN (214);
====================================================================================================
===========================================================================================================================
select table_name from dict where table_name like '%CONS%';
===========================================================================================================================
select 'GRANT EXECUTE ON '||OWNER||'.'||OBJECT_NAME||' TO CHS_READ;' FROM DBA_OBJECTS WHERE OWNER='DSSPRIVATE' AND OBJECT_TYPE ='PROCEDURE';

select 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||OWNER||'."'||OBJECT_NAME||'" TO ROK_S;' FROM DBA_OBJECTS WHERE OWNER='O2OMDDL' AND OBJECT_TYPE IN ('TABLE');

select 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO MCIVER_F;' FROM DBA_OBJECTS WHERE OWNER='OADBA' AND OBJECT_TYPE IN ('TABLE');

select distinct owner from dba_segments where segment_name in ('MPPI_USERWALLET','MPPI_INSTRUMENT','MPPI_INSTRUMENT_TYPE','WALLET_PAYMENT_INSTRUMENT','MPPI_TRANSACTION','TOPUP_TRANSACTION','MPPI_AUDIT_USERGENERAL');

select 'GRANT SELECT ON '||OWNER||'.'||SEGMENT_NAME||' TO BI_DSTAGE_EXT;' FROM dba_segments where segment_name in ('MPPI_USERWALLET','MPPI_INSTRUMENT','MPPI_INSTRUMENT_TYPE','WALLET_PAYMENT_INSTRUMENT','MPPI_TRANSACTION','TOPUP_TRANSACTION','MPPI_AUDIT_USERGENERAL');

select FREE_SPACE,MAX_FREE_SIZE,USED_SPACE,AVG_USED_SIZE from v$shared_pool_reserved;
select 'exec DBMS_JOB.BROKEN (job==> '||JOB|| ',broken==>TRUE);' from dba_jobs where LOG_USER!='SYS;

select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||' ;' from dba_role_privs where GRANTEE='C_NALABOTHU_R';

select 'execute dbms_stats.gather_table_stats (ownname =>'||''''||OWNER||''''||',tabname=> '||''''||TABLE_NAME||''''||',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8,cascade => TRUE);' from dba_tables where OWNER='STAGING' and LAST_ANALYZED IS NULL;

select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

select profile,resource_name,limit from dba_profiles order by profile;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

select table_name,constraint_name,status from dba_constraints where constraint_name='SYS_C008302';

select count(*),OBJECT_TYPE,owner from dba_objects where owner NOT IN ('SYSTEM','SYS','QUEST') group by OBJECT_TYPE,owner;

select owner,last_analyzed,num_rows from dba_tables where table_name=''

select bytes/1024/1024,segment_name from dba_segments where segment_type='INDEX' and owner='QUEST' order by 1;

select TABLESPACE_NAME,SEGMENT_NAME,EXTENTS from dba_segments where segment_name='AUD$';

select 'alter system kill session '||''''||sid||','||serial# ||''''||' immediate ;' from v$session where trunc(logon_time)<sysdate;

select sid||','||username||','||osuser||','||to_char(logon_time,'DD-MON-YY HH24:MI') from v$session where program like '%TOAD%' and logon_time < sysdate-4 order by logon_time;

select sid,serial#,logon_time from v$session where upper(program) like '%TOAD%';

select holding_session from dba_blockers a where not exists (select 'x' from dba_waiters b where b.waiting_session = a.holding_session)

select default_tablespace from dba_users ;

select username,account_status from dba_users where username like ''

select PRIVILEGE from dba_sys_privs where GRANTEE=MULLIGAN_N';

select GRANTED_ROLE from DBA_ROLE_PRIVS where GRANTEE='HSERVER';

select PRIVILEGE from dba_sys_privs where GRANTEE='HSERVER';

select sum(bytes/1024/1024) from dba_segments where owner='O2OMDML';

select * from v$access where object=''

select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username='MKALDAS';

select count(*),trunc(first_time) from v$log_history group by trunc(first_time);

select sum(bytes/1024/1024/1024) from dba_segments;

select sum(bytes/1024/1024) from dba_segments where segment_name='AUD$';

select sum(bytes/1024/1024) from dba_segments where owner='EPIMM';

select tablespace_name,bytes/1024/1024/1024 from sm$ts_avail order by 1;

select tablespace_name,bytes/1024/1024/1024 from sm$ts_used order by 1;

select tablespace_name,bytes/1024/1024/1024 from sm$ts_free order by 1;

select bytes/1024/1024 as mb,tablespace_name from sm$ts_free where tablespace_name='SYSTEM';

select bytes/1024/1024,file_name from dba_data_files where tablespace_name='UNDOTBS1'

select bytes/1024/1024 as mb,tablespace_name from sm$ts_avail;

select bytes/1024/1024 as mb,tablespace_name from sm$ts_used;

select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

select sid,serial#,status,logon_time from v$session where program like 'rman%';

select count(*) from v$session where type='USER';

select count(*),trunc(logon_time) from v$session where type='USER' group by trunc(logon_time);

select fuzzy,count(*) from v$datafile_header group by fuzzy;

select sid,failures,last_date,this_date from dba_jobs_running where job in (' ');

select sid,serial#,username from v$session where lockwait is not null;

select * from gv$session where lockwait is not null;

select * from v$session where lockwait is not null;

s

select HOLDING_SESSION from dba_blockers where HOLDING_SESSION not in (select WAITING_SESSION from dba_waiters);

select inst_id from gv$instance;

select inst_id,username,count(*) from gv$session group by inst_id,username;

select hash_value from v$sql where sql_text like ' ';

select hash_value,sql_text from v$sqltext where hash_value = '&1' order by piece;

select * from gv$lock where block <1;

select TABLESPACE_NAME,
2 ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='amo_msoffline_std';

select file_name from dba_temp_files;

select memeber from v$logfile;

select name from v$controlfile;

select open_mode from v$database;

select instance_name from v$instance;

select name from v$tablespace;

select name,status from v$datafile;

select temporary_tablespace from dba_users;

select * from dba_synonyms where owner='';

select username,owner,action_time,tiemstamp from dba_audit_objects where object_name='';

select max(sequence#) from v$log_history;

select host_name from v$instance;

select host from dba_db_links;

select dbid from v$database;

select * from registry$history;

select * from dba_registry;

select * from v$backup;

select * from v$recoverfile;

select distinct status from v$datafile;

select TEXT from dba_source where name='VERIFY_FUNCTION';

select * from V$PWFILE_USERS;

select * from ejbtimer;
===========================================================================================================================

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

alter user username identified by

alter database datafile '' offline drop;

alter database datafile 'PATH' resize ;

alter database open;

alter database default temporary tablespace '';

alter user QUEST default tablespace QUEST_INDEX;

alter tablespace UNDOTBS1 add datafile '/crmc1p_db17/oradata/undotbs05.dbf' size 2000m;

alter database backup controlfile to trace;

alter profile O2_USER_PROFILE limit PASSWORD_VERIFY_FUNCTION NULL

alter user SRIRAM_S PASSWORD EXPIRE;

ALTER USER C_NALABOTHU_R QUOTA 10M ON USR;

alter view STAGING.TD_D_SCORE_V compile;

CREATE INDEX "IX_sms_sentstate_3" ON "sms_sentstate"(CustomerID) TABLESPACE "O2OM_DATA_INDEX_01";

===========================================================================================================================
drop tablespace tablespace_name including contents and datafiles;

drop temporary tablespace tablespace_name including contents and datafiles;

drop user username;

drop user username cascade;

drop table tablename purge;

drop table tablename cascade constraints;

drop table tablename;
====================================================================================================

find tablespaces is online or offiline :

select tablespace_name, online_status from dba_data_files where tablespace_name='tablespace_name';

find tablespaces is autoexten, tbalespace name :

select file_name , tablespace_name, autoextensible from dba_data_files;

find the privilege in database;

select privilege||' '||table_name "Privileges for " from dba_tab_privs where grantee='&data'
union
select privilege from dba_sys_privs where grantee='&data'
union
select granted_role from dba_role_privs where grantee='&data';
10:33:13 AM: Lakshmidevi V: this is to check all the privs for a user

create user localbuduser default profile identified by loc@lbudus3r default tablespace LOCALBUD temporary tablespace TPS00001 QUOTA UNLIMITED ON LOCALBUD;

create user N507052 identified by N507052 default tablespace USERS temporary tablespace TPS00001;

http://dev.fyicenter.com/faq/oracle/oracle_basic_concept.php

STEP BY STEP RMAN RECOVERY CATALOG CREATION AND CONFIGURATION. (WINDOWS)

RMAN Recovery Catalog Creation and Configuration. (Windows)

The RMAN Recovery Catalog is a database schema that holds the metadata detailing the RMAN backup operations performed on the target database. The metadata include the following information from about the target database: database structure, RMAN configuration, data file and archive log backups (backup sets, pieces and copies), archived redo logs and their copies. In addition to the metadata the recovery catalog can also store scripts that can be used by all registered target databases.

This document will detail the steps to create an RMAN recovery catalog using Oracle Database 11gR2 on Windows OS. The process is the same for other versions of UNIX, Solaris and AIX.

It is recommended that the recovery catalog be in a database separate from any of the target databases. Many organizations store the recovery catalog in the same database as their Enterprise Manager Grid Control repository. It is also recommended that the database that houses the recovery catalog to be in ARCHIVELOG mode. In this demo I am using catalog database with the name of CATDB.

First create a table space to house the recovery catalog schema.

C:\Users\rajjha>set oracle_sid=catdb

C:\Users\rajjha>sqlplus

SQL*Plus: Release 11.2.0.1.0

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace rec_catalog
datafile ‘D:\app\rajjha\oradata\CATDB\rec_catalog.dbf’
size 10M autoextend on
extent management local uniform size 1M;

Next we create the recovery catalog owner. The user SYS cannot be the owner of the recovery catalog.

SQL> create user rcat identified by password
default tablespace rec_catalog
quota unlimited on rec_catalog;

User created.

SQL> grant recovery_catalog_owner to rcat;

Grant succeeded.

The role RECOVERY_CATALOG_OWNER has all of the privileges need to query and maintain the recovery catalog.

SQL> select privilege from dba_sys_privs where grantee = ‘RECOVERY_CATALOG_OWNER’;

PRIVILEGE
—————————————-
CREATE SYNONYM
CREATE CLUSTER
ALTER SESSION
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE SESSION
CREATE TYPE
CREATE VIEW
CREATE TRIGGER

11 rows selected.

SQL>

The actual creation of the recovery catalog is performed in RMAN while connected to the database in which the catalog is to be created. The command CREATE CATALOG will create the recovery catalog in the default table space of the user. If you need to store the catalog in a table space other than the users default table space you can provide the table space name using TABLESPACE.

C:\Users\rajjha>rman target / catalog rcat@CATDB

Recovery Manager: Release 11.2.0.1.0 –

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: CATDB (DBID=2365959513)
recovery catalog database Password:
connected to recovery catalog database

RMAN> create catalog tablespace rec_catalog;

recovery catalog created

To the new database to be backed up, you have to register it on the rman catalog you just created: (Fon any new database you can use this catalog after register database in catalog)

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Configure the persistent parameters.

RMAN> configure retention policy to recovery window of 2 days;

starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> configure default device type to disk;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN>
RMAN> configure channel device type disk format ‘D:\oraclebackup\Backup%d_DB_%U_%S_%P’;

starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘D:\oraclebackup\Backup%d_DB_%U_%S_%P’;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Take database full backup. The full database backup should be taken first time. Afterwards you can use Incremental, Full or archivelog backup depend on your backup policy as well as space available.

C:\Users\rajjha>rman target / catalog rcat/password@CATDB
RMAN> run{
backup database plus archivelog;
delete noprompt obsolete;
}

RMAN> exit
Now the RMAN setup is completed successfully. Here is the info about RMAN.
Primary DB = MYDB
Catalog DB = CATDB
RMAN Backup location = D:\oraclebackup.
Now take the full backup. Every day run the below script that backup the new archive log files. You may run through scheduler

C:\Users\rajjha>rman target / catalog rcat@CATDB
RMAN> run{
delete noprompt obsolete;
backup archivelog all;
}

RMAN> exit

Saturday, 10 June 2017

Asm and rdbms interation opening datafile

ASM & RDBMS Interactions: Opening a Datafile

Once your RDBMS instance has found the ASM instance it is relying on, what happens when the RDBMS needs to actually open a file that is held on ASM storage?

It seems fairly straightforward. The RDBMS maintains a series of connections to the ASM instance via the Onnn background processes. Requests to open a file go via these background processes to the ASM instance. The ASM instance then sends the extent map to the RDBMS instance.

The RDBMS can then use the extent map to perform I/O direct to the file. It is important to bare in mind that the I/O requests from the RDBMS do not go via the ASM instance, but straight to the devices by using the extent map.

The ASM instance only looks after the metadata that describes diskgroups and extent allocations. It would not be efficient for the RDBMS to have to send it’s I/O via the ASM instance and this does not happen.

ASM & RDBMS Interactions: Creating a file

How do new files get created when running with ASM?

It is the responsibility of the RDBMS instance to begin the procedure for creating the new file:

The RDBMS instance sends a request to the ASM instance to create the file via the Onnn background processes. This request contains information on the size of the file to create, the type, and the diskgroup name where to create the file in.

The ASM instance then uses this information to allocate the appropriate number of extents on the correct disks. After allocating the extents for the file ASM sends the extent map information back to the RDBMS instance and creates an entry in its Continuing Operations Directory (COD) metadata file.

Once the RDBMS has the extent map it can issue the I/O to initialise the file. Remember I/O to datafiles never goes through the ASM instance it is always from the RDBMS instance direct to the file in question. The ASM instance just takes care of the metadata only.

After this I/O has completed, the RDBMS instance messages the ASM instance that the creation of the file has completed successfully. The ASM instance then updates it’s metadata files to reflect the change.

Should the RDBMS decide to abort the file creation, then ASM can use the COD to rollback the creation of the file, which includes freeing the space that was allocated for the new file.

Thanks

Ref : https://jarneil.wordpress.com/2014/10/07/12c-upgrade-and-concurrent-stats-gathering/

ASM and Rdbms interation

ASM & RDBMS Interactions: Finding the ASM Instance

I thought it would be worthwhile having a couple of blog posts on how the RDBMS instance interacts with the ASM instance in various situations.

So I guess we should start at the beginning and ask how the RDBMS actually knows where to contact the ASM instance that it is storing its files in at all?

When an ASM instance is started it registers with the Cluster Synchronisation Services (CSS) daemon running as occsd.bin. This is part of Oracle Clusterware, but the occsd.bin runs in a single instance configuration as well.

Whenever an RDBMS instance needs for the first time to access files that reside on ASM storage and the RDBMS has not needed prior access to the ASM instance, the RDBMS needs to find the connection details for the ASM instance.

These connection details are NOT stored anywhere and you do not need to configure them. The RDBMS instance contacts CSS and gets passed the connect string for the ASM instance. The RDBMS then connects as sysdba to the ASM instance using OS authentication.

It’s actually the RDBMS’s ASMB background process that connects to the ASM instance and remains connected while there are ASM files open. Termination of this ASMB background process is fatal for the instance.

Garuda Oracle SQL postgresql AWS blog