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

NSA
===============

CREATE USER NIGAMS02 IDENTIFIED BY PfiZer_234

DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
profile default;

CREATE USER ANAJIP IDENTIFIED BY PfiZer_234

DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP01
profile default;
User created.

SQL> grant create session to DENGEL;

Grant succeeded.

select name,open_mode from v$database;

select username,account_status from dba_users where username in

('KAURM04','MUNJAS03');

SELECT GRANTEE,GRANTED_ROLE FROM DBA_ROLE_PRIVS where grantee='ANNAMK04';

Please be informed that the user account is created in the database and granted
required roles. Please find the same below and revert back have issues going
forward I am resolving this ticket.

I will send the password in a separate mail.

PfiZer_123

insert into DB_AUDIT.ALLOWED_USER_PROGRAMS values ('<username>','%','%');

select name,open_mode from v$database;

select username,account_status from dba_users where username like '%KINGVL';

select distinct tablespace_NAME FROM dba_tablespaces where tablespace_NAME like

'%TEMP%';

select distinct temporary_tablespace,count(*) from dba_users group by

temporary_tablespace;

select distinct profile,count(*) from dba_users group by profile;

select distinct role from dba_roles where role like '%

select distinct grantee,owner,PRIVILEGE from dba_tab_privs where

grantee='DATAX_RO_ROLE';

insert into DB_AUDIT.ALLOWED_USER_PROGRAMS values ('<username>','%','%'); ( For

some SOX Databases( Ex: GPSP,ICMP.. We need to use this ) and then use commit

Sysedge
-======

select FILE_ID,sum(BYTES/1024/1024/1024) as "free" from dba_free_space where

FILE_ID in (select file_id from dba_data_files where file_name like '%&partition%')
group by file_id;

select file_name,bytes/1024/1024/1024,maxbytes/1024/1024/1024 from dba_data_files

where file_id=&file_id;

alter database datafile '/ora09/oradata/cmssa/data/cmssa_cms_olap_indx_10.dbf'

resize 14500M;

select ss.tablespace_name,sum((ss.used_blocks*ts.blocksize))/1024/1024 mb from

gv$sort_segment ss, sys.ts$ ts where ss.tablespace_name = ts.name group by
ss.tablespace_name;

select file_name,bytes/1024/1024,maxbytes/1024/1024 from dba_temp_files where

tablespace_name='TEMP'
and file_name like '%&partition%';

ALTER DATABASE TEMPFILE '/ora09/oradata/cmssta/data/cmssta_temp1_07.dbf' DROP

INCLUDING DATAFILES;

ALTER TABLESPACE TEMP add tempfile '/ora09/oradata/cmssta/data/cmssta_temp1_07.dbf'

size 5000M AUTOEXTEND ON NEXT 1M MAXSIZE 5500M;

find /app/oracle/admin/<<DB_NAME>>/trace/adump -name "*.trc" -mtime +7 -exec rm

{} ;

EXP and imp

===========
Exp Command

==========

Source database:

nohup exp userid=" '/ as sysdba' " parfile=exp_GRS_mdarcpp_03MAR11.par &

file=exp_GRS_mdarcpp_03MAR11.dmp
log=exp_GRS_mdarcpp_03MAR11.log
owner=GRS
compress=N
consistent=Y
statistics=none
buffer=20480000

nohup exp userid=" '/ as sysdba' " file=MET16590649i log=MET16590649i.log

tables=CTDAPP.ICTD_BO_PROTOCOL_ATTRIB statistics=none

nohup exp userid=" '/ as sysdba' " parfile=exp_GRS_mdarcpd_03MAR11.par &

file=exp_GRS_mdarcpd_03MAR11.dmp
log=exp_GRS_mdarcpd_03MAR11.log
owner=GRS
compress=N
consistent=Y
statistics=none
buffer=20480000

Target database:

nohup exp userid=" '/ as sysdba' " parfile=exp_EPHARM2_OWNER_CLNDEV5_26oct10.par &

file=exp_EPHARM2_OWNER_CLNDEV5_26oct10.dmp
log=exp_EPHARM2_OWNER_CLNDEV5_26oct10.log
owner=EPHARM2_OWNER
compress=N
consistent=Y
statistics=none
buffer=20480000

In Target database:

set lines 150

set head off
spool PRIVS_EPHARM2_OWNER.sql

Select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'

from dba_tab_privs where grantor='EPHARM2_OWNER';

set head off

set lines 250
set verify off

set feedback off

set echo off

spool drop_GRS_OWNER.sql

select 'drop '||object_type||' ' ||owner||'.'||object_name||';' from dba_objects

where owner in ('GRS') and object_type not in ('TABLE','INDEX')
union select 'drop '||object_type||' ' ||owner||'.'||object_name||' cascade
constraints;' from dba_objects where owner in ('GRS') and object_type='TABLE' order
by 1 desc;

spool off

SQL>@drop_EPHARM2_OWNER.sql

Imp Command
===========

nohup imp userid=" '/ as sysdba' " parfile=imp_IPTUSER_IPTP_14Feb12.par &

file=exp_IPTUSER_IPTP_08Feb12.dmp
fromuser=IPTUSER
touser=IPTTESTUSER
log=imp_IPTUSER_IPTP_14Feb12.log
grants=y
buffer=20480000

exp_GRS_mdarcpp_03MAR11.dmp

nohup imp userid=EPHARM2_OWNER/test123

parfile=imp_EPHARM_OWNER_CLNPRD9_26oct10.par &

nohup exp userid=" '/ as sysdba' " file=exp_mix2p_OPCENTER_25Feb12.dmp

log=exp_mix2p_OPCENTER_25Feb12.log owner=OPCENTER compress=N consistent=Y
statistics=none buffer=20480000 &

nohup exp userid=" '/ as sysdba' " file=exp_mix2p_OPCENTER_ARCH_25Feb12.dmp

log=exp_mix2p_OPCENTER_ARCH_25Feb12.log owner=OPCENTER_ARCH compress=N consistent=Y
statistics=none buffer=20480000 &

select FILE_ID,sum(BYTES/1024/1024/1024) as "free" from dba_free_space where

FILE_ID in (select file_id from dba_data_files where file_name like '%&partition%')
group by file_id;

select file_name,bytes/1024/1024/1024,maxbytes/1024/1024/1024 from dba_data_files

where file_id=&file_id;

alter database datafile '/ora03/oradata/argst01/data/argst01_undotbs1_08.dbf'

resize 7000M;+

select 'alter '||object_type||' ' ||owner||'.'||object_name||' compile;' from

dba_objects where status='INVALID' and owner='EPHARM2_OWNER';

nohup impdp userid=" '/ as sysdba' " parfile=impdp.par &

CREATE OR REPLACE DIRECTORY exp_dir AS '/ora10/IND16590803i';

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp

logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp

logfile=impdpDB10G.log

role refresh
=============
CREATE ROLE UPDATE_ANY_PEDAG73 NOT IDENTIFIED;

set echo off

set feedback off
set verify off
set head off
spool SELECT_EUCCM.sql
select 'Grant select on EUCCM_CUSTOMER.'||object_name|| ' to UPDATE_EUCCM_CUST;'
from dba_objects where owner='ANWARA07' and object_type in
('TABLE','VIEW','SYNONYM','MATERIALIZED VIEW');

spool off
set echo off
set feedback off
set verify off
set head off
spool UPDATE_EQMP.sql
select 'Grant insert,update,delete on EUCCM_CUSTOMER.'||object_name|| ' to
UPDATE_EUCCM_CUST;' from dba_objects where owner='ANWARA07' and object_type in
('TABLE','VIEW','SYNONYM','MATERIALIZED VIEW')

select 'grant select,insert,update,delete on '||owner||'.'||object_name||' to

UPDATE_EUCCM_CUST;'
from dba_objects where owner = 'EUCCM_CUSTOMER'
and object_type not in ('PACKAGE','PACKAGE BODY','DATABASE
LINK','PROCEDURE','FUNCTION') ;

====================
Useful UNIX Commands:
=====================
====================

/usr/local/bin/pbrun -u oracle bash

/usr/local/bin/sudo su - oracle bash

./sudo -i -u oracle

~oracle/admin/bin/leap
/app/oracle/admin/bin/leap

/app/oracle/admin/bin/jump

bash-2.03$ export ORACLE_SID=PPGPP

bash-2.03$ export ORACLE_HOME=/app/oracle/product/9.2
bash-2.03$ export PATH=$ORACLE_HOME/bin:$PATH
bash-2.03$ sqlplus

Connecting 7i Database:
=======================
7i
export ORACLE_SID=PPGPP
export ORACLE_HOME=/app/oracle/product/9.2
export PATH=$ORACLE_HOME/bin:$PATH
svrmgrl
connect internal
select * from global_name;

$ svrmgrl

Oracle Server Manager Release 2.3.3.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.3.0.0 - Production Release

PL/SQL Release 2.3.3.0.0 - Production

SVRMGR> connect internal

Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.

SYNONYM CREATION
=================
select 'create synonym '||object_name||' for '||owner||'.'||object_name||';' from
dba_objects where owner='TM_OWNER' and object_type in ('TABLE');

EXPORTING USING PIPE

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

mknod exp_SRMW3_pipe p

gzip < exp_SRMW3_pipe > exp_full_SRMW3_13Dec11.dmp.gz &

exp file=exp_SRMW3_pipe log=exp_full_SRMW3_13Dec11.log full=y compress=n

consistent=y statistics=none buffer=2000000

imp system/licorm3_mgr@licorm3 FROMUSER='OPS$LICORV3' TOUSER='OPS$LICORM3' commit=y

ignore=y grants=y buffer=1024000 file=exp_opslicorv3_06may08.dmp
log=imp_opslicorv3_opslicorm3_06May08.log indexes=y constraints=y

nohup imp userid=oramon/oramon1 file=exp_BETSY_failed_table2.dmp

log=imp_BETSY_failed_table2_24feb08.log fromuser=BETSY touser=BETSY commit=y
buffer=200000000 &

IMPORTING USING PIPE

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

mknod imp_pipe p

gunzip < exp_SAMMP_SAMM_SAMM_STAGE_03Apr09.dmp.gz > imp_pipe &

nohup imp userid=mahadn02/pfizer00 file=imp_pipe log=imp_sammprd_samm_apr03.log

fromuser=SAMM touser=SAMM buffer=204800000 &

TABLESPACE CHECK REPORT

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

select sum(bytes/(1024*1024*1024)) from dba_free_space where

tablespace_name='&TABLESPACENAME';

col file_name format a80

set linesize 200
DATA FILES
===========
select file_name,BYTES/(1024*1024) "ALLOCATED",MAXBYTES/(1024*1024) "MAX SIZE" FROM
DBA_DATA_FILES WHERE tablespace_name='&TABLESPACENAME';

SELECT sum(BYTES/(1024*1024)) "ALLOCATED",sum(MAXBYTES/(1024*1024)) "MAX SIZE",

sum(BYTES)*4/3/(1024*1024) "75% MARK",(sum(BYTES)*4/3-sum(maxbytes))/(1024*1024)
"Required Increment" FROM DBA_DATA_FILES WHERE tablespace_name='&TABLESPACENAME';

alter tablespace EPALMS_DATA add datafile

'/ora04/oradata/epalmsp/data/epalmsp_epalms_data_15.dbf' size 3000m autoextend on
maxsize 12000m;

TEMP FILES
============
select file_name,BYTES/(1024*1024) "ALLOCATED",MAXBYTES/(1024*1024) "MAX SIZE" FROM
DBA_TEMP_FILES WHERE tablespace_name='&TABLESPACENAME';

SELECT sum(BYTES/(1024*1024)) "ALLOCATED",sum(MAXBYTES/(1024*1024)) "MAX SIZE",

sum(BYTES)*4/3/(1024*1024) "75% MARK",(sum(BYTES)*4/3-sum(maxbytes))/(1024*1024)
"Required Increment" FROM DBA_TEMP_FILES WHERE tablespace_name='&TABLESPACENAME';

select sum(bytes_free/(1024*1024*1024)) from v$temp_space_header where

tablespace_name='TEMP';

alter tablespace TEMP add tempfile

'/ora02/oradata/edgoltpp/data/edgoltpp_temp_06.dbf' size 10000m autoextend on
maxsize 23000m;

alter database datafile

'/ora04/oradata/SMASTG/data/SMASTG_PFZ_DATA_02_16K_250M_08.dbf' autoextend on
maxsize 10240m;

===
Monitoring Tablespaces
======================

select sum(bytes)/1024/1024/1024,sum(maxbytes)/1024/1024/1024 from dba_data_files

where tablespace_name='UNDOTBS1';

select tablespace_name,sum(bytes)/1024/1024/1024 from dba_free_space where

tablespace_name in ('RUPS_BKP_DATA','RUPS_BKP_INDX','UNDOTBS1') group by
tablespace_name;

select tablespace_name,sum(bytes)/1024/1024 as allocated from dba_temp_files df

where/ tablespace_name like 'TEMP' group by tablespace_name order by
tablespace_name;

select tablespace_name,sum(bytes)/1024/1024 as allocated,sum(MAXBYTES/(1024*1024))

"MAX SIZE" from dba_temp_files df where tablespace_name like 'TEMP' group by
tablespace_name order by tablespace_name;

SET HEAD ON
SET VERIFY OFF
col tspace form a25 Heading "Tablespace"
col tot_ts_size form 99999999.99 Heading "Size (Mb)"
col free_ts_size form 99999999.99 Heading "Free (Mb)"
col Used_ts_size form 99999999.99 Heading "Used (Mb)"
col ts_pct1 form 999.99 Heading "% Used"
col ts_pct form 999.99 Heading "% Free"
SELECT df.tablespace_name tspace,
df.bytes/(1024*1024) tot_ts_size,
decode(round(df.bytes/(1024*1024)-sum(fs.bytes)/(1024*1024),2),
NULL,df.bytes/(1024*1024), round(df.bytes/(1024*1024)-sum(fs.bytes)/(1024*1024),2))
Used_ts_size,
decode(round((df.bytes-sum(fs.bytes))*100/df.bytes), NULL,100.00,round((df.bytes-
sum(fs.bytes))*100/df.bytes)) ts_pct1,
decode(round(sum(fs.bytes)/(1024*1024),2), NULL,0.00,
round(sum(fs.bytes)/(1024*1024),2)) free_ts_size,
decode(round(sum(fs.bytes)*100/df.bytes),NULL,0.00,round(sum(fs.bytes)*100/
df.bytes)) ts_pct
FROM dba_free_space fs ,
(select
tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name ) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes
having decode(round(sum(fs.bytes)*100/df.bytes),NULL,0.00,round(sum(fs.bytes)*100/
df.bytes)) < 99
order by df.tablespace_name
/

Locks
==========

select l1.sid, ' IS BLOCKING ', l2.sid

from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

select a.object_name,a.object_type,b.object_id,b.session_id
from dba_objects a,v$locked_object b
where a.object_id=b.object_id and oracle_username='IDM';

To check which statement is currently execting for the given stored procedure.
select sid, sql_text
from v$session ss, v$sqltext sq
where ss.sql_address=sq.address and ss.sql_hash_value=sq.hash_value
and ss.sid=&sid
order by sid, piece
/

SET LINESIZE 500

SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share ',
5, 'S/Row-X (SSX)',
6, 'Exclusive ',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

Query for killing sessions

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

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

v$session where sid in (655);

select 'kill -9 '||p.spid from v$session s, v$process p where s.paddr=p.addr and

s.sid in (655);

The following query displays a session if it is not waiting for a locked object but
is holding a lock on an object for which another session is waiting.
===================================================================================
======================================
col "O/SUser" for a10
col OraUser for a20
col Blocking for a12
col ObjLocked for a15
col sid for 99999999
col pid for a8
select s.osuser "O/SUser", s.username "OraUser", s.sid "SID",
s.serial# "Serial", s.process "PID", s.status "Status",l.name "ObjLocked",
l.mode_held "Lock Held" from V$SESSION s,DBA_DML_LOCKS l,V$PROCESS p
where l.session_id = s.sid and p.addr = s.paddr;

CPU UTILIZATON
=============

gsunk865a-ocp02 $ prstat -s cpu -n 10

gsunk865a-ocp02 $ prstat -s cpu -a -n 8
gsunk865a-ocp02 $ prstat -s size -n 5

sar

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

To generate AWR report

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

@/app/oracle/product/10.2.0.3_64/rdbms/admin/awrrpt.sql should be run

html

snapshot numbers

For getting a file from UNIX server to ur mail ID

uuencode PACECSC3_7thFeb_report.html PACECSC3_7thFeb_report.html |mailx -s
PACECSC3_7thFeb_report.html durgadhar.ponnaganti@pfizer.com

----------------------------------
To enable(ie broken=n) the specified jobs: exec dbms_job.broken(8409,FALSE);
---------------------------------
scheduler activities are in either crontab or in dba_jobs

Login as Schema account

SQL> exec dbms_job.broken(1041,FALSE);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

===============================================
To execute a Job

SQL> conn LOADER/mickey

Connected.
SQL> sho user
USER is "LOADER"
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
ENIP8.GROTON.PFIZER.COM

SQL> exec dbms_job.run(42);

EXEC dbms_job.run(105);

NOTE:

select JOB_NAME from dba_scheduler_jobs;(>10g databases)

SQL> show user

USER is "SIGHTS_OWNER"
SQL> select * from dba_jobs_running;

=====================================
Import STATUS
================

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'imp%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'imp%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;

Renaming global_name:
=====================
alter database rename global_name to PCAPE.PFIZER.COM;

Split files
==============
================

split -b 800m SIEBEL_SRMW_ENIP125_30NOV09_1.dmp.gz

SIEBEL_SRMW_ENIP125_30NOV09_1.dmp.gzaa SIEBEL_SRMW_ENIP125_30NOV09_1.dmp.gz
ab SIEBEL_SRMW_ENIP125_30NOV09_1.dmp.gzac SIEBEL_SRMW_ENIP125_30NOV09_1.dmp.gzad

Get DB LINK INFO

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

set long 1000

SELECT dbms_metadata.get_ddl('DB_LINK','CONDUIT','CONDUIT_DEV_OWNER') from dual;

============
MV refresh logs
===============

all_mview_refresh_times

***********************************************************************************
************************************************************************
OEM RELATED
***********************************************************************************
*************************************************************************

emctl start agent � to start agent

emctl status agent � status of the agent

emctl stop agent � stop the agent

emctl upload agent � to check the upload process

emctl config agent listtargets � to list all the targets

Redp Transport Error

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

PRimary
=========

select name, open_mode from v$database;

select * from v$log where STATUS='CURRENT';

select DEST_ID,STATUS,error from v$archive_dest;

Standby
======

select SEQUENCE#,APPLIED from v$archived_log where SEQUENCE# =190556;

select process,status, sequence# from v$managed_standby;

Checking for Argus Databases

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

Once you mount the standby database ENIP131 & ENIP132 the DGMGRL should
automatically put these databases in recovery mode.

As a last check you can check the status of these database by the below set of
commands.

gsunk910a-enip131 $ dgmgrl /

DGMGRL for Solaris: Version 10.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> show configuration;

Configuration
 Name: enip131

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

enip131_gsunk910b - Primary database

enip131_gsunk910a - Physical standby database

enip131_amrndhp006a - Physical standby database

SUCCESS

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

Health of database
+++++++++++++++++++++++
===========================

1. select name, open_mode from v$database;

It must be READ ONLY

2. select distinct status from v$datafile;

It must show as SYSTEM and ONLINE

3. select distinct status from dba_tablespaces;

It must show as ONLINE

4. select distinct status from dba_data_files;

It must show as AVAILABLE

5. Check if archive destinations are full

alter system switch logfile;
System should not hang.

SQL> select count(*) from v$recover_file;

Archive locations will be in /app/oracle/admin/<DBNAME>/trace/bdump

6. Check for remote connectivity.

COLD backup
===========
bring the database in mount state and give the script.

{
allocate channel d1 type disk format '/db/GBL12704693i/full_close_backup1_argus_st_
%t_%s_%p_%U.bck';
allocate channel d2 type disk format '/db/GBL12704693i/full_close_backup2_argus_st_
%t_%s_%p_%U.bck';
backup database tag = full_closed_backup_disk ;
release channel d1;
release channel d2;
allocate channel d3 type disk format '/db/GBL12704693i/full_close_backup3_argus_st_
%t_%s_%p_%U.bck';
backup current controlfile;
release channel d3;
shutdown normal;
}

RMAN compressed disk backup

==========================
Target database should always in nomount state.

run
{
allocate channel d1 type disk format
'/oramisc/GBL11106837i/full_open_backup1_soass1_%t_%s_%p_%U.bck' maxpiecesize
10240M;
allocate channel d2 type disk format
'/oramisc/GBL11106837i/full_open_backup2_soass1_%t_%s_%p_%U.bck' maxpiecesize
10240M;
allocate channel d3 type disk format
'/oramisc/GBL11106837i/full_open_backup3_soass1_%t_%s_%p_%U.bck' maxpiecesize
10240M;
allocate channel d4 type disk format
'/oramisc/GBL11106837i/full_open_backup4_soass1_%t_%s_%p_%U.bck' maxpiecesize
10240M;
backup incremental level = 0 AS COMPRESSED BACKUPSET database
tag = full_open_backup_disk;
sql 'alter system archive log current';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
allocate channel d5 type disk format
'/oramisc/GBL11106837i/arch_open_backup5_soass1_%t_%s_%p_%U.bck' maxpiecesize
10240M;
backup archivelog from logseq 3783;
backup current controlfile format '/oramisc/GBL11106837i/ctrl_open_backup6_soass1_
%t_%s_%p_%U.bck';
release channel d5;
}

select min(sequence#) Seq from v$log where status='INACTIVE' and archived='YES';

rman target / catalog rman9ir7/bried8@rman9ip.groton.pfizer.com

cmdfile=rman_disk_bkp_ahgctxp_19feb10.rcv log=rman_disk_bkp_ahgctxp_19feb10.log
mailx -s "Rman full disk backup of ahgctxp is completed" dl-sams-operations-
oracle@pfizer.com < rman_disk_bkp_ahgctxp_19feb10.log
RMAN normal disk backup
==================

run {
allocate channel d1 type disk format
'/ora01/rman/LICORV6/open/full_open_backup1_LICORV6_%t_%s_%p_%U.bck' maxpiecesize
10240M;
allocate channel d2 type disk format
'/ora01/rman/LICORV6/open/full_open_backup2_LICORV6_%t_%s_%p_%U.bck' maxpiecesize
10240M;
allocate channel d3 type disk format
'/ora01/rman/LICORV6/open/full_open_backup3_LICORV6_%t_%s_%p_%U.bck' maxpiecesize
10240M;
allocate channel d4 type disk format
'/ora01/rman/LICORV6/open/full_open_backup4_LICORV6_%t_%s_%p_%U.bck' maxpiecesize
10240M;
backup incremental level 0 database tag = full_open_backup_disk;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
sql 'alter system archive log current';
allocate channel d5 type disk format
'/ora01/rman/LICORV6/open/arch_open_backup4_ahgctxp_%t_%s_%p_%U.bck' maxpiecesize
10240M;
backup archivelog from logseq 37843;
backup current controlfile format
'/ora01/rman/LICORV6/open/ctrl_open_backup4_ahgctxp_%t_%s_%p_%U.bck';
release channel d5;
}

select min(sequence#) Seq from v$log where status='INACTIVE' and archived='YES';

rman target / catalog rman9ir7/bried8@rman9ip.groton.pfizer.com

cmdfile=rman_disk_bkp_LICORV6_12OCT11.rcv log=rman_disk_bkp_LICORV6_12OCT11.log
mailx -s "Rman full disk backup of LICORV6 is completed"
durgadhar.ponnaganti@pfizer.com < rman_disk_bkp_LICORV6_12OCT11.log

Disk Backup using soft links

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

amrndhp005b-LICRUS2P $ cd /tmp/rman_backups
amrndhp005b-LICRUS2P $ ls
LICRMX1P rman_backups
amrndhp005b-LICRUS2P $ ls -lrr
total 16
lrwxrwxrwx 1 oracle dba 21 Jun 8 17:16 rman_backups ->
/oramisc/rman_backups
drwxr-xr-x 2 oracle dba 8192 Jun 8 13:43 LICRMX1P
amrndhp005b-LICRUS2P $ pwd
/tmp/rman_backups

select min(sequence#) Seq from v$log where status='INACTIVE' and archived='YES';

run {
allocate channel d1 type disk format
'/tmp/rman_backups/LICRUS2P/open/full_open_backup1_LICRUS2P_%t_%s_%p_%U.bck'
maxpiecesize 10240M;
backup incremental level 0 database
tag = full_open_backup_disk;
release channel d1;
sql 'alter system archive log current';
allocate channel d2 type disk format
'/tmp/rman_backups/LICRUS2P/open/arch_open_backup2_LICRUS2P_%t_%s_%p_%U.bck'
maxpiecesize 10240M;
ackup archivelog from logseq 48828;
backup current controlfile format
'/tmp/rman_backups/LICRUS2P/open/ctrl_open_backup3_LICRUS2P_%t_%s_%p_%U.bck';
release channel d2;
}

rman target / catalog RMAN9IR7/bried8@rman9ip.groton.pfizer.com

cmdfile='rman_LICRUS2P_disk_bkp_24June2010.rcv'
log='rman_LICRUS2P_disk_bkp_24June2010.log'
mailx -s "Full disk backup of LICRUS2P is completed" prathik.varma@pfizer.com dl-
sams-operations-oracle@pfizer.com < rman_LICRUS2P_disk_bkp_24June2010.log

TEMP FILE FOR SYSEDGE

===================--

du -sh /ora13/oradata/*/data/* | grep -i temp

select ss.tablespace_name,sum((ss.used_blocks*ts.blocksize))/1024/1024 mb from

gv$sort_segment ss, sys.ts$ ts where ss.tablespace_name = ts.name group by
ss.tablespace_name;
select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from
gv$sort_segment where tablespace_name = 'TEMP';

col file_name format a80

set linesize 200
select file_name,bytes/1024/1024,maxbytes/1024/1024 from dba_temp_files where
tablespace_name='TEMP';

ALTER DATABASE TEMPFILE '/ora05/oradata/SMTINT/data/smtint_temp_01.dbf' DROP

INCLUDING DATAFILES;

ALTER TABLESPACE TEMP add tempfile '/ora05/oradata/SMTINT/data/smtint_temp_01.dbf'

size 100M AUTOEXTEND ON MAXSIZE 10240m;

Moving DataFile
===============

ALTER TABLESPACE DIF_SND1_DATA OFFLINE NORMAL;

cp /ora13/oradata/dif_d/data/DIF_D_DIF_SND1_DATA_02.dbf
/ora12/oradata/dif_d/data/DIF_D_DIF_SND1_DATA_02.dbf

ALTER TABLESPACE DIF_SND1_DATA RENAME DATAFILE

'/ora13/oradata/dif_d/data/DIF_D_DIF_SND1_DATA_02.dbf' TO
'/ora12/oradata/dif_d/data/DIF_D_DIF_SND1_DATA_02.dbf';

ALTER TABLESPACE DIF_SND1_DATA ONLINE;

select * from v$recover_file;

Restricted Session
====================

SQL> startup restrict

You can take the database in and out of restricted mode with the alter database
command as seen in this example:

-- Put the database in restricted session mode.

SQL> alter system enable restricted session;

-- Take the database out of restricted session mode.

SQL> alter system disable restricted session;

Writing a sQL script and executing as .sh file

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

sqlplus "/as sysdba" <<END_SCRIPT

set echo on
set feedback on
set time on
spool XMS71PS1.log
select * from global_name;
sho user;
@step2.sql
@step3a.sql
@step3b.sql
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;
spool off
END_SCRIPT

Maximum Sessions exceeded

===========================
ps -ef | grep qtsgprod | grep LOCAL=NO - inactive sessions

1. Select count(*) from v$session;

2. show parameter processes;
3. set pages 500;
4. select 'alter system kill session '''||sid ||','||serial#||''';' from
v$session where status='INACTIVE' and rownum < 50 order by logon_time;
5. Execute the output you got from the above query.
6. select 'kill -9 '||p.spid from v$session s, v$process p where s.paddr=p.addr
and s.status='INACTIVE' and rownum < 50 order by logon_time;
7. Execute the output you got from executing the above query at the OS level.
8. Inform the team that the inactive sessions have been killed in the database
and also at the OS level.

Tracing
==========
1. Get the SID and SERIAL# for the process you want to trace.
SQL> select sid, serial# from sys.v_$session where ...
SID SERIAL#
---------- ----------
8 13607

2. Enable tracing for your selected process:

SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

SQL> execute dbms_system.set_sql_trace_in_session(8, 13607, true);

3. Ask user to run just the necessary to demonstrate his problem.

4. Disable tracing for your selected process:

SQL> execute dbms_system.set_sql_trace_in_session(8,13607, false);

SQL> ALTER SYSTEM SET TIMED_STATISTICS = FALSE;

5. Look for trace file in USER_DUMP_DEST

$ cd /app/oracle/admin/oradba/udump
$ ls -ltr
total 8
-rw-r----- 1 oracle dba 2764 Mar 30 12:37 ora_9294.trc

6. Run TKPROF to analyse trace output

$ tkprof ora_9294.trc x EXPLAIN=monitor/oramon SYS=NO

7. View or print the output file x.prf.

tkprof ep06_np_ora_385379_5tcm45gr0jhd3.trc ep06_np_ora_385379_5tcm45gr0jhd3.txt

sys=no

==============================EXP and IMP

commands=========================================================================
to refresh only some objects in schema.

nohup expdp userid=" '/ as sysdba' " directory=exp_dir

dumpfile=expdp_FUN_VIE_PROC_DSSINT_H8DWDELP_17Aug2011.dmp
logfile=expdp_FUN_VIE_PROC_DSSINT_H8DWDELP_17Aug2011.log
INCLUDE=PROCEDURE,FUNCTION,VIEW CONTENT=METADATA_ONLY schemas=DSSINT &

Tables Export
===============

nohup exp userid=" '/ as sysdba' "

parfile=exp_tables_S_NQ_SCHED_ECMST35_05May10.par &

file=exp_tables_S_NQ_SCHED_ECMST35_05May10.dmp
log=exp_tables_S_NQ_SCHED_ECMST35_05May10.log
tables=S_NQ_SCHED.S_NQ_ERR_MSG,
S_NQ_SCHED.S_NQ_JOB
consistent=y
compress=n
statistics=none
buffer=500000000
feedback=2000000
Tables Import
=============

imp userid=" '/ as sysdba' " parfile=imp_tables_S_NQ_SCHED_ECMST35_05May10.par

file=exp_tables_S_NQ_SCHED_WPOANAP2_05May10.dmp
log=imp_tables_S_NQ_SCHED_ECMST35_05May10.log
tables=S_NQ_ERR_MSG,
S_NQ_JOB
fromuser=S_NQ_SCHED
touser=S_NQ_SCHED
commit=y
buffer=200000000

DISTINCT TABLESPACES
=======================
select distinct tablespace_name,owner from dba_segments where owner in
('IRG_RO''TRIRIGA_DATA_OWNER');

Taking Privs
============

set lines 150

set head off
spool PRIVS_PLBT.sql
Select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
from dba_tab_privs where grantor='RIGHTANSWERS';

Drop Script
===========

set head off

set verify off
set feedback off
set echo off
spool drop_PLBT.sql
select 'drop '||object_type||' ' ||owner||'.'||object_name||';' from dba_objects
where owner in ('PLBT') and object_type not in ('TABLE','INDEX')
union
select 'drop '||object_type||' ' ||owner||'.'||object_name||' cascade constraints;'
from dba_objects where owner in ('PLBT') and object_type='TABLE' order by 1 desc;

Exp Command
==========

nohup exp userid=" '/ as sysdba' " file=exp_HCAPP_ENIP51_04May10.dmp

log=exp_HCAPP_ENIP51_04May10.log owner=HCAPP compress=N consistent=Y
statistics=none buffer=20480000 &

Imp Command
===========

nohup imp userid=" '/ as sysdba' " fromuser=PLB touser=PLBT

file=exp_clnprd1_PLB_03May2010.dmp log=imp_PLBT_CLNTST1_05May10.log grants=y
buffer=20480000 &

Objects Count
===============

select Count(object_name), object_type, status from dba_objects where

owner='LOGILITY' group by object_type, status order by object_type, status;

Compiling
==========

select 'alter '||object_type||' ' ||owner||'.'||object_name||' compile;' from

dba_objects where status='INVALID' and owner='&name';

EXPDP
=====
CREATE OR REPLACE DIRECTORY exp_dir AS '/ora05/IND16590803i';

nohup expdp userid=" '/ as sysdba' " parfile=expdp_IPTSTG_IPT_OWNER_09APR2012.par &

directory=exp_dir
dumpfile=expdp_IPTSTG_IPT_OWNER_09APR2012.dmp
logfile=expdp_IPTSTG_IPT_OWNER_09APR2012.log
schemas=IPT_OWNER
EXCLUDE=TABLE:"IN('IPT_USER','IPT_USER_GROUP_COUNTRY','IPT_SYSTEM_PARAMETER','IPT_U
SER_PRODUCT_GROUP','IPT_HFM_PARAM','IPT_HFM_FUNCTION','IPT_TRANSACTION_CONTEXT')"
EXCLUDE=PROCEDURE:"IN('PR_PRICING_PRAXIS_DATA')"

IPT_USER_PRODUCT_GROUP

CARS EXPDP
==========
nohup expdp userid=" '/ as sysdba' " parfile=EXPDP_CARSPRD_3SCHEMAS_042310.par &

CREATE OR REPLACE DIRECTORY exp_dir AS '/oramisc/exp/carsprd';

DIRECTORY=exp_dir
DUMPFILE=EXPDP_CARSPRD_3SCHEMAS_042310.dmp
LOGFILE=EXPDP_CARSPRD_3SCHEMAS_042310.log
SCHEMAS=CARSNG,IDM,CIG_USER
PARALLEL=3
FLASHBACK_TIME="to_timestamp(to_char(sysdate, 'dd/mon/yyyy hh24:mi:ss'),
'dd/mon/yyyy hh24:mi:ss')"

EXPDP Multiple Dump files

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

dumpfile=expdp_dir1:CARSNG_CARSPRD_19APR2010_DP_1.dmp,expdp_dir2:CARSNG_CARSPRD_19A
PR2010_DP_2.dmp,expdp_dir3:CARSNG_CARSPRD_1
9APR2010_DP_3.dmp
logfile=expdp_dir1:CARSNG_CARSPRD_19APR2010_DP.log
schemas=CARSNG
filesize=50G
parallel=3
job_name=MET11480999i

IMPDP
======

CREATE OR REPLACE DIRECTORY imp_dir AS '/ora02/MET11414360i';

nohup impdp userid=" '/ as sysdba' " directory=imp_dir
dumpfile=expdp_TRIRIGA_DATA_OWNER_ENIP53_08Apr10.dmp
logfile=impdp_TRIRIGA_DATA_OWNER_ENIS53_08Apr10.log
remap_schema=TRIRIGA_DATA_OWNER:TRIRIGA_DATA_OWNER &

CARS IMPDP
==========

CREATE OR REPLACE DIRECTORY imp_dir AS '/ora04/exp/carsprd';

nohup impdp userid=" '/ as sysdba' " parfile=IMPDP_CARSSTG_AMR11458820i_041710.par

&

DIRECTORY=imp_dir
DUMPFILE=EXPDP_CARSPRD_AMR11458820i_DP_041610.dmp
LOGFILE=IMPDP_CARSSTG_AMR11458820i_041710.log
REMAP_SCHEMA=(CARSNG:CARSNG_STG,CARS_BATCH:CARS_BATCH_STG,CIG_RO:CIG_STG_RO,CIG_USE
R:CIG_STG_USER,COGNOS:COGNOS_STG,ECMF:ECMF_STG,IDM:IDM_STG)
REMAP_TABLESPACE=(CARSNG_DATA:CARSNG_STG_DATA,CARSNG_INDX:CARSNG_STG_INDX,CARS_BATC
H_DATA:CARS_BATCH_STG_DATA,CARS_BATCH_INDX:CARS_BATCH_STG_INDX,CIG_DATA:CIG_STG_DAT
A,CIG_INDX:CIG_STG_INDX,COGNOS_DATA:COGNOS_STG_DATA,ECMF_DATA:ECMF_STG_DATA,IDM_DAT
A:IDM_STG_DATA,IDM_INDX:IDM_STG_INDX)

ATLAS FOR SALES - Tables Export

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

exp userid=" '/ as sysdba' " parfile=exp_5tables_10Jan10.par

file=exp_5tables_ATMS_STG_OWNER_ENIS103_10Jan10.dmp
log=exp_5tables_ATMS_STG_OWNER_ENIS103_10Jan10.log
tables=ATMS_STG_OWNER.FFR_JOB_ROLE,
ATMS_STG_OWNER.FF_TRANSFER_HIST,
ATMS_STG_OWNER.FF_EMPL_UPDATE,
ATMS_STG_OWNER.FF_PROMOTION_HIST,
ATMS_STG_OWNER.FF_STATUS_HISTORY
compress=N
statistics=none
buffer=20480000
==================================
MKNOD EXPORT

mknod exp_pipe p

nohup gzip < exp_pipe > exp_full_SRMW3_ecmst3_13Dec11.dmp.gz &

nohup exp userid="/ as sysdba" parfile=exp.par &

exp.par
owner=SRMW3
file=exp_pipe
log=exp_full_SRMW3_ecmst3_13Dec11.log
consistent=y
compress=n
direct=y
statistics=none
buffer=500000000

exp_SIEBEL_edgoltpp_24nov2011.par

file=exp_pipe
owner=SIEBEL
log=exp_SIEBEL_edgoltpp_24nov2011.log
statistics=none
buffer=5000000
compress=n
consistent=y

Create a compressed export on the fly. Depending on the type of data, you probably
can export up to 10 gigabytes to a single file. This example uses gzip. It offers
the best compression I know of, but you can also substitute it with zip, compress
or whatever.

# create a named pipe

mknod exp.pipe p
# read the pipe - output to zip file in the background
gzip < exp.pipe > scott.exp.gz &
# feed the pipe
exp userid=scott/tiger file=exp.pipe ...
Import directly from a compressed export:

# create a name pipe

mknod imp_pipe p
# read the zip file and output to pipe
gunzip < exp_SIEBEL_edgoltpp_24nov2011.dmp.gz > imp_pipe &
# feed the pipe
imp system/pwd@sid file=imp_pipe log=imp_pipe.log ...

IPTDEV REFRESH
================

set lines 150

set pages 5000
set head off
spool PRIVS_IPTSTG_9APR12.sql
Select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
from dba_tab_privs where grantor in ('IPT_OWNER');
spool off

set head off

set verify off
set feedback off
set echo off
spool drop_IPTSTG_9APR12.sql
select 'drop '||object_type||' ' ||owner||'.'||object_name||';' from dba_objects
where owner in ('IPT_OWNER') and object_type not in ('TABLE','INDEX')
union
select 'drop '||object_type||' ' ||owner||'.'||object_name||' cascade constraints;'
from dba_objects where owner in ('IPT_OWNER') and object_type='TABLE' order by 1
desc;
SPOOL OFF
DIRECTORY=imp_dir
DUMPFILE=expdp_IPTPRD_IPT_OWNER_09APR2012.dmp
REMAP_SCHEMA=(IPT_OWNER:IPT_OWNER)
LOGFILE=IMPDP_IPTSTG_IPT_OWNER_09APR2012.log
EXCLUDE=DB_LINK:"IN('IPTT.PFIZER.COM','PRAXIS2P_LK.PFIZER.COM')"

EXCLUDE=PROCEDURE:"IN('PR_PRICING_PRAXIS_DATA')"
REMAP_SCHEMA=(CARSNG:CARSNG_DEV,CARS_BATCH:CARS_BATCH_DEV,CIG_RO:CIG_DEV_RO,CIG_USE
R:CIG_DEV_USER,COGNOS:COGNOS_DEV,ECMF:ECMF_DEV,IDM:IDM_DEV)
REMAP_TABLESPACE=(CARSNG_DATA:CARSNG_DEV_DATA,CARSNG_INDX:CARSNG_DEV_INDX,CIG_INDX:
CIG_DEV_INDX,CIG_DATA:CIG_DEV_DATA,CARS_BATCH_DATA:CARS_BATCH_DEV_DATA,CARS_BATCH_I
NDX:CARS_BATCH_DEV_INDX,COGNOS_DATA:COGNOS_DEV_DATA,ECMF_DATA:ECMF_DEV_DATA,IDM_DAT
A:IDM_DEV_DATA,IDM_INDX:IDM_DEV_INDX)

CREATE OR REPLACE DIRECTORY imp_dir AS '/ora05/IND16590803i';

nohup impdp userid=" '/ as sysdba' " parfile=IMPDP_IPTSTG_IPT_OWNER_09APR12.par &

Multiple Files EXPDP

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

CREATE OR REPLACE DIRECTORY exp_dir AS '/oramisc/AMR13791991i';

CREATE OR REPLACE DIRECTORY exp_dir1 AS '/oraarch/AMR13791991i';

dumpfile=exp_dir:ASPEN7_P2LS02_18Mar2011_DP_1.dmp,exp_dir1:ASPEN7_P2LS02_18Mar2011_
DP_2.dmp,exp_dir1:ASPEN7_P2LS02_18Mar2011_DP_3.dmp,exp_dir1:ASPEN7_P2LS02_18Mar2011
_DP_4.dmp
logfile=exp_dir:ASPEN7_P2LS02_18Mar2011_DP.log
schemas=ASPEN7
filesize=40G
parallel=4
job_name=AMR13791991i

nohup expdp userid=" '/ as sysdba' " parfile=ASPEN7_P2LS02_18Mar2011_DP.par &

Multiple Files IMPDP

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

DIRECTORY=imp_dir
DUMPFILE=SIEBEL_EDGOLTPP_24MAR2011_DP_1.dmp,
SIEBEL_EDGOLTPP_24MAR2011_DP_2.dmp,
SIEBEL_EDGOLTPP_24MAR2011_DP_3.dmp
LOGFILE=IMPDP_SIEBEL_EDGOLTPT_24Mar2011.log
REMAP_SCHEMA=(SIEBEL:SIEBEL)
REMAP_TABLESPACE=(SIEBEL_DATA:SIEBEL_DATA,SIEBEL_INDX:SIEBEL_INDX)

CREATE OR REPLACE DIRECTORY imp_dir AS '/ora04/GBL13797723i_do_not_delete';

nohup impdp userid=" '/ as sysdba' " parfile=IMPDP_SIEBEL_EDGOLTPT_24Mar2011.par &

Dropping 10g and above Databases

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

Create pfile from spifle if databasse is running on spfile and take the backup of
pfile as _bkup or as per ur wish

Then exceute the below commands

shutdown immediate;
startup mount exclusive restrict;
drop database;

DATABASE LINK
=============

CREATE DATABASE LINK "H8DWPROD.PFIZER.COM"

CONNECT TO MDM_IDENTITY identified by <password>
USING 'H8DWPROD.PFIZER.COM';

RMAN
BACKUP
==================
===============
vi rman_tape_regd1_21Jan2011.rcv
Run
{
Execute script full_open_backup_tape;
Execute script archivelog_nodelete_open_backup_tape;
}

vi rman_tape_regd1_21Jan2011.sh
rman target / catalog rman10gr1/bried8@rman10gp.groton.pfizer.com
cmdfile=rman_tape_regd1_21Jan2011.rcv log=rman_tape_regd1_21Jan2011.log
EOF
echo "rman backup for regd1 completed at - `date` " >>
rman_tape_regd1_21Jan2011.log
mailx -s "rman tape backup log -- open -- regd1 " prathik.varma@pfizer.com <
rman_tape_regd1_21Jan2011.log

Identifying tapes for 10g and above databases

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

run {
set until time "to_date('04-NOV-2010 17:00:00','DD-MON-YYYY HH24:MI:SS')";
allocate channel t1 type 'SBT_TAPE';
send 'NSR_ENV=(NSR_SERVER=amrndhs009.pfizer.com)';
restore database preview;
release channel t1;
}

Creating temporary database from tapes

========================================
run {
allocate auxiliary channel t1 type 'sbt_tape';
allocate auxiliary channel t2 type 'sbt_tape';
allocate auxiliary channel t3 type 'sbt_tape';
allocate auxiliary channel t4 type 'sbt_tape';
send 'NSR_ENV=(NSR_SERVER=amrndhs009.pfizer.com(tape
server),NSR_CLIENT=amrndhp005b.pfizer.com (source server))';
set until time "to_date('04-NOV-2010 17:00:00','DD-MON-YYYY HH24:MI:SS')";
set newname for datafile 1 TO
'/ora15/oradata/enip151r/data/enip151r_system_01.dbf';
set newname for datafile 2 TO
'/ora15/oradata/enip151r/data/enip151r_undotbs1_01.dbf';
set newname for datafile 3 TO
'/ora15/oradata/enip151r/data/enip151r_undotbs1_02.dbf';
set newname for datafile 4 TO
'/ora15/oradata/enip151r/data/enip151r_sysaux_01.dbf';
set newname for datafile 5 TO
'/ora15/oradata/enip151r/data/enip151r_sysaux_02.dbf';
set newname for datafile 6 TO
'/ora15/oradata/enip151r/data/enip151r_users_01.dbf';
set newname for datafile 7 TO
'/ora15/oradata/enip151r/data/enip151r_tools_01.dbf';
set newname for datafile 8 TO
'/ora15/oradata/enip151r/data/enip151r_perfstats_d1_01.dbf';
set newname for datafile 9 TO
'/ora15/oradata/enip151r/data/enip151r_perfstats_i1_01.dbf';
set newname for datafile 10 TO
'/ora15/oradata/enip151r/data/enip151r_sys_audit_d1_01.dbf';
set newname for datafile 11 TO
'/ora15/oradata/enip151r/data/enip151r_sys_audit_i1_01.dbf';
set newname for datafile 12 TO
'/ora15/oradata/enip151r/data/enip151r_td_data_01.dbf';
set newname for datafile 13 TO
'/ora15/oradata/enip151r/data/enip151r_td_data_02.dbf';
set newname for tempfile 1 to
'/ora15/oradata/enip151r/data/enip151r_temp_01.dbf';
set newname for tempfile 2 to
'/ora15/oradata/enip151r/data/enip151r_temp_02.dbf';
duplicate target database to enip151r
logfile
group 1 ('/ora15/oradata/enip151r/redo/enip151r_g1m1.rdo',
'/ora15/oradata/enip151r/redo/enip151r_g1m2.rdo') size 100M,
group 2 ('/ora15/oradata/enip151r/redo/enip151r_g2m1.rdo',
'/ora15/oradata/enip151r/redo/enip151r_g2m2.rdo') size 100M,
group 3 ('/ora15/oradata/enip151r/redo/enip151r_g3m1.rdo',
'/ora15/oradata/enip151r/redo/enip151r_g3m2.rdo') size 100M;
}

Restore from tape when tapes are online from DP team

========================================================
rman_restore_pgpprodt_03Apr2011.rcv
run {
allocate auxiliary channel t1 type 'sbt_tape';
allocate auxiliary channel t2 type 'sbt_tape';
allocate auxiliary channel t3 type 'sbt_tape';
allocate auxiliary channel t4 type 'sbt_tape';
send
'NSR_ENV=(NSR_SERVER=amrndhs256.pfizer.com,NSR_CLIENT=amrndhl227.pfizer.com)';
set until time "to_date('02-APR-2011 13:00:00','DD-MON-YYYY HH24:MI:SS')"; or
set until logseq 94965(MIN SEQUENCE) thread 1;
set newname for datafile 1 to
'/ora01/oradata/pgpprodt/data/pgpprodt_system_01.dbf';
set newname for datafile 2 to
'/ora01/oradata/pgpprodt/data/pgpprodt_sysaux_01.dbf';
set newname for datafile 3 to
'/ora01/oradata/pgpprodt/data/pgpprodt_sysaux_02.dbf';
set newname for datafile 4 to
'/ora01/oradata/pgpprodt/data/pgpprodt_undotbs1_01.dbf';
set newname for datafile 5 to
'/ora01/oradata/pgpprodt/data/pgpprodt_undotbs1_02.dbf';
set newname for datafile 6 to '/ora01/oradata/pgpprodt/data/pgpprodt_users_01.dbf';
set newname for datafile 7 to '/ora01/oradata/pgpprodt/data/pgpprodt_tools_01.dbf';
set newname for datafile 8 to
'/ora01/oradata/pgpprodt/data/pgpprodt_perfstats_d1_01.dbf';
set newname for datafile 9 to
'/ora01/oradata/pgpprodt/data/pgpprodt_perfstats_i1_01.dbf';
set newname for datafile 10 to
'/ora01/oradata/pgpprodt/data/pgpprodt_sys_audit_d1_01.dbf';
set newname for datafile 25 to
'/ora04/oradata/pgpprodt/data/pgpprodt_uspmgt_data_01.dbf';
set newname for datafile 26 to
'/ora04/oradata/pgpprodt/data/pgpprodt_uspmgt_data_02.dbf';
set newname for datafile 27 to
'/ora04/oradata/pgpprodt/data/pgpprodt_uspmgt_index_02.dbf';
set newname for datafile 32 to '/ora01/oradata/pgpprodt/data/pgpprodt_xdb01.dbf';
set newname for tempfile 1 to '/ora01/oradata/pgpprodt/data/pgpprod_temp_01.dbf';
set newname for tempfile 2 to '/ora01/oradata/pgpprodt/data/pgpprod_temp_02.dbf';
duplicate target database to pgpprodt skip tablespace
PCMGT_DATA,USPPNL_DATA,PCMGT_INDX,PCMGT_A_INDEX_01,PCPNL_INDX,PCPNL_A_INDX,PCPNL_A_
DATA,USPMED_DATA,USPMED_INDEX,USPPNL_INDEX,PCMGT_A_DATA_01,PCPNL_DATA
logfile
group 1 ('/ora01/oradata/pgpprodt/redo/pgpprodt_g1m1.rdo',
'/ora01/oradata/pgpprodt/redo/pgpprodt_g1m2.rdo') size 100M,
group 2 ('/ora01/oradata/pgpprodt/redo/pgpprodt_g2m1.rdo',
'/ora01/oradata/pgpprodt/redo/pgpprodt_g2m2.rdo') size 100M,
group 3 ('/ora01/oradata/pgpprodt/redo/pgpprodt_g3m1.rdo',
'/ora01/oradata/pgpprodt/redo/pgpprodt_g3m2.rdo') size 100M;
}

rman target / auxiliary sys/pfizer00@enip151r.pfizer.com

cmdfile='rman_restore_enip151r_from_enip151.rcv'
log='rman_restore_enip151r_from_enip151.log'
mailx -s "Restore of ENIP151R is done" dl-sams-operations-oracle@pfizer.com <
rman_restore_enip151r_from_enip151.log

moplgties05-WERCS $ more rman_closed_bkup_02Apr10.sh

rman target / catalog RMAN9IR1/bried8@rman9ip.groton.pfizer.com
cmdfile=rman_closed_bkup_02Apr10.rcv log=rman_closed_bkup_02Apr10.log
mailx -s "RMAN closed backup of WERCS completed" <
chandrasekhar.dhanekula@pfizer.com
moplgties05-WERCS $ more rman_closed_bkup_02Apr10.rcv
run
{
allocate channel d1 type disk format
'/x0101/rman_backups/WERCS/full_close_backup1_WERCS_%t_%s_%p_%U.bck';
allocate channel d2 type disk format
'/x0101/rman_backups/WERCS/full_close_backup2_WERCS_%t_%s_%p_%U.bck';
backup incremental level = 0 database tag = full_closed_backup_disk ;
release channel d1;
release channel d2;
allocate channel d3 type disk format
'/x0101/rman_backups/WERCS/full_close_backup3_WERCS_%t_%s_%p_%U.bck';
backup current controlfile;
release channel d3;
shutdown normal;
}

Full DB RESTORE USING TAPE ( Existing database is dropped and fully restored from
Tape )
==========

amrndhp0001g:NONE-> more HCMGPY_restore.rcv

run {
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
allocate channel t3 type 'SBT_TAPE';
allocate channel t4 type 'SBT_TAPE';
allocate channel t5 type 'SBT_TAPE';
send
'NSR_ENV=(NSR_SERVER=amrndhs009.pfizer.com,NSR_CLIENT=amrndhp0001g.pfizer.com)';
set until time "to_date('22-DEC-2010 23:59:00','DD-MON-YYYY HH24:MI:SS')";
restore controlfile;
sql 'alter database mount';
restore database;
recover database;
sql 'alter database open resetlogs';
}

amrndhp0001g:NONE-> more HCMGPY_restore.sh

rman target / catalog rman11gr2/bried8@rman11gp.groton.pfizer.com

cmdfile='HCMGPY_restore.rcv' log='HCMGPY_restore.log'
mailx -s "restore of HCMGPY is completed" dl-sams-operations-oracle@pfizer.com <
HCMGPY_restore.log

amrndhp0001g:NONE->

UUENCODE COMMAND
================ ( Getting log file to ur Mail ID )

uuencode obsolete_disc_bug_fix_B1851065_16aug2011_140453.log
obsolete_disc_bug_fix_B1851065_16aug2011_140453.log |mailx -s
obsolete_disc_bug_fix_B1851065_16aug2011_140453.log durgadhar.ponnaganti@pfizer.com

Some Useful Queues and Other Stuff

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

GBL-WTI-AHS UNIX L2
GBL-WTE-SAMS-DBA L3
GBL-WTI-AHS UNIX L1
GBL-PGRDI-CE-PLATFORM
GBL-GSS-SAMS-DBA CAPACITY
GBL-WTI-AHS DP L2
GBL-WTI-AHS WINTEL L2
pfizer Global Wintel Support

GMT - 5 = EST

UK time is 1 hr behind CET

so from india, the diff will be currently 3.5 hrs

PST+3= EST

GBL-WTI-EM OPERATIONS

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

389659 - RFC for upgrade

395065 - RFC for upgrade

APAC APC

APC-GSS-SAMS-DBA

EXECUTE dbms_mview.REFRESH('ATMS_PROD_OWNER.M_S_GEOGRAPHY_HIERARCHY','C');

s
:1,$s/SELECT_ANY_PEGASUS/PEGASUS_INTERFACE/g

ALTER TABLESPACE SQLLIMS_INDEX DEFAULT STORAGE ( MAXEXTENTS UNLIMITED);

prstat -s cpu -a -n 8

ftp

bin

prompt

stty erase ^?

1,$ s /licorv3/LICRSG1V/g---to replace in vi

orapwd file=orapwregd3 password=pfizer#234 entries=4

orakill HCXWIN2 <spid>

Kalyan Anumakonda at +91 98490 37502

Pavan Mallapragada at +91 97044 17776

SELECT comp_name, status, substr(version,1,10) as version from dba_registry;

cat listener.ora| grep "PORT = "

For Groton _ Recall Tapes Q
==================

Assignment Region - AMER

Area - GRO

Assignment group - GRO-WTI-EUS INFRASTRUCTURE

Mail ID - WTI Groton Infrastructure Site Services

For Needham _ Recall Tapes Q

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

Assignment Region - AMER

Area - NDH

Assignment group NDH-WTI-EUS INFRASTRUCTURE

Mail ID - WTI Needham DC Operations; DL-CompuCom-Needham

Roles Privs ( Assigning Privs to Roles)

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

CREATE ROLE SELECT_ANY_PLBT NOT IDENTIFIED;

CREATE ROLE UPDATE_ANY_PLBT NOT IDENTIFIED;

set echo off

set feedback off
set verify off
set head off
spool SELECT_PLBT.sql
select 'Grant select on PLBT.'||object_name|| ' to SELECT_ANY_PLBT;' from
dba_objects where owner='PLBT' and object_type in
('TABLE','VIEW','SYNONYM','MATERIALIZED VIEW');
spool off

grant SELECT_ANY_PLBT to UPDATE_ANY_PLBT;

set echo off

set feedback off
set verify off
set head off
spool UPDATE_PLBT.sql
select 'Grant insert,update,delete on PLBT.'||object_name|| ' to UPDATE_ANY_PLBT;'
from dba_objects where owner='PLBT' and object_type in
('TABLE','VIEW','SYNONYM','MATERIALIZED VIEW')
union
select 'Grant execute on PLBT.'||object_name|| ' to UPDATE_ANY_PLBT;' from
dba_objects where owner='PLBT' and object_type in
('PROCEDURE','FUNCTION','PACKAGE');
REVOKE SELECT_ANY_PLBT FROM SYS;
REVOKE UPDATE_ANY_PLBT FROM SYS;

Getting MetaData for DBLINK

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

set long 1000;

SELECT dbms_metadata.get_ddl('DB_LINK','PEGASUS.AMER.PFIZER.COM','TWIRGDBP') from
dual;

dblink name dblink owner

Gathering schema stats

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

exec dbms_stats.gather_schema_stats(ownname => 'PFORB', estimate_percent => 20,

degree => 2 ,cascade => TRUE);

Gathering table stats

=======================
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent =>
15);

Parameter change steps:

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

take the backup of pfile and spfile

cretae pfile from spfile

take the backup of pfile and spfile

chane the parameters

shutdown the database

create spfile from pfile.

pfile is editable

TO see backup lists

===================
select * from ( select trim(recid), to_char(START_TIME,'DD-MON-YY HH24:MI')
st,to_char(COMPLETION_TIME,'DD-MON-YY HH24:MI') ct,trim(BACKUP_TYPE),SET_STAMP
from v$backup_set
where incremental_level=0 order by START_TIME desc )
where rownum <80;

select min(sequence#) Seq from v$log where status='INACTIVE' and archived='YES';

password backup
================
select 'alter user '||username||' identified by values '||chr(39)||password||
chr(39)||';' from dba_users where account_status='OPEN';

To know current running queries

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

select a.sid, a.serial#, a.username,

to_char(sysdate-last_call_et/24/60/60,'hh24:mi:ss') started,
trunc(last_call_et/60) || ' mins, ' || mod(last_call_et,60) ||' secs' dur,
(select sql_text from v$sqlarea where address = sql_address ) sql_text,
(select sharable_mem from v$sqlarea where address = sql_address ) sharable_mem,
(select sorts from v$sqlarea where address = sql_address ) sorts,
(select disk_reads from v$sqlarea where address = sql_address ) disk_reads,
(select buffer_gets from v$sqlarea where address = sql_address ) buffer_gets,
(select rows_processed from v$sqlarea where address = sql_address ) rows_processed,

b.event,b.seconds_in_wait,b.state
from v$session a,v$session_wait b where username is not null and last_call_et > 0
and status = 'ACTIVE' and a.sid=b.sid

select s.sid,s.serial#,p.spid,s.MACHINE,to_char(logon_time,'dd-mon-yyyy hh24:mi

s'),s.username,s.status from v$session s,v$process p where s.username='&username'
and s.paddr=p.addr order by logon_time;

to check long running query info

================================
select * from V$SESSION_LONGOPS where sid=869

DB refresh of REGD3.PFIZER.COM (Dev) from REGP1.PFIZER.COM (Prod) steps

=======================================================================
/app/oracle/admin/pv810_p/temp

take all passwords backup in target database

remove all data,ctl and redo files in the target database.

select file_name from dba_data_files union

select file_name from dba_temp_files union
select name from v$controlfile union
select member from v$logfile
order by 1;

start the target database in nomount state.

take the compressed backup(10g and above) or normal backup of source database.

rman target / catalog rman10gr6/bried8@rman10gp.groton.pfizer.com

cmdfile=rman_disk_bkp_regp1_18apr2011.rcv log=rman_disk_bkp_regp1_18apr2011
.log
mailx -s "Rman full disk backup of regp1 is completed"
durgadhar.ponnaganti@pfizer.com < rman_disk_bkp_regp1_18apr2011.log

.rcv file refer above

FTP all files location should be same in source and target(or create soft link)

from the backup log check the log sequence and add 1 give in the refresh script.

check the similar files and give in diff locations (mount points in target
database)

below refresh script give in source database.

select sum(bytes)/1024/1024/1024,substr(file_name,0,20) from dba_data_files group

by substr(file_name,0,20) ;
select 'set newname for datafile '||file_id ||' to '''||file_name||''';' from
dba_data_files;

rman target / auxiliary sys/pfizer_321@regd3.PFIZER.COM

cmdfile='rman_clone_regd3.rcv' log='rman_clone_regd3.log'
mailx -s "Rman clone of regd3 is completed" durgadhar.ponnaganti@pfizer.com <
rman_clone_regd3.log

run{
allocate auxiliary channel d1 type disk;
allocate auxiliary channel d2 type disk;
allocate auxiliary channel d3 type disk;
set until logseq 57648 thread 1;
set newname for datafile 1 to '/ora01/oradata/regd3/data/system01.dbf';
set newname for datafile 2 to '/ora04/oradata/regd3/data/api01.dbf';
set newname for datafile 3 to '/ora02/oradata/regd3/data/pfetms02.dbf';
set newname for datafile 4 to '/ora02/oradata/regd3/data/rightrack02.dbf';
set newname for datafile 5 to '/ora02/oradata/regd3/data/apix101.dbf';
set newname for datafile 6 to '/ora03/oradata/regd3/data/blueflame.dbf';
set newname for tempfile 1 to '/ora01/oradata/regd3/data/regd3_temp01_01.dbf';
duplicate target database to regd3
logfile
group 1 ('/ora02/oradata/regd3/redo/regd3_g1m1.log',
'/ora03/oradata/regd3/redo/regd3_g1m2.log') size 100M,
group 2 ('/ora03/oradata/regd3/redo/regd3_g2m1.log',
'/ora04/oradata/regd3/redo/regd3_g2m2.log') size 100M,
group 3 ('/ora04/oradata/regd3/redo/regd3_g3m1.log',
'/ora02/oradata/regd3/redo/regd3_g3m2.log') size 100M;
}

revert the target passwords

restore steps
-------------

take all passwords backup in target database

ideintify the tapes to restoe point

raise a ticket to dp team that bring tapes online.

check the last backup was success for target database in DBA matrix.

drop all data,ctl and redo files in the target database.

once tapes are online start the refresh to restore ponit from tapes

here created a duplicate database started the restore

cretae a pwd file in target database and connect from source

rman target / catalog rman9ir2/bried8@rman9ip.groton.pfizer.com auxiliary

sys/abc123@pv810_d1.groton.pfizer.com cmdfile=rman_clone_pv810d1_fro
m_pv810_p_21apr2011.rcv log=rman_clone_pv810d1_from_pv810_p_21apr2011.log
mailx -s "rman clone of pv810_D1 is completed - Please check the log "
durgadhar.ponnaganti@pfizer.com < rman_clone_pv810d1_from_pv810_p_21apr
2011.log

run {
allocate auxiliary channel t1 type 'sbt_tape';
allocate auxiliary channel t2 type 'sbt_tape';
allocate auxiliary channel t3 type 'sbt_tape';
allocate auxiliary channel t4 type 'sbt_tape';
send
'NSR_ENV=(NSR_SERVER=amrgros035.pfizer.com,NSR_CLIENT=GSUN830.PFIZER.COM)';
set until logseq 94981 thread 1;
set newname for datafile 1 to
'/ora16/oradata/pv810_d1/data/pv810_d1_system_01.dbf';
set newname for datafile 2 to '/ora16/oradata/pv810_d1/data/pv810_d1_amapp_01.dbf';
set newname for datafile 3 to
'/ora16/oradata/pv810_d1/data/pv810_d1_amlarge_01.dbf';

here log sequence we get from min sequence from tape to restoe ponint

NSR_SERVER = tape server

NSR_CLIENT = source database server

once restore is completed from tapes do the refresh from source to target as above.

Active cloning
==============
amrndhl336.pfizer.com-fstus $ more clone_FSTUS_from_fstvs.rcv
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
allocate auxiliary channel c5 type disk;
allocate auxiliary channel c6 type disk;
allocate auxiliary channel c7 type disk;
duplicate target database to fstus from active database;
}

amrndhl336.pfizer.com-fstus $ more clone_FSTUS_from_fstvs.sh

rman target sys/pfizer_123@fstvs.pfizer.com auxiliary
sys/pfizer_123@fstus.pfizer.com cmdfile=clone_FSTUS_from_fstvs.rcv
log=clone_FSTUS_from_
fstvs.log
mailx -s "Clone of FSTUS from FSTVS completed - Please check the log"
sivaramakishore.alapat@pfizer.com < clone_FSTUS_from_fstvs.log

skiping of tablespces and cloning

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

run {
allocate auxiliary channel t1 type 'sbt_tape';
allocate auxiliary channel t2 type 'sbt_tape';
allocate auxiliary channel t3 type 'sbt_tape';
allocate auxiliary channel t4 type 'sbt_tape';
send
'NSR_ENV=(NSR_SERVER=amrndhs256.pfizer.com,NSR_CLIENT=amrndhl227.pfizer.com)';
set until time "to_date('02-APR-2011 13:00:00','DD-MON-YYYY HH24:MI:SS')";
set newname for datafile 1 to
'/ora01/oradata/pgpprodt/data/pgpprodt_system_01.dbf';
set newname for datafile 2 to
'/ora01/oradata/pgpprodt/data/pgpprodt_sysaux_01.dbf';
set newname for datafile 3 to
'/ora01/oradata/pgpprodt/data/pgpprodt_sysaux_02.dbf';
set newname for datafile 4 to
'/ora01/oradata/pgpprodt/data/pgpprodt_undotbs1_01.dbf';
set newname for datafile 5 to
'/ora01/oradata/pgpprodt/data/pgpprodt_undotbs1_02.dbf';
set newname for datafile 6 to '/ora01/oradata/pgpprodt/data/pgpprodt_users_01.dbf';
set newname for datafile 7 to '/ora01/oradata/pgpprodt/data/pgpprodt_tools_01.dbf';
set newname for datafile 8 to
'/ora01/oradata/pgpprodt/data/pgpprodt_perfstats_d1_01.dbf';
set newname for datafile 9 to
'/ora01/oradata/pgpprodt/data/pgpprodt_perfstats_i1_01.dbf';
set newname for datafile 10 to
'/ora01/oradata/pgpprodt/data/pgpprodt_sys_audit_d1_01.dbf';
set newname for datafile 25 to
'/ora04/oradata/pgpprodt/data/pgpprodt_uspmgt_data_01.dbf';
set newname for datafile 26 to
'/ora04/oradata/pgpprodt/data/pgpprodt_uspmgt_data_02.dbf';
set newname for datafile 27 to
'/ora04/oradata/pgpprodt/data/pgpprodt_uspmgt_index_02.dbf';
set newname for datafile 32 to '/ora01/oradata/pgpprodt/data/pgpprodt_xdb01.dbf';
set newname for tempfile 1 to '/ora01/oradata/pgpprodt/data/pgpprod_temp_01.dbf';
set newname for tempfile 2 to '/ora01/oradata/pgpprodt/data/pgpprod_temp_02.dbf';
duplicate target database to pgpprodt skip tablespace
PCMGT_DATA,USPPNL_DATA,PCMGT_INDX,PCMGT_A_INDEX_01,PCPNL_INDX,PCPNL_A_INDX,PCPNL_A_
DATA,USPMED_DATA,USPMED_INDEX,USPPNL_INDEX,PCMGT_A_DATA_01,PCPNL_DATA
logfile
group 1 ('/ora01/oradata/pgpprodt/redo/pgpprodt_g1m1.rdo',
'/ora01/oradata/pgpprodt/redo/pgpprodt_g1m2.rdo') size 100M,
group 2 ('/ora01/oradata/pgpprodt/redo/pgpprodt_g2m1.rdo',
 '/ora01/oradata/pgpprodt/redo/pgpprodt_g2m2.rdo') size 100M,
group 3 ('/ora01/oradata/pgpprodt/redo/pgpprodt_g3m1.rdo',
'/ora01/oradata/pgpprodt/redo/pgpprodt_g3m2.rdo') size 100M;
}

rman target / auxiliary sys/pfizer_123@pgpprodt.pfizer.com

cmdfile='rman_restore_pgpprodt_03Apr2011.rcv'
log='rman_restore_pgpprodt_03Apr2011.log'
mailx -s "Restore of pgpprodt is completed" dl-sams-operations-oracle@pfizer.com <
rman_restore_pgpprodt_03Apr2011.log

to identify the tapes online or not

===================================
mminfo -s < tape server > -q "volume=<tapeid>" -r "location"

gsun830-pv810_p $ mminfo -s amrgros035 -q "volume=GD0731" -r "location"

amrgros035-jba (online)

db in restricted mode
======================
alter system enable restricted session;

SQL> select INSTANCE_NAME,logins from v$instance;

sftp syntax
============
sftp ponnad/<pwd>@<server_name>

take metadata of dblink:

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

select 'select dbms_metadata.get_ddl(''DB_LINK'','''||DB_LINK||''','''||OWNER||''')

from dual;' from dba_db_links;

set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL('DB_LINK','GDMSD2.WORLD') FROM dual;

select dbms_metadata.get_ddl('DB_LINK','GDMSD2.WORLD','GDMS_OWNER') from dual;

SELECT dbms_metadata.get_ddl('TABLESPACE','SYS_AUDIT_I1') from dual;

DBstats
=======
sqlplus "/as sysdba" <<END_SCRIPT
set echo on
set feedback on
set time on
spool stats_ECMST28_30Mar2011.log
select * from global_name;
exec dbms_stats.gather_schema_stats(ownname => 'SIEBEL', estimate_percent => 20,
degree => 2 ,cascade => TRUE);
exec dbms_stats.gather_schema_stats(ownname => 'PCORESTG', estimate_percent => 20,
degree => 2 ,cascade => TRUE);
exec dbms_stats.gather_schema_stats(ownname => 'EUCAN_DW_LOAD', estimate_percent =>
20, degree => 2 ,cascade => TRUE);
spool off
END_SCRIPT

creation of DB link
===================

Source Schema : GDMS_OWNER

Source Database: PGMPDMS

Target Schema :GDMS_OWNER

Target Database :GDMST2

create dblink in source database.

SQL> grant create database link to gdms_owner;

Grant succeeded.

SQL> conn GDMS_OWNER

Enter password:
Connected.
SQL> CREATE DATABASE LINK GDMST2.world
CONNECT TO PDM_GDMS_RO
IDENTIFIED BY pfizer#735
USING 'GDMST2.groton.pfizer.com' ; 2 3 4

Database link created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
amrndhp005a-pgmpdms $ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 29 01:04:57 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> revoke create database link from gdms_owner;

Revoke succeeded.

SQL> sho user

USER is "IAODS"

SQL> select * from global_name;

GLOBAL_NAME

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

ANRT.PFIZER.COM

SQL> select * from dual@WYE_MEDCLMS.PFIZER.COM;

sending log file to mail from server

====================================
uuencode fdutf8d1_ora_325690.txt impdp_tables_CTRM_KT_MANDAD01_11MAR11.log| mailx -
s "log" durgadhar.ponnaganti@pfizer.com

granting unlimited to user tables space

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

select distinct tablespace_name from dba_segments where owner like 'MANDAD01';

desc

select DEFAULT_TABLESPACE from dba_users where USERNAME like 'MANDAD01';

alter user MANDAD01 quota unlimited on users;

to switch to oracle user

========================
/usr/local/bin/pbrun -u oracle bash

/usr/local/bin/sudo su - oracle bash

cd /usr/local/bin
./sudo -i -u oracle(password:NTID password)

no of active sessions
========================
The connection details are as below.

SQL> select name,open_mode from v$database;

SQL> select osuser,count(*) from v$session where status='ACTIVE' group by osuser;

SQL> select name,open_mode from v$database;

SQL> select count(*) from v$session;

SQL> select count(*) from v$session where status like 'ACTIVE';

SQL> select count(*) ,username,status from v$session group by username,status;

to check if the tapes has spread across other tapes
==================================================

mminfo -s gsun756 -c gsun830 -q "volume=amrgros035" -v -ot | grep pv810_pgsun830-

pv810_p

mminfo -s amrndhs256.pfizer.com -q "volume=CX1536" -r "location

if nooutput no tapes are online

if there is a signal like 'like jb_ndh-k' then tape is online.

retirement of 10g
=================
startup mount exclusive restrict;
drop database;
password backup
===============
select 'alter user '||username||' identified by values '||chr(39)||password||
chr(39)||';' from dba_users;

to know object count after refresh

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

select Count(object_name), object_type, status from dba_objects where owner in

('QTA_ATT_OWNER','REPOSITORY_OWNER') group by object_type, status order by
object_type, status;

creating Job
=============

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'PEGASUS.PKG_PAYMENTS.AUTOMATICPAYMENT;'
,next_date => to_date('26/06/2011 00:18:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'NEXT_DAY(TRUNC(SYSDATE), ''SUNDAY'') + 00.30/24'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

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

agent installation

amrndhl418.pfizer.com- $ pwd
/share/orashare/Server_Software/oracle/linux/x86_64/base_agent_10.2.0.5.0_64/
agent/linux_x64/agent
export DISPLAY=170.116.212.235:1.0

export ORACLE_HOME=/app/oracle/product/agent10g
export PATH=$ORACLE_HOME/bin:/opt/bin:/bin:/usr/bin:/usr/ccs/bin:.:/GNU/bin/make:/
usr/bin:/usr/local/bin

amrndhl418.pfizer.com- $ pwd
/share/orashare/Server_Software/oracle/linux/x86_64/base_agent_10.2.0.5.0_64/
agent/linux_x64/agent
amrndhl418.pfizer.com- $ pwd
/share/orashare/Server_Software/oracle/linux/x86_64/base_agent_10.2.0.5.0_64/
agent/linux_x64/agent
amrndhl418.pfizer.com- $ export DISPLAY=
amrndhl418.pfizer.com- $ export DISPLAY=170.116.212.229:1.0
amrndhl418.pfizer.com- $ xterm

here run the ./runinstaller

it opens one window
set the path
/app/oracle/product
give username=pgoem.pfizer.com
password:pgoem.pfizer.com/oemp1agent

Kill session
=============
If the session is first killed with ALTER SYSTEM KILL SESSION and the corresponding
users process does not terminate normally,
then the killed session will continue to appear in V$SESSION during the life of
the instance.
The reason for this is that the session state object cannot be cleaned out until
the associated user process clears the pointer to it.
Since the session abnormally aborted, this will not happen.

If they persistently stay for a long long time, then probably they will stay more
until you restart your instance.
When they are abnormally terminated, they continue to stay till you restart your
instance.

So better approach to kill the session is by its SPID <Server Process ID>

you can find out process id by using below query.

select spid from v$process where addr in (select paddr from v$session where
sid=&a);
kill -9 SPID

alter database recover managed standby database disconnect from session;

BASSD MTES ORACLE CM LEAD

To check DB link working fine or not

====================================
select * from dual@dblink name;

If we use below command in 9i database we will get below error.

SQL> drop database;
drop database
*
ERROR at line 1:
ORA-02026: missing LINK keyword

Incomplete recovery - it opens the database with reset logs (that it gives the seq
nO to logs from start)

complete recovery - no reset logs

To check for OEM

================
sids agent

emctl config agent listtargets

Moving of database
==================
EXP of all schemas in the database binfd1 on the server emasans002 has been taken
in the location /oramisc/EMA14533611i.
Proceed with the creation of the database binfd1 on the server edcemadbo04, FTP the
source schemas dump to the target server edcemadbo04,
import the schemas, take all the privileges and user scripts from the source
database and execute in target and do all
post database creation steps and drop the database binfd1 on the server emasans002
once all done.

Error -this error due to check mount point filled 100%.

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

More than 20 informatica session got failed because of following Database Driver
Error:-

Database driver error...

Function Name : Connect
Database Error: Failed to connect to database using user [SADMIN] and connection
string [ANAP1.SANDWICH.PFIZER.COM].]
TRANSF_1_1_1> Sun Aug 28 09:07:55 2011
TRANSF_1_1_1> CMN_1076 ERROR creating database connection.

Installation
-------------

amrsoml030.pfizer.com- $ pwd
/share/orashare/Server_Software/oracle/linux/x86_64/base_11.2.0.2.0_64/database
export DISPLAY=170.116.212.228:1.0
xterm
give below commands
export ORA_NLS10=$ORACLE_HOME/ocommon/nls/data
export ORACLE_HOME=/app/oracle/product/11.2.0.2_64
export ORACLE_BASE=/app/oracle/
export ORATAB=/etc/oratab
export PATH=$ORACLE_HOME/bin:/opt/bin:/bin:/usr/bin:/usr/ccs/bin:.:/GNU/bin/make:/
usr/bin:/usr/local/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/usr/openwin/
lib:/usr/dt/lib:/usr/ccs/lib
export TMPDIR=/tmp
export TMP=/tmp
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
/app/oracle/oraInventory

./runinstaller

GBL12473475i(run root.sh cretae a ticket to unix team)

conn '/as

to check the agent

==================
ocd32 $ export ORACLE_HOME=/app/oracle/product/agent10g (agent path)
ocd32 $ export PATH=$PATH:$ORACLE_HOME/bin

. ./.profile

Kernal
=====
amrndhl629.pfizer.com- $ /sbin/sysctl -a |grep shmmax

oracle Text
===========
to check text has been installed or not

select COMP_NAME,VERSION,STATUS from dba_registry;

to install text run @?/ctx/admin/catctx.sql present in create_p4_context.sql in

location /app/oracle/admin/prmarptt/create

Privs to role
============
SQL> desc role_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)

BLAST_OWNER_DEF_M

Privs
=====
select 'Grant select on ADMINDB_OWNER.'||object_name|| ' to SELECT_ANY_ADMINDB;'
from dba_objects where owner='ADMINDB_OWNER' and object_type in
('TABLE','VIEW','SEQUENCE','MATERIALIZED VIEW');

select 'Grant insert,update,delete on ADMINDB_OWNER.'||object_name|| ' to

UPDATE_ANY_ADMINDB;' from dba_objects where
owner='ADMINDB_OWNER' and object_type in ('TABLE','VIEW','SEQUENCE','MATERIALIZED
VIEW')
UNION
select 'Grant execute on ADMINDB_OWNER.'||object_name|| ' to UPDATE_ANY_ADMINDB;'
from dba_objects where owner='ADMINDB_OWNER' and object_type in
('PROCEDURE','FUNCTION','PACKAGE');

creation of softlink:
====================

ln -s path (/oramisc3/GBL14871331i/LICORV6/open) <Softlinkname>

create synonym spool

======================
select 'create synonym BLAST_USER.'||table_name||' for BLAST_OWNER.'||
table_name||';' synonyms from dba_tables where owner='BLAST_OWNER';
select 'create synonym BLAST_USER.'||VIEW_name||' for BLAST_OWNER.'||VIEW_name||';'
synonyms from dba_VIEWS where owner='BLAST_OWNER';

connect to 8i database
======================
C:set ORACLE_SID=STRKPROD

C:set LOCAL= STRKPROD

C:svrmgrl

C:connect internal
In windows to get 32bit or 64bit
==================================
msinfo32 and to check services services.mcc

schema restoration
====================
� Restore the PLATEAU_OWNER specific tablespaces to a temporary database.
You can use 18-OCT-2011 8 AM EDT as the restore point. Use the skip tablespace
option and restore only the PLATEAU_OWNER specific tablespaces

� From the restored temporary database, take an export of the PLATEAU_OWNER

schema and import it into the PLATEAU_OWNER schema in ENID1 database.

To check whether software is installed or not

============================================
go to home path
ls -ltr
sqlplus /

shared dump path in windows.

===========================
ndhnas004.pfizer.comORASHARE

ndhnas004.pfizer.comORASHAREServer_Softwareoraclewindows

to check agent

set local=agent
emctl status agent

====================
gsun863
amrndhs240

to uninstall oracle software

===========================
go to folder cd $ORACLE_HOME/oui/bin remove then.
alter table <<RDS_TABLENAME>> disable row movement.

11g alert log location

======================
/ora1padm/app/oracle/admin/diag/rdbms/h8dwprod/H8DWPROD/trace

grant permission on dir

=======================
GRANT permission ON DIRECTORY alias TO {user | role | PUBLIC};
permission is one of the following:

READ for read-only access

WRITE for write-only access
ALL for read and write access

CREATE DIRECTORY olapdemo as '/users/oracle/OraHome1/demo';

GRANT READ ON DIRECTORY olapdemo TO PUBLIC;

example;

SQL> CREATE OR REPLACE DIRECTORY extract_rpu as

'/app_share/itst1_int/regulatory/medicaid/RPU/data/out/extract_rpu';

Directory created.

SQL> GRANT ALL ON DIRECTORY extract_rpu to MEDICAID;

Grant succeeded.

-- first increase AWR retention period (in minutes)

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

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION=> 43200);

-- then you can increase window size (in days)

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE ( WINDOW_SIZE => 30);

-- info about the window

select BASELINE_NAME, BASELINE_TYPE, START_SNAP_TIME, END_SNAP_TIME

from DBA_HIST_BASELINE;

SQL> select dbid from DBA_HIST_BASELINE;

DBID
----------
2405529110

SQL> select DBID,retention FROM dba_hist_wr_control;

DBID RETENTION
----------
---------------------------------------------------------------------------
3049683116 +00008 00:00:00.0
1265822102 +00008 00:00:00.0
2405529110 +00008 00:00:00.0
1600538323 +00038 00:00:00.0
SQL> select MOVING_WINDOW_SIZE from DBA_HIST_BASELINE;

MOVING_WINDOW_SIZE
------------------
8.

SQL> select BASELINE_NAME,BASELINE_TYPE,START_SNAP_TIME,END_SNAP_TIME from

DBA_HIST_BASELINE;

BASELINE_NAME BASELINE_TYPE START_SNAP_TIME

END_SNAP_TIME
-------------------- -------------------- ----------------------------------------
----------------------------------------
SYSTEM_MOVING_WINDOW MOVING_WINDOW 29-DEC-11 06.00.17.353 AM
06-JAN-12 05.00.14.095 AM

FGA has been disabled in database REGS1 for all objects except one
=======================================================================
select
'begin dbms_fga.disable_policy(object_schema=>'''||
OBJECT_SCHEMA||''',object_name=>'''||object_name||''',policy_name=>'''||
policy_name||'''); end; /'
from DBA_AUDIT_POLICIES order by POLICY_NAME desc;

and got results like

begin
dbms_fga.disable_policy(object_schema=>'REG_API_6',object_name=>'VISIT_REPORT_TYPE'
,policy_name=>'VISIT_REPORT_TYPE'); end;
/

renaming of database;
=======================

http://www.dba-oracle.com/t_rename_database_oracle_sid.htm

REMOTE_LOGIN_PASSWORDFILE
==========================
Values:

NONE

Oracle ignores any password file. Therefore, privileged users must be authenticated
by the operating system.

SHARED

More than one database can use a password file. However, the only user recognized
by the password file is SYS.

EXCLUSIVE

The password file can be used by only one database and the password file can
contain names other than SYS
========================================================
CONSISTENT = Y

and we said...
it makes all of the queries run by exp be "as of the same point in time--
consistent with
regards to eachother"

imagine if you started an export at 9am of the EMP and DEPT tables.

EMP started exporting at 9am and DEPT at 9:15am.

Now, the EMP data would be "as of 9am", but the DEPT data would be as of 9:15am.
What if
you import that data now -- will it work? maybe, maybe not -- perhaps at 9:10am,
someone
fired the last employee in department 50 and deleted department 50. Your EMP
export
would have them in this deptno, your DEPT export would not have this deptno. The
import
would fail.

If you use consistent=y, Oracle will export all of the data "as of 9am", so
deptno=50
will be included in all tables and the import will succeed.
================================================
977512.1 is the metalink ID for timejone

Cursor_sharing
==============
CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their
texts match exactly. This is the default behavior. Using this setting, similar
statements cannot shared; only textually exact statements can be shared.
Setting CURSOR_SHARING to either SIMILAR or FORCE allows similar statements to
share SQL. The difference between SIMILAR and FORCE is that SIMILAR forces similar
statements to share the SQL area without deteriorating execution plans. Setting
CURSOR_SHARING to FORCE forces similar statements to share the executable SQL area,
potentially deteriorating execution plans.
The CURSOR_SHARING initialization parameter can solve some performance problems. It
has the following values: FORCE, SIMILAR, and EXACT (default). Using this parameter
provides benefit to existing applications that have many similar SQL statements.
The optimal solution is to write sharable SQL, rather than rely on the
CURSOR_SHARING parameter. This is because although CURSOR_SHARING does
significantly reduce the amount of resources used by eliminating hard parses, it
requires some extra work as a part of the soft parse to find a similar statement in
the shared pool.
this parameter set as force to achieve the best performance to avoid different
plans for each similar query( difference in plans in terms of bind variables )
===============
Alter system command is used for only dynamic parameters when db is running with
spfile.
alter system set=() scope =spfile (only for static parameters here need the bounce
of database to reflect static values.
scope=both is used for dydnamic parameter when db is running with spfile.

==========================================
ORA-00031: session marked for kill
Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be
killed immediately (because it is rolling back or blocked on a network operation),
but it has been marked for kill. This means it will be killed as soon as possible
after its current uninterruptable operation is done.

Action: No action is required for the session to be killed, but further executions
of the ALTER SYSTEM KILL SESSION command on this session may cause the session to
be killed sooner.

================================
to check no of instances in RAC

SQL> select instance_name from gv$instance;

INSTANCE_NAME
----------------
PACECSP11
PACECSP14
PACECSP13
PACECSP12

commands to kill any session in the RAC environment which is a bit different.

select inst_id from gv$session where sid='679';

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
ALTER SYSTEM KILL SESSION '679,52039,@1';or
SQL>alter system kill session '9,171,@1' immediate;

performance
===========
Locks: No other locks on the objects of the schema
Blocking Session: NO
Dependent tables were last_analysed on 24-Dec-11
Data, Index and UNDO tablespaces have free space.
db_file_multiblock_read_count integer 16

tnsnames
========

tnsname.ora=sqlnet.ora= it is connection string at client server side-to avoide no

of entries in tnsnames.ora we will maintain a one repository i.e OID entry it is a
centrilized and

through this we can connect to no of databases...after client installation is done

for any server.

we can maintain either tnsname.ora or sqlnet.ora

listener.ora= it is connection string at database side

to check the remote connection from the client side it will establish a
connection to database by awaking tnsnames.ora or sqlnet and ldap it will connect
to database
only when listener entry is added to that database and listener is up and running
fine for that database.

exclude tables in expdp

========================
expdp <other_parameters> SCHEMAS=scott EXCLUDE=TABLE:�IN (�EMP�,'DEPT�)�;
schemas=PRAXIS2S
directory=exp_dir
dumpfile=expdp_PRAXIS2S_PRAXIS2S_15jul09.dmp
logfile=expdp_PRAXIS2S_PRAXIS2S_15jul09.log
EXCLUDE=TABLE:"IN('PSP','FINISHED_PRODUCT','APPROVED_SELLING_PRICE_LIST')"

session info
==============
select count(*),schemaname,status from v$session where program not like '%backgroud
%' group by status,schemaname order by 1,2,3;

parent - child relation(constraints)

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

set linesize 100

col constraint_name format a30
col table_name format a30
col owner format a30
select constraint_name , table_name, owner
from dba_constraints
where R_CONSTRAINT_NAME in (select constraint_name
from dba_constraints
where table_name ='ATTACHMENT'
and owner = 'GCSW_PRD_OWNER' );

alter table <Child Table> disable constraint <Foreign Key> ;

alter table GIFODS.ODS_CORPORATIONS RENAME TO ODS_CORPORATIONS_bkp;

insert into GIFODS.ods_corporations

(select * from GIFODS.ODS_CORPORATIONS_backup where corp_id not in (select corp_id
from GIFODS.ods_corporations));

GCSW_PRD_OWNER.BDFER_ATTACH_SECCHECK

STUDY link
==========
http://arjudba.blogspot.com/2008_04_13_archive.html

Flashback
========
guarntee restore point

if we create restore point say t1 at 11:55 we can restore the database to this
restore point.

this guarntee point depends on size of flashback area not retention period ..that
means even this retenteion period is less we have
alreday saved data to particular restore point so we can get back the data using
restore point not depends on db retention period.

check no of CPUS
=================
psrinfo -v -gives no of cpus that server is allocated from datacenter
cpu utilization:TOP in Linux
prstat in linux sun solaries
gsunk865a-ocp02 $ prstat -s cpu -n 10
gsunk865a-ocp02 $ prstat -s cpu -a -n 8
gsunk865a-ocp02 $ prstat -s size -n 5

blackout
=========
emctl start blackout blackout_2 nlsun79 -host level

emctl start blackout amrndhp0002p_nodelevel -nodelevel

Last committed transatcon on a table

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

SQL> select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from

RUPS.TPA_DISPUTED_NDC;
SQL> select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from
RUPS.TPA_DISPUTED_NDC;

MAX(ORA_ROWSCN)
---------------
SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))
---------------------------------------------------------------------------
1.0840E+13
06-JAN-12 07.09.42.000000000 AM

06-JAN-12 07.09.42.000000000 AM was the last time a dml was issued on the table.

To know what was the DML we may need to mine archive logs.

restoting control file from tape

=================================
amrndhs251-cmssea $ more rman_restore_cmssea_17JAN_controlfile.rcv
run
{
allocate channel t1 type 'sbt_tape';
allocate channel t2 type 'sbt_tape';
send 'NSR_ENV=(NSR_SERVER=amrnwls006.pfizer.com,NSR_CLIENT=amrndhs251.pfizer.com)';
set until time "to_date('17-JAN-2012 12:00:00','DD-MON-YYYY HH24:MI:SS')";
restore controlfile;
sql 'alter database mount';
}

copy dump to if ftp and sftp wont works

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

/net/gsun863/orashare/

or

/share/orashare/temp

rman target/ sys/pwd@serveice name catalog 2 - for normal refresh

rman target sys/pwd@service auxulary sys/pwd@service for active cloning

amrndhs263-PGSDWD_T $ more rman_restore_to_PGSDWD_T_19Jan2012.rcv_old
run {
allocate auxiliary channel t1 type 'SBT_TAPE' PARMS
'ENV=(NSR_SERVER=amrndhs009.pfizer.com,NSR_CLIENT=amrndhs263)';
allocate auxiliary channel t2 type 'SBT_TAPE' PARMS
'ENV=(NSR_SERVER=amrndhs009.pfizer.com,NSR_CLIENT=amrndhs263)';
allocate auxiliary channel t3 type 'SBT_TAPE' PARMS
'ENV=(NSR_SERVER=amrndhs009.pfizer.com,NSR_CLIENT=amrndhs263)';
allocate auxiliary channel t4 type 'SBT_TAPE' PARMS
'ENV=(NSR_SERVER=amrndhs009.pfizer.com,NSR_CLIENT=amrndhs263)';

restore points
==============
This example shows how to create a normal restore point:

SQL> CREATE RESTORE POINT <name or timestamp> ;

Restore point created

create a guaranteed restore point:

SQL> CREATE RESTORE POINT <name or timestamp> GUARANTEE FLASHBACK DATABASE;

Restore point created.

Stats coleection job scheduled

==============================
SQL> SELECT OWNER,JOB_NAME,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE from
dba_scheduler_jobs where job_name='STATS_COLLECTION_JOB';

OWNER JOB_NAME LAST_START_DATE LAST_RUN_DURATION

NEXT_RUN_DATE
------ ------------------------------ --------------------
------------------------------ ------------------------------
SYS STATS_COLLECTION_JOB 24-JAN-12 12.00.00.4 +000000000
00:10:13.824724 25-JAN-12 12.00.00.000000 AM +
23609 AM +08:00
08:00
SQL> SELECT DISTINCT TRUNC(LAST_ANALYZED) FROM DBA_TABLES WHERE
OWNER='OPS$LICRAU6P';

TRUNC(LAST_ANALYZE
------------------
24-JAN-12

sys issue
=========

Please be informed that on server ASPNAGL001 I am not able to login sa sysdba as I

am facing the below error:

aspnagl001.pfizer.com-ampsng2p $ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 26 19:25:40 2012

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

@ERROR:
ORA-01031: insufficient privileges

I find that the /app/oracle/product/11.2.0.2_64/rdbms/lib/config.c file has below

details:
#define SS_DBA_GRP "samsdba"
#define SS_OPER_GRP ""
#define SS_ASM_GRP ""
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

where ideally it should have been

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "dba"
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

To check character set of database

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

SQL> SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8

RMAN> list backup summary - will show all backups in the database.

consistent
=============

default N

consistent=Y - once export started it won't export updated data of that table at
that moment while exporting schema.may chance to get UNDO error.

Consistent=N - Once export started

table1 - 5GB
table2- 10GB

if 5GB table export has started...

updated table table1 and table2

then it will export orginal table1(not updated data) because export started for
that table and export updated data for table2.

Create function based index

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

select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME from dba_indexes where

index_name='ARIBAINDEX_ITEM56';
Create index aribaindex_item56 on ariba.POLINEITEMTAB(LOWER(poli_pfe_partnumber))
tablespace ARIBA_PERF_IDX

SQL loader:
==========

CTL file:

load data
infile 'C:Documents and SettingsponnadDesktopdeploymydata1.csv'
append
into table tempemp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )

CSV file:

10001,"Scott Tiger",1000,40

sqlldr gems_dev_owner@gemspli.pfizer.com/renwovedsmeg# control=aspxovwhrp71bsSAP

Integration Projectloader2.ctl

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

exp userid=" '/ as sysdba' " file=exp_1table_MBR_ICMP_6Oct2011.dmp

log=exp_1table_MBR_ICMP_6Oct2011.log tables=MBR.Load_Dea_Number compress=N
statistics=none buffer=20480000

sqlldr MBR/greentree_123@icmp.pfizer.com control=dea_load.ctl data=MA11010.txt

log=dea_load.log bad=dea_log.bad
=============================================

exp userid=" '/ as sysdba' " file=exp_1table_APR_MART_ENIP132_04Feb2011.dmp

log=exp_1table_APR_MART_ENIP132_04Feb2011.log tables=APR_MART.LM_PVA compress=N
statistics=none buffer=20480000

truncate table LM_PVA

Sqlldr apr_mart/prodmart08@ENIP132.pfizer.com control=LM_PVA

select * from APR_MART.LM_PVA where LIC_PARTY_ID is null and LIC_PARTY_TYPE =

'Out';

select * from LM_PVA where PRODUCT_FAMILY_ID is null;

INDEX file(for LOB objects) while doing diff versions import

==========
imp userid=" '/ as sysdba' " fromuser=MEDICAID touser=MEDICAID
file=exp_MEDICAID_MMEDIP_07Oct2011.dmp
log=indexfile_MEDICAID_PACEMDT1_07Oct2011.log buffer=204800000
indexfile=MEDICAID_PACEMDT1_07Oct2011.sql