Friday, January 22, 2021

Helpers




Database Scripts Library Index [ID 131704.1]
Modified 03-NOV-2011     Type REFERENCE     Status ARCHIVED

Database Scripts Last updated on October 23, 2009
This document has been archived and will no longer be updated.
AdvancedQueueing ContentMgmt.Spatial ContentMgmt.Text ContentMgmt.UltraSearch
ContentMgmt.XDB ContentMgmt.interMedia DBA.Admin DBA.Architecture
DBA.DBCreationAndConfiguration DBA.Monitoring DBA.SQL DBA.Storage
DBWarehouse.MaterialView DBWarehouse.ParallelExecution DBWarehouse.Partitioning Distributed.General
Distributed.Replication Distributed.Streams Globalization Globalization.DST
HighAvailability.BR HighAvailability.Corruption HighAvailability.DataGuard HighAvailability.RMAN
Install.Installer Install.UnixGeneric JVM Manageability.MemoryMgmt
Manageability.Utilities.ExportImport Manageability.Utilities.SqlLoader OLAP.ExpressServer Performance.Database
Performance.Locking Performance.SqlTuning Scalability.OPS Scalability.RAC
Security.DBSecurity Storage.Exadata
AdvancedQueueing - Advanced Queuing
[Top]
ContentMgmt.Spatial - Oracle Intermedia Spatial and Spatial Data Option
[Top]
ContentMgmt.Text - Oracle Text (formerly interMedia Text)
[Top]
ContentMgmt.UltraSearch - Oracle Ultra Search
[Top]
ContentMgmt.XDB - XML Database
[Top]
ContentMgmt.interMedia - Oracle interMedia (Image, Video, Sound etc)
[Top]
DBA.Admin - General DBA Activities (Copying DB etc...)
[Top]
DBA.Architecture - Oracle Server Architecture (processes and SGA)
[Top]
DBA.DBCreationAndConfiguration - Database Create and Configuration
[Top]
DBA.Monitoring - Database Monitoring
[Top]
DBA.SQL - SQL Scripts, Examples and Reference Information
[Top]
DBA.Storage - Space Management and Object Storage
[Top]
DBWarehouse.MaterialView - Materialized Views - Distributed & Local Summary
[Top]
DBWarehouse.ParallelExecution - Parallel Execution
[Top]
DBWarehouse.Partitioning - Partitioning
[Top]
Distributed.General - Distributed Database Issues
[Top]
Distributed.Replication - Master Replication
- No comments:

Thursday, March 28, 2019

DLL of a user in oracle 

-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/script_creation/user_ddl.sql
-- Author       : Tim Hall
-- Description  : Displays the DDL for a specific user.
-- Call Syntax  : @user_ddl (username)
-- Last Modified: 07/08/2018
-- -----------------------------------------------------------------------------------

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
 
variable v_username VARCHAR2(30);

exec :v_username := upper('&1');

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from   dba_users u
where  u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from   dba_ts_quotas tq
where  tq.username = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp
where  sp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rp.default_role = 'YES'
and    rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
/

set linesize 80 pagesize 14 feedback on trimspool on verify on
- No comments:

Monday, December 12, 2016

Rman backup status

Rman backup status 
 
set linesize 999
set pagesize 500
col STATUS format a30
col START_TIME format a30
col END_TIME format a30
col hrs format 999.99
select
SESSION_KEY,
INPUT_TYPE,
STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from
V$RMAN_BACKUP_JOB_DETAILS
order
by
session_key;
 
Example out put 
 
- No comments:

Sunday, December 4, 2016

To check which Exadata server complete details

To Check the exadata server complete details 

command : dmidecode -s system-product-name

Oracle Exadata Database Machine Database Server Model Exadata Storage Server Model
Oracle Exadata Database Machine X5-2 Oracle Server X5-2 Database Server
ORACLE SERVER X5-2		 Exadata Storage Server X5-2L Servers
ORACLE SERVER X5-2L
Oracle Exadata Database Machine X5-8 Oracle Server X5-8 Server
ORACLE SERVER X5-8		 Exadata Storage Server X5-2L Servers
ORACLE SERVER X5-2L
Oracle Exadata Database Machine X4-2 Sun Server X4-2 Oracle Database Server
SUN SERVER X4-2		 Exadata Storage Server X4-2L Servers
SUN SERVER X4-2L
Oracle Exadata Database Machine X3-2 Sun Server X3-2 Oracle Database Server
SUN FIRE X4170 M3		 Exadata Storage Server X3-2 Servers
SUN FIRE X4270 M3
Oracle Exadata Database Machine X2-2 Sun Fire X4170 M2 Oracle Database Server
SUN FIRE X4170 M2 SERVER		 Exadata Storage Server with Sun Fire X4270 M2 Servers
SUN FIRE X4270 M2 SERVER
Oracle Exadata Database Machine X4-8 Full Rack Sun Server X4-8 Oracle Database Server
SUN SERVER X4-8		 Exadata Storage Server X4-2L Servers
SUN SERVER X4-2L
Oracle Exadata Database Machine X3-8 Full Rack Sun Server X2-8 Oracle Database Server
Sun Fire X4800 M2		 Exadata Storage Server X3-2 Servers
SUN FIRE X4270 M3
Oracle Exadata Database Machine X2-8 Full Rack Sun Fire X4800 Oracle Database Server or Sun Server X2-8 Oracle Database Server
Sun Fire X4800 or Sun Fire X4800 M2		 Exadata Storage Server with Sun Fire X4270 M2 Servers
SUN FIRE X4270 M2 SERVER
Oracle Exadata Database Machine Sun Fire X4170 Oracle Database Server
SUN FIRE X4170 SERVER		 Exadata Storage Server with Sun Fire X4275 Servers
SUN FIRE X4275 SERVER


- No comments:

Monitoring ambient temparatures of exadata cluster 

dcli -g /opt/oracle.SupportTools/onecommand/all_group -l root 'ipmitool   \
sunoem cli "show /SYS/T_AMB" | grep value'
 
example : 
 
[root@fldadbadm01 bin]# ./dcli -g /opt/oracle.SupportTools/onecommand/all_group -l root 'ipmitool   \
> sunoem cli "show /SYS/T_AMB" | grep value': value = 21.250 degree C
fldadbadm02: value = 21.000 degree C
fldadbadm03: value = 20.750 degree C
fldadbadm04: value = 21.000 degree C
fldaceladm01: value = 21.000 degree C
fldaceladm02: value = 20.750 degree C
fldaceladm03: value = 21.000 degree C
fldaceladm04: value = 20.000 degree C
fldaceladm05: value = 21.000 degree C
fldaceladm06: value = 21.000 degree C
fldaceladm07: value = 21.250 degree C


 


















-





No comments:
  







































          

        

          

        

Tuesday, May 3, 2016


          

        









Oracle database size sql query












How to find an oracle database size allocated and how much is occupied by the actual data..

my own query ..:) not copied from any where... :)



set serveroutput on

set linesize 99

col DATABASE_NAME format a20

select * from

(SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL)m ,

(select name "DATABASE_NAME" from v$database) f,

(select a.data_size+b.temp_size+c.redo_size "ALLOCATED_SIZE_IN_GB"

from ( select sum(bytes) / 1024 / 1024 / 1024 data_size

         from dba_data_files ) a,

     ( select nvl(sum(bytes) / 1024 / 1024 / 1024,0) temp_size

         from dba_temp_files ) b,

     ( select sum(bytes) / 1024 / 1024 / 1024 redo_size

         from sys.v$log ) c ) t,

(select sum(bytes) /1024 / 1024 /1024 "ACTUAL_DATA_OCCUPIED_IN_GB" from dba_segments ) z;



Output would be similar as below ...





NOW   DATABASE_NAME   ALLOCATED_SIZE_IN_GB ACTUAL_DATA_OCCUPIED_IN_GB

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

05-03-2016 15:18:39 RDTEST                        60.9462891                 2.46868896















Thanks 







Rajesh R D














-





No comments:
  







































          

        

          

        

Wednesday, April 6, 2016


          

        









 Rman duplicate database to another server ASM to ASM 












Duplicating database to another server ASM to ASM



We have two options, were we can use db_convert_files and log convert files parameter if this is at a different structure ( different filesystem / different disk group name )

second one is we can use the set new name for datafile option. i felt this set newname for datafile option is easiest .



The below query can be used to produce the set newname file from the source machine.





SELECT 'SET NEWNAME FOR DATAFILE '||FILE#||' TO '''||'+DATAFILE_Diskgroupname'||

SUBSTR(NAME, 

INSTR(NAME, '/', -1, 1),

INSTR(NAME, '.',1,2) - INSTR(NAME, '/',-1,1)) ||'.dbf'';' 

FROM V$DATAFILE;



Then, let the target db started in nomount , and also add the tns entries for the source database on the target server



connect auxiliary /

CONNECT target sys/xxxxx@BOORAxxxx20

CONNECT CATALOG RMAN/detaxx@BORxxxxxOD;

run

{

SET UNTIL TIME "to_date('16-OCT-2013 04:00:00','DD-MON-YYYY HH24:MI:SS')";

allocate channel C1 device type disk;

allocate auxiliary channel C2 device type disk;

allocate auxiliary channel C3 device type disk;

allocate auxiliary channel C4 device type disk;

SET NEWNAME FOR DATAFILE 1 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 2 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 3 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 4 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 5 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 6 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 7 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 8 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 9 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 10 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 11 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 12 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 13 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 14 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 15 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 16 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 17 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 18 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 19 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 20 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 21 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 22 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 23 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 24 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 25 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 26 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 27 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 28 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 29 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 30 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 31 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 32 TO '+DATAFILE_COORADEVL20';

SET NEWNAME FOR DATAFILE 33 TO '+DATAFILE_COORADEVL20';

duplicate target database to ORDEVL20 NOFILENAMECHECK

LOGFILE

  GROUP 1 ('+LOG1', '+LOG2')  SIZE 100M,

  GROUP 2 ('+LOG1', '+LOG2')  SIZE 100M,

  GROUP 3 ('+LOG1', '+LOG2')  SIZE 100M,

  GROUP 4 ('+LOG1', '+LOG2')  SIZE 100M,

  GROUP 5 ('+LOG1', '+LOG2')  SIZE 100M,

  GROUP 6 ('+LOG1', '+LOG2')  SIZE 100M;

  }



  












-





No comments:
  







































        

      









Subscribe to:
Posts (Atom)









How to Trouble shoot Logfile_sync wait event



   First  Identify and break down LGWR wait events. Query wait events for LGWR. In this instance LGWR sid is 3 (and usually it is). select s...