scp -rp stratadmin_pffin1_25April11.dmp
oraffin1@fsdev.us.estee.com:/db/ffin1/backu
scp -rp
ABP_WIP_PFIN3_22MAY2012.dmp oraqfin3@oradev3.us.estee.com:/db/qfin3/export
a) script for
disable constraints:
set heading off
set linesize 100
spool drop_constraints.sql
select 'ALTER TABLE
' || OWNER || '.' || TABLE_NAME || ' DROP CONSTRAINT ' || constraint_name|| ';'
from dba_constraints where owner in ('STRATADMIN') and constraint_type='R';
select 'ALTER TABLE
' || OWNER || '.' || TABLE_NAME || ' DROP CONSTRAINT ' || constraint_name|| ';'
from dba_constraints where owner in ('ABP','ABP_WIP') and constraint_type='R';
b) script for drop
objects:
spool
drop_objects.sql
select 'DROP ' ||
OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ';' from dba_objects where
owner in ('STRATADMIN') and object_type!='INDEX' order by 1;
select 'DROP ' ||
OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ';' from dba_objects where
owner in ('ABP','ABP_WIP') and object_type!='INDEX' order by 1;
scp -rp smi_prodsupphase2group3db.dmp
oraalmd1@infa5qb.am.elcompanies.net:/db/hpalmd1/export
1. Restore the schema:
a. default_smi_phase2_group3_db
DEFAULT_SMI_PHASE2_GROUP3_DB
DEFAULT_SMI_PHASE2_GROUP3_DB
2. Source SID: INFA4PB
3. Target SID: INFA5QB
scp -rp
smi_prodsupphase2group3db.dmp raalmd1@infa5qb.am.elcompanies.net:/db/hpalmd1/export
nohup impdp
parfile=imp.par &
cat > imp.par
userid=system/d01infa5qb
dumpfile=smi_prodsupphase2group3db.dmp
logfile=default_smi_phase2_group3_db_imp.log
SCHEMAS=DEFAULT_SMI_PHASE2_GROUP3_DB
REMAP_SCHEMA=DEFAULT_SMI_PHASE2_GROUP3_DB:DEFAULT_SMI_PHASE2_GROUP3_DB
directory=DATA_PUMP_DIR
SQL> select
object_type,count(*) from dba_objects where owner='DEFAULT_SMI_PHASE2_GROUP3_DB'
group by object_type;
nohup exp
system/p01rp1 file=ABP_WIP_PFIN3_22MAY2012.dmp
log=ABP_WIP_PFIN3_22MAY2012_imp.log owner=ABP,ABP_WIP buffer=52428800
consistent=y &
imp system/p396081
FILE=/db/ffin1/backup/startadmin_pfin1_25April.dmp FROMUSER=stratadmin
TOUSER=stratadmin TABLES=TBLLFS rows=y ignore=y
nohup imp
system/q03if1
file=ABP_WIP_PFIN3_22MAY2012.dmp log=ABP_WIP_PFIN3_22MAY2012_imp.log
full=y buffer=10485760 commit=y ignore=y &
imp
system/p01rp1
file=pfin3_abp_exp_17052012.dmp INDEXFILE=pfin3abp.sql rows=n FULL=y
Please copy all contents of the DRM schema
in the CPMTEST database to the DRM-OLD schema in the CPMTEST database,
replacing the current contents of the DRM-OLD schema
After that is completed, please copy all
contents of the DRM schema in the CPMPROD database to the DRM schema in the
CPMTEST database, replacing the current contents of the DRM schema
Task1
--------
1) Export of DRM
Schema from CPMTEST
2) Import into
DRMOLD schema in CPMTEST
Task2
----
1) Export of DRM
Schema from CPMPROD
2) Import into
DRM schema in CPMTEST
>expdp
system/Access09$
dumpfile=DRM_CPMTEST_DP.dmp logfile=DRM_CPMTEST_expdp.log
directory=DATA_PUMP_DIR SCHEMAS=DRM
>impdp
system/Access09$ DUMPFILE=DRM_CPMTEST_DP.dmp
LOGFILE=DRM_CPMTEST_DP_impdp.log DIRECTORY=DATA_PUMP_DIR
REMAP_SCHEMA=DRM:DRMOLD REMAP_TABLESPACE=MDM_DATA:MDM_DATA
TABLE_EXISTS_ACTION=REPLACE
>expdp
system/access09 dumpfile=DRM_CPMPROD_DP.dmp logfile=DRM_CPMPROD_expdp.log
directory=DATA_PUMP_DIR SCHEMAS=DRM
cpmprod> scp
-rp DRM_CPMPROD_DP.dmp
oracpmts@oradev1.am.elcompanies.net:/db/cpmtest/export/csc
impdp
system/Access09$ DUMPFILE=DRM_CPMPROD_DP.dmp
LOGFILE=DRM_CPMPROD_DP.dmp_impdp.log DIRECTORY=DATA_PUMP_DIR
script for
disable constraints:
===============================
set heading off
set linesize 100
spool
drop_constraints.sql
select 'ALTER
TABLE ' || OWNER || '.' || TABLE_NAME || ' DROP CONSTRAINT ' ||
constraint_name|| ';' from dba_constraints where owner in ('DRM') and
constraint_type='R';
script for drop
objects:
==========================
spool
drop_objects.sql
select 'DROP ' ||
OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ';' from dba_objects where
owner in ('DRM') and object_type!='INDEX' order by 1;
SQL> select
sum(bytes)/1024/1204/1024 GB from dba_segments where owner='FDR';
GB
----------
40.5103821
SQL> select
sum(bytes)/1024/1204/1024 GB from dba_segments where owner='FDRB';
GB
----------
30.8884967
SQL> select
object_type,count(*) from dba_objects where owner='DRM' group by object_type;
OBJECT_TYPE COUNT(*)
-------------------
----------
INDEX 238
PROCEDURE 11
VIEW 2
TABLE 107
SEQUENCE 24
LOB 8
6 rows selected.
SQL> select
OWNER, TABLE_NAME, TABLESPACE_NAME, LAST_ANALYZED from dba_tables where
table_name='ODS_FLD_ORDER_DTL';
OWNER TABLE_NAME
TABLESPACE_NAME LAST_ANAL
------------------------------
------------------------------ ------------------------------ ---------
ODS
ODS_FLD_ORDER_DTL ODS 10-FEB-11
Would you kindly
migrate the following table data from Prod (oraprod4, cpmprod) to QA (oradev1,
cpmqa)? Please note that, if required, the data from the QA tables should be
truncated prior to running in the new data from Prod.
Tables
needing migration:
· fdr.iz_bw_mcust_sales
· fdr.iz_bw_mcustomer_member
· fdr.iz_bw_ledger_data
· fdr.iz_bw_copa_data
TABLES=(fdr.iz_bw_mcust_sales,
fdr.iz_bw_mcustomer_member, fdr.iz_bw_ledger_data, fdr.iz_bw_copa_data)
cpmprod> scp -rp
FDR_Tbls_cpmprod.dmp.Z racpmqa@oradev1.am.elcompanies.net:/db/cpmqa/export/
SQL> select
count(*) from dba_tables where owner='FDR';
COUNT(*)
----------
225
SQL> truncate
table FDR.iz_bw_sellthru_ziso002_data;
Table truncated.
SQL> truncate
table FDR.iz_bw_sellthru_ziso010_data;
Table
truncated...............
cpmqa> more
FDR_Tbls_237_exp.ctl USERID=system/Access09$ FILE=/db/cpmqa/export/fdr_exp_pipe
GRANTS=Y INDEXES=Y
BUFFER=65586748COMPRESS=N DIRECT=Y ROWS=Y CONSTRAINTS=Y
TABLES=(FDR.BW_237_ACCT_FA_SET_MAP,FDR.BW_237_ACCT_FA_SET_MAP_MINUS,...........................)
LOG=/db/cpmqa/export/FDR_Tbls_231_cpmqa.log
CONSISTENT=Y
SQL> select
count(*) from dba_tables where owner='FDR';
COUNT(*)
----------
230
SQL> select
count(*) from dba_views where owner='FDR';
COUNT(*)
----------
240
Re-Compile Invalid
Objects
----------------------------
-->To list all
invalid objects of all users
select owner,count(*)
from dba_objects where status like 'INVA%' group by owner;
--> To list all
invalid objects of a particular user, to re-compile
select 'ALTER
'||OBJECT_TYPE||' ' ||OWNER||'.'||OBJECT_NAME||' COMPILE;' from dba_objects where status like 'INVA%'
and owner like 'STRATADMIN';
SQL> select
owner, object_type,count(*) from dba_objects where owner in ('ABP','ABP_WIP')
group by owner,object_type;
select
object_type,count(*) from dba_objects where owner='ABP' group by object_type;
select object_type,object_name,status
from dba_objects where owner='ABP' and object_type='VIEW';
select 'Grant
select on '||object_name||' to abp_user' from dba_objects where owner='ABP' and
object_type='VIEW';
--> To
Re-Compile, following are the syntaxes
ALTER PACKAGE my_package
COMPILE;
ALTER PACKAGE
my_package COMPILE BODY;
ALTER PROCEDURE
my_procedure COMPILE;
ALTER FUNCTION
my_function COMPILE;
ALTER TRIGGER
my_trigger COMPILE;
ALTER VIEW my_view
COMPILE;
ALTER SYNONYM
my_synonym COMPILE;
ALTER PUBLIC
SYNONYM my_synonym COMPILE;
--> If
compilation error is showing the message DBMS_LOCK, then grant the 'execute'
permission and compile the objects once again, the revoke the grants.
GRANT EXECUTE ON
DBMS_LOCK TO PUBLIC;
REVOKE EXECUTE ON
DBMS_LOCK FROM PUBLIC;
User management:--------------------------------------------------------------------
>CREATE USER SOE
IDENTIFIED BY welcome123 DEFAULT TABLESPACE ELHRP TEMPORARY TABLESPACE TEMP
PROFILE SERVICE_PROFILE ACCOUNT UNLOCK;
>GRANT CONNECT
TO SOE;GRANT OUHRP_HUMAN_RESOURCES TO SOE;...............
>alter user soe
quota unlimited on users;
>drop user SOE
cascade;
SQL> select
USERNAME, TABLESPACE_NAME from DBA_TS_QUOTAS where USERNAME = 'INTEGRATOR';
11:32:54 SQL>
alter user integrator quota unlimited on INTGRTON_NDX;
>alter
tablespace INTGRTON add datafile '/db/prod06/data6/intgrton02.dbf' size 3000M;
11:33:48 SQL>
select USERNAME, TABLESPACE_NAME from DBA_TS_QUOTAS where
USERNAME='INTEGRATOR';
USERNAME TABLESPACE_NAME
------------------------------
------------------------------
INTEGRATOR INTGRTON_NDX
INTEGRATOR INTGRTON
SQL> select table_name from dba_tables where
owner='FDR' and table_name like 'IZ_PLN%';
SQL> select USERNAME , DEFAULT_TABLESPACE
from dba_users where username like 'TRECS';
SQL> CREATE USER VGAITE IDENTIFIED BY VALUES '0C98D1CE49112F67' DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE PTM_PROFILE ACCOUNT
UNLOCK;
SQL>select username,account_status from
dba_users where username in ('AJORDAN', 'SKELLMKB', 'SKELLNER', 'MSAFRON',
'LPERSAUD');
SQL>select grantee , GRANTED_ROLE from
dba_role_privs where grantee='RHERMANS';
16:28:44 SQL> select username,account_status,
default_tablespace from dba_users where username = upper('infaREPO85_qa');
CREATE TEMPORARY TABLESPACE TEMP----------------------------------------
first ... extract
the tablespace using embarcadero.then drop the tablespace then delete the files
then create the temp tablespace no alter
database drop or add
>CREATE
TEMPORARY TABLESPACE TEMP TEMPFILE
'/db/ptmtrain/data24/temp04.dbf' SIZE 5000M AUTOEXTEND OFF, '/db/ptmtrain/data24/temp05.dbf' SIZE 5000M
AUTOEXTEND OFF, EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;
DATABASE LINK--------------------------------------------------------------------------
SQL>select * from dba_db_links where host like 'PTM%';
SQL>CREATE DATABASE LINK JDAPAY_TEST CONNECT TO ELODBMS
IDENTIFIED BY elc414ret USING 'jdatest';
# JDADEV TEST Box
JDATEST =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP )
(Host = jdadev)
(Port = 1523)
)
(CONNECT_DATA =
(SID = jdatest))
(GLOBAL_DBNAME = hpal.com)
)
SQL> select * from dual@jdapay_test;
set lines 150
col file_name for a50
select file_name,bytes/1024/1024 MB from dba_data_files where
tablespace_name='INTGRTON';
Tablespace Check
==============
set sqlcase mixed
COLUMN tablespace_name HEADING Tablespace|Name FORMAT a30
COLUMN totalspace HEADING Total|Space FORMAT 99999999
COLUMN free HEADING Free FORMAT 99999999.99
COLUMN Percent_free HEADING Percent|Free FORMAT 99999999.99
set lines 500
set pages 500
select a.tablespace_name ,c.bytes/1024/1024 TOTALSPACE ,
round((b.bytes/1024/1024),2) FREE,
100-round((b.bytes/c.bytes)*100,2)
Percent_Used, round((b.bytes/c.bytes)*100,2) Percent_free from
dba_tablespaces a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space GROUP BY tablespace_name ) b,
( select count(1) datafiles, SUM(bytes) bytes,
tablespace_name from dba_data_files GROUP BY tablespace_name ) c
where
b.tablespace_name (+) = a.tablespace_name
AND
c.tablespace_name (+) = a.tablespace_name
-- a.tablespace_name='INTGRTON'
-- b.tablespace_name='INTGRTON'
-- c.tablespace_name='INTGRTON'
--and round((b.bytes/c.bytes)*100,2)<6
ORDER BY ((c.bytes-b.bytes)/c.bytes) DESC
SQL> select TABLESPACE_NAME,sum(BYTES_USED/1024/1024) "in
MB",sum(BYTES_FREE/1024/1024) "Free in MB",(sum(BYTES_FREE/1024/1024)*100)/(sum(BYTES_FREE/1024/1024)+sum(BYTES_USED/1024/1024))
"pct_free%" from v$temp_space_header group by tablespace_name;
SQL> ALTER TABLESPACE temp ADD TEMPFILE
'J:\TEMPFILES\BG01_TEMP02.DBF' SIZE 1024M;
SQL> ALTER DATABASE TEMPFILE '/db/cpmprod/data4/temp02.dbf' RESIZE
5000M;
SQL> ALTER DATABASE TEMPFILE '/db/cpmprod/data4/temp02.dbf'
AUTOEXTEND OFF;
SQL> select sum(bytes)/1024/1024/1024 GB from dba_segments where
tablespace_name='WEBMDATA_MDA';
SQL> select owner,segment_type,sum(bytes)/1024/1024 MB from
dba_segments where tablespace_name='WEBMDATA_MDA' group by owner,segment_type
order by owner,segment_type;
SQL> select FILE_NAME, BYTES/1024/1024 MB from dba_temp_files where
TABLESPACE_NAME='TEMP';
FILE_NAME
MB
------------------------------ ----------
/db/cpmprod/data3/temp01.dbf
32767
/db/cpmprod/data4/temp02.dbf
5000
to see tablespace freespace,pctfree,pctused,total
space------------------------------------
COLUMN tablespace_name HEADING Tablespace|Name FORMAT a30
COLUMN totalspace HEADING Total|Space FORMAT 99999999
COLUMN free HEADING Free FORMAT 99999999.99
COLUMN Percent_free HEADING Percent|Free FORMAT 99999999.99
COLUMN Percent_used HEADING Percent|Used FORMAT 99999999.99
set lines 500
set pages 500
select a.tablespace_name ,c.bytes/1024/1024 TOTALSPACE , round((b.bytes/1024/1024),2)
FREE,
100-round((b.bytes/c.bytes)*100,2) Percent_used
,round((b.bytes/c.bytes)*100,2) Percent_free from
dba_tablespaces a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space GROUP BY tablespace_name ) b,
( select count(1) datafiles, SUM(bytes) bytes,
tablespace_name from dba_data_files GROUP BY tablespace_name ) c
where
b.tablespace_name (+) = a.tablespace_name
AND
c.tablespace_name (+) = a.tablespace_name ORDER BY
((c.bytes-b.bytes)/c.bytes) DESC
/
Tablespace Total Percent Percent
Name
Space Free Used Free
------------------------------ --------- ------------ ------------
------------
TEMP
SYSTEM 480 2.25 99.53 .47
SYSAUX
240 3.50 98.54 1.46
EXAMPLE
100 31.75 68.25 31.75
USERS
5 2.00
60.00 40.00
UNDOTBS1
30 12.56 58.12 41.88
Inrease the UNDO talespace
size:-------------------------------------------------
SQL> select tablespace_name,file_name,bytes/1024/1024 MB from
dba_data_files where tablespace_name like '%UN%';
ALTER DATABASE DATAFILE '/behp03/MDEV/data1/undotbs1MDEV.dbf' RESIZE
1024M
/
ALTER DATABASE DATAFILE '/behp03/MDEV/data1/undotbs1MDEV.dbf'
AUTOEXTEND OFF
/
SQL> select owner,segment_name,segment_type,bytes/1024/1024 MB from
dba_segments where segment_name like '%MSG%LOG%' and segment_type='TABLE';
OWNER
SEGMENT_NAME
SEGMENT_TYPE MB
------------------------------ ----------------------------------------
------------------ ----------
AGQUANTUM
MSGLOG
TABLE 10368
AGQUANTUM
TBL_ELC_ARCH_MSGLOG
TABLE 10368
Changes done in PFILE to process parameter as 652
====================================================
Sessions = (1.5 * process) +22
dwmos> more initdwmos.ora |grep process
*.job_queue_processes=1
*.processes=652# min 500, increase for higher load
gb-mdt-ux02 oracle /db/dwmos/oracle/product/11.1.0/dbs
Old values in database====================
SQL> select name, value from v$parameter where name in
('sessions','processes');
NAME VALUE
-------------------------- --------------------------------------------------------------------------------
sessions 500
processes 555
DBA broken jobs:
SQL> JOB, LOG_USER, SCHEMA_USER, to_char(LAST_DATE,'dd-mon-yy
hh:mi:ss'), TOTAL_TIME, BROKEN, FAILURES from dba_jobs where broken='Y' order
by job;
col job for a40
col se for a80
set lines 150
SQL>select 'exec DBMS_JOB.BROKEN('||job||',false);' Job,'exec
'||what se from dba_jobs where job in(select JOB from dba_jobs where
broken='Y') order by job;
SQL> col job for a40
SQL> select 'exec
DBMS_JOB.BROKEN('||job||',false);' Job,'exec '||what se from dba_jobs where job
in(select JOB from dba_jobs where broken='Y') order by job;
JOB
SE
---------------------------------------- --------------------------------------------------------------------------------
exec DBMS_JOB.BROKEN(1,false);
exec BEGIN EL_SEND_ORA_EMAIL; END;
exec DBMS_JOB.BROKEN(44,false);
exec BEGIN EL_SEND_ORA_EMAIL; END;
SQL> exec DBMS_JOB.BROKEN(44,false);
PL/SQL procedure successfully completed.
SQL> !date
sql>set time on;
set lines 150
col username for a15
col program for a25
SQL>select sid,serial#,server,username,status,
program,to_char(logon_time,'DD-MON-YY HH:MI:SS'), last_call_et from v$session
where sid=23;
SQL> select sid,serial# from v$session where sid>80 and
SID<100;
Find & Kill the sessiosns by using the SID, Searial#
======================================================
select 'kill -9 '||spid from v$session,v$process where
v$session.paddr=v$process.addr and v$session.username is not null and
v$session.sid=&SID and v$session.serial#=&Serail order by logon_time
desc;
SQL> select sql_address from v$session where sid =687;
SQL_ADDRESS
----------------
000000044756A5C0
SQL> select sql_text from
v$sql where ADDRESS='000000044756A5C0';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(*) FROM STG_SOURCING S WHERE S.ITEM = :B2 AND S.DEST = :B1
SQL>select DBMS_XPLAN.DISPLAY() from dual
SQL>@$ORACLE_HOME/rdbms/admin/utlxpls.sql
SQL>select 'kill -9 '||spid from v$session,v$process where
v$session.paddr=v$process.addr and
v$session.username is not null and v$session.sid=&SID and v$session.serial#=&Serail
order by logon_time desc;
12:23:51 SQL> select sql_address from v$session where sid =
&sid;
Enter value for sid: 28
old 1: select sql_address from
v$session where sid = &sid
new 1: select sql_address from
v$session where sid = 28
Please ensure that the FDRDOOR user account is not locked after the
import.
Feel free to reach out to me should you have any questions.
Also, please let us know when the migration is complete.
Thank you!
No comments:
Post a Comment