bhagyaraj

1.CURRENT DATE and TIME:

Select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "Current Date/Time" from dual;

2. DATABASE Information:

select INSTANCE_NAME,host_name,version,startup_time,status,INSTANCE_ROLE, log_mode from v$instance, v$database ;

3.Backup check:

To check rman backup is running or not

Ps –ef|grep rman

To check export backup is running or not

Ps –ef|grep exp

4. Check the file system utilization.

>df -h ------------à Linux

> bdf or df -k -----------à HPUX
> df -m or df –gt --------------à AIX

Archive log Location utilization:

Sqlplus / as sysdba

Sql> archive log list -------à Here we will get archive location path

Exit

df –h <archive_path>

Tablespace’s utilization:

By executing below command we can get the tablespace utilization.

If any tablespace utilization is above threshold(85%) value add the space.

set lines 3000 pages 3000

SELECT a.tablespace_name,

a.used File_size,

a.maxallocated,

(a.used -b.freespace) Actualused,

a.maxallocated -(a.used-b.freespace) actuallFree,

ROUND((((a.used-b.freespace)/a.maxallocated)*100),0) percent_used,

100 - ROUND((((a.used-b.freespace)/a.maxallocated)*100),0) percent_free

FROM

(SELECT tablespace_name,

SUM(bytes) used,

SUM(maxallocated) maxallocated

FROM

(SELECT tablespace_name,

ROUND((a.bytes/1048576),0) bytes,

CASE

WHEN round(maxbytes,0)<A.BYTES

THEN ROUND((A.bytes /1048576),0)

ELSE ROUND((A.maxbytes/1048576),0)

END maxallocated

FROM sys.DBA_DATA_FILES a

)

GROUP BY tablespace_name

)a,

(SELECT tablespace_name ,

ROUND(SUM(bytes/1048576),0) freespace

FROM dba_free_space

GROUP BY TABLESPACE_NAME

where a.TABLESPACE_NAME=B.TABLESPACE_NAME (+)

ORDER BY 6 DESC;

Temp tablespace information:

Use below command to check the temp tablespace utilization.

select h.tablespace_name temp_tablespace_name,

round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,

round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,

round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,

100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,

round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,

round(sum(f.maxbytes) / 1048576) max

from sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f

where p.file_id(+) = h.file_id

and p.tablespace_name(+) = h.tablespace_name

and f.file_id = h.file_id

and f.tablespace_name = h.tablespace_name

group by h.tablespace_name

ORDER BY Pct_used desc;

temp tablespace can be monitored using following views :

temp -- v$tempfile, dba_temp_file and v$temp_space_header

select file_id,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 "Size",MAXBYTES/(1024*1024) "Maxsize",AUTOEXTENSIBLE from dba_temp_files;

Used & Free space in temp:

col file_name for a60;

select tablespace_name, SUM(bytes_used/1024/1024) as Used_MB,SUM(bytes_free/1024/1024) as free_mb FROM V$temp_space_header GROUP BY tablespace_name;

undo --- just monitor the undo tablespace as using normal tablespace.

Alert Log :

Sql> show parameter back; ----------------à To get the alert log location

Sql> show parameter dump;

Sql> select * from v$diag_info; -----------à This command is used in 12c DB’s to check

3) Long Running Session:

We can find long running sessions or command by using below statments

set lines 300 pages 1000

col username for a20

col machine for a15

col stmt for a70

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,

SID,

MACHINE,

REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,

ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'

|| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'

|| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT

FROM V$SESSION SES,

V$SQLtext_with_newlines SQL

where SES.STATUS = 'ACTIVE'

and SES.USERNAME is not null

and SES.SQL_ADDRESS = SQL.ADDRESS

and SES.SQL_HASH_VALUE = SQL.HASH_VALUE

and Ses.AUDSID <> userenv('SESSIONID')

order by runt desc, 1,sql.piece;

(OR)

select * from
(
select
     opname,
     start_time,
     target,
     sofar,
     totalwork,
     units,
     elapsed_seconds,
     message
   from
        v$session_longops
order by start_time desc
)
where rownum <=1;

(Or)

long running:

col OPNAME for a30

col TARGET for a40

select inst_id,sid,serial#,opname,target,sofar,totalwork,sql_id from gv$session_longops WHERE sofar<>totalwork;

Blocking Session:

Use below commands to find the blocking sessions details.

select SID,SERIAL#,username,FINAL_BLOCKING_SESSION, BLOCKING_SESSION from gv$session where username not in 'SYS' and FINAL_BLOCKING_SESSION is not null;

To get sid using pid(OS pid):

By using below command you can find the SID using PID.

select a.serial#, sid, a.serial#, b.username from v$session a, v$process b where b.spid=<<PID>> and a.paddr=b.addr;

Example:

select a.serial#, sid, a.serial#, b.username from v$session a, v$process b where b.spid=9786 and a.paddr=b.addr;

Once you get the SID give it to application team, If they want us to kill that blocking SID, Use below command to kill the SID.

Note: Based on user request we can kill the blocking session by using below command.

Sql>alter system kill session 'sid,serial#’ immediate;

Example: SQL>alter system kill session ‘688,6789’ immediate;

Command To kill Inactive user

set pages 0

spool kkk.lst

select '!kill -9 '||p.spid||chr(10)||

'alter system kill session '''||s.sid||','||s.serial#||''';'

from v$session s, v$process p

where s.username like upper('%&USERNAME%')

and s.username is not null and s.username <> 'SYS'

and s.paddr=p.addr and s.status='INACTIVE'

5) V$ACCESS:

To find what session have locked a library cache object we can use the v$access view for this or We can use v$locked_object.

column object format a30

select
   a.object,
   a.type,
   a.sid,
   b.serial#,
   b.username,
   b.osuser,
   b.program
from
   v$access a,
   v$session b
where
   a.sid    = b.sid
and
   a.owner = DECODE(UPPER('&1'), 'ALL', a.object, upper('&1'))
and
a.object = DECODE(UPPER('&2'), 'ALL', a.object, upper('&2'))
order by
   a.object;

6) Fragmentation:

Fragmentation check for tables:

==============================================================

select t.owner owner,t.table_name table_name,t.tablespace_name tablespace,

t.last_analyzed last_analyzed,round((t.NUM_ROWS*t.AVG_ROW_LEN)/1024/1024) USED_MB,

round((st.bytes)/1024/1024) ALLOC_MB,

round((st.bytes)/1024/1024 - (t.NUM_ROWS*t.AVG_ROW_LEN)/1024/1024 ) frg_mb,

(((1 - ROUND (( ((t.NUM_ROWS*t.AVG_ROW_LEN)/1024/1024) / round((st.bytes)/1024/1024)), 2)) * 100)) frg_pct

FROM dba_tables t,dba_segments st

WHERE t.owner like '%' and t.partitioned='NO'

and T.OWNER=ST.OWNER and T.TABLE_NAME=ST.SEGMENT_NAME

and t.owner not in ('PUBLIC','WMSYS','CTXSYS','DBSNMP','MDDATA','MDSYS','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','DM

SYS','XDB','KPMS','HR','OE','OC','PM','IX','SH','CSMIG','PERFSTAT','EXFSYS','TSMSYS','DBASPOT2','ASGDBASPOT2','DBASPOT1','ASGDBASPOT1','DERBYDBA','OWBSYS','APPQOSSYS','FLOWS_FILES') and t.owner not like 'APEX%'

and round((st.bytes)/1024/1024) > 10

AND ( round((st.bytes)/1024/1024 - (t.NUM_ROWS*t.AVG_ROW_LEN)/1024/1024 ) > 50

AND (((1 - ROUND ((round((t.NUM_ROWS*t.AVG_ROW_LEN)/1024/1024) / round((st.bytes)/1024/1024)), 2)) * 100)) > 20 )

ORDER BY frg_pct desc;

Partitioned tables:

select p.table_owner owner,p.table_name table_name,p.partition_name partition_name,p.tablespace_name tablespace,

p.last_analyzed last_analyzed,round((p.NUM_ROWS*p.AVG_ROW_LEN)/1024/1024) USED_MB,

(sp.bytes)/1024/1024 ALLOC_MB,

round((sp.bytes)/1024/1024 - (p.NUM_ROWS*p.AVG_ROW_LEN)/1024/1024 ) frg_mb,

(((1 - ROUND (( ((p.NUM_ROWS*p.AVG_ROW_LEN)/1024/1024) / round((sp.bytes)/1024/1024)), 2)) * 100)) frg_pct

FROM dba_tab_partitions p,dba_segments sp

where P.TABLE_NAME=SP.SEGMENT_NAME and P.PARTITION_NAME=SP.PARTITION_NAME and P.TABLE_OWNER=SP.OWNER

and p.table_owner not in ('PUBLIC','WMSYS','CTXSYS','DBSNMP','MDDATA','MDSYS','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN',

'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','DMSYS','XDB','KPMS','HR','OE','OC','PM','IX','SH','CSMIG','PERFSTAT','EXFSYS','TSMSYS',

'DESIGN_REPOS_CLIENT')

and p.table_owner like '%' and sp.segment_type like '%PARTITION%'

and round((sp.bytes)/1024/1024) > 10

AND ( round((sp.bytes)/1024/1024 - (p.NUM_ROWS*p.AVG_ROW_LEN)/1024/1024 ) > 50

AND (((1 - ROUND ((round((p.NUM_ROWS*p.AVG_ROW_LEN)/1024/1024) / round((sp.bytes)/1024/1024)), 2)) * 100)) > 20 )

ORDER BY frg_pct desc;

Non-Partitioned tables:

select t.owner owner,t.table_name table_name,t.tablespace_name tablespace,

t.last_analyzed last_analyzed,round((t.NUM_ROWS*t.AVG_ROW_LEN)/1024/1024) USED_MB,

round((st.bytes)/1024/1024) ALLOC_MB,

round((st.bytes)/1024/1024 - (t.NUM_ROWS*t.AVG_ROW_LEN)/1024/1024 ) frg_mb,

(((1 - ROUND (( ((t.NUM_ROWS*t.AVG_ROW_LEN)/1024/1024) / round((st.bytes)/1024/1024)), 2)) * 100)) frg_pct

FROM dba_tables t,dba_segments st

WHERE t.owner like '%' and t.partitioned='NO'

and T.OWNER=ST.OWNER and T.TABLE_NAME=ST.SEGMENT_NAME

and t.owner not in ('PUBLIC','WMSYS','CTXSYS','DBSNMP','MDDATA','MDSYS','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN',

'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','DMSYS','XDB','KPMS','HR','OE','OC','PM','IX','SH','CSMIG','PERFSTAT','EXFSYS','TSMSYS',

'DESIGN_REPOS_CLIENT')

and round((st.bytes)/1024/1024) > 10

AND ( round((st.bytes)/1024/1024 - (t.NUM_ROWS*t.AVG_ROW_LEN)/1024/1024 ) > 50

AND (((1 - ROUND ((round((t.NUM_ROWS*t.AVG_ROW_LEN)/1024/1024) / round((st.bytes)/1024/1024)), 2)) * 100)) > 20 )

ORDER BY frg_pct desc;

Defragmentation implementation Notes:

======================================================================

--Check database size before doing table defragmentation activity

--Also note the fragmentated tables space utilization

--You can use the logops query to check the table move status if it is taking long time to complete

--Please table, index_name before defrag

-- also make sure we should have sufficient free space in tablespace(space free = actual object size)

Step 1: Move parallel

ALTER TABLE OWNER.TABLENAME MOVE PARALLEL 4;

Step2: Move NO parallel

ALTER TABLE OWNER.TABLENAME NOPARALLEL;

Step3: Check for Unusable indexes in NISOURCE and rebuild the same.

SELECT TABLE_NAME,INDEX_NAME FROM DBA_INDEXES WHERE STATUS='UNUSABLE' AND OWNER='NISOURCE';

-Dynamic sql to generate index rebuild commands script

SELECT 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD ONLINE PARALLEL 4;'

FROM DBA_INDEXES WHERE STATUS='UNUSABLE' AND OWNER='NISOURCE';

---NO parallel

SELECT 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD NOPARALLEL;’ FROM DBA_INDEXES WHERE STATUS='UNUSABLE' AND OWNER='NISOURCE';

--Run the index rebuild commands from above dynamic sql, there should not be unusable index after rebuild the indexes.

SELECT TABLE_NAME,INDEX_NAME FROM DBA_INDEXES WHERE STATUS='UNUSABLE' AND OWNER='NISOURCE';

Step4: Gather table status

EXEC DBMS_STATS.GATHER_TABLE_STATS (OwnName=> 'OWNER_NAME',TabName=> 'TABLE_NAME', degree => 8, Estimate_Percent=> 10,Cascade=> TRUE);

EX:

EXEC DBMS_STATS.GATHER_TABLE_STATS (OwnName=> 'NISOURCE',TabName=> 'A39', degree => 8, Estimate_Percent=> 10,Cascade=> TRUE);

7) RMAN:

Backing up database using RMAN:

While taking backup to tape we would use below command, we need to change the server name and database name and NB_ORA_POLICY name accordingly

Case 1:

rman target / catalog rman_<<dbname>>/rman_<<dbname>>@<<catdbname>>

run {

allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=ORACLE_napdci71-bac)';

allocate channel t2 type 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=ORACLE_napdci71-bac)';

allocate channel t3 type 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=ORACLE_napdci71-bac)';

allocate channel t4 type 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=ORACLE_napdci71-bac)';

backup full database include current controlfile

tag='QTCS HOT db backup'

filesperset 6

format 'QTCS_db_bk_%s_%p_%t';

resync catalog;

sql 'alter system archive log current';

change archivelog all crosscheck;

backup

format 'QTCS_log_%s_%p_%t'

(archivelog all delete input);

release channel t1;

release channel t2;

release channel t3;

release channel t4;

}

Case 2: While taking backup on disk we use below command

RUN {

ALLOCATE CHANNEL ch01 TYPE DISK;

ALLOCATE CHANNEL ch02 TYPE DISK;

ALLOCATE CHANNEL ch03 TYPE DISK;

ALLOCATE CHANNEL ch04 TYPE DISK;

ALLOCATE CHANNEL ch05 TYPE DISK;

backup full database include current controlfile

tag='QTCS HOT db backup'

filesperset 6

format 'QTCS_db_bk_%s_%p_%t';

resync catalog;

sql 'alter system archive log current';

change archivelog all crosscheck;

backup

format 'QTCS_log_%s_%p_%t'

(archivelog all delete input);

RELEASE CHANNEL ch01;

RELEASE CHANNEL ch02;

RELEASE CHANNEL ch03;

RELEASE CHANNEL ch04;

RELEASE CHANNEL ch05;

}

(Or)

Check RMAN backup jobs status

col dbsize_mbytes for 99,999,990.00 justify right head "DBSIZE_MB"

col input_mbytes for 99,999,990.00 justify right head "READ_MB"

col output_mbytes for 99,999,990.00 justify right head "WRITTEN_MB"

col output_device_type for a10 justify left head "DEVICE"

col complete for 990.00 justify right head "COMPLETE %"

col compression for 990.00 justify right head "COMPRESS|% ORIG"

col est_complete for a20 head "ESTIMATED COMPLETION"

col recid for 9999999 head "ID"

select recid

, output_device_type

, dbsize_mbytes

, input_bytes/1024/1024 input_mbytes

, output_bytes/1024/1024 output_mbytes

, (output_bytes/input_bytes*100) compression

, (mbytes_processed/dbsize_mbytes*100) complete

, to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete

from v$rman_status rs

, (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile)

where status='RUNNING'

and output_device_type is not null

(OR)

Check RMAN progress:

select sid,start_time,totalwork,sofar,(sofar/totalwork) * 100 pct_done from v$session_longops where totalwork > sofar AND opname NOT LIKE '%aggregate%' AND opname like 'RMAN%';

select * from v$rman_status;

RMAN configure retention policy:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF <#> DAYS;

Delete Archive logs using rman:

Cross check archivelog all;

delete archivelog until time 'SYSDATE-1'

8) User Creation/Password Reset:

If we have reference user ID to create new user we can use below command to get the DDL.

And we can create the user.

After login with database---> get the ddl of any existing user--->

Set heading off;

Set echo off;

Set pages 999;

Set long 90000;

select dbms_metadata.get_ddl( 'USER', 'COLANTOK' ) from dual

UNION ALL

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'COLANTOK' ) from dual

UNION ALL

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'COLANTOK' ) from dual

UNION ALL

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SUNDARAR' ) from dual

union all

select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'COLANTOK' ) from dual;

Now create the new user as per ddl in above Step.

Other way of giving access to schema is using dba_tables ,dba_views and dba_source

(Or)

If we don’t have Reference ID use below command to create user.

SQL> create user <<username>> identified by <<password>> ;

User Unlock:

SQL> alter user <<username>> account lock;

Password reset:

Nishant--- take backup of old password before res

SQL> alter user <<username>> identified by <<password>>;

User Disable:

SQL>alter user <<username>> password expire;

9) DDL Commands:

set heading off;

set echo off;

Set pages 999;

set long 90000;

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;--àTable DDL

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;àINDEX DDL

Here ‘DEPT’ is name of the table or Index

‘Scott’ is owner of the table or Index or View.

11) Steps to increase SGA size:

Step1: Check the current SGA size.

show sga;

Step 2: create pfile from spfile.

create pfile from spfile;

Step 3: change the SGA max size.

alter system set sga_max_size=20m scope=spfile;

Step 4: Restart the database.

shutdown immediate;

startup;

step 5: Check the size.

EXample:

SQL> connect sys as sysdba

Enter password: ******

Connected.

SQL> show sga;

Total System Global Area 147615836 bytes

Fixed Size 282716 bytes

Variable Size 113246208 bytes

Database Buffers 33554432 bytes

Redo Buffers 532480 bytes

SQL> show parameter spfile

NAME TYPE VALUE

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

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

spfile string

%ORACLE_HOME%DATABASESPFILE%

ORACLE_SID%.ORA

SQL> create pfile from spfile;

File created.

SQL> alter system set sga_max_size=20m scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

12) PGA SIZE:

Follow the below steps to increase the PGA size.

Step1: Check the current SGA size.

show parameter target;

Step 2: create pfile from spfile.

create pfile from spfile;

Step 3: change the PGA size.

ALTER SYSTEM SET pga_aggregate_target=500m SCOPE=both;

Step 4: Restert the database.

shutdown immediate;

startup;

step 5: Check the size.

13)Export and Import of table statistics :

Normal Steps:

**************create stat table with tablespace

exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SA', stattab => 'STATS_TABLE_17102013', tblspace => 'DATA');

**************create stat table without tablespace

execute DBMS_STATS.CREATE_STAT_TABLE('SA','STATS_TAB');

*****schema level export

exec DBMS_STATS.EXPORT_SCHEMA_STATS('SA', 'STATS_TABLE_17102013', NULL, 'SA');

Nishant—export this table and import to another database and then use belwo cmd to import

****table_level_export

execute DBMS_STATS.EXPORT_TABLE_STATS(ownname => 'SA', tabname => 'TABLE_X_HAZARD_NOTE', stattab => 'STATS_TAB');

***********gather_stats for a particular table******************

exec dbms_stats.gather_table_stats(ownname=>'SA',tabname=>'TABLE_X_HAZARD_NOTE',degree=>5,cascade=>TRUE);

***********gather_stats for a schema******************

exec dbms_stats.gather_schema_stats (ownname=>'${SCHEMA}',degree=>5,cascade=>TRUE)

***********delete stats*************

EXEC DBMS_STATS.DELETE_TABLE_STATS('SA','TABLE_X_HAZARD_NOTE');

**********IMPORT Stats***********

execute DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'SA', STATTAB=>'STATS_TAB',tabname=>'TABLE_X_HAZARD_NOTE');

For Levis: Follow below process

Stats export and Import

1. Export the table stats

ORAMAINT schema has a table called Stat_trans. This can be used for this.

Ex: The support team requested to bring stats from pnp02 to pnv02 for 5 tables

ALAC_ATTR_CONFIG

MALLOC_MWTD_PREAGG

MWTD_TEMP

LV10MAST

MFINC

Execute the below commands on pnp02 (source db) Make sure the statid (highlighted below) matches with the table and the date. It should be unique

exec dbms_stats.export_table_stats(ownname =>'MAXDATA',tabname=>'ALAC_ATTR_CONFIG',statown=>'ORAMAINT',stattab=>'STAT_TRANS',statid=>'ALAC_ATTR_CONFIG_091419');

exec dbms_stats.export_table_stats(ownname =>'MAXDATA',tabname=>'MALLOC_MWTD_PREAGG',statown=>'ORAMAINT',stattab=>'STAT_TRANS',statid=>'MALLOC_MWTD_PREAGG_091419');

exec dbms_stats.export_table_stats(ownname =>'MAXDATA',tabname=>'MWTD_TEMP',statown=>'ORAMAINT',stattab=>'STAT_TRANS',statid=>'MWTD_TEMP_091419');

exec dbms_stats.export_table_stats(ownname =>'MAXDATA',tabname=>'LV10MAST',statown=>'ORAMAINT',stattab=>'STAT_TRANS',statid=>'LV10MAST_091419');

exec dbms_stats.export_table_stats(ownname =>'MAXDATA',tabname=>'MFINC',statown=>'ORAMAINT',stattab=>'STAT_TRANS',statid=>'MFINC_091419');

2. Copy the exported stats to stat_trans table owned by oramaint on pnv02. For this, use a dblink on pnv02 pointing to pnp02 (Name – pnp02). Use the sql statement with like clause for the statid

insert into ORAMAINT.STAT_TRANS select * from oramaint.stat_trans@pnp02 where statid like '%091419%'

commit

3. import the stats to respective tables

Execute the below commands on pnv02 (target db)

exec dbms_stats.import_table_stats(ownname =>'MAXDATA',tabname=>'ALAC_ATTR_CONFIG',statown=>'ORAMAINT',stattab=>'STAT_TRANS',statid=>'ALAC_ATTR_CONFIG_091419');

exec dbms_stats.import_table_stats(ownname =>'MAXDATA',tabname=>'MALLOC_MWTD_PREAGG',statown=>'ORAMAINT',stattab=>'STAT_TRANS',statid=>'MALLOC_MWTD_PREAGG_091419');

exec dbms_stats.import_table_stats(ownname =>'MAXDATA',tabname=>'MWTD_TEMP',statown=>'ORAMAINT',stattab=>'STAT_TRANS',statid=>'MWTD_TEMP_091419');

exec dbms_stats.import_table_stats(ownname =>'MAXDATA',tabname=>'LV10MAST',statown=>'ORAMAINT',stattab=>'STAT_TRANS',statid=>'LV10MAST_091419');

exec dbms_stats.import_table_stats(ownname =>'MAXDATA',tabname=>'MFINC',statown=>'ORAMAINT',stattab=>'STAT_TRANS',statid=>'MFINC_091419');

14) Creation and altering of user:

After login with database---> get the ddl of any existing user--->

Set heading off;

Set echo off;

Set pages 999;

Set long 90000;

select dbms_metadata.get_ddl( 'USER', 'COLANTOK' ) from dual

UNION ALL

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'COLANTOK' ) from dual

UNION ALL

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'COLANTOK' ) from dual

UNION ALL

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SUNDARAR' ) from dual

union all

select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'COLANTOK' ) from dual;

Now create the new user as per ddl in above Step.

Other way of giving access to schema is using dba_tables ,dba_views and dba_source

15) Disabling account of a user:

SQL> alter user <<username>> account lock;

16) Steps to increase REDO log sizes:

You do it for performance reasons – mostly because your original files are too small and you get log switches too often

For an example:

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

GROUP# MEMBERS STATUS MB

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

1 1 INACTIVE 50

2 1 INACTIVE 50

3 1 CURRENT 50

SQL> select group#,member from v$logfile;

GROUP# MEMBER

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

3 /u01/app/oracle/oradata/orcl/redo03.log

2 /u01/app/oracle/oradata/orcl/redo02.log

1 /u01/app/oracle/oradata/orcl/redo01.log

This is my configuration as done by the DBCA. Now I want to get 100m sized logfiles and I want them mirrored for security reason. First, I create new log groups of the desired size:

SQL> alter database add logfile

('/u01/app/oracle/oradata/orcl/redo_g4m1.rdo',

'/u01/app/oracle/oradata/orcl/redo_g4m2.rdo')

size 100m;

Database altered.

SQL> alter database add logfile

('/u01/app/oracle/oradata/orcl/redo_g5m1.rdo',

'/u01/app/oracle/oradata/orcl/redo_g5m2.rdo')

size 100m;

Database altered.

SQL> alter database add logfile

('/u01/app/oracle/oradata/orcl/redo_g6m1.rdo',

'/u01/app/oracle/oradata/orcl/redo_g6m2.rdo')

size 100m;

Database altered.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

GROUP# MEMBERS STATUS MB

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

1 1 INACTIVE 50

2 1 INACTIVE 50

3 1 CURRENT 50

4 2 UNUSED 100

5 2 UNUSED 100

6 2 UNUSED 100

6 rows selected.

I like the above naming convention, because I can determine by the name easily what member of what group I see and because the suffix rdo is not so easy misinterpreted as a mere ascii textfile that can be deleted if space is needed – believe me, that has happened many times and made the DBA of that site curse and suffer 🙂

Now I am going to drop the small files – with SQL commands first!

SQL> alter database drop logfile group 1;

Database altered.

That was easy, wasn’t it? Notice that the OS file is not gone yet – we have to delete them manually (if they are not OMF). All groups that have the status INACTIVE can be dropped that way.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

GROUP# MEMBERS STATUS MB

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

2 1 INACTIVE 50

3 1 CURRENT 50

4 2 UNUSED 100

5 2 UNUSED 100

6 2 UNUSED 100

SQL> alter database drop logfile group 2;

Database altered.

If the status is CURRENT or ACTIVE, it won’t be possible to drop the group – you can do nothing wrong here!

SQL> alter database drop logfile group 3;

alter database drop logfile group 3

ERROR at line 1:

ORA-01623: log 3 is current log for instance orcl (thread 1) - cannot drop

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'

CURRENT means that LGWR is actually using this group to write the redo entries into. If a log switch happens, the status can become ACTIVE or INACTIVE:

SQL> alter system switch logfile;

System altered.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

GROUP# MEMBERS STATUS MB

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

3 1 ACTIVE 50

4 2 CURRENT 100

5 2 UNUSED 100

6 2 UNUSED 100

ACTIVE means, the checkpoint associated to the redo entries in that group is not yet finished. It is in other words still needed for a possible instance recovery and cant be dropped therefore. But we can force the checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select group#,members,status,bytes/1024/1024 as mb from v$log;

GROUP# MEMBERS STATUS MB

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

3 1 INACTIVE 50

4 2 CURRENT 100

5 2 UNUSED 100

6 2 UNUSED 100

SQL> alter database drop logfile group 3;

Database altered.

Now I am finished with my SQL commands and I can continue with deleting the files of the small groups from the OS. Never delete the OS files before the SQL drop commands! The instance was OPEN all the time and no user was in any way concerned by this whole action.

By the way, I mirrored the log groups to protect my database against really serious problems that would arise otherwise, if a whole log group gets lost – for example because someone deletes a file from the OS. You would want to have the mirrored files on different devices, of course.

17) UNDO:

The undo segments may be scattered all over the UNDO datafile.

In this case we have to do the following on the database:

Step 1: Create new undo tablespace with smaller size.

SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size 2G;

Step 2: Set the new tablespace as an undo_tablespace

SQL> alter system set undo_tablespace=undo_rbs1;

Step 3: Drop the old tablespace.

SQL> drop tablespace undo_rbs0 including contents.

Step 4: Create new undo tablespace with smaller size.

SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size 2G;

Step 5: Set new tablespace as undo_tablespace

SQL> alter system set undo_tablespace=undo_rbs1;

Step 6: Drop the old tablespace.

SQL> drop tablespace undo_rbs0 including contents.

Dropping the old tablespace may give the ORA-30013 : undo tablespace '%s' is currently in use.

This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.

Setting the Undo Retention Period:

You set the undo retention period by setting the UNDO_RETENTION initialization parameter. This parameter specifies the desired minimum undo retention period in seconds. As described in "Undo Retention", the current undo retention period may be automatically tuned to be greater than UNDO_RETENTION, or, unless retention guarantee is enabled, less than UNDO_RETENTION if space is low.

To set the undo retention period:

Do one of the following:

Set UNDO_RETENTION in the initialization parameter file.

UNDO_RETENTION = 1800

Change UNDO_RETENTION at any time using the ALTER SYSTEM statement:

ALTER SYSTEM SET UNDO_RETENTION = 2400;

The effect of an UNDO_RETENTION parameter change is immediate, but it can only be honored if the current undo tablespace has enough space.

18)Shutdown and startup in standby mode:

1. To shuts down the database:

SQL> shutdown immediate

2. Steps to startup the database:

SQL> startup nomount

SQL> alter database mount standby database;

Start the recovery on standby database (Start the mrp process).

SQL> alter database recover managed standby database disconnect from session;

On Production Enable the log shipping to standby.

SQL> alter system set log_archive_dest_state_2='enable';

switch log and verify the apply.

SQL> alter system switch logfile;

Check and confirm if archives are getting transferred to DR site and applied or not.

19) Steps to enable Flash back:

Step1: shut down the database.

Sql>shut immediate

Step2: start the database in mount state.

SQL> startup mount

Step3: change the database to flash back mode.

SQL> alter database flashback on;

Step4: open the database.

SQL> alter database open.

20) Steps to Enable or To Disable the database archive mode:

Enable the Database in archive mode:

Step1: shut down the database.

SQL>shut immediate

Step2: start the database in mount state.

SQL> startup mount

Step3: change the database to archive mode

SQL> alter database archivelog

Step4: alter database open

Disable the Database from archive mode:

Step1: shut down the database.

SQL>shut immediate

Step2: start the database in mount state.

SQL> startup mount

Step3: change the database to archive mode

SQL> alter database noarchivelog

Step4: alter database open

21) DATABASE SHUT DOWN STEPS:

1. Stop the listener.

$ lsnrctl stop listener_name

2. Login to the database and do the checkpoint and switch logfile (at least 3-4 times). Check dump location.

SQL> Sqlplus / as sysdba

SQL> alter system switch logfile;

SQL> alter system checkpoint;

SQL> show parameter dump;

3. Kill the all sessions excluding sys user

SQL> set lines 300

SQL> set pages 0

SQL> spool kill.sql

SQL> select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from

V$session where username not like '%SYS%';

SQL>@ kill.sql

4. Shutdown the database and monitor the alert.log file from parallel session.

SQL> shutdown immediate;

22) DATABASE STARTUP STEPS:

1. Login to Sqlplus, it should connect to idle instance.

$sqlplus sys as sysdba

2. . Mount the database

SQL> startup mount;

3. Output of below command must be no rows selected

SQL>select * from v$recover_file;

4. Open the database

SQL>alter database open;

4. Check the database mode by logging into the DB as sysdba and run the below query, DB mode should be read write.

SQL> Select name, open_mode from database;

5. Start the listener by the below command :

$lsnrctl start listener_name

6. Check the listener status by the below command:

$lsnrctl status listener_name

Search This Blog

bhagyaraj

Comments

Post a Comment