USE TO DO

SCRIPT MOVE TABLE PARTITION

SELECT ‘alter table ‘||owner||’.’
|| segment_name
|| ‘ move partition ‘
|| partition_name
|| ‘ tablespace TB_DW_IDX_AGREG_BI COMPRESS;’
|| ‘ ;’, trunc(bytes/1024/1024,2) as MB

FROM dba_segments a
WHERE — owner = ‘BI’ AND
segment_type = ‘TABLE PARTITION’
AND tablespace_name = ‘TB_DW_DATA_AGREG_BI’
AND ( partition_name LIKE ‘%201101%’
OR partition_name LIKE ‘%201102%’)

—–
select ‘alter table ‘||owner||’.’||segment_name||’ move tablespace EDW_DATA_MIG;’ from dba_segments where tablespace_name=’EDW_MART_DEF’ and segment_type=’TABLE’;

SCRIPT DE CREATION DE USER
CREATE USER IDENTIFIED BY PROFILE SECURITY;
GRANT CONNECT,RESOURCE, CREATE SESSION TO ;
ALTER USER PASSWORD EXPIRE;

rm -rf sql_*.sql
rm -rf *.out

split -1 t.sql sql_
ls sql_* |awk ‘{print “mv ” $1 ” ” $1 “.sql” }’ |sh
ls sql_* |awk ‘{print “echo \” \” >> ” $1 }’ |sh

ls sql_* |awk ‘{print “nohup sqlplus / as sysdba <” $1 ” > ” $1 “.out & ” }’ |sh

SCRIPT MOVE INDEX PARTITION

select ‘alter index ‘ || owner || ‘.’ || segment_name || ‘ rebuild partition ‘||partition_name|| ‘ tablespace EDW_DATA_4;’
from dba_segments
where
1=1
and tablespace_name=’EDW_SUMD_DATA_201006′
and segment_type=’INDEX PARTITION’

DROP PARTITIONS

SELECT ‘alter table ‘||owner||’.’
|| segment_name
|| ‘ drop partition ‘
|| partition_name
|| ‘ ;’, trunc(bytes/1024/1024/1024,2) as GB
FROM dba_segments a
WHERE owner = ‘BASE’ AND segment_type = ‘TABLE PARTITION’
—- AND tablespace_name =’EDW_MART_DEF’
AND ( partition_name LIKE ‘%201106%’
OR partition_name LIKE ‘%201107%’
OR partition_name LIKE ‘%201108%’) —- OR partition_name like ‘%201002%’ )
ORDER BY SUBSTR (partition_name, LENGTH (partition_name) – 7, 8),
segment_name,
1 DESC,
partition_name;

NB : Cette requête crée les des lignes « ATLER TABLE .. DROP PARTITION … » pour supprimer les partitions en fonction des dates des partitions dans les clauses « LIKE »

DROP WBS PM_DROPPED_CDRS0 and PM_RATED_CDRS0 PARTITIONS
select ‘ALTER TABLE ‘||owner||’.’||segment_name||’ drop partition ‘||partition_name||’;’, trunc(bytes/1024/1024/1024,2) as GB from dba_segments where owner=’PM_PROD’ and segment_name=’PM_DROPPED_CDRS0′ and partition_name like ‘P2015%’

ALTER TABLE PM_PROD.PM_DROPPED_CDRS0 DROP PARTITION P20141203_CDRS;

select ‘ALTER TABLE ‘||owner||’.’||segment_name||’ drop partition ‘||partition_name||’;’, trunc(bytes/1024/1024/1024,2) as GB from dba_segments where owner=’PM_PROD’ and segment_name=’PM_RATED_CDRS0′ and partition_name like ‘P201%’
ALTER TABLE PM_PROD.PM_RATED_CDRS0 DROP PARTITION P20140618_CDRS;

KILL DES SESSIONS INACTIVES SUR UNE BD

select ‘alter system kill session ”’||sid||’,’||serial#||””||’ immediate;’
From v$session
Where status =’INACTIVE’;

Reconstruction des index
Scripts to rebuild index
select ‘alter index ‘ || index_owner || ‘.’ || index_name || ‘ rebuild partition ‘ || partition_name
||’ tablespace ‘ || tablespace_name
||’;’
from dba_ind_partitions
where 1=1
and status != ‘USABLE’ and status!=’N/A’

Reconstruction des index subpartitions
select
‘ALTER INDEX ‘ ||index_owner|| ‘.’ ||index_name || ‘ REBUILD SUBPARTITION ‘ ||subpartition_name || ‘ tablespace ‘|| tablespace_name|| ‘;’
from DBA_IND_SUBPARTITIONS
where index_name in (‘PM_SUMMARY$INVOICE_PER1′,’PM_SUMMARY_PK1′,’PM_SUMMARY$REPRICE_SEQ_NO1′,’PM_SUMMARY1$UNIQ_SUMMARY1’) ;

REBUILD INDEX NON PARTITIONNES

select ‘alter index ‘ ||owner || ‘.’ ||index_name || ‘ rebuild tablespace ‘ || tablespace_name
||’;’ –||STATUS||’;’
from dba_indexes
where 1=1
and status = ‘UNUSABLE’ and table_owner not in (‘SYS’,’SYSTEM’) and table_name not like ‘%$%’

select ‘alter table ‘||owner||’.’||segment_name||’ move tablespace EDW_DATA_MIG;’ from dba_segments where tablespace_name=’EDW_MART_DEF’
and owner =’MART’ and segment_type=’TABLE’;

select ‘alter index ‘ || owner || ‘.’ || segment_name || ‘ rebuild tablespace EDW_DATA4;’
from dba_segments
where
1=1
and tablespace_name=’EDW_DATA3′
and segment_type=’INDEX’
———-

and tablespace_name not like ‘%20910X%’

—and partition_name not like ‘%200910%’
–and partition
_name not like ‘%200906%’
—and tablespace_name not like ‘%20910X%’
–and index_name not like ‘%MSC%’
order by substr(partition_name,length(partition_name)-5,8),partition_name
–);

Construction requête Changement de tablespace d’une table
select ‘alter table ‘||owner||’.’||table_name||’ move tablespace DATA1;’ from dba_tables
where tablespace_name=’USERS’
and owner not in(‘SCOTT’,’SYS’,’SYSTEM’);

SCRIPT MOVE PARTITION DE EDW

SELECT ‘alter table ‘||owner||’.’
|| segment_name
|| ‘ move partition ‘
|| partition_name
|| ‘ tablespace EDW_BASE_DATA_201111X;’
FROM dba_segments a
WHERE owner = ‘BASE’
AND segment_type = ‘TABLE PARTITION’
AND tablespace_name = ‘EDW_BASE_DATA_201110X’
ORDER BY SUBSTR (partition_name, LENGTH (partition_name) – 7, 8),
segment_name,
1 DESC,
partition_name;

SCRIPT MOVE LOBSEGMENT SUR UN AUTRE TABLESPACE

select ‘alter table ‘ || t.owner || ‘.’ || t.table_name || ‘ move lob (‘||column_name||’) store as lobsegment (tablespace EDW_DATA_MIGRE);’
from all_lobs l, dba_tables t where l.owner=t.owner and l.table_name = t.table_name and l.SEGMENT_NAME
in ( select segment_name from dba_segments where segment_type like ‘LOBSEGMENT’ and tablespace_name = ‘EDW_DATA1’)
order by t.owner, t.table_name;

Export partition script for ccbs.

Sauvegarde des partitions de 2008, 2009 de certaines tables de la base CCBS.

NB : Penser à dropper les tables sauvegardées.

nohup exp system/Admindba567$ file=/backup/ccbs/export/historics_0809_ccbs.DMP log=/backup/ccbs/export/historics_0809_ccbs.log TABLES=CCARE.HIS_INF_SERVICE_PARA:P2008,CCARE.HIS_INF_PRODUCTS:P2009 BUFFER=81920 STATISTICS=NONE FEEDBACK=1000 &

PROCEDURE CREATION DE PARTITION MENSUEL

————- POUR LES PARTITIONS BY LIST ——-

Utiliser la clause « VALUES » au lieu de « VALUES LESS THAN »

————- FIN ——-

PROCEDURE STOCKEE (ini)

NB : Permet d’ajouter des partitions NON ENCORE EXISTANTES selon les dates définies pour une table donnée.

DECLARE
v_count NUMBER := 0;
v_date DATE := TO_DATE (20111101, ‘yyyymmdd’);
v_sql VARCHAR2 (1000);

BEGIN
DBMS_OUTPUT.put_line (‘RUN STARTED’);

WHILE v_date < TO_DATE (20111201, ‘yyyymmdd’)
LOOP
v_sql :=
‘ALTER TABLE base.BASE_EVD_VTU_ACCOUNTTX ‘ ‘—OWNER. —-
|| ‘ ADD PARTITION B_VTU_ACCOUNTTX_’ ‘—NOM PARTITION A CREER > —-
|| TO_CHAR (v_date, ‘yyyymmdd’)
|| ‘ VALUES LESS THAN (‘
|| TO_CHAR (v_date + 1, ‘yyyymmdd’)
|| ‘) TABLESPACE EDW_BASE_DATA_’ ‘—NOM TBS DU MOIS
|| TO_CHAR (v_date, ‘yyyymm’);
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.put_line (v_sql);
v_date := v_date + 1;
v_count := v_count + 1;
END LOOP;
DBMS_OUTPUT.put_line (‘RUN FINISHED:’ || v_count);
END;

————- POUR LES DEUX DERNIERES TABLES DE STG ——-

DECLARE
v_count NUMBER := 0;
v_startdate NUMBER(8);
v_enddate NUMBER(8);
v_date DATE := TO_DATE (‘&v_startdate’, ‘yyyymmdd’);
v_sql VARCHAR2 (1000);
v_table VARCHAR2(1000);
v_partname VARCHAR2(1000);
v_maxpart VARCHAR2(1000);
v_tbs VARCHAR2(1000);

BEGIN
DBMS_OUTPUT.put_line (‘RUN STARTED’);

SELECT table_name, max(partition_name),substr(partition_name,1,length(partition_name)-8) into v_table,v_maxpart,v_partname
FROM dba_tab_partitions
WHERE partition_name LIKE ‘%201111%’
AND table_owner in (‘STG’)
and table_name in (‘&tablename’)
GROUP BY table_name,substr(partition_name,1,length(partition_name)-8)
order by table_name;

WHILE v_date < TO_DATE (‘&v_enddate’, ‘yyyymmdd’)

LOOP

v_sql :=
‘ALTER TABLE STG.’||v_table||’ ‘
|| ‘ ADD PARTITION ‘||v_partname||”
|| TO_CHAR (v_date, ‘yyyymmdd’)
|| ‘ VALUES LESS THAN (‘
|| TO_CHAR (v_date + 1, ‘yyyymmdd’)
|| ‘) TABLESPACE EDW_STG_DATA’;
–|| TO_CHAR (v_date, ‘yyyymm’);
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.put_line (v_sql);
v_date := v_date + 1;
v_count := v_count + 1;
END LOOP;
DBMS_OUTPUT.put_line (‘RUN FINISHED:’ || v_count);
END;

———– PARTITION POUR TBS NON PARTITIONNE PAR MOIS ——————–

DECLARE
v_count NUMBER := 0;
v_date DATE := TO_DATE (20111205, ‘yyyymmdd’);
v_sql VARCHAR2 (1000);

BEGIN
DBMS_OUTPUT.put_line (‘RUN STARTED’);

WHILE v_date < TO_DATE (20120101, ‘yyyymmdd’)
LOOP
v_sql :=
‘ALTER TABLE PROD.DWH_SEGMENTATION_JOUR ‘ –OWNER. —-
|| ‘ ADD PARTITION DWH_SEGMENTATION_’ –NOM PARTITION A CREER > —-
|| TO_CHAR (v_date, ‘yyyymmdd’)
|| ‘ VALUES LESS THAN (‘
|| TO_CHAR (v_date + 1, ‘yyyymmdd’)
|| ‘) TABLESPACE TB_CLM_DATA_PROD’; –—NOM TBS DU MOIS
–|| TO_CHAR (v_date, ‘yyyymm’); — #CE AVEC LES AUTRES REQUETES. EVITE LES MOIS
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.put_line (v_sql);
v_date := v_date + 1;
v_count := v_count + 1;
END LOOP;
DBMS_OUTPUT.put_line (‘RUN FINISHED:’ || v_count);
END;

————————————————————————–

PROCEDURE STOCKEE (BASE)

BEGIN
DBMS_OUTPUT.put_line (‘RUN STARTED’);

SELECT table_name, max(partition_name),substr(partition_name,1,length(partition_name)-8) into v_table,v_maxpart,v_partname
FROM dba_tab_partitions
WHERE partition_name LIKE ‘%201110%’
AND table_owner in (‘BASE’)
and table_name in (‘&tablename’)
GROUP BY table_name,substr(partition_name,1,length(partition_name)-8)
order by table_name;

WHILE v_date < TO_DATE (‘&v_enddate’, ‘yyyymmdd’)

LOOP

v_sql :=
‘ALTER TABLE base.’||v_table||’ ‘
|| ‘ ADD PARTITION ‘||v_partname||”
|| TO_CHAR (v_date, ‘yyyymmdd’)
|| ‘ VALUES LESS THAN (‘
|| TO_CHAR (v_date + 1, ‘yyyymmdd’)
|| ‘) TABLESPACE EDW_BASE_DATA_’
|| TO_CHAR (v_date, ‘yyyymm’);
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.put_line (v_sql);
v_date := v_date + 1;
v_count := v_count + 1;
END LOOP;
DBMS_OUTPUT.put_line (‘RUN FINISHED:’ || v_count);
END;

NB : Toutes

PROCEDURE STOCKEE (MART)

BEGIN
DBMS_OUTPUT.put_line (‘RUN STARTED’);

SELECT table_name, max(partition_name),substr(partition_name,1,length(partition_name)-8) into v_table,v_maxpart,v_partname
FROM dba_tab_partitions
WHERE partition_name LIKE ‘%201109%’
AND table_owner in (‘MART’)
and table_name in (‘&tablename’)
GROUP BY table_name,substr(partition_name,1,length(partition_name)-8)
order by table_name;

WHILE v_date < TO_DATE (‘&v_enddate’, ‘yyyymmdd’)

LOOP

v_sql :=
‘ALTER TABLE mart.’||v_table||’ ‘
|| ‘ ADD PARTITION ‘||v_partname||”
|| TO_CHAR (v_date, ‘yyyymmdd’)
|| ‘ VALUES LESS THAN (‘
|| TO_CHAR (v_date + 1, ‘yyyymmdd’)
|| ‘) TABLESPACE EDW_SUMD_DATA_’
|| TO_CHAR (v_date, ‘yyyymm’);
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.put_line (v_sql);
v_date := v_date + 1;
v_count := v_count + 1;
END LOOP;
DBMS_OUTPUT.put_line (‘RUN FINISHED:’ || v_count);
END;

CREATION DE TABLESPACES SUR EDW

/*============== PARTIE BASE =============================*/

create BIGFILE tablespace EDW_BASE_DATA_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

create BIGFILE tablespace EDW_BASE_IDX_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

create BIGFILE tablespace EDW_EDR_DATA_201110X
nologging datafile ‘+ASM_DATA_TIER01’
size 1G autoextend on
next 20M maxsize UNLIMITED;

create BIGFILE tablespace EDW_BASE_TEMP_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

/*========PARTIE MART DETAIL==============================*/

create BIGFILE tablespace EDW_EDR_IDX_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

create BIGFILE tablespace EDW_EDR_DATA_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

/*create BIGFILE tablespace EDW_MARTX_DATA_201111
nologging datafile ‘+ASM_DATA_TIER03’
size 1G autoextend on
next 1G maxsize 800G;*/

create BIGFILE tablespace EDW_MARTX_IDX_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

create BIGFILE tablespace EDW_MART_DATA_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

create BIGFILE tablespace EDW_MART_IDX_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

create BIGFILE tablespace EDW_EDRX_DATA_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

create BIGFILE tablespace EDW_EDRX_IDX_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

/*===============PARTIE MART AGGREGAT======================*/

create BIGFILE tablespace EDW_SUMD_DATA_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

create BIGFILE tablespace EDW_SUMD_IDX_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

create BIGFILE tablespace EDW_SUMM_DATA_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

create BIGFILE tablespace EDW_SUMM_IDX_201111
nologging datafile ‘+ASM_DATA_TIER05’
size 1G autoextend on
next 20M maxsize UNLIMITED;

REMPLACEMENT DE TABLESPACE TEMP SUR EDW

1. CREATE TEMP2

CREATE BIGFILE TEMPORARY TABLESPACE TEMP2 TEMPFILE
‘+ASM_DATA_TIER01’ SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
TABLESPACE GROUP ”
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

alter database default temporary tablespace temp2;

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

create temporary tablespace temp tempfile ‘/u01/tmp.dbf’ SIZE 1024M extent management local uniform size 100M;

alter database default temporary tablespace temp ;

drop tablespace temp2 including contents and datafiles;

select file#,name,status from v$tempfile;

alter tablespace temp drop tempfile 1;

alter tablespace temp add tempfile ‘/restofact/oradata/temp04.dbf’ size 300M;

DROP TABLESPACE EDW_EDR_DATA_201110X INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE EDW_BASE_TBS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

CREATE BIGFILE TEMPORARY TABLESPACE TEMP TEMPFILE
‘+ASM_DATA_TIER01’ SIZE 10080M AUTOEXTEND ON NEXT 5120M MAXSIZE 137438953376K
TABLESPACE GROUP ”
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

EXCHANGE PARTITION SUR EDW

SELECT ‘drop table mart.TMP;’,
‘create table mart.TMP tablespace EDW_DATA_EXCHANGE as select * from ‘
|| owner
|| ‘.’
|| segment_name
|| ‘ where 1=2;’,
‘alter table ‘
|| owner
|| ‘.’
|| segment_name
|| ‘ exchange partition ‘
|| partition_name
|| ‘ with table STG.CBS_CDR_POS_0001;’
FROM dba_segments
WHERE ta_name IN
(‘CBS_CDR_POS_0001’)
AND owner IN (‘STG’)
AND segment_type = ‘TABLE PARTITION’
AND ( partition_name LIKE ‘%201108%’
OR partition_name LIKE ‘%201012%’
OR partition_name LIKE ‘%201101%’
OR partition_name LIKE ‘%201103%’
OR partition_name LIKE ‘%201011%’
OR partition_name LIKE ‘%201010%’
OR partition_name LIKE ‘%201105%’
OR partition_name LIKE ‘%201109%’
OR partition_name LIKE ‘%201105%’
OR partition_name LIKE ‘%201110%’)
AND tablespace_name IN
(‘EDW_EDR_DATA_201108X’,
‘EDW_EDR_DATA_201012XX’,
‘EDW_EDR_DATA_201101X’,
‘EDW_EDR_DATA_201103X’,
‘EDW_INDEX_SMALLX’,
‘EDW_STG_DATA’,
‘EDW_EDR_DATA_201011XX’,
‘EDW_EDR_DATA_201010X’,
‘EDW_BASE_TEMP01’,
‘EDW_MART_DATA_201105X’,
‘EDW_EDR_DATA_201109X’,
‘EDW_EDR_DATA_201105XX’);

SUPPRESSION PARTITION SUR EDW

DROP TABLESPACE EDW_BASE_DATA_201109 including contents and datafiles;

DROP TABLESPACE EDW_BASE_DATA_201110X including contents and datafiles;

SELECT ‘alter table ‘
|| owner
|| ‘.’
|| segment_name
|| ‘ move partition ‘
|| partition_name
|| ‘ tablespace TBS_DATA;’
FROM dba_segments a
WHERE owner = ‘DBDW’
AND segment_type = ‘TABLE PARTITION’
AND tablespace_name = ‘TBS_DATA_DW_02’
AND ( partition_name LIKE ‘%201101%’
OR partition_name LIKE ‘%201102%’
OR partition_name LIKE ‘%201103%’
OR partition_name LIKE ‘%201104%’
OR partition_name LIKE ‘%201105%’
OR partition_name LIKE ‘%201106%’
OR partition_name LIKE ‘%201107%’
OR partition_name LIKE ‘%201108%’
OR partition_name LIKE ‘%201109%’)
— OR partition_name LIKE ‘%201110%’
— OR partition_name LIKE ‘%201111%’)
ORDER BY SUBSTR (partition_name, LENGTH (partition_name) – 7, 8),
segment_name,
1 DESC,
partition_name;

PURGE TABLESPACE

PURGE TABLESPACE EDW_MART_DEF user MART;

——— CREATION DE PARTITIONS

DECLARE
v_count NUMBER := 0;
v_date DATE := TO_DATE (20110801, ‘yyyymmdd’);
v_sql VARCHAR2 (1000);

BEGIN
DBMS_OUTPUT.put_line (‘RUN STARTED’);

WHILE v_date < TO_DATE (20111201, ‘yyyymmdd’)
LOOP
v_sql :=
‘ALTER TABLE PROD.DWH_SEGMENTATION_JOUR ‘
|| ‘ ADD PARTITION DWH_SEGMENTATION_’
|| TO_CHAR (v_date, ‘yyyymmdd’)
|| ‘ VALUES LESS THAN (‘
|| TO_CHAR (v_date + 1, ‘yyyymmdd’)
|| ‘) TABLESPACE TB_CLM_DATA_PROD ‘;
–|| TO_CHAR (v_date, ‘yyyymm’);
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.put_line (v_sql);
v_date := v_date + 1;
v_count := v_count + 1;
END LOOP;
DBMS_OUTPUT.put_line (‘RUN FINISHED:’ || v_count);
END;

INFOS SUR LES TABLESPACES D’UNE BD

select
a.TABLESPACE_NAME,
a.CONTENTS,
a.EXTENT_MANAGEMENT,
a.ALLOCATION_TYPE,
a.SEGMENT_SPACE_MANAGEMENT,
a.BIGFILE,
a.STATUS,
nvl(sum(b.count_files),0) FILES,
nvl(sum(b.bytes),0) “SIZE”,
nvl(sum(b.maxbytes),0) MAX_SIZE,
nvl(sum(b.bytes),0)-nvl(sum(c.free_bytes),0) “USED”
from DBA_TABLESPACES a,
(
select TABLESPACE_NAME,
sum(BYTES) bytes,
count(*) count_files,
sum(greatest(MAXBYTES,BYTES)) maxbytes
from DBA_DATA_FILES
group by TABLESPACE_NAME
union all
select TABLESPACE_NAME,
sum(BYTES),
count(*),
sum(greatest(MAXBYTES,BYTES)) maxbytes
from DBA_TEMP_FILES
group by TABLESPACE_NAME
) b,
(
select TABLESPACE_NAME,
sum(BYTES) free_bytes
from DBA_FREE_SPACE
group by TABLESPACE_NAME
union all
select TABLESPACE_NAME,
sum(BYTES_FREE) free_bytes
from V$TEMP_SPACE_HEADER
group by TABLESPACE_NAME
) c
where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
and a.TABLESPACE_NAME = c.TABLESPACE_NAME (+)
group by
a.TABLESPACE_NAME,
a.CONTENTS,
a.EXTENT_MANAGEMENT,
a.ALLOCATION_TYPE,
a.SEGMENT_SPACE_MANAGEMENT,
a.BIGFILE,
a.STATUS
order by a.TABLESPACE_NAME;

—
select t.tablespace_name,
t.total_size “Taille totale (Mo)”,
f.free_size “Espace Libre (Mo)” ,
(( t.total_size – f.free_size)*100 / t.total_size ) “%occ”
from
( select tablespace_name,
round(sum(bytes)/(1024*1024),2) total_size
from dba_data_files group by tablespace_name) t ,
( select tablespace_name,
round(sum(bytes)/(1024*1024),2) free_size
from dba_free_space group by tablespace_name) f
where t.tablespace_name= f.tablespace_name
order by 4 desc;

———
RENOMMER UN USER

Method 1
Suppose we wanted to rename the Scott user to Tiger.
Step1. Export the scott schema details to a dump file using exp or expdp utility
Object list of Scott user is given below.

SQL> select object_name, object_type from all_objects where owner=’SCOTT’;

OBJECT_NAME OBJECT_TYPE
—————————— ——————-
DEPT TABLE
EXAMPLE TABLE PARTITION
EXAMPLE TABLE PARTITION
EXAMPLE TABLE
SYS_C00278761 INDEX
EMP1 TABLE
EXAMPLE_PARTITION TABLE
TMP$$_SYS_C002787610 INDEX
GT_EMP TABLE

9 rows selected.

$ expdp dumpfile=scott.dmp logfile=scott.log directory=exp_dir schemas=SCOTT

Export: Release 11.1.0.7.0 – 64bit Production on Wednesday, 22 June, 2011 1:45:10

Username: / as sysdba

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
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: /******** AS SYSDBA dumpfile=scott.dmp logfile=scott.log directory=exp_dir schemas=SCOTT
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.437 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “SCOTT”.”EXAMPLE_PARTITION” 837.4 KB 95120 rows
. . exported “SCOTT”.”EXAMPLE”:”EXAMPLE_P1″ 441.3 KB 49999 rows
. . exported “SCOTT”.”EXAMPLE”:”EXAMPLE_P2″ 408.3 KB 45121 rows
. . exported “SCOTT”.”DEPT” 5.945 KB 4 rows
. . exported “SCOTT”.”EMP1″ 5.890 KB 3 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/SCOTT/scott.dmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 01:55:10

Step3. Create the target user(TIGER) in the database with necessary privileges.

SQL> create user tiger identified by welcome;

User created.

SQL> alter user tiger default tablespace users;

User altered.

SQL> alter user tiger quota unlimited on users;

User altered.

Step4. Import the dump to the new user with remap schema option with impdp or fromuser touser option in imp utility.
$ impdp dumpfile=scott.dmp logfile=imp_scott.log directory=exp_dir remap_schema=scott:tiger

Import: Release 11.1.0.7.0 – 64bit Production on Wednesday, 22 June, 2011 2:05:38

Username: / as sysdba

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
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″: /******** AS SYSDBA dumpfile=scott.dmp logfile=imp_scott.log directory=exp_dir remap_schema=scott:tiger
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”TIGER” already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “TIGER”.”EXAMPLE_PARTITION” 837.4 KB 95120 rows
. . imported “TIGER”.”EXAMPLE”:”EXAMPLE_P1″ 441.3 KB 49999 rows
. . imported “TIGER”.”EXAMPLE”:”EXAMPLE_P2″ 408.3 KB 45121 rows
. . imported “TIGER”.”DEPT” 5.945 KB 4 rows
. . imported “TIGER”.”EMP1″ 5.890 KB 3 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYS”.”SYS_IMPORT_FULL_01″ completed with 1 error(s) at 02:05:55

You can ignore the error, why because the user already created manually.
Step5: Validate the object list

SQL> select object_name, object_type from all_objects where owner=’TIGER’;

9 rows selected.

Method 2 : This method is not recommended by oracle because this is a manual updation of data dictionary tables.
SQL> select object_name, object_type from all_objects where owner=’SCOTT’;

9 rows selected.

Step 1. Connect to sqlplus using sys as sysdba
Step 2. Update the name column of sys.user$ table for the scott user as tiger
SQL> update user$ set name=’TIGER’ where name=’SCOTT’;
1 row updated.
Step 3. Reset the password of Tiger user
SQL> alter user tiger identified by welcome;
User altered.

OUTIL DATA PUMP

CONN / AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS ‘/u01/app/oracle/oradata/’;
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
Table Exports/Imports
The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.
expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

Database Exports/Imports
The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.
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

CHANGE TABLESPACE OF TABLES

select ‘alter table ‘||owner||’.’||table_name||’ move tablespace EDW_MART_DEF_EX;’ from dba_tables
where tablespace_name=’EDW_MART_DEF’
and owner in (‘MART’);

GRANT SELECT SUR UNE TABLE

grant select on mart.dim_msisdn_t2 to RA;

commit;

COMMANDE : DD
In this example, you back up from one raw device to another raw device:
% dd if=/dev/rsd1b of=/dev/rsd2b bs=8k skip=8 seek=8 count=3841

In this example, you back up from a raw device to a file system:
% dd if=/dev/rsd1b of=/backup/df1.dbf bs=8k skip=8 count=3841

AJOUT/SUPPRESSION DE GROUPE DE REDO LOG

 Connection à la BD : ANAD

echo $ORACLE_SID

ANAD

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 1 15:09:15 2011

SQL> conn /as sysdba

Connected.

 Voir les groups de redo et fichiers associés

set line 300
col lmember format a25
set pagesize 50
select thread#,l.group#,l.status,substr(lf.member,1,50) lmember ,l.bytes/1024/1024 size_Mb from v$log l,v$logfile lf where l.group#=lf.group# order by thread#,2;

GROUP# STATUS SUBSTR(LF.MEMBER SIZE_MB
———- —————- —————- ———-
1 ACTIVE /u01/oradata/ANA 100
1 ACTIVE /u02/oradata/ANA 100
1 ACTIVE /u03/oradata/ANA 100
2 ACTIVE /u01/oradata/ANA 100
2 ACTIVE /u02/oradata/ANA 100
2 ACTIVE /u03/oradata/ANA 100
3 ACTIVE /u01/oradata/ANA 100
3 ACTIVE /u02/oradata/ANA 100
3 ACTIVE /u03/oradata/ANA 100
4 CURRENT /u01/oradata/ANA 250

10 rows selected.

 Ajouter un groupe de redo

SQL> ALTER DATABASE ADD LOGFILE GROUP 7 (
‘/u01/oradata/ANAD/REDO/redo71.log’,’/u02/oradata/ANAD/REDO/redo72.log’, ‘/u03/oradata/ANAD/REDO/redo73.log’) SIZE 250M ;

 Changer le groupe de redo actif (ayant le statut “CURRENT”)

SQL> ALTER SYSTEM SWITCH LOGFILE ;

SQL> select thread#,l.group#,l.status,substr(lf.member,1,50),l.bytes/1024/1024 size_Mb from v$log l,v$logfile lf where l.group#=lf.group#;

GROUP# STATUS SUBSTR(LF.MEMBER,1,26) SIZE_MB
———- —————- ————————– ———-
1 INACTIVE /u01/oradata/ANAD/redo11.l 100
1 INACTIVE /u02/oradata/ANAD/redo12.l 100
1 INACTIVE /u03/oradata/ANAD/redo13.l 100
5 CURRENT /u01/oradata/ANAD/redo51.l 250
5 CURRENT /u02/oradata/ANAD/redo52.l 250
5 CURRENT /u03/oradata/ANAD/redo52.l 250
6 INACTIVE /u01/oradata/ANAD/redo61.l 250
6 INACTIVE /u02/oradata/ANAD/redo62.l 250
6 INACTIVE /u03/oradata/ANAD/redo62.l 250
7 INACTIVE /u01/oradata/ANAD/redo71.l 250
7 INACTIVE /u02/oradata/ANAD/redo72.l 250

GROUP# STATUS SUBSTR(LF.MEMBER,1,26) SIZE_MB
———- —————- ————————– ———-
7 INACTIVE /u03/oradata/ANAD/redo72.l 250

12 rows selected.

 Supprimer un groupe s’il n’est pas en utilisation (« CURRENT ») ou actif (« ACTIVE »)

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

SQL> select l.group#,l.status,substr(lf.member,1,26),l.bytes/1024/1024 size_Mb from v$log l,v$logfile lf where l.group#=lf.group#;

GROUP# STATUS SUBSTR(LF.MEMBER,1,26) SIZE_MB
———- —————- ————————– ———-
5 CURRENT /u01/oradata/ANAD/redo51.l 250
5 CURRENT /u02/oradata/ANAD/redo52.l 250
5 CURRENT /u03/oradata/ANAD/redo52.l 250
6 INACTIVE /u01/oradata/ANAD/redo61.l 250
6 INACTIVE /u02/oradata/ANAD/redo62.l 250
6 INACTIVE /u03/oradata/ANAD/redo62.l 250
7 INACTIVE /u01/oradata/ANAD/redo71.l 250
7 INACTIVE /u02/oradata/ANAD/redo72.l 250
7 INACTIVE /u03/oradata/ANAD/redo72.l 250

9 rows selected.

 NB : Si le groupe est en statut « ACTIVE », faire un checkpoint pour le rendre « INACTIVE »

SQL> ALTER SYSTEM CHECKPOINT;

Ajouter un member à un groupe de REDO
ALTER DATABASE ADD LOGFILE MEMBER ‘+DATA/rocfm/redo12.log’ TO GROUP 1;

Drop standby logs on standby database

ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;

Recreate the new Standby logs

alter database add standby logfile THREAD 1 group 4 (‘+DATA(ONLINELOG)’)’) SIZE 1000M;

ESPACES DISQUES UTILISES DANS UNE BD

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;

########### Incremental Backups level0 #####################
ORACLE_HOME=/opt/oracle/product/10gR2
export ORACLE_HOME
ORACLE_SID=fdCORE
export ORACLE_SID

rman catalog rman/rman@rman target / << EOF
run {
allocate channel d1 device type disk format ‘/future_use/backup/fdcore/level0/backup_level0_db_%d_S_%s_P_%p_T_%t’;

allocate channel d2 device type disk format ‘/future_use/backup/fdcore/level0/backup_level0_db_%d_S_%s_P_%p_T_%t’;

allocate channel d3 device type disk format ‘/future_use/backup/fdcore/level0/backup_level0_db_%d_S_%s_P_%p_T_%t’;

allocate channel d4 device type disk format
‘/future_use/backup/fdcore/level0/backup_level0_db_%d_S_%s_P_%p_T_%t’;
backup
incremental level 0
filesperset 4
(database);
backup
archivelog all
delete input;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
EXIT;
EOF

Démarrer les resources ASM et BD MIMIR après reboot OS

./crsctl start has
./crsctl start resource –all

Resolving checkpoint issues :

1) Give the checkpoint process more time to cycle through the logs
– add more redo log groups
– increase the size of the redo logs
2) Reduce the frequency of checkpoints
– increase LOG_CHECKPOINT_INTERVAL
– increase size of online redo logs
3) Improve the efficiency of checkpoints enabling the CKPT process
with CHECKPOINT_PROCESS=TRUE
4) Set LOG_CHECKPOINT_TIMEOUT = 0. This disables the checkpointing
based on time interval.
5) Another means of solving this error is for DBWR to quickly write
the dirty buffers on disk.

RMAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.18.0.34)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLMD)
)
)

SUPPRESSION ARCHIVES RAC LMS
$su – grid
$export ORACLE_SID=+ASMx
$ asmcmd
ASMCMD> lsdg
ASMCMD> cd FRA
ASMCMD> ls
LMSDB/
ASMCMD> cd *
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
ONLINELOG/
ASMCMD> cd ARCHIVELOG
ASMCMD> ls
2011_12_25/
2011_12_26/
2011_12_27/
ASMCMD> cd 2011_12_25
ASMCMD> rm -f *

KILL SESSIONS
Select ‘alter system kill session ”’||sid||’,’||serial#||””||’;’
From v$session Where status =’INACTIVE’;

BI HUAWEI PFILE

Sql> startup pfile=/opt/oracle/app/oracle/admin/bi/pfile/init.ora

MANAGE TEMPORARAY TABLESPACES
ALTER TABLESPACE temp_new

ALTER TABLESPACE TBS_DATA_TEMP
ADD TEMPFILE ‘/dev/rrbi_tbs_temp_11’ SIZE 15000M;ALTER DATABASE TEMPFILE ‘/u02/oradata/tempnew02.dbf’ DROP;

ALTER DATABASE TEMPFILE ‘/dev/rrbi_tbs_temp_10’ RESIZE 10000M;

select ‘alter table ‘||owner||’.’||table_name||’ move lob (‘||column_name||’) store as ‘||segment_name||’ (tablespace EDW_STG_DAT);’
from all_lobs
where segment_name in (select segment_name from dba_segments where segment_type=’LOBSEGMENT’ and tablespace_name=’EDW_STG_DATA’)

OBJETS CONTENUS DANS UN TABLESPACE

select owner,segment_name,segment_type,partition_name from dba_segments
where tablespace_name=’EDW_STG_DATA’

select distinct SEGMENT_NAME, segment_type, sum(bytes)/(1024*1024) from dba_segments
where owner in (‘ADOU’,’AISSAN’,’AKOSSI’,’BAD’,’BASSORY’,’BI’,’BILL’,’BIOCS_REPORT’,’BROUROG’,’CCBMPROV’,’CUSTSYNC’,’DATATRANS’,’DBSNMP’,’DJE’,’EAS’,’EAS_VOCALCOM’,’EDW’,’EPHREM’,’EXPORTER’,’FRONT’,’GILLESBI’,’HOUFFOUE’,’IT_SECURE’,’IVR’,’JMSUSER’,’JMSUSER1′,’KADJA’,’KONEBI’,’KONEI’,’MABOUTOU’,’MDSSER’,’MDSTOPENG’,’MEDCDR’,’MONITOR’,’MTNBILL’,’NBILLING’,’NGUESSAN’,’NM’,’NSER’,’OLIVIER’,’OUTLN’,’PARFAIT’,’PERFSTAT’,’POSTING’,’PRMUAT’,’RA’,’RACBS’,’REPORT’,’SVC_PRM.EAS’,’SYS’,’SYSTEM’,’TABS’,’TASKMON’,’TEST_USER’,’TOPENG’,’UAT’,’USER_BILLING’,’USER_CCARE’,’USER_TOPENG’,’VOCALCOM’,’WMSYS’)
group by SEGMENT_NAME, segment_type

SELECT substr(s.USERNAME,1,10) USER,
substr(s.OSUSER,1,10) OSUSER,
s.SID,
s.SERIAL#,
p.SPID,
substr(s.SERVER,1,20) SERVER,
s.STATUS,
substr(s.MACHINE,1,15) MACHINE,
substr(s.PROGRAM,1,10) PROGRAM,
TO_CHAR(s.LOGON_TIME, ‘hh24:mi:ss’) LOGON_TIME,
d.name DISP,
ss.name SERV
FROM V$PROCESS p,
V$SESSION s,
V$DISPATCHER d,
V$CIRCUIT c,
V$SHARED_SERVER ss
WHERE p.ADDR = s.PADDR
AND s.SADDR=c.SADDR (+)
AND c.DISPATCHER=d.PADDR (+)
AND c.SERVER=ss.PADDR (+)
AND s.USERNAME IS NOT NULL
ORDER BY s.USERNAME, p.SPID;

select ”||owner||’.’||segment_name||’:’||partition_name||’,’
from dba_segments
where –owner=’BILLING’
(partition_name like ‘%2011020%’ or
partition_name like ‘%20110210%’ or
partition_name like ‘%20110211%’)
and segment_type=’TABLE PARTITION’
and owner=’DBDW’

$ srvctl stop database -d LMSDB
[oracle@SVR-LMSDB-02 admin]$ srvctl start database -d LMSDB

/opt/app/11.2.0/grid/bin/crsctl status resource –t

./crsctl stop crs
./crsctl start crs

./crsctl status resource –t

ORA-28650: Primary index on an IOT cannot be rebuilt.
Posted on December 5, 2007 by admin
________________________________________
ORA-28650: Primary index on an IOT cannot be rebuilt.
Index Organized tables (IOT’s) do not store their data in the table but in the index (as the name suggests). For that reason, you cannot rebuild the indexes, but have to move the parent table to re-organise the segments in there.
I was trying to clear a tablespace out, by moving tables and indexes, but the ORA-28650 occurred, cannot rebuild primary IOT index. Using the queries that normally worked, I had to find a way around it. Here I am moving all data from the GORR_DE_FIX tablespace to the SMALL_TS_TABLES tablespace, prior to dropping the GORR_DE_FIX tablespace.
I moved the tables using the following piece of code:
begin
for c1 in (select ‘alter table ‘||owner||’.’||segment_name||’ move tablespace SMALL_TS_TABLES’ txt
from dba_segments
where segment_type = ‘TABLE’
and tablespace_name = ‘GORR_DE_FIX’) loop
execute immediate c1.txt;
end loop;
end;
/
Then I wanted to move the indexes defined, with the following text:
set serveroutput on size 9999
begin
for c1 in (select ‘alter index ‘||owner||’.’||segment_name||’ rebuild tablespace SMALL_TS_TABLES’ txt
from dba_segments
where segment_type = ‘INDEX’
and tablespace_name = ‘GORR_DE_FIX’) loop
execute immediate c1.txt;
end loop;
end;
/
Checking the inner code of the cursor gave me:
select ‘alter index ‘||owner||’.’||segment_name||’ rebuild tablespace SMALL_TS_TABLES’ txt
from dba_segments
where segment_type = ‘INDEX’
and tablespace_name = ‘GORR_DE_FIX’
TXT
——————————————————————————–
alter index ER9C.SYS_IOT_TOP_40209 rebuild tablespace SMALL_TS_TABLES
alter index ER9C.SYS_IOT_TOP_40211 rebuild tablespace SMALL_TS_TABLES
alter index ER9C.SYS_IOT_TOP_40213 rebuild tablespace SMALL_TS_TABLES
alter index ER9C.SYS_IOT_TOP_40215 rebuild tablespace SMALL_TS_TABLES
alter index ER9C.SYS_IOT_TOP_40217 rebuild tablespace SMALL_TS_TABLES
But, when trying to move the index, it failed.
SQL> alter index ER9C.SYS_IOT_TOP_40209 rebuild tablespace SMALL_TS_TABLES;
alter index ER9C.SYS_IOT_TOP_40209 rebuild tablespace SMALL_TS_TABLES
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt
So, I had to find the table for the IOT.
SQL> select owner, table_name from dba_indexes where index_name = ‘SYS_IOT_TOP_40209’;
OWNER TABLE_NAME
—————————— ——————————
ER9C ER_ACCOUNTS_FAULT
and you can see the table has no tablespace name.
SQL> select owner, tablespace_name from dba_tables where table_name = ‘ER_ACCOUNTS_FAULT’;
OWNER TABLESPACE_NAME
—————————— ——————————
ER9C
From there, with no tablespace for the table (It’s an IOT table, so no physical table involved), I moved the table in question.
SQL> alter table ER9C.ER_ACCOUNTS_FAULT move tablespace SMALL_TS_TABLES;
Table altered.
and confirmed that it had been moved.
SQL> select tablespace_name from dba_indexes where index_name = ‘SYS_IOT_TOP_40209’;
TABLESPACE_NAME
——————————
SMALL_TS_TABLES
IOT indexes can be found with the index type of ‘IOT – TOP’.
SQL> select distinct index_Type from dba_indexes;
INDEX_TYPE
—————————
CLUSTER
FUNCTION-BASED NORMAL
IOT – TOP
LOB
NORMAL
So, I changed the index move script I was using to cater for IOT tables in the segments table, from this:
set serveroutput on size 9999
begin
for c1 in (select ‘alter index ‘||owner||’.’||segment_name||’ rebuild tablespace SMALL_TS_TABLES’ txt
from dba_segments
where segment_type = ‘INDEX’
and tablespace_name = ‘GORR_DE_FIX’) loop
dbms_output.put_line(c1.txt);
– execute immediate c1.txt;
end loop;
end;
/
To this:
set serveroutput on size 9999
begin
for c1 in (select ‘alter index ‘||owner||’.’||index_name||’ rebuild tablespace SMALL_TS_TABLES’ txt
from dba_indexes
where index_type = ‘NORMAL’
and tablespace_name = ‘GORR_DE_FIX’
union
select ‘alter table ‘||owner||’.’||table_name||’ move tablespace SMALL_TS_TABLES’ txt
from dba_indexes
where index_type = ‘IOT – TOP’
and tablespace_name = ‘GORR_DE_FIX’) loop
– dbms_output.put_line(c1.txt);
execute immediate c1.txt;
end loop;
end;
/
You could reverse the logic, and move the IOT tables with the tables in the tablespace themselves.

GRANT SELECT on all objects of a user
select distinct ‘grant select on ‘||owner||’.’||object_name||’ to META;’ from all_objects where owner=’BIB’

VUES CONCERNEES PAR LE TBS TEMP USED PAR DES PROCESS

V$TEMPFILE
V$TEMPSTAT
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_SPACE_HEADER
V$TEMPSEG_USAGE (Oracle 9i and later releases)
V$SORT_USAGE (Oracle 8.1.7, 8.1.6 and 8.1.5)

SELECT a.username, a.osuser,a.status, b.spid,c.tablespace
FROM v$session a, v$process b,v$TEMPSEG_USAGE c
WHERE a.paddr = b.addr and A.SADDR=C.SESSION_ADDR and c.tablespace=’TEMP’;

select ‘kill -9 ‘||b.spid||’ ;’
FROM v$session a, v$process b,v$TEMPSEG_USAGE c
WHERE a.paddr = b.addr and A.SADDR=C.SESSION_ADDR and c.tablespace=’TEMP’;

Procedure for Renaming Datafiles in a Single Tablespace
To rename datafiles in a single tablespace, complete the following steps:
1. Take the tablespace that contains the datafiles offline. The database must be open.
For example:
ALTER TABLESPACE users OFFLINE NORMAL;
2. Rename the datafiles using the operating system.
3. Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.
For example, the following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf, respectively:
ALTER TABLESPACE TB_CLM_DATA_PROD
RENAME DATAFILE ‘/U01/oradata/CLMD/DATA/CLM1.dbf’
TO ‘/CLMDATA2/oradata/CLMD/data/CLM1.dbf’;

Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.
4. PUT TABLESPACE ONLINE
5. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Site oracle : edeleverey.oracle.com

SELECT –tablespace_name,
‘alter table ‘
|| a.owner
|| ‘.’
|| a.segment_name
|| ‘ move partition ‘
|| a.partition_name
|| ‘ tablespace EDW_BASE_DEF’
|| ‘ compress;’ DD
FROM dba_segments a, (
SELECT table_owner, table_name, partition_name, last_analyzed, HIGH_VALUE , NUM_ROWS
FROM dba_tab_partitions a
WHERE table_owner = ‘BASE’
and a.num_rows < 100000 — and last_analyzed < sysdate -2
) b
WHERE segment_name = table_name and tablespace_name=’EDW_BASE_DATA_201203X’
ORDER BY SUBSTR (a.partition_name, LENGTH (a.partition_name) – 7, 8),
a.partition_name DESC

INF_PRODUCT, INF_SUBSCRIBER_ALL de CCARE

WAIT EVENTS

SELECT DECODE(px.qcinst_id,NULL,username, ‘ – ‘||LOWER(SUBSTR(pp.SERVER_NAME,
LENGTH(pp.SERVER_NAME)-4,4) ) )”Username”, DECODE(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” ,
TO_CHAR( px.server_set) “SlaveSet”, s.program, TO_CHAR(s.SID) “SID”,
TO_CHAR(px.inst_id) “Slave INST”, DECODE(sw.state,’WAITING’, ‘WAIT’, ‘NOT WAIT’ ) AS STATE,
CASE sw.state WHEN ‘WAITING’ THEN SUBSTR(sw.event,1,30) ELSE NULL END AS wait_event ,
DECODE(px.qcinst_id, NULL ,TO_CHAR(s.SID) ,px.qcsid) “QC SID”,
TO_CHAR(px.qcinst_id) “QC INST”, px.req_degree “Req DOP”, px.DEGREE “Actual DOP”,
DECODE(px.server_set,”,s.last_call_et,”) “Elapsed seconds”
FROM gv$px_session px, gv$session s, gv$px_process pp, gv$session_wait sw
WHERE px.SID=s.SID (+)
AND px.serial#=s.serial#(+)
AND px.inst_id = s.inst_id(+)
AND px.SID = pp.SID (+)
AND px.serial#=pp.serial#(+)
AND sw.SID = s.SID
AND sw.inst_id = s.inst_id
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;

IMPORT EXCLUDE TABLES ON RE DHAT
impdp exporter/exporter directory=IMPBKP dumpfile=MIMIRT_SCHEMAS-24042012-20h00mn.dmp logfile=IMP_24042012.log schemas=BIB exclude=TABLE:\”IN $\’FCT_MONTHLY_KPI\’,\’FCT_WEEKLY_KPI\’$\”

WITH SOLARIS
expdp exporter/password DIRECTORY=BKPDIR DUMPFILE=IFSDB2_FULL_150314.dmp LOGFILE=IFSDB2_FULL_150314.log FULL=YES exclude=TABLE:\”IN $\’RA_INV_PART_VOU_TAB\’$\” COMPRESSION=ALL &

nohup expdp exporter/exporter DIRECTORY=BACKUP DUMPFILE=MOMO_EXCLUDE.dmp LOGFILE=MOMO_EXCLUDE.log SCHEMAS=FUNDAMO exclude=TABLE:\”IN $\’MESSAGE_CONTENT007\’,\’MESSAGE_CONTENT006\’\,\’MESSAGE_CONTENT004\’\,\’MESSAGE_CONTENT003\’\,\’MESSAGE007\’\,\’ENTRY001\’\,\’MESSAGE_CONTENT005\’\,\’MESSAGE_CONTENT001\’\,\’MESSAGE004\’\,\’MESSAGE001\’\,\’MESSAGE003\’\,\’MESSAGE006\’\,\’TRANSACTION001\’$\” &

impdp exporter/exporter directory=BACKUP dumpfile=Msg07_may14.dmp tables=FUNDAMO.MESSAGE007 logfile=message007.log TABLE_EXISTS_ACTION=APPEND

NB : data_options=skip_constraint_errors to avoid unique constraint violated

en tant que root, créer un disk /

asm create disk ASMDISK14 /dev/dm-27 -labeller

/dev/ora add disk

Alter diskgroup

Root
/etc/init.d/oracleasm

alter diskgroup
add disk ” (ie: /dev/oracleasm/disks/ASMDISK1)

alter diskgroup DG_DATA

alter diskgroup DG_DATA add disk /dev/oracleasm/disks/ASMDISK14’;

desc user$ (vue pr les mp users)

DETERMINER REQUETES GOURMANDES

Select A.username, A.sid, B.spid, C.sql_text, A.status
from v$session A, v$process B, v$sqltext C
Where A.paddr=B.addr and A.sql_hash_value=C.hash_value
and B.spid in (”)

DB VAULT LMST11

URL d’accès : https://10.2.6.118:1158/dva
-Security administrator : dbv_admin/oracle_4U
-Auditor : dbv_auditor/oracle_4U
-Account Manager : dbv_acctmrg/oracle_4U

EXPORT : EXCLUDE SCHEMA

expdp system/passwd directory=test dumpfile=test.exp logfile=test.log EXCLUDE=SCHEMA:\”in $\’TEST\’, \’SCOTT\’$\”

PASSWORD SECURITY PARAMETERS
• FAILED_LOGIN_ATTEMPTS : Nombre d’erreurs permises à la saisie du mot de passe avant que le compte soit verrouillé,
• PASSWORD_GRACE_TIME : En cas de péremption d’un mot de passe dû à un délai fixé par l’administrateur, cette option permet de paramétrer une durée (en jours) pendant laquelle l’utilisateur pourra tout de même se connceter, mais recevra un avertissement,
• PASSWORD_LIFE_TIME : Durée (en jours) de vie maximum d’un mot de passe,
• PASSWORD_LOCK_TIME : Durée (en jours) pendant laquelle un compte sera verrouillé après qu’il ait atteint le nombre d’erreurs permises à la saisie de son mot de passe (FAILED_LOGIN_ATTEMPTS),
• PASSWORD_REUSE_MAX : Nombre de changement de mots de passe requis avant de pouvoir ré-utiliser un mot de passe déjà utilisé,
• PASSWORD_REUSE_TIME : Durée (en jours) minimum pendant laquelle l’utilisateur ne peut pas ré-utiliser un mot de passe déjà utilisé, à partir du moment où celui-ci a été changé,
• PASSWORD_VERIFY_FUNCTION : permet de préciser une fonction (PL/SQL) vérifiant la compexité du mot de passe.
select profile,RESOURCE_NAME,LIMIT from dba_profiles where profile=’SECURITY’;

RESOLUTION PB SYSTEM FILE CORRUPTED SUR RX

– Find Current Scn Of The Database
– Set _Mimimum_Giga_Scn To Higher Value (We Did Not Use Below Given Procedure To Do So)
– Offline Roolback Segment _Syssmu6$
– Startup The Database Unsing Recover Procedure
– Create A New Undo Tablespace Undotbs0
– Find All Roolback Segment And Offline Them (Using Pfile)
– Drop Old Undo Tablespace
– Remove _allow_error_simulation, _allow_resetlos_corruption, _corrupted_rollback_segments, _minimum_giga_scn from pfile
– Set Follow Parameters : Undo_Management = Auto And Undo_Tablespace = Undotbs0
– Re-start the database

This configuration, an 11g feature, allows to delete an archive log as soon as it is applied to all standby destinations
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
——— DATAGUARD COMMANDS ——————————————-

SELECT THREAD#,SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER
BY THREAD#,SEQUENCE#;

— FOR LOGICAL STANDBY —-
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED,STATUS FROM V$ARCHIVED_LOG WHERE STATUS=’A’ ORDER BY SEQUENCE#;

select process,status,sequence#,block#,blocks,delay_mins from v$managed_standby; (Diaby)

select severity,error_code,message,to_char(timestamp,’DD-MON-YYYY HH24:MI:SS’) from v$dataguard_status where dest_id=2; (On both nodes if needed)

select NAME,OPEN_MODE,DATABASE_ROLE from v$database;

select severity,error_code,message,to_char(timestamp,’DD-MON-YYYY HH24:MI:SS’) from v$dataguard_status where dest_id=2;

select dest_id, status from v$archive_dest;

select thread#,process, status,sequence#,block#,blocks, delay_mins from v$managed_standby;

select max(sequence#) from v$archived_log where APPLIED=’YES’;

select severity, error_code,message,to_char(timestamp,’DD-MON-YYYY HH24:MI:SS’) from v$dataguard_status where dest_id=2;

select sequence# -1 from v$log where status = CURRENT;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

Disconnect or stop replication ————————
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Restart replication ————————
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
********************* RESTART ON LOGICAL STBY *************
SQL> alter database stop logical standby apply;
SQL > shutdown immediate;
SQL > startup;
SQL> alter database start logical standby apply immediate;

********************* OUVRIR BD 11G EN LECTURE ET DR *************
SQL > shutdown immediate;
SQL > startup;
*******************************************************************
select * from v$dataguard_status;
SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

select force_logging from v$database;

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

select switchover_status from v$database;

select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

select value from v$parameter where name =’log_archive_dest_2′;

select value from v$parameter where name =’log_archive_dest_3′;

SELECT decode(count(*),0,0,1) FROM v$managed_standby WHERE (PROCESS=’ARCH’AND STATUS NOT IN (‘CONNECTED’)) OR (PROCESS=’MRP0′ AND STATUS NOT IN (‘WAIT_FOR_LOG’,’APPLYING_LOG’)) OR (PROCESS=’RFS’ AND STATUS NOT IN (‘IDLE’,’RECEIVING’));

————- Using DGMGRL —————————-

DGMGRL> SHOW CONFIGURATION;
DGMGRL> show database stby

******* Ajout/retrait de brocker ************

export ORACLE_SID=DB_SID

dgmgrl /

create configuration dg_config as primary database is IFSBD connect identifier is IFSBD;

add database IFSBDR as connect identifier is IFSBDR maintained as physical;

show configuration

enable configuration;

Config niveau listener :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = genoa1_js_dgmgrl)
(ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)
(SID_NAME = genoa1)
)
)
————- End DGMGRL —————————-

select database_role,switchover_status from v$database; (check switchover readiness)

Changer le rôle des bds primary-> Standby –
DGMGRL>switchover to KYC_DR
Arrêt / Redémarrage du brocker
SQL> alter system set dg_broker_start=false;
SQL> alter system set dg_broker_start=true;
Performing DDL on a Logical Standby Database
http://docs.oracle.com/cd/B28359_01/server.111/b28294/manage_ls.htm
This section describes how to add a constraint to a table maintained through SQL Apply.
By default, only accounts with SYS privileges can modify the database while the database guard is set to ALL or STANDBY. If you are logged in as SYSTEM or another privileged account, you will not be able to issue DDL statements on the logical standby database without first bypassing the database guard for the session.
The following example shows how to stop SQL Apply, bypass the database guard, execute SQL statements on the logical standby database, and then reenable the guard. In this example, a soundex index is added to the surname column of SCOTT.EMP in order to speed up partial match queries. A soundex index could be prohibitive to maintain on the primary server.
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.

SQL> ALTER SESSION DISABLE GUARD;
PL/SQL procedure successfully completed.

SQL> CREATE INDEX EMP_SOUNDEX ON SCOTT.EMP(SOUNDEX(ENAME));
Table altered.

SQL> ALTER SESSION ENABLE GUARD;
PL/SQL procedure successfully completed.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
Database altered.

SQL> SELECT ENAME,MGR FROM SCOTT.EMP WHERE SOUNDEX(ENAME) = SOUNDEX(‘CLARKE’);

ENAME MGR
———- ———-
CLARK 7839
Oracle recommends that you do not perform DML operations on tables maintained by SQL Apply while the database guard bypass is enabled. This will introduce deviations between the primary and standby databases that will make it impossible for the logical standby database to be maintained.
PB : ARCHIVE LOGS NOT APPLIED ON LOGICAL STANDBY

Check status of log applied :

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

Identify Error on applying logs

SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIMESTAMP, COMMIT_SCN;

Skip DML or DDL according to error log

Ex: skip DML and DDL on table FUNDAMO.USER_ACCOUNT_101115

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

EXECUTE DBMS_LOGSTDBY.SKIP(‘SCHEMA_DDL’, ‘FUNDAMO’,’USER_ACCOUNT001′, NULL);

EXECUTE DBMS_LOGSTDBY.SKIP(‘DML’, ‘FUNDAMO’,’USER_ACCOUNT001′, NULL);

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

EX : skip creation of index (DDL) USER_ACC_HOLD

From Oracle Support : I recommend to use the following check instead:

Primary: SQL>
select thread#, max(sequence#) “Last Primary Seq Generated” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

PhyStdby:SQL>
select thread#, max(sequence#) “Last Standby Seq Received” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

Primary: SQL> alter system archive log current;

Primary: SQL>
select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:SQL>
select thread#, max(sequence#) “Last Standby Seq Received” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:SQL>
select thread#, max(sequence#) “Last Standby Seq Applied” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in (‘YES’,’IN-MEMORY’)
group by thread# order by 1;
————————————————————————————————————–

SQL SERVER : LES FONCTIONNALITES A SELECTIONNER

REPLICATION EVD

IFSAPP GRANT TO EVESIS

grant execute on ifsapp.Inventory_Part_API to evesis;
grant execute on ifsapp.Mpccom_System_Event_Api to evesis;
grant execute on ifsapp.Company_Site_API to evesis;
grant select on ifsapp.inventory_transaction_hist2 to evesis;

GRANT SELECT ON CCBS MONTHLY TABLE

—————ON OLD CBS MEDIATION ——————————–

select distinct ‘GRANT SELECT ON ‘||owner||’.’||segment_name|| ‘ TO ADOU,AISSAN,AKOSSI,EAS,EAS_VOCALCOM,EDW,KADJA,MABOUTOU,MTNBILLING,NGUESSAN,RA,RACBS, “SVC_PRM.EAS”;’
from dba_segments where segment_name like ‘%201404%’ and segment_type not like ‘%INDEX%’

—————ON NEW CBS MEDIATION ——————————–
select distinct ‘GRANT SELECT ON ‘||owner||’.’||segment_name|| ‘ TO ADOU,EDW,KADJA,MABOUTOU,RACBS;’
from dba_segments where segment_name like ‘%201404%’ and segment_type not like ‘%INDEX%’

SUPPRESSION CARACTERES INDESIRABLES

SELECT REGEXP_REPLACE ( CONVERT (Name,’AL32UTF8′,’WE8ISO8859P1′), ‘[^!@/\.,;:<>#$%&()_=[:alnum:][:blank:]]’) a from fundamo.subscriber where OID=’312453235415970017′

GENERATION REPORT PARAMETRES DB

ALTER SESSION SET nls_date_format = ‘DD/MM/YYYY HH24:MI:SS’;
SET PAGESIZE 900
SET LINESIZE 255
COL COMPONENT FORMAT A25
SPOOL ASMM_RESIZE.TXT

/* Database Identification */
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
select * from V$version where banner like ‘Oracle Database%’;
select INSTANCE_NAME, to_char(STARTUP_TIME,’DD/MM/YYYY HH24:MI:SS’) “STARTUP_TIME” from v$instance;

/* ASMM SGA Resize Operations */
select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 “INITIAL (MB)”, FINAL_SIZE/1024/1024 “FINAL (MB)”, END_TIME
from v$sga_resize_ops
order by start_time, component;

/* SGA Advice where SGA_SIZE is in Mb */
select * from v$sga_target_advice order by sga_size;
SPOOL OFF

ACTIVATE SQL AGENT MENU

sp_configure ‘show advanced options’,0
go
reconfigure

sp_configure ‘Agent XPs’,1
go
reconfigure

RUN ARCHIVE JOB

select DATEDIFF(dd,min(datecreated),MAX(datecreated)) from txrefs
go
exec ArchiveNightly
go
select DATEDIFF(dd,min(datecreated),MAX(datecreated)) from txrefs
go
Remise à 0 de content
update Properties set Content=0 where PropertyId=100

REMOVE TRAPS AND SYSLOG MESSAGES:
1. Start > All Programs > SolarWinds Orion > Advanced Features > Database Manager.
2. Click Add Server.
3. Select or provide your SQL Server.
Note: If you do not know your SQL Server, complete the following steps:
1. Click Start > All Programs > Solarwinds Orion > Documentation and Support > Configuration Wizard.
2. Check Database, and then click Next.
Your SQL Server credentials are now displayed in this window.
4. Select your preferred login method, and then, if you are using a SQL Server userid and password, provide your User Name and Password.
5. Click Connect to Database Server.
6. Click + to expand your SQL instance.
7. Click + to expand your Orion NPM database.
Note: The default Orion NPM database name is NetPerfMon.
8. If you are also running Orion NCM, click + to expand your Orion NCM database.
Note: The default Orion NCM database name is ConfigMgmt.
9. Right-click Syslog, and then click Query Table.
10. Clear the provided query, and then enter the following query to delete all Syslog messages, traps, and trap variable bindings that are older than a designated date:
Delete from Syslog Where datetime <= ‘4/1/2010’
Delete from Traps Where datetime <= ‘4/1/2010′
Delete from TrapVarBinds where TrapID not in (select TrapID from Traps)
11. If you want to delete all existing Syslog messages, traps, and trap variable bindings, use Truncate commands as follows:
Truncate Table Syslog
Truncate Table Traps
Truncate Table TrapVarBinds
DETECTION DE LOGS
select
con.session_id
,ses.login_name
,ses.nt_domain
,ses.nt_user_name
,con.connect_time
,atr.transaction_begin_time
,tdb.database_transaction_begin_time
,db_name(tdb.database_id) as [Database]
,(tdb.database_transaction_log_bytes_used + tdb.database_transaction_log_bytes_used_system) / (1024*1024) AS Log_Used_Mb
,(tdb.database_transaction_log_bytes_reserved + tdb.database_transaction_log_bytes_reserved_system) / (1024*1024) AS Log_Reserved_Mb
,con.client_net_address
,con.client_tcp_port
,con.net_transport
,con.protocol_type
,con.auth_scheme
,ses.client_interface_name
,ses.[program_name]
FROM [sys].[dm_exec_connections] con
INNER JOIN [sys].[dm_exec_sessions] ses ON con.session_id = ses.session_id
INNER JOIN [sys].[dm_tran_session_transactions] tra ON ses.session_id = tra.session_id
INNER JOIN [sys].[dm_tran_active_transactions] atr ON tra.transaction_id = atr.transaction_id
INNER JOIN [sys].[dm_tran_database_transactions] tdb ON tra.transaction_id = tdb.transaction_id

DBCC SQLPERF(LOGSPACE)

col TABLESPACE_NAME format A40
col TOTAL_SIZE_mo format A20
col FREE_SPACE_mo format A20
col USED format A20
set line 400
select t.tablespace_name “TABLESPACE_NAME”,
to_char( t.total_size,’99,999,999′) “TOTAL_SIZE_mo”,
to_char(f.free_size,’99,999,999′) “FREE_SPACE_mo” ,
to_char( (( t.total_size – f.free_size)*100 / t.total_size ),’00.00′) “USED”
from
( select tablespace_name,
round(sum(bytes)/(1024*1024),2) total_size
from dba_data_files group by tablespace_name) t ,
( select tablespace_name,
round(sum(bytes)/(1024*1024),2) free_size
from dba_free_space group by tablespace_name) f
where t.tablespace_name= f.tablespace_name
order by 4 ;

col TABLESPACE_NAME format A20
col FILE_NAME format A60

select tablespace_name, file_name ,AUTOEXTENSIBLE,MAXBYTES,INCREMENT_BY from dba_data_files order by 1,2;

SYS-01 : Purge logs orion
Delete from Syslog Where substring(convert(varchar,datetime,103),1,10) <= ’31/12/2012′

Delete from Traps Where substring(convert(varchar,datetime,103),1,10) <= ’31/12/2012’
Or WHERE YEAR(datetime)=2016 AND MONTH(datetime)=6 AND DAY(datetime) between 28 and 30
AFFICHER LES OPTIONS FULL DE CONFIGURATION
sp_configure ‘show advanced options’,1
go
reconfigure
go
sp_configure
EX : MODIFIER AWE
sp_configure ‘awe enabled’,0
go
reconfigure
go
sp_configure

NB : CTRL+ALT+t : Templates disponibles
Rechercher les données d’une date donnée dans txrefs
select * from TxRefs where substring(convert(varchar,DateCreated,103),1,10) = ’21/03/2013′

–

–select substring(convert(varchar,GETDATE(),108),1,10)

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)
– Oct 2 2008 11:01AM
SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy – 10/02/2008
SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02
SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) – dd mon yyyy
SELECT convert(varchar, getdate(), 107) – mon dd, yyyy
SELECT convert(varchar, getdate(), 108) – hh:mm:ss
SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)
– Oct 2 2008 11:02:44:013AM
SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) – yyyymmdd
SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm
– 02 Oct 2008 11:02:07:577
SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm
– 2008-10-02T10:52:47.513
– SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd
SELECT convert(varchar(7), getdate(), 126) – yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8) – mon yyyy

select grantee, privilege,admin_option from dba_sys_privs where grantee in ( ‘SYSTEM’,’FUNDAMO’,’OLIVIER’,’HLB_NGA’,’EXPORTER’,’EAS’,’BIREPORT’,’EPHREM’,’FUNDAMO_CONS’,’GATEWAY’,’DBSNMP’,’USSDPOS’,’USSDSUB’,’PERFSTAT’,’SYS’,’DIE’) order by grantee

select grantee,table_name, privilege, owner from dba_tab_privs where grantee in ( ‘SYSTEM’,’FUNDAMO’,’OLIVIER’,’HLB_NGA’,’EXPORTER’,’EAS’,’BIREPORT’,’EPHREM’,’FUNDAMO_CONS’,’GATEWAY’,’DBSNMP’,’USSDPOS’,’USSDSUB’,’PERFSTAT’,’SYS’,’DIE’) order by grantee

select grantee,table_name, privilege, owner from dba_tab_privs where grantee =’PUBLIC’ and owner like ‘USER%’

USERS PERMISSIONS ON SQL DB

EXECUTE AS LOGIN = ‘MTNCI\kadja’;
SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);
REVERT;

FICHIERS DE BD SQL SERVER
select name,physical_name from sys.master_files

select distinct owner,SEGMENT_NAME, segment_type, sum(bytes)/(1024*1024) from dba_segments
where owner in (‘BILLING’) and segment_name like ‘CDR_PPS_00%’
group by owner,SEGMENT_NAME, segment_type

AWR &

SQL> @?/rdbms/admin/awrrpt.sql

SQL>@?/rdbms/admin/spreport.sql (oracle 9i)

ADDM

SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql

Obtenir la version de toutes les instances SQL Server :
select @@version

ORACLE MEMORY USAGE
select (sga+pga) /1024/1024/1024 as “SGA_PGA” from
(select sum(value) sga from v$sga),
(select sum(pga_used_mem) pga from v$process);

Suppression old files dans un rép.
find . -type f -mtime +3 |xargs rm -Rf {} \;

find . -type f -mtime +1 -exec rm -f {} \;
find /ora_arch/audit_logs -type f -mmin +10 -exec rm -f {} \; (delete if >= 30 mn)

CHECKING DATAGUARD GOOD HEALTH.

set linesize 500 pages 0
col value for a90
col name for a50
select name, value
from v$parameter
where name in (‘db_name’,’db_unique_name’,’log_archive_config’, ‘log_archive_
dest_1′,’log_archive_dest_2’,
‘log_archive_dest_state_1′,’log_archive_dest_state_2’, ‘remote_
login_passwordfile’,
‘log_archive_format’,’log_archive_max_processes’,’fal_
server’,’db_file_name_convert’,
‘log_file_name_convert’, ‘standby_file_management’);

SECURITY SCRIPTS FOR USERS AND PROFILES

1/ ============ Profiles and resources ===========================
col PROFILE format A30
col RESOURCE_NAME format A30
col LIMIT format A30

select distinct profile from dba_profiles;

set line 300
select profile,RESOURCE_NAME,LIMIT from dba_profiles order by profile;

2/============= Users accounts ===================================

col USERNAME format A30
col ACCOUNT_STATUS format A30
col PROFILE format A30
col EXPIRY_DATE format A30

select distinct username,ACCOUNT_STATUS,PROFILE,EXPIRY_DATE from dba_users where username not in (‘WKSYS’,’MDSYS’,’OLAPSYS’,’MGMT_VIEW’,’OUTLN’,’CTXSYS’,’ORACLE_OCM’,’SCOTT’,’EXFSYS’,’ORDPLUGINS’,’MDDATA’,’ORDSYS’,’XDB’,’ANONYMOUS’,’WMSYS’,’COMMON’,’DMSYS’,’SYSMAN’,’DIP’,’TSMSYS’,’DBSNMP’);

CHECK AUDIT OPTIONS ENABLED FOR ARCSIGHT

col OWNER format a20
set line 300
SELECT * FROM sys.dba_obj_audit_opts; –WHERE owner =’KYC’;

AUDIT SELECT,INSERT , UPDATE, DELETE ON TST_PROD.PM_PARTNER BY ACCESS;

NOAUDIT CREATE ANY TABLE BY IFSAPP ;
NOAUDIT CREATE TABLE BY IFSAPP ;
NOAUDIT DELETE TABLE BY IFSAPP ;
NOAUDIT INSERT TABLE BY IFSAPP ;
NOAUDIT SELECT TABLE BY IFSAPP ;
NOAUDIT UPDATE TABLE BY IFSAPP ;
NOAUDIT CREATE SESSION BY DBSNMP
Generate AUDIT and NOAUDIT Statements for Current Audit Settings (Doc ID 287436.1)

Use these commands to turn off all of the above AUDIT settings:

noaudit all;
noaudit all privileges;
noaudit exempt access policy;
noaudit select any dictionary;
noaudit SELECT TABLE ;
noaudit INSERT TABLE ;
noaudit UPDATE TABLE ;
noaudit DELETE TABLE ;
noaudit grant procedure;
noaudit grant table;
noaudit alter table;

AFFICHER DISKS MONTES SUR UN SERVEUR
$palimpsest

IMPORT CCBS PRM TABLE

imp system/oracle file=CDR_INTER_IVLD_201306.dmp fromuser=PRM touser=PRM tables=CDR_INTER_IVLD_201306 log=imp_cdr.log

exp prm/prm file=cdr_inter_201305_01.dmp tables=prm.cdr_inter_201305 query=\”where partition_day \= \’01\’\”

impdp exporter/exporter directory=BACKUP dumpfile=p4me_archiveroamcall_17062016.dmp tables=CBM.ARCHIVEROAMCALL remap_schema=CBM:TEST query=\”where STARTDATETIME \> TO_DATE$20160616,\’YYYYMMDD\’$`\”

# hastatus –sum
#hagrp -switch service_mdsolddb -to mdsolddb1a

lftp -u dba 10.18.1.57

INSTALL PKG USING YUM CMD
Connexion à internet
#yum install gcc-*

ORACLE SUPPORT INSTALL SOFT ORACLE 11G FOR REDHAT 6
Hello Kouame,

I’m Prasad with the Oracle Support Services.
I was assigned your SR & have the following updates-

Please note that as of now,only Oracle Client 11.2.0.3. is certified on RHEL 6 (32-Bit ) & (64-Bit).
Please review the Installation related details in the Doc – Requirements for Installing Oracle 11gR2 RDBMS on RHEL6 or OL6 64-bit (x86-64) [Doc ID 1441282.1]

i) You may download the Oracle Database 11g Release 2 Client (11.2.0.3) for Oracle Linux 6 (64-bit) from the below Link –
Download Link – https://updates.oracle.com/download/10404530.html
Note – The 64-Bit Client Package would be the 4th Zip File – p10404530_112030_Linux-x86-64_4of7.zip

ii) You may download the Oracle Database 11g Release 2 Client (11.2.0.3) for Oracle Linux 6 (32-bit) from the below Link –
Download Link – https://updates.oracle.com/download/10404530.html
Note – The 32-Bit Client Package would be the 4th Zip File – p10404530_112030_LINUX_4of7.zip

You may read through the below helpful Docs –
Where do I find that on My Oracle Support (MOS) [Video] (Doc ID 1194734.1)
How To Find RDBMS patchsets on My Oracle Support (Doc ID 438049.1)
Quick Reference to Patchset Patch Numbers (Doc ID 753736.1)
Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 (Doc ID 1189783.1)

Please lemme know if you need additional assistance/info.
Thank You for choosing to work with the Oracle Support Services.

Thank You
Prasad Kulkarni
Oracle Support Service

Ci-dessous la procédure de changement de password entre base principale et base DR :

CHANGER LE PASSWORD SUR LA BASE DE PRODUCTION

SQL> alter user sys identified by passwd ;

Changez le password sur la base DR

$cd $ORACLE_HOME/dbs
$orapwd file=orapwSID_DR password=’passwd’ entries=10 force=y

ENVOI MAIL JOINT

uuencode FMC_15072013-18h00mn.csv FMC_15072013-18h00mn.csv | mailx -s “test de fichier” bertrand.zoguei@mtn.ci

CLEAN HIGH WATER MARK

Clean up the High Water Mark, the action is as following:
1) — inf_orderinfo
create table inf_orderinfo_lc_temp as select * from inf_orderinfo;
truncate table inf_orderinfo;
insert into inf_orderinfo select * from inf_orderinfo_lc_temp;
commit;

2) —inf_workorder
create table inf_workorder_lc_temp as select * from inf_workorder;
truncate table inf_workorder;
insert into inf_workorder select * from inf_workorder_lc_temp;
commit;

3) — inf_businessinfo
create table inf_businessinfo_lc_temp as select * from inf_businessinfo;
truncate table inf_businessinfo;
insert into inf_businessinfo select * from inf_businessinfo_lc_temp;
commit;

4) — INF_ORDER_PRODUCTCHG
create table INF_ORDER_PRODUCTCHG_lc_temp as select * from INF_ORDER_PRODUCTCHG;
truncate table INF_ORDER_PRODUCTCHG;
insert into INF_ORDER_PRODUCTCHG select * from INF_ORDER_PRODUCTCHG_lc_temp;
commit;

ORACLE PATCH SET
Patch Number 10404530 Oracle Database Family: Patchset
11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER
..
Installation Type Zip File
Oracle Database (includes Oracle Database and Oracle RAC)Note: you must download both zip files to install Oracle Database. p10404530_112030_platform_1of7.zipp10098816_112020_platform_2of7.zip
Oracle Grid Infrastructure (includes Oracle ASM, Oracle Clusterware, and Oracle Restart) p10404530_112030_platform_3of7.zip
Oracle Database Client p10404530_112030_platform_4of7.zip
Oracle Gateways p10404530_112030_platform_5of7.zip
Oracle Examples p10404530_112030_platform_6of7.zip
Deinstall p10404530_112030_platform_7of7.zip

SELECT p.spid, s.username, s.program FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != ‘BACKGROUND’ and s.username=’&username’;

DEPLACER LES BACKUPS DE PLUS DE 90 JOURS

[svr-mmdblive-01]=>cat move-script.sh
# deplacer les backups de plus de 90 jours sur le serveur EDW

cd /future_use/backup/rman

for i in `find . -mtime +90`;

ls -lhrt $i >> /tmp/file_tmp
mv $i /BACKUP
done

CONNEXION ASM SUR FMS (IRISDB) DB

Positionner correctement ORACLE_SID=+ASM et ORACLE_HOME= /oracle/app/product/ASM11G et le PATH

SCRIPT TAILLE DE BD
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “DB_SIZE”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “DB_USED”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “DB_FREE”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;

ACL ON LMS DB

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => ‘adduserwsvint1.xml’,
description => ‘Permissions to access 19 server’,
principal => ‘ MTNIC_LOYALTY_UAT’,
is_grant => TRUE,
privilege => ‘connect’);
END;

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => ‘adduserwsvint1.xml’,
host => ‘ 10.100.1.41’,
lower_port => 8001,
upper_port => 9000);
END;

CHECK ACL CONFIGURED

SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, ‘connect’ AS PRIVILEGE,
DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, ‘connect’), 1,’GRANTED’, 0,’DENIED’, NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)
UNION ALL
SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, ‘resolve’ AS PRIVILEGE,
DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, ‘resolve’), 1,’GRANTED’, 0,’DENIED’, NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);

========== BACKUP SQL DB SERVERS ==========================
DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ‘D:\BKP_DB_SYSTEM\’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’ReportServer’,’ReportServerTempDB’,’AdventureWorks’)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate +’_’ + CONVERT(nvarchar,DAY(GETDATE())) + CONVERT(nvarchar,MONTH(GETDATE())) + CONVERT(nvarchar,YEAR(GETDATE())) +’_’+ CONVERT(nvarchar,datepart(hh,GETDATE())) +’H’+ CONVERT(nvarchar,datepart(mi,GETDATE())) +’Min’+ CONVERT(nvarchar,datepart(SS,GETDATE()))+’s’+ ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

–select name,sum(size) from sys.master_files
–group by name
—
–select name,size from sys.master_files
–order by size desc

========== CHECKING LOG BD =====================================

dbcc errorlog
xp_readerrorlog 1

select * from sys.sysprocesses where blocked<>0

cluadmin.msc
==============================================================

select username||’;’||account_status||’;’||lock_date||’;’||expiry_date||’;’||created||’;’||profile from dba_users;

Requête recherche doublons

select column_name, count(column_name) from table
group by column_name having count (column_name) > 1;

MAP USER TO A LOGIN

USE [DatabaseName]
ALTER USER [UserName]
WITH LOGIN = [UserName]

REMOTE CONNEXION WITH SQLPLUS

$sqlplus sys/oracle@10.2.6.118:1521/lmst as sysdba

http://laetitia-avrot.blogspot.com/2011/10/duplicate-rman-06217-et-rman-04006.html

GATHER TABLE STATS
exec dbms_stats.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’);

exec dbms_stats.gather_table_stats(‘IFSAPP’, ‘RA_INV_PART_VOU_TAB’);

SELECT DISTINCT ‘GRANT SELECT ON ‘||OWNER||’.’||OBJECT_NAME||’ TO SVC_BI;’ from dba_objects where owner=’BILLINGCDRDB’ and (object_type like ‘%TABLE%’ or object_type like ‘%VIEW%’)

SCRIPT GENERATION NOAUDIT STATEMENTS

spool audit_undo.sql

select ‘NOAUDIT ‘||m.name||decode(u.name,’PUBLIC’,’ ‘,’ BY ‘||u.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,’ WHENEVER SUCCESSFUL ‘,
2,’ WHENEVER SUCCESSFUL ‘,
10,’ WHENEVER NOT SUCCESSFUL ‘,
11,’ ‘,
20, ‘ WHENEVER NOT SUCCESSFUL ‘,
22, ‘ ‘,’ /* not possible */ ‘)||’ ;’
“NOAUDIT STATEMENT”
FROM sys.audit$ a, sys.user$ u, sys.stmt_audit_option_map m
WHERE a.user# = u.user# AND a.option# = m.option#
and bitand(m.property, 1) != 1 and a.proxy# is null
and a.user# <> 0
UNION
select ‘NOAUDIT ‘||m.name||decode(u1.name,’PUBLIC’,’ ‘,’ BY ‘||u1.name)||
‘ ON BEHALF OF ‘|| decode(u2.name,’SYS’,’ANY’,u2.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,’ WHENEVER SUCCESSFUL ‘,
2,’ WHENEVER SUCCESSFUL ‘,
10,’ WHENEVER NOT SUCCESSFUL ‘,
11,’ ‘, — default
20, ‘ WHENEVER NOT SUCCESSFUL ‘,
22, ‘ ‘,’ /* not possible */ ‘)||’;’
“AUDIT STATEMENT”
FROM sys.audit$ a, sys.user$ u1, sys.user$ u2, sys.stmt_audit_option_map m
WHERE a.user# = u2.user# AND a.option# = m.option# and a.proxy# = u1.user#
and bitand(m.property, 1) != 1 and a.proxy# is not null
UNION
select ‘NOAUDIT ‘||p.name||decode(u.name,’PUBLIC’,’ ‘,’ BY ‘||u.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,’ WHENEVER SUCCESSFUL ‘,
2,’ WHENEVER SUCCESSFUL ‘,
10,’ WHENEVER NOT SUCCESSFUL ‘,
11,’ ‘, — default
20, ‘ WHENEVER NOT SUCCESSFUL ‘,
22, ‘ ‘,’ /* not possible */ ‘)||’ ;’
“NOAUDIT STATEMENT”
FROM sys.audit$ a, sys.user$ u, sys.system_privilege_map p
WHERE a.user# = u.user# AND a.option# = -p.privilege
and bitand(p.property, 1) != 1 and a.proxy# is null
and a.user# <> 0
UNION
select ‘NOAUDIT ‘||p.name||decode(u1.name,’PUBLIC’,’ ‘,’ BY ‘||u1.name)||
‘ ON BEHALF OF ‘|| decode(u2.name,’SYS’,’ANY’,u2.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,’ WHENEVER SUCCESSFUL ‘,
2,’ WHENEVER SUCCESSFUL ‘,
10,’ WHENEVER NOT SUCCESSFUL ‘,
11,’ ‘, — default
20, ‘ WHENEVER NOT SUCCESSFUL ‘,
22, ‘ ‘,’ /* not possible */ ‘)||’;’
“AUDIT STATEMENT”
FROM sys.audit$ a, sys.user$ u1, sys.user$ u2, sys.system_privilege_map p
WHERE a.user# = u2.user# AND a.option# = -p.privilege and a.proxy# = u1.user#
and bitand(p.property, 1) != 1 and a.proxy# is not null;

select unique
‘– Please correct the problem described in note 455565.1:’
||chr(13)||chr(10)||
‘delete from sys.audit$ where user#=0 and proxy# is null;’
||chr(13)||chr(10)||’commit;’
from sys.audit$ where user#=0 and proxy# is null;

select ‘– Please correct the problem described in bug 6636804:’
||chr(13)||chr(10)||
‘update sys.STMT_AUDIT_OPTION_MAP set option#=234′
||chr(13)||chr(10)||’ where name =”ON COMMIT REFRESH”;’
||chr(13)||chr(10)||’commit;’
from sys.STMT_AUDIT_OPTION_MAP where option#=229 and name =’ON COMMIT REFRESH’;

select
‘– Please correct the problem described in bug 6124447:’
||chr(13)||chr(10)||
‘noaudit truncate;’
from sys.audit$ where option#=155;

select unique ‘– Please correct the problem described in note 1529792.1:’
||chr(13)||chr(10)||
‘insert into javaobj$ select object_id,’
||chr(13)||chr(10)||'(select AUDIT$ from javaobj$ where rownum=1)’
||chr(13)||chr(10)||’ from dba_objects where object_type=”JAVA CLASS”’
||chr(13)||chr(10)||
‘ and status=”VALID” and object_id not in (select obj# from javaobj$);’
||chr(13)||chr(10)||’commit;’
from dba_objects
where object_type=’JAVA CLASS’
and status=’VALID’
and object_id not in (select obj# from javaobj$);

spool off

set heading on

RESOLUTION ERROR RENCONTRE INSTALL DE GRID INFRA
Thank you Ivan for the right inputs , i could complete installation of oracle11g r2 on solaris 10 with out linking errors. ( i didn’t upgrade to solaris10 update 6 as per the oracle documents).

To avoid the ins_network.mk error , edited /u01/app/oracle/product/11.2.0/dbhome_1/network/lib/env_network.mk , searched for string constants with ASFLAGS64 and the line looks like

ASFLAGS64=-P -m64 -K PIC
ASPFLAGS64=-P -xarch=v9 $(PFLAGS)
NOKPIC_ASFLAGS64=-P -xarch=v9

and replaced -m64 with -xarch=v9

ASFLAGS64=-P -xarch=v9 -K PIC
ASPFLAGS64=-P -xarch=v9 $(PFLAGS)
NOKPIC_ASFLAGS64=-P -xarch=v9

linking of ntcontab.o error in ins_network.mk is resolved.

Also for ins_rdbms.mk which is in $ORACLE_HOME/rdbms/lib/env_rdbms.mk , replaced the s to resolve ins_rdbms.mk error
ASFLAGS64=-P -xarch=v9 -K PIC
ASPFLAGS64=-P -xarch=v9 $(PFLAGS)
NOKPIC_ASFLAGS64=-P -xarch=v9

and CONFIG_COMPILE_LINE=$(AS) -P -m64 config.s — line -m64 is also replaced which is shown below
CONFIG_COMPILE_LINE=$(AS) -P -xarch=v9 config.s

it resolved installation issues.
I also got out of memory when using dbca , to overcome that , i created using dbca dbcreation script which is the last step and executed is manually from
.sql .

DUPLICATE SCRIPT FROM ACTIVE DB

run
{DUPLICATE TARGET DATABASE TO “IFST” FROM ACTIVE DATABASE
DB_FILE_NAME_CONVERT (‘/data1/oradata/IFST/DATA/’,’/data/oradata/IFSDB/DATA/’,
‘/u01/oradata/IFST/DATA/’,’/data/oradata/IFSDB/DATA/’, ‘/u02/oradata/IFST/DATA/’,’/data/oradata/IFSDB/DATA/’,
‘/u03/oradata/IFST/DATA/’,’/data/oradata/IFSDB/DATA/’,’/u04/oradata/IFST/DATA/’,’/data/oradata/IFSDB/DATA/’,
‘/u05/oradata/IFST/DATA/’,’/data/oradata/IFSDB/DATA/’)
SPFILE
SET LOG_FILE_NAME_CONVERT ‘/u01/oradata/IFST/REDO/’,’/data/oradata/IFSDB/REDO/’,’/u02/oradata/IFST/REDO/’,’/data/oradata/IFSDB/REDO/’,’/u03/oradata/IFST/REDO/’,’/data/oradata/IFSDB/REDO/’
SET AUDIT_FILE_DEST ‘/opt/app/oracle/admin/IFSDB/adump’
SET DB_CREATE_FILE_DEST ‘/data/oradata/IFSDB/DATA/’
SET DIAGNOSTIC_DEST ‘/opt/app/oracle’
SET CONTROL_FILES ‘/data/oradata/IFSDB/CTL/control01.ctl’,’/data/oradata/IFSDB/CTL/control02.ctl’;
}

CROSSCHECK ARCHIVELOG ALL ;
backup
incremental level 0
#skip inaccessible
#filesperset 6 #shielded by caokun at guangzhou
# recommended format
format ‘/backup/data/back_%s_%p_%T_cbpdb1a’
#AS COMPRESSED backupset
(database);
delete obsolete;
sql ‘alter system archive log current’;
# backup all archive logs
backup
#skip inaccessible
#filesperset 10
format ‘/backup/arch/arclogback_%s_%p_%t_cbpdb1a’
#AS COMPRESSED backupset #shielded by caokun at guangzhou
(archivelog all
delete input);
delete obsolete;
release CHANNEL t1 ;
}
IFSMIG DBVAULT ACCOUNTS
dbv_owner/ oracle_4u
dbv_acctmgr/ oracle_4u
ifsapp/ ifsapp123$

1- login as: hartmann
2- Pwd ?
3- hartmann@omu0:~> su
4- PWD : root pwd
5- omu0:/export/home/hartmann # cd..
6- omu0:/export/home # cd..
7- omu0:/export # cd..
8- omu0:/ # ssh uscdbmt@172.16.128.80
9- uscdbmt@172.16.128.80’s password:Huawei@2009
10- uscdbmt@rac1:~> su – oracle
11- Password:huawei

SQL SERVER – BIG TABLES

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable ‘sp_spaceused ”?”’
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ‘ KB’, ”) AS integer) DESC
DROP TABLE #temp

UPDATE STATISTICS [dbo].[CCBS_DATA_ROAMING]

DECONFIGURE GRID INFRA INSTALLATION

Cf Doc ID 1377349.1

# <$GRID_HOME>/crs/install/rootcrs.pl -deconfig -force -verbose

Once the above command finishes on all remote nodes, on local node, as root execute:
# <$GRID_HOME>/crs/install/rootcrs.pl -deconfig -force -verbose -lastnode

CONFIGURE LINUX NETWORK
#ifup
#setup
#vi /etc/hosts
#vi /etc/sysconfig/network

CREATE INSTANCE ON WINDOWS WITH ORADIM
c:
cd C:\oracle\product\11.2.0\dbhome_1\BIN
oradim -new -sid emctest -intpwd oracle -maxusers 20 startmode auto -pfile C:\oracle\product\11.2.0\dbhome_1\database\SPFILEEMCTEST.ORA

oradim -new -sid emctest -intpwd oracle -startmode A -maxusers 100 -pfile C:\oracle\ora81\database\initPROD.ora -timeout 60

oradim -new -sid emctest -intpwd oracle -startmode A -maxusers 100 -pfile C:\oracle\product\11.2.0\dbhome_1\database\SPFILEEMCTEST.ORA -timeout 60#vi /etc/resolv.conf

RESOLVE OMS ISSUE ON CLOUD CONTROL

$AGENT_HOME/bin/emctl start agent

$OMS_HOME/bin/emctl stop oms -all

$OMS_HOME/bin/emctl start oms

$OMS_HOME/bin/emctl stop oms -all

$OMS_HOME/bin/emctl start oms

$OMS_HOME/bin/emctl stop oms

$OMS_HOME/bin/emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd oracle -new_pwd oracle

$OMS_HOME/bin/emctl stop oms -all

$OMS_HOME/bin/emctl start oms

OEM videos

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=493312287969395&id=1615382.1&_afrWindowMode=0&_adf.ctrl-state=6q3x3213l_4 VIDEO CLOUD 12C

Oracle Patchsets

https://community.oracle.com/community/support/support-blogs/database-support-blog/blog/2014/10/21/oracle-database-12c-release-1-patchset-12102-availability

SCRIPT CHECKING MOMO DB SERVER

#!/bin/bash
ORACLE_SID=fdCOREP ; export ORACLE_SID
ORACLE_HOME=/opt/oracle/product/10gR2; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/usr/lib ; export LD_LIBRARY_PATH
PATH=$PATH:/opt/oracle/product/10gR2/bin; export PATH

cd /opt/oracle/dba/scripts

echo “—– CHECKING MOBILE MONEY PROD DB ——– ” > log

echo ” ” >> log
echo “—– 1.Partition OS ————– ” >> log
df -h / /opt /home >> log
echo ” ” >> log

echo “—– 2.Backup via EXPORT ————– ” >> log
echo ” ” >> log
echo “BD FDCORE ” >> log
ls -ltrh /future_use/ora_backup/export/FDCORE/* >> log
echo ” ” >> log
tail -1 /future_use/ora_backup/export/FDCORE/*.log >> log
echo ” ” >> log

echo “BD FDFSS ” >> log
ls -ltrh /future_use/ora_backup/export/FDFSS/* >> log
echo ” ” >> log
tail -1 /future_use/ora_backup/export/FDFSS/*.log >> log
echo ” ” >> log
echo ” ” >> log

echo “—– 3. Backup via NBU ————– ” >> log
echo ” ” >> log

echo “Last backups ” >> log
echo ” ” >> log
ls -ltrh /usr/openv/netbackup/ext/db_ext/oracle|tail -2 >> log
echo ” ” >> log

echo “FDCORE Backup type :” >> log
ls -ltrh /usr/openv/netbackup/ext/db_ext/oracle/nbu_oracle_backup_DB_fdCOREP*|tail -1|awk ‘{print $9}’|while read line; do more $line|grep NB_ORA_INCR;done >> log
echo ” ” >> log

echo “FDCORE Backup status :” >> log
echo ” ” >> log
ls -ltrh /usr/openv/netbackup/ext/db_ext/oracle/nbu_oracle_backup_DB_fdCOREP*|tail -1|awk ‘{print $9}’|while read line; do more $line|tail -5 ;done >> log
echo ” ” >> log

echo “FDCORE Backup size : ” >> log
echo ” ” >> log
sh /opt/oracle/dba/scripts/rman_fdcorep_report.sh|tail -6|head -4 >> log
echo ” ” >> log
echo ” ” >> log
echo “FDFSS Backup type :” >> log
ls -ltrh /usr/openv/netbackup/ext/db_ext/oracle/nbu_oracle_backup_DB_fdFSS*|tail -1|awk ‘{print $9}’|while read line; do more $line|grep NB_ORA_INCR;done >> log
echo ” ” >> log

echo “FDFSS Backup status :” >> log
echo ” ” >> log
ls -ltrh /usr/openv/netbackup/ext/db_ext/oracle/nbu_oracle_backup_DB_fdFSS*|tail -1|awk ‘{print $9}’|while read line; do more $line|tail -5 ;done >> log
echo ” ” >> log

echo “FDFSS Backup size : ” >> log
echo ” ” >> log
sh /opt/oracle/dba/scripts/rman_fdfssrep_report.sh|tail -6|head -4 >> log
echo ” ” >> log
echo ” ” >> log

echo “—– 4. Dataguard checking ————– ” >> log
echo ” ” >> log
echo “BD FDCORE DR ” >> log
echo ” ” >> log
sh /opt/oracle/dba/scripts/check_fdcore_dr_logstby.sh|tail -6|head -4 >> log
echo ” ” >> log
echo ” ” >> log

echo “—– 5. PROD – ASM DISK checking ————– ” >> log
echo ” ” >> log
cat /home/grid/dba/scripts/check_asm_log >> log
echo ” ” >> log

cat log|mailx -s “MOMO CHECKING” DB_Systems@mtn.ci

cat /opt/oracle/dba/scripts/check_fdcore_dr_logstby.sh
sqlplus system/user_password@FDCORER << EOF

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

exit;
EOF

As grid user

00 05 * * * sh /home/grid/dba/scripts/check_asm.sh > /home/grid/dba/scripts/check_asm_log

grid>cat /home/grid/dba/scripts/check_asm.sh

#!/bin/bash
ORACLE_SID=+ASM ; export ORACLE_SID
ORACLE_BASE=/opt/grid ; export ORACLE_BASE
ORACLE_HOME=/opt/grid/11.2 ; export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/lib32:/usr/lib:/usr/ccs/bin:$ORACLE_HOME/bin:/usr/bin:/usr/ucb:. ; export PATH

asmcmd << EOF
lsdg
cd FRA
cd FDCOREP
cd AR*
ls
exit
EOF

HOW TO DETERMINE NUMBER OF CPU AND CORE

UNLOCK SQL USER

alter login RAUSER with check_policy=off

RESTAURER LES PARTITIONS ANTERIEURES A 201406 (JUSQU’A 201212) DE LA TABLE HUAWEI_GSM.CELLSTATS60

ALTER TABLE HUAWEI_GSM.CELLSTATS60
SPLIT PARTITION P_201406 AT (TO_DATE(‘ 2016-06-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION P_201405,
PARTITION P_201406)
UPDATE GLOBAL INDEXES;

ALTER TABLE HUAWEI_GSM.CELLSTATS60
SPLIT PARTITION P_201405 AT (TO_DATE(‘ 2016-05-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION P_201404,
PARTITION P_201405)
UPDATE GLOBAL INDEXES;

………

ALTER TABLE HUAWEI_GSM.CELLSTATS60
SPLIT PARTITION P_201301 AT (TO_DATE(‘ 2013-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION P_201212,
PARTITION P_201301)
UPDATE GLOBAL INDEXES;

EXEC DBMS_STATS.gather_table_stats(‘HUAWEI_GSM’, ‘CELLSTATS60’, cascade => TRUE);

nohup impdp exporter/****** directory=BACKUP dumpfile=FACTS_TABLES_26122014.dmp parfile=impfile CONTENT=DATA_ONLY logfile=impcel60_av0614.log &

avec contenu de impfile :

tables=(
HUAWEI_GSM.CELLSTATS60:P_201212,
HUAWEI_GSM.CELLSTATS60:P_201301,
HUAWEI_GSM.CELLSTATS60:P_201302,
HUAWEI_GSM.CELLSTATS60:P_201303,
HUAWEI_GSM.CELLSTATS60:P_201304,
HUAWEI_GSM.CELLSTATS60:P_201305,
HUAWEI_GSM.CELLSTATS60:P_201306,
HUAWEI_GSM.CELLSTATS60:P_201307,
HUAWEI_GSM.CELLSTATS60:P_201308,
HUAWEI_GSM.CELLSTATS60:P_201309,
HUAWEI_GSM.CELLSTATS60:P_201310,
HUAWEI_GSM.CELLSTATS60:P_201311,
HUAWEI_GSM.CELLSTATS60:P_201312,
HUAWEI_GSM.CELLSTATS60:P_201401,
HUAWEI_GSM.CELLSTATS60:P_201402,
HUAWEI_GSM.CELLSTATS60:P_201403,
HUAWEI_GSM.CELLSTATS60:P_201404,
HUAWEI_GSM.CELLSTATS60:P_201405)

Résolution pb ASM après redémarrage serveur CLF TT

[root@SVR-CLFBIDB-01 soft]# vi /etc/sysconfig/oracleasm
# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER=”dm”

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE=”sda”
[root@SVR-CLFBIDB-01 soft]# /etc/init.d/oracleasm restart

grid> . oraenv
+ASM

Sqlplus / as sysasm

SQL> startup

SHRINK TABLE

SQL> alter table mytable enable row movement;
Table altered

SQL> alter table mytable shrink space cascade;
Table altered

=========== ORACLE PATCHSET =======================

p10404530_112030_platform_1of7.zip

p10404530_112030_platform_2of7.zip

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Oracle Grid Infrastructure (includes Oracle ASM, Oracle Clusterware, and Oracle Restart)

p10404530_112030_platform_3of7.zip

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Oracle Database Client

p10404530_112030_platform_4of7.zip

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Oracle Gateways

p10404530_112030_platform_5of7.zip

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Oracle Examples

p10404530_112030_platform_6of7.zip

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Deinstall

p10404530_112030_platform_7of7.zip

====== ORACLE : HOW TO FIND PATCH / PATCHSET ================
How To Find and Download The Latest Patchset and Associated Patch Number For Oracle Database Release (Doc ID 330374.1) To Bottom

________________________________________

Applies to:
Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 10.2.0.3
Information in this document applies to any platform.
Goal

This document explains

1.How to quickly obtain information about Latest Patchsets for
different platforms and get the associated patch numbers to download from Metalink.

2.How to view certifications

3.How to download the patch

4.How to find a patchset

5.Locations of Installation Media

Solution

IMPORTANT NOTES:-

CHECK THE ORACLE SOFTWARE BIT VERSIONS BEFORE DOWNLOADING PATCH/PATCHSET.

For 32 bit Software on 64 bit Operating System, you need to download patch/patchset for 32 bit platform.

(i.e) For 32 bit Oracle on 64 bit Solaris ,Download patchset for 32 bit Solaris.

To identify Oracle Bit version in Unix,Use “file” command.

file $ORACLE_HOME/bin/oracle

– Client-Only installations will need to use the Server patchset to upgrade to
the next release.

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

1.How To Obtain Latest Patchset Information For a Platform

o Login to Metalink

o Click on Patches & Updates

o Click on “Quick Links to the Latest Patchsets, Mini Packs, and Maintenance Packs”

o Position Mouse pointer on Oracle Database and Scroll through the platforms to search your platform and follow the link to check the patchset number.

o Click on the Patchset Number .This leads to that page where you can download the patchset.

o Follow the Readme Instructions to Apply the Patchset.

2.How To View Certifications

o Login to Metalink

o Click on “Certify”

o Click on ‘View Certifications by Platform”

o Select your Platform . e.g. “Microsoft Windows 2003 x86”

o Click “Submit”

o Select “Database/Server”

o Select Edition e.g. “Oracle Database – Standard Edition”

o Click “Submit”

3.How To Download Patches or Patchsets When the Patch or Patchset number is Known

Follow the steps:

o Login to Metalink

o Click on “Patches & Updates” button on left panel

o Click on “Simple Search”

o “Patch Number” –> < e.g. give the patch number as 3959430 >

o Select your platform “Solaris Operating System (SPARCS 32-bit)”

o Click on ” Go ” Button

o Select the Release for which patch is required. e.g. 10.1.0.4

o You can download the Patch No 3959430

o Do go through the “ReadMe” before applying the patch.

Ensure about the platform.
The ReadMe of the patch has the instructions to apply the patchset/patch.

4.How To Find and Download a Patchset

Follow the steps:

o Login to Metalink

o Click on “Patches & Updates” button on left panel

o Click on “Advanced Search”

o “Product or Product Family”, click flashlight icon

o “Search in”, select “Database & Tools” then click “Go”

o Scroll down to “RDBMS Server”. Select it.

o “Release”.Select the Release for which patchset is required. e.g. 10.1.0.4

o “Platform or Language”, select the appropriate platform.

o “Patch Type”, select “Patchset/Minipack”.

o Click on “Go”

5. Installation Media Sources

o edelivery.oracle.com

o otn.oracle.com

Ensure about the platform.
The ReadMe of the patchset has the instructions to apply the patchset.

11g
Patch 13390677 – 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
Patch 10404530 – 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER
Patch 10098816 – 11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER
Patch 6890831 – 11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER

10g
Patch 8202632 – 10.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER
Patch 6810189 – 10.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER
Patch 5337014 – 10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
Patch 4547817 – 10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
Patch 4505133 – 10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER
Patch 4163362 – 10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER
Patch 3761843 – 10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER
9i
Patch 4547809 – 9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER
Patch 4163445 – 9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER
Patch 3948480 – 9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER
Patch 3501955 – 9.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER
Patch 3095277 – 9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER
Patch 2761332 – 9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
Patch 2632931 – 9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
Patch 3301544 – 9.0.1.5 PATCH SET FOR ORACLE DATABASE SERVER
Patch 2517300 – 9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER
8i
Patch 2376472 – 8.1.7.4 PATCH SET FOR ORACLE DATA SERVER
Patch 2189751 – 8.1.7.3 PATCH SET FOR ORACLE DATA SERVER
Patch 1909158 – 8.1.7.2 PATCH SET FOR ORACLE DATA SERVER

EXPECT AND SFTP
#!/usr/bin/expect
set timeout -1
set user username
#set pass The_passwd
set host 10.100.43.10
spawn sftp username @10.100.43.10

expect Password:
send ” The_passwd\r”
expect sftp>

send “lcd /home/oracle/wangbin/table\r”
expect sftp>

send “cd /home/sysomc/export_cfg/temp\r”

send “mput *.unl\r”
send “quit\r”
expect sftp>
send “exit\r”
expect eof

explain plan for
SELECT SUM(AMOUNT) FROM fundamo.ENTRY WHERE ACCOUNT_OID = 112420431676040008 index_entry001
AND ENTRY_DATE >= TO_TIMESTAMP(‘2014-12-29 00:00:00.000’ ,
‘YYYY-MM-DD HH24:MI:SS.FF3’);
select * from table(dbms_xplan.display);

RMAN – CHECKING PROGRESS

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,sysdate + TIME_REMAINING/3600/24 end_at from v$session_longops where totalwork > sofar AND opname NOT LIKE ‘%aggregate%’ AND opname like ‘RMAN% ;

Checking FRA Usage :

SQL> select FILE_TYPE,PERCENT_SPACE_USED from v$flash_recovery_area_usage where FILE_TYPE like ‘ARCHIVED%’;

— Create windows user with SQLCMD and sysadmin role

sqlcmd -S MYSQLSERVER -E

PRINT SUSER_NAME();
GO

CREATE LOGIN [MTNCI\SVC_SQLBACKUP] FROM WINDOWS

EXEC master..sp_addsrvrolemember @loginame = N’MTNCI\SVC_SQLBACKUP’, @rolename = N’sysadmin’

========== EXTRACT DATA FOR CRM FOR SUBEX ===================
[CRMDB2][/home/oracle/dba/scripts]$cat extract4subex.sh

DATE=`date +%d%m%Y-%Hh%Mmn`; export DATE
ORACLE_SID=crmdb2 ;export ORACLE_SID
ORACLE_HOME=/opt/oracle/product/11gR2/db ;export ORACLE_HOME
ORACLE_BASE=/home/oracle; export ORACLE_BASE
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib ;export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH;export PATH
DIR=/home/oracle/dba/scripts;export DIR

cd /backup/subex/
rm -f *.txt *.zip

sqlplus “/as sysdba” @$DIR/extract4subex.sql

zip -9 crm_extract_$DATE.zip *.txt

#scp crm*.zip svc.trans@10.18.35.11:/rocfm_binaries/FMSROOT/FMSData/DataSourceCBP/

[CRMDB2][/home/oracle/dba/scripts]$cat extract4subex.sql
#——— 1. TABLE HIS_ORDER_SVRRESOURCECHG ————–
set trimspool on;
set termout off;
set verify off;
set pagesize 0;
set feedback off heading off space 0 echo off trimout on
set trimspool on lines 120 linesize 32767 pages 0 long 1000000

spool HIS_ORDER_SVRRESOURCECHG.txt

select order_no||’|’||res_type_id from ccare.his_order_svrresourcechg ;

spool off;

#——— 2. TABLE HIS_ORDERINFO —————–
set trimspool on;
set termout off;
set verify off;
set pagesize 0;
set feedback off heading off space 0 echo off trimout on
set trimspool on lines 120 linesize 32767 pages 0 long 1000000

spool HIS_ORDERINFO.txt

select order_no||’|’||busi_seq||’|’||sub_id||’|’||to_char(order_create_date,’YYYYMMDDHHMMSS’)||’|’||msisdn from CCARE.HIS_ORDERINFO where TRUNC (order_create_date) = TRUNC (SYSDATE – 1);

spool off;

#——— 3. TABLE INF_BUSINESSINFO —————–
set trimspool on;
set termout off;
set verify off;
set pagesize 0;
set feedback off heading off space 0 echo off trimout on
set trimspool on lines 120 linesize 32767 pages 0 long 1000000

spool INF_BUSINESSINFO.txt

select busi_seq from CCARE.INF_BUSINESSINFO where TRUNC (busi_date) = TRUNC (SYSDATE – 1);

spool off;

#——— 4. TABLE INF_CONTACT_PERSON ——————-
set trimspool on;
set termout off;
set verify off;
set pagesize 0;
set feedback off heading off space 0 echo off trimout on
set trimspool on lines 120 linesize 32767 pages 0 long 1000000

spool INF_CONTACT_PERSON.txt

select CUST_ID||’|’||RELA_TEL1||’|’||RELA_TEL2||’|’||to_char(CREATE_DATE,’YYYYMMDDHHMMSS’ ) from ccare.INF_CONTACT_PERSON;

spool off;

exit;
[CRMDB2][/home/oracle/dba/scripts]$

Exécution RDA
$ perl rda.pl -T hcve

RESAURATION DE PARTITIONS SUPPRIMEES

Utiliser la commande ALTER ci-dessous pour splitter la partition supérieure P20150814_CDRS en deux partitions P20150813_CDRS et P20150814_CDRS
ALTER TABLE pm_prod.pm_rated_cdrs0 SPLIT PARTITION P20150814_CDRS AT (TO_DATE(‘ 2015-08-14 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) INTO (PARTITION P20150813_CDRS,PARTITION P20150814_CDRS) UPDATE GLOBAL INDEXES;

Script à utiliser pour la restauration :
$impdp exporter/password parfile=imp.txt
Avec le contenu ci-dessous du fichier paramètre imp.txt :
DIRECTORY=RESTO
DUMPFILE=wbs1_full.dmp
LOGFILE=imp.log
TABLE_EXISTS_ACTION=APPEND
DATA_OPTIONS=skip_constraint_errors
tables= (
PM_PROD.PM_RATED_CDRS0:P20150813_CDRS,
PM_PROD.PM_RATED_CDRS0:P20150812_CDRS,
….
PM_PROD.PM_RATED_CDRS0:P20150801_CDRS)

DISKS IN ASM DISKGROUPS

COLUMN disk_group_name FORMAT a25 HEAD ‘Disk Group Name’
COLUMN disk_file_path FORMAT a20 HEAD ‘Path’
COLUMN disk_file_name FORMAT a20 HEAD ‘File Name’
COLUMN disk_file_fail_group FORMAT a20 HEAD ‘Fail Group’
COLUMN total_mb FORMAT 999,999,999 HEAD ‘File Size (MB)’
COLUMN used_mb FORMAT 999,999,999 HEAD ‘Used Size (MB)’
COLUMN pct_used FORMAT 999.99 HEAD ‘Pct. Used’

BREAK ON report ON disk_group_name SKIP 1

COMPUTE sum LABEL “” OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL “Grand Total: ” OF total_mb used_mb ON report

SELECT NVL(a.name, ‘[CANDIDATE]’) disk_group_name, b.path disk_file_path, b.name disk_file_name, b.failgroup disk_file_fail_group, b.total_mb total_mb,(b.total_mb – b.free_mb) used_mb, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) ORDER BY a.name,b.path ;

RA – Shrink SQL Data files
EXEC sp_helpfile
DBCC SHRINKFILE (RA_RECON_PP_POS)

DECONNEXION FUNDAMO USERS

select * from login001 where username=’xxxxxxxxx’;

update fundamo.login001 set LOGGED_IN_CHANNELS =’0′ where username =’xxxxxxxxx’;

commit;

LMS PURGE OLD TABLES

De : Jothiram Madhavan [mailto:Jothiram.Madhavan@tecnotree.com]
Envoyé : mercredi 19 novembre 2014 10:20
À : KOUAME Olivier Innocent [MTN – Côte d’Ivoire]; N’ZI Jules Kouassi [MTN – Côte d’Ivoire]
Cc : DB_Systems; DL-SU-Loyalty.India; EAS_CRM; KOUAME Serge Landry Martial [MTN – Côte d’Ivoire]
Objet : RE: Ivory Coast Table space

Hi Oliver,

Please find the below requested details.

OWNER SEGMENT Duration Comments TYPE SIZE (MB)
MTNIC_LOYALTY_PROD LYT_TMP_EVENT_DATA 1 Month Records to maintain Partitions Older than 1 month can be Purged TABLE PARTITION 21 018
MTNIC_LOYALTY_PROD LYT_DUPLICATE_CHECK_DATA 15 Days Records to maintain Partitions Older than 1 month can be Purged TABLE PARTITION 68 011
MTNIC_LOYALTY_PROD LYT_INTERFACE_LOG 15 Days Records to maintain Partitions Older than 1 month can be Purged TABLE PARTITION 36 439
MTNIC_LOYALTY_PROD LYT_CYCLE_SUM_DETAILS_060214 Backup Tables can be dropped This is backup table can be dropped TABLE 22 360
MTNIC_LOYALTY_PROD FEC_RESPONSE_BKP_16MAY Backup Tables can be dropped This is a backup table can be dropped TABLE 16 969
MTNIC_LOYALTY_PROD FEC_RESPONSE_17 Backup Tables can be dropped This is a backup table can be dropped TABLE 16 888
MTNIC_LOYALTY_PROD BK_INTERFACE_LOG Backup Tables can be dropped This is a backup table can be dropped TABLE 10 757
MTNIC_LOYALTY_PROD DUPLICATE_CHECK$UI No Action Required No Action Required INDEX 97 464
MTNIC_LOYALTY_PROD LYT_EVENT_DATA No Action Required No Action Required TABLE PARTITION 49 511
MTNIC_LOYALTY_PROD LYT_CYCLE_SUMMARY_DETAILS No Action Required No Action Required TABLE 34 989
MTNIC_LOYALTY_PROD CYCLE_SUMMARY_DTLS$UI No Action Required No Action Required INDEX 30 085
MTNIC_LOYALTY_PROD EVENT_DATA$UI No Action Required No Action Required INDEX 21 822
MTNIC_LOYALTY_PROD EVENT_DATA$I1 No Action Required No Action Required INDEX 17 104
MTNIC_LOYALTY_PROD DUPLICATE_CHECK$I1 No Action Required No Action Required INDEX 11 036
MTNIC_LOYALTY_PROD FEC_RESPONSE No Action Required No Action Required TABLE 16 982

Thanks,
Jothiram

DROP OLD PARTITIONS IN LMS

ALTER TABLE MTNIC_LOYALTY_PROD.LYT_INTERFACE_LOG DROP PARTITION SYS_P34008;

LMS PARTITION TABLES ————————-

De : Jogi George [mailto:Jogi.George@tecnotree.com]
Envoyé : lundi 20 octobre 2014 13:49
À : BEUGRE Aubin Daple Joseph [MTN – Côte d’Ivoire]; Kanika Mary Christina; N’ZI Jules Kouassi [MTN – Côte d’Ivoire]; KOUAME Olivier Innocent [MTN – Côte d’Ivoire]; Amar Sathwik; Suchitra A S.
Cc : DB_Systems; EAS_CRM; Hare Krushna Montry; KOUAME Serge Landry Martial [MTN – Côte d’Ivoire]; GNAMIEN Adou Affolo Theophile [MTN – Côte d’Ivoire]; Raghavachar Ranganatha Ramanathapura Srinivasa; Jothiram Madhavan; Rajashekhar B G; Lakshmana Rao Golla; support@dbassist.ci; ‘K. SORO’; Vidyasankar G
Objet : RE: PARTITIONS ON LMS DB

++ Script Doc Attached

Hi Oliver,

We have created partitions for given tables for November-2014 in MTNIC Production Database.

Please find scripts given below to create partitions for Dec-2014.
Same script can be modified and used hereafter for subsequent months with appropriate changes in partition name and dates marked in red.

To create partitions of Dec’14 data you can run below scripts:

JANUS_LOYALTY_INTERFACE_LOGS This table is not used
LYT_CALCULATION_LOG
ALTER TABLE MTNIC_LOYALTY_PROD..LYT_CALCULATION_LOG ADD PARTITION PROCESS_DATE_201501010000 VALUES LESS THAN (TO_DATE(‘2015-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LYT_CAMP_EXECUTION_DTLS alter table MTNIC_LOYALTY_PROD.LYT_CAMP_EXECUTION_DTLS ADD PARTITION PROCESS_DATE_D_201501010000 VALUES LESS THAN (TO_DATE(‘2015-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LYT_DUPLICATE_CHECK_DATA alter table MTNIC_LOYALTY_PROD.LYT_DUPLICATE_CHECK_DATA ADD PARTITION EVENT_DATA_201501010000 VALUES LESS THAN (TO_DATE(‘2015-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LYT_EMAIL#LETTER_DATA alter table MTNIC_LOYALTY_PROD.LYT_EMAIL#LETTER_DATA ADD PARTITION LYT_EMAIL_201501010000 VALUES LESS THAN (TO_DATE(‘2015-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LYT_ERROR alter table MTNIC_LOYALTY_PROD.LYT_ERROR ADD PARTITION DATE_201501010000 VALUES LESS THAN (TO_DATE(‘2015-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LYT_EVENT_DATA alter table MTNIC_LOYALTY_PROD.LYT_EVENT_DATA ADD PARTITION EVENT_DATA_201501010000 VALUES LESS THAN (TO_DATE(‘2015-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LYT_SMS_MESSAGE_QUEUE alter table MTNIC_LOYALTY_PROD.LYT_SMS_MESSAGE_QUEUE ADD PARTITION LYT_SMS_201501010000 VALUES LESS THAN (TO_DATE(‘2015-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LYT_TRANS_POINTS alter table MTNIC_LOYALTY_PROD.LYT_TRANS_POINTS ADD PARTITION TRANS_DATE_201501010000 VALUES LESS THAN (TO_DATE(‘2015-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
LYT_USAGE_DETAILS_HISTORY alter table MTNIC_LOYALTY_PROD.LYT_USAGE_DETAILS_HISTORY ADD PARTITION USAGE_DTLS_HIST201501010000 VALUES LESS THAN (TO_DATE(‘2015-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))

Regards,
Jogi George
————————————————————————————–

RECREATE SYNONYMS AFTER IMPORT AND REMAP_SCHEMA

SELECT ‘CREATE PUBLIC SYNONYM’ || synonym_name || ‘ FOR ‘ || table_owner || ‘.’ || table_name|| ‘;’ from dba_synonyms where TABLE_OWNER=”schema_name” and OWNER=’PUBLIC’;

SELECT ‘CREATE OR REPLACE SYNONYM TST_CLIENT.’ || synonym_name || ‘ FOR TST_PROD.’ || table_name|| ‘;’ from dba_synonyms where TABLE_OWNER=’PM_PROD’ and OWNER=’PM_CLIENT’;

WBS USER ACCESS RIGHT ISSUE

Insert into PM_PROD.PM_COMPANIES (COMPANY_ID,DB_USER,DESCRIPTION,UTC,TZ_NAME,TZ_ABBREVIATION,LAST_MODIFIED_BY,LAST_MODIFIED_ON,AUDIT_TRAIL_ID) values (1,’SVC_RAWBS’,’4.2 Client User’,’+00:00′,’Africa/Kampala’,’EAT’,’Administrator’,to_timestamp_tz(’11-05-19 15:15:19.000000000 +05:30′,’RR-MM-DD HH24:MI:SS.FF TZR’),’1′);

Commit;

COPIE DEPUIS UN SERVER VIA SCP

scp oracle@ip_source:full_path_file_source file_dest

scp oracle@10.18.33.15:/backup/clean.sh ifs_file

EXECUTION // DE REQUETES

select /*+ parallel(s,16) */ * from table table_name s

RDA EXECUTION – DB CHECKING

Dezipper le package de rda et se connecter au rép rda
$perl rda.pl -T hcve

WBS PURGE OLD DATA

TABLE_NAME DURATION TO KEEP
(ONLINE DATA) PERIODICITY OF
ARCHIVAL ARCHIVAL
IDENTIFIER COMMENTS
PM_RATED_CDRS 6 Months Monthly Range partition on CALL_DATE 6 Months of online data should be kept in production DB & remaining data should be kept in DWH. Partitions older than 6 months should be dropped.
PM_REJECTED_CDRS 6 Months Monthly Range partition on CALL_DATE 6 Months of online data should be kept in production DB & remaining data should be kept in DWH. Partitions older than 6 months should be dropped.
PM_SUMMARY 6 Months Monthly Range partition on CALL_DATE 6 Months of online data should be kept in production DB & remaining data should be kept in DWH. Partitions older than 6 months should be dropped.
PM_TAP_CDRS 6 Months Monthly Range partition on CALL_DATE 6 Months of online data should be kept in production DB & remaining data should be kept in DWH. Partitions older than 6 months should be dropped.
PM_DROPPED_CDRS 6 Months Monthly Range partition on CALL_DATE 6 Months of online data should be kept in production DB & remaining data should be kept in DWH. Partitions older than 6 months should be dropped.
PM_REPRICE_CDRS 6 Months Monthly Nothing (Not Partitioned) 6 Months of online data should be kept in production DB & remaining data should be kept in DWH. Data older than 6 months should be dropped.
PM_TAP_FILE_SUMRY 6 Months Monthly Nothing (Not Partitioned) 6 Months of online data should be kept in production DB & remaining data should be kept in DWH. Data older than 6 months should be dropped.
PM_REPRICE_FILES 6 Months 6 Months of online data should be kept in production DB & remaining data should be kept in DWH. Data older than 6 months should be dropped
PM_PRERATING_LOGS 6 Months 6 Months of online data should be kept in production DB & remaining data should be kept in DWH. Data older than 6 months should be dropped

Data Base Maintenance activities:

1) Periodical stats gathering on Schema Objects
a) Daily Incremental gather stat
b) Weekend full gather stat

2) Rebuild of fragmented indexes
3) Archival document for the above mentioned WBS tables should be archived every month .

PURGE NEON ——————————————————————
Hi Olivier/Jules,
Please find the purging script for MTN-CIV in the below location and steps for Purging.

/home/oracle/PURGE_11JAN16
Steps to follow :

1. Make down the application completely (@Jules please make jboss down on all the servers).
2. Login to database server (10.18.33.33) with username and password.(Already shared)
3. cd /home/oracle/PURGE_11JAN16
4. nohup sh purge.sh &
5. Check tail -f nohup.out ( If the message ‘Disconnect from Oracle’ is found then check the log file ‘purge.log’ for any errors).
6. Check tail –f invalid_objects.log (If any data is fetched, please escalate to DBA Team)
——————————————————————

WBS – POLICY POUR ACCES AUX TABLES DE WBS

Ligne à créer dans une table particulière de WBS pour autoriser le SELECT:

BEGIN dbms_stats.gather_table_stats
(OWNNAME=>’PM_PROD’,
TABNAME=>’PM_SUMMARY0′,
METHOD_OPT=> ‘FOR ALL LOCAL INDEXES’,
DEGREE=> 20,
CASCADE=> TRUE);
END;

Enable and generate db audit in xml files

GATHER STATS COMMANDS

exec dbms_stats.gather_system_stats(‘start’);
exec dbms_stats.gather_system_stats(‘stop’);
exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
exec DBMS_STATS.GATHER_DICTIONARY_STATS;

PURGE CMS TABLES

select ‘DROP TABLE CMS_ANALYSIS.’||segment_name||’;’ from dba_segments where owner=’CMS_ANALYSIS’ and tablespace_name=’CMS_DATA’ and segment_name like ‘TAB%DEC15’ order by 1

PAYMENT_TRANSACTION_TAB, ACCOUNTING_CODE_PART_VALUE_TAB ET ACCOUNTING_PERIOD_TAB

Options de montage nfs pour oracle
mount -t nfs -o rw,bg,hard,rsize=32768,wsize=32768,actimeo=0,nointr,timeo=600,tcp

IFS – RESTAURATION SCHEMA IFSAPP SUR IFSMIG

NB : Privilèges à octroyer à IFSAPP après import du schéma IFSAPP sur IFSMIG
1/
select ‘grant execute on SYS.’||object_name||’ to IFSAPP;’ from dba_objects where owner=’SYS’ and object_type like ‘%PACKAGE%’
2/ Grant des roles et privileges systèmes à IFSAPP (Retirer les ‘REVOKE’)

ARCHIVE LOGS : QTE GENEREE SUR UNE PERIODE

SELECT
to_char(first_time,’DD-MON-YYYY’) day,
to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’9999′) “00”,
to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’9999′) “01”,
to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’9999′) “02”,
to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’9999′) “03”,
to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’9999′) “04”,
to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’9999′) “05”,
to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’9999′) “06”,
to_char(sum(decode(to_char(first_time,’HH24′),’07’,1,0)),’9999′) “07”,
to_char(sum(decode(to_char(first_time,’HH24′),’08’,1,0)),’9999′) “08”,
to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’9999′) “09”,
to_char(sum(decode(to_char(first_time,’HH24′),’10’,1,0)),’9999′) “10”,
to_char(sum(decode(to_char(first_time,’HH24′),’11’,1,0)),’9999′) “11”,
to_char(sum(decode(to_char(first_time,’HH24′),’12’,1,0)),’9999′) “12”,
to_char(sum(decode(to_char(first_time,’HH24′),’13’,1,0)),’9999′) “13”,
to_char(sum(decode(to_char(first_time,’HH24′),’14’,1,0)),’9999′) “14”,
to_char(sum(decode(to_char(first_time,’HH24′),’15’,1,0)),’9999′) “15”,
to_char(sum(decode(to_char(first_time,’HH24′),’16’,1,0)),’999′) “16”,
to_char(sum(decode(to_char(first_time,’HH24′),’17’,1,0)),’9999′) “17”,
to_char(sum(decode(to_char(first_time,’HH24′),’18’,1,0)),’999′) “18”,
to_char(sum(decode(to_char(first_time,’HH24′),’19’,1,0)),’9999′) “19”,
to_char(sum(decode(to_char(first_time,’HH24′),’20’,1,0)),’9999′) “20”,
to_char(sum(decode(to_char(first_time,’HH24′),’21’,1,0)),’9999′) “21”,
to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’9999′) “22”,
to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’9999′) “23”,
count(*) Tot
from
v$log_history
WHERE first_time > sysdate -7
GROUP by
to_char(first_time,’DD-MON-YYYY’),trunc(first_time) order by trunc(first_time);

SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24:MI’) snap_time,
dhsso.owner, dhsso.object_name,sum(db_block_changes_delta) as maxchanges
FROM dba_hist_seg_stat dhss,dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj# AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time BETWEEN to_date(‘2016-09-07 00:00:00′,’YYYY-MM-DD HH24:MI:SS’)
AND to_date(‘2016-09-07 10:40:00′,’YYYY-MM-DD HH24:MI:SS’)
GROUP BY begin_interval_time,
dhsso.owner, dhsso.object_name order by maxchanges asc;

SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24:MI’),
dbms_lob.substr(sql_text,5000,1),
dhss.instance_number,
dhss.sql_id,executions_delta,rows_processed_delta
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE ‘%YRHOSTVOL%’
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND begin_interval_time BETWEEN to_date(‘2016-09-07 00:00:00′,’YYYY-MM-DD HH24:MI:SS’)
AND to_date(‘2016-09-07 10:40:00′,’YYYY-MM-DD HH24:MI:SS’)
AND dhss.sql_id = dhst.sql_id;

LINUX SWAP MEMORY
1. Systems with 4GB of ram or less require a minimum of 2GB of swap space
2. Systems with 4GB to 16GB of ram require a minimum of 4GB of swap space
3. Systems with 16GB to 64GB of ram require a minimum of 8GB of swap space
4. Systems with 64GB to 256GB of ram require a minimum of 16GB of swap space
GATHER STATS ON WBS TABLES

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘PM_PROD’,’PM_RATED_CDRS0′,DEGREE=>10)
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘PM_PROD’,’PM_TAP_CDRS0′,DEGREE=>10)
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘PM_PROD’,’PM_TAP_FILE_SUMRY0′,DEGREE=>10)

WBS – CREATION DES PARTITIONS

Tables concernées:

PM_DROPPED_CDRS0,
PM_RATED_CDRS0,
PM_REJECTED_CDRS0,
PM_SUMMARY0,
PM_TAP_CDRS0
1. Créer le tablespace mensuel (ex : PM_TDAT_201610)

CREATE BIGFILE TABLESPACE PM_TDAT_201601 DATAFILE
‘+DATAC1’ SIZE 100M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

2. Créer les partitions de PM_PROD.PM_DROPPED_CDRS0 (ex : du 01 au 30/10/2016)

DECLARE
v_count NUMBER := 0;
v_date DATE := TO_DATE (20161001, ‘yyyymmdd’);
v_sql VARCHAR2 (1000);

BEGIN
DBMS_OUTPUT.put_line (‘RUN STARTED’);

WHILE v_date < TO_DATE (20161101, ‘yyyymmdd’)
LOOP
v_sql :=
‘ALTER TABLE PM_PROD.PM_DROPPED_CDRS0 ‘
|| ‘ ADD PARTITION P’
|| TO_CHAR (v_date, ‘yyyymmdd’)
|| ‘_CDRS VALUES LESS THAN (TO_DATE(”’
|| TO_CHAR (v_date +1 , ‘yyyy-mm-dd’)
|| ‘ 00:00:00’||”’,”’|| ‘SYYYY-MM-DD HH24:MI:SS’||”’,”’|| ‘NLS_CALENDAR=GREGORIAN’||”’))’||’ TABLESPACE PM_TDAT_201610;’;
–EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.put_line (v_sql);
v_date := v_date + 1;
v_count := v_count + 1;
END LOOP;
DBMS_OUTPUT.put_line (‘RUN FINISHED:’ || v_count);
END;

LSNRCTL> change_password >> if >=10g

Old password:

New password:

Reenter new password:

save_config

In 9i

LSNRCTL> set password

Password:

LSNRCTL>stop
LSNRCTL>start

SCRIPT DE CREATION DE USER
CREATE USER IDENTIFIED BY PROFILE SECURITY;
GRANT CONNECT,RESOURCE,CREATE SESSION TO ;
ALTER USER PASSWORD EXPIRE;

AUDITED STATEMENTS
select * from dba_stmt_audit_opts;

PARTITIONNEMENT D’UNE TABLE EXISTANTE

— creating new table structure

CREATE TABLE FUNDAMO.ENTRY001_TEMP
(
OID NUMBER(19) NOT NULL,
LAST_UPDATE TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
ENTRY_DATE TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
AMOUNT FLOAT(126) NOT NULL,
ACCOUNT_OID NUMBER(19) NOT NULL,
TRANSACTION_OID NUMBER(19) NOT NULL,
DESCRIPTION VARCHAR2(50 BYTE) NOT NULL,
TRANSACTION_NUMBER NUMBER(22),
ENTRY_TYPE_OID NUMBER(19),
ENTRY_CODE CHAR(6 BYTE),
GROUPED CHAR(5 BYTE) DEFAULT ‘false’
)
TABLESPACE FUNDAMO
PARTITION BY RANGE (ENTRY_DATE)
( — 2008
PARTITION JAN2008 VALUES LESS THAN (TIMESTAMP’2008-02-01 00:00:00 +0:00′),
PARTITION FEB2008 VALUES LESS THAN (TIMESTAMP’2008-03-01 00:00:00 +0:00′),
PARTITION MAR2008 VALUES LESS THAN (TIMESTAMP’2008-04-01 00:00:00 +0:00′),
PARTITION APR2008 VALUES LESS THAN (TIMESTAMP’2008-05-01 00:00:00 +0:00′),
PARTITION MAY2008 VALUES LESS THAN (TIMESTAMP’2008-06-01 00:00:00 +0:00′),
PARTITION JUN2008 VALUES LESS THAN (TIMESTAMP’2008-07-01 00:00:00 +0:00′),
PARTITION JUL2008 VALUES LESS THAN (TIMESTAMP’2008-08-01 00:00:00 +0:00′),
PARTITION AUG2008 VALUES LESS THAN (TIMESTAMP’2008-09-01 00:00:00 +0:00′),
PARTITION SEP2008 VALUES LESS THAN (TIMESTAMP’2008-10-01 00:00:00 +0:00′),
PARTITION OCT2008 VALUES LESS THAN (TIMESTAMP’2008-11-01 00:00:00 +0:00′),
PARTITION NOV2008 VALUES LESS THAN (TIMESTAMP’2008-12-01 00:00:00 +0:00′),
PARTITION DEC2008 VALUES LESS THAN (TIMESTAMP’2009-01-01 00:00:00 +0:00′),
— 2009
PARTITION JAN2009 VALUES LESS THAN (TIMESTAMP’2009-02-01 00:00:00 +0:00′),
PARTITION FEB2009 VALUES LESS THAN (TIMESTAMP’2009-03-01 00:00:00 +0:00′),
PARTITION MAR2009 VALUES LESS THAN (TIMESTAMP’2009-04-01 00:00:00 +0:00′),
PARTITION APR2009 VALUES LESS THAN (TIMESTAMP’2009-05-01 00:00:00 +0:00′),
PARTITION MAY2009 VALUES LESS THAN (TIMESTAMP’2009-06-01 00:00:00 +0:00′),
PARTITION JUN2009 VALUES LESS THAN (TIMESTAMP’2009-07-01 00:00:00 +0:00′),
PARTITION JUL2009 VALUES LESS THAN (TIMESTAMP’2009-08-01 00:00:00 +0:00′),
PARTITION AUG2009 VALUES LESS THAN (TIMESTAMP’2009-09-01 00:00:00 +0:00′),
PARTITION SEP2009 VALUES LESS THAN (TIMESTAMP’2009-10-01 00:00:00 +0:00′),
PARTITION OCT2009 VALUES LESS THAN (TIMESTAMP’2009-11-01 00:00:00 +0:00′),
PARTITION NOV2009 VALUES LESS THAN (TIMESTAMP’2009-12-01 00:00:00 +0:00′),
PARTITION DEC2009 VALUES LESS THAN (TIMESTAMP’2010-01-01 00:00:00 +0:00′)
;

— check table can be redefined
exec dbms_redefinition.can_redef_table(‘FUNDAMO’, ‘ENTRY001’);

— starting redefinition
exec dbms_redefinition.start_redef_table(‘FUNDAMO’, ‘ENTRY001’, ‘ENTRY001_TEMP’);

— to stop redefinition
–exec dbms_redefinition.abort_redef_table(‘FUNDAMO’, ‘TRANSACTION001’, ‘TRANSACTION001_TEMP’);

— [synchronize new table with interim data before index creation] –optional
exec dbms_redefinition.sync_interim_table(‘FUNDAMO’, ‘ENTRY001’, ‘ENTRY001_TEMP’);

— create primary key, foreign key and triggers etc
alter table fundamo.entry001_temp add constraint entry_oid_pk primary key(oid);

— completing redefinition
exec dbms_redefinition.finish_redef_table(‘FUNDAMO’, ‘ENTRY001’, ‘ENTRY001_TEMP’);

— remove original table which is now having the name of the new table
–drop table entry001_temp;

— testing new table structure and data
select * from entry001;

— adding remaining indexes
create index entry_idx1 on entry001(account_oid, entry_date) parallel 5;
create index entry_idx2 on entry001(account_oid, entry_date, entry_code, amount) parallel 5;
create index entry_idx3 on entry001(transaction_oid) parallel 5;

——————— TUNING REQUETE SQL AVEC DBMS_SQLTUNE ———————

http://www.shannura.com/archives/2226261108_sql_tune_01.html

1/ Tâche à créer

DECLARE
l_sql_tune_task_id varchar2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_id => ‘antpaufafz2kr’,
scope => dbms_sqltune.scope_comprehensive,
time_limit => 60,
task_name => ‘sql_tune_01’,
description => ‘sql_tuning_task_01’);
dbms_output.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/

2/Exécution de la tâche créée

exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘sql_tune_01’);

3/Voir temps d’exécution de la requête en cours

Select owner, task_name, status from dba_advisor_log where task_name = ‘sql_tune_01’;

4/Voir les optimisations

spool wbs2_recom2.txt
set long 10000
set pagesize 1000
set linesize 220
set pagesize 100
col output for a1000
select
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘sql_tune_01’) as output
from
dual
/
spool off

5/Suppression de tâche d’ optimisation

exec DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => ‘sql_tune_01’)
——————————————————————————————————

Optimisation : Best practices – 10 Mars 2017
En optimization, il faut voir ce qui bloque

Indicateur important : DBTime= nb connexions actives x temps snapshot
Si nb connexion actives dans une période de collecte est > dbtime, il y a probablement un souci. Il faut s’intéresser aux évènements d’attente
L’event db sequential read : attente de lecture de bloc

db full sequential read : attente de lecture de scan de blocs
Event db file sync : indique un souci de commit

La bd va fréquemment lire les mémoires cache : buffer cache pour les données et cache library pour les méta données et programmes
Penser à mettre les stats à jour pour générer des plans d’exécution corrects et les histogrammes (les différentes valeurs d’une colonne)
NB : Pour une requête, ce n’est pas le nb de lignes ramenées qui compte mais le nb de blocs de données ramenées. Si une ligne est dans un bloc de données, c’est tout le bloc qui est ramené par oracle. Ce qui n’est pas le cas avec Exadata où la performance de la machine vient du fait que le logiciel de smart scan ramène lal ligne en mémoire, voire la colonne en mémoire, réduisant les temps cpu et i/o. Il faut regarder la valeur de logical buffer get. S’il y a bcp de physical read avant logical read, cela va accroître les soucis de perf. Voir la valeur de buffer get rows / nb rows

Db cpu est lié aux requêtes qui font bcp de buffer gets
Dans les plans d’exécutions, il faut se « balader » avec la table qui ramène le moins de lignes, à joindre avec les autres tables. Le hash join crée une table de hashage, souvent préféré au nested loop. Si plus de 90% par exemple de la table est lue, mieux vaut faire un full scan en même temps au lieu de créer un index

En règle gl, il faut cibler ce qui pose pb. Et avec la panoplie des outils à connaître, chercher à résoudre selon le cas posé

Pour les jobs, les jobs de stats système peuvent rester en auto mais penser à personnaliser l’exécution des stats des autres tables

Le gros souci sur l’exa a été résolu en 2 étapes majeures :
1. Le nb de sessions actives été élevé / au nb de cpu et une seule requêtes exécutaient en même tps plusieurs fois une requête. Il fallait choisir un plan d’exécution pour utiliser le smart scan. Ceci a été rendu possible en générant un plan d’exécution avec les /+hints*/ et en fixant ce plan pour exécution de la requête
2. Il y avait bcp de verrous qui étaient dûs à la maj d’un index bitmap. Cet index sera déésactivé lors de chargements et rebuild après les loads

AUDIT TABLE;
AUDIT ALTER TABLE;
AUDIT ALTER ANY TABLE;
AUDIT CREATE ANY TABLE ;
AUDIT CREATE PROCEDURE ;
AUDIT PROCEDURE ;
AUDIT DROP ANY PROCEDURE ;
AUDIT ALTER ANY PROCEDURE ;
AUDIT CREATE ANY PROCEDURE ;
AUDIT CREATE EXTERNAL JOB ;
AUDIT CREATE ANY JOB ;
AUDIT CREATE ANY LIBRARY ;
AUDIT CREATE PUBLIC DATABASE LINK ;
AUDIT DATABASE LINK ;
AUDIT CREATE ANY DIRECTORY;
AUDIT DIRECTORY;
AUDIT ALTER USER;
AUDIT CREATE USER;
AUDIT DROP USER;
AUDIT USER;
AUDIT ALTER profile;
AUDIT DROP profile;
AUDIT CREATE PROFILE;
AUDIT GRANT ANY PRIVILEGE ;
AUDIT GRANT ANY ROLE ;
AUDIT GRANT ANY OBJECT PRIVILEGE;
AUDIT ROLE ;
AUDIT CREATE ROLE;
AUDIT DROP ANY ROLE;
AUDIT ALTER ANY ROLE;
AUDIT ALTER DATABASE ;
AUDIT ALTER system ;
AUDIT AUDIT system ; –(or AUDIT system AUDIT ) ;
AUDIT CREATE SESSION ;
AUDIT CONNECT ;

SCRIPTS POUR LES CONTROLES PRIMAIRES

I – SQL SERVER

1/Requête SQL pour voir les users et profil

select sp.name as principal_name,
sp.is_disabled as status,
sp.type_desc as principal_type,
spr.name as security_entity,
‘role membership’ as security_type,
null as state_desc,
sp.type
from sys.server_principals sp
inner join sys.server_role_members srm
on sp.principal_id = srm.member_principal_id
inner join sys.server_principals spr
on srm.role_principal_id = spr.principal_id
where sp.type in (‘s’, ‘u’)
order by principal_type,principal_name

2/ Requête pour voir les serveurs liés

sp_linkedservers

II- ORACLE

1/Requête checking des users sous Oracle

select username,account_status,profile from dba_users –where (account_status not like ‘%LOCKED%’ or profile<>’DEFAULT’)
where username not in (‘SCOTT’,’ORACLE_OCM’,’XS$NULL’,’MDDATA’,’DIP’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’FLOWS_FILES’,’MDSYS’,’ORDSYS’,’EXFSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’ORDDATA’,’CTXSYS’,’ANONYMOUS’,’SYSMAN’,’XDB’,’ORDPLUGINS’,’OWBSYS’,’SI_INFORMTN_SCHEMA’,’OLAPSYS’,’OUTLN’,’MGMT_VIEW’) –and account_status not like ‘%LOCKED%’)
order by username

2/Requêtes pour les db links sous Oracle

select * from dba_db_links

********************************* MISE EN MODE TRACE D’UNE SESSION
set lines 250
set pages 50000
col LOGON_TIME for A30
col module for A35
col username for A20
col program for A35
col LOGON_TIME for A20
select sid, a.serial#, PID, spid, a.sql_id,
to_char(logon_time,’dd/mm/yyyy hh24:mi:ss’) “LOGON_TIME”, a.program,a.username, a.event, a.osuser, a.status, a.machine from v$session a, v$process b
where a.paddr = b.addr and status = ‘ACTIVE’ and TYPE <> ‘BACKGROUND’ order by 6;

oradebug setospid
oradebug event 10046 trace name context forever, level 8
oradebug tracefile_name
oradebug event 10046 trace name context off

ALTER SYSTEM SET EVENTS ‘sql_trace [sql:&&sql_id] bind=true, wait=true’; (10046 pour un sql_id donné)

ALTER SYSTEM SET events ‘trace[rdbms.SQL_Optimizer.*][sql:sql_id]’ (10053 pour un sql_id donné)

alter session set events ‘sql_trace level 12’;

********************************************************************************************************

OPTIMISATION AVEC SQLTUNE

1/ CREER LA TACHE AVEC LE SQLID DE LA REQUETE

DECLARE
l_sql_tune_task_id varchar2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_id => ‘g371x6bqswp2x’,
scope => dbms_sqltune.scope_comprehensive,
time_limit => 600,
task_name => ‘sql_tune_01’,
description => ‘sql_tuning_task_02’);
dbms_output.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/

2/ EXECUTION DE LA TACHE

exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘sql_tune_01’);

3/ PROGRESSION DE L’EXECUTION

Select owner, task_name, status from dba_advisor_log where task_name = ‘sql_tune_01’;

4/RESULTAT DE L’EXECUTION

spool wbs2.txt
set long 10000
set pagesize 1000
set linesize 220
set pagesize 100
col output for a1000
select
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘sql_tune_01’) as output
From dual /
spool off

exec DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => ‘sql_tune_01’) ;

Script désactivation/activation comptes sur crm
Sur CRMDB2 :
/home/oracle/dba/scriptsdisable_crm_oper2.sql

TOAD – AFFICHAGE PARTITIONS DE TABLES

SCRIPT DE CREATION DE USER
CREATE USER IDENTIFIED BY PROFILE SECURITY;
GRANT CONNECT, RESOURCE, CREATE SESSION TO ;
ALTER USER PASSWORD EXPIRE;

ENABLE MOMO ACCOUNT

select oid,username,enabled,LOGGED_IN_CHANNELS from fundamo.login001 where oid = ‘113662725142610067’;
update login001 set enabled=’true’ where oid=113662725142610067;
update fundamo.login001 set LOGGED_IN_CHANNELS =’0′ where oid=113662725142610067;
commit;

#—- EXTRACT DATA FOR SECURITY TEAM
00 1 * * * sh /home/oracle/dba/scripts/extract4security.sh

[CRMDB2][/home/oracle]$cat /home/oracle/dba/scripts/extract4security.sh
DATE=`date +%d%m%Y-%Hh%Mmn`; export DATE
ORACLE_SID=crmdb2 ;export ORACLE_SID
ORACLE_HOME=/opt/oracle/product/11gR2/db ;export ORACLE_HOME
ORACLE_BASE=/home/oracle; export ORACLE_BASE
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib ;export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH;export PATH
DIR=/home/oracle/dba/scripts;export DIR

cd /backup/crm_appl/

sqlplus “/as sysdba” @$DIR/extract4security.sql

mv /backup/crm_appl/CRM_APP_LOG.TXT /backup/crm_appl/CRM_APP_LOG_$DATE.TXT

chmod 777 CRM_APP_LOG_*

[CRMDB2][/home/oracle]$cat /home/oracle/dba/scripts/extract4security.sql
set trimspool on;
set termout off;
set verify off;
set pagesize 0;
set feedback off heading off space 0 echo off trimout on
set trimspool on lines 120 linesize 32767 pages 0 long 1000000

spool /backup/crm_appl/CRM_APP_LOG.TXT

select ‘ACTION’||’;’||’HEURE_ACTION’||’;’||’STAFFNAME’||’;’||’ACTION_DESC’||’;’||’USER_IP’||’;’||’DETAILS’ from dual
union all
select (select distinct b.msginfo from (select distinct datadesc,typeid,dataid from crmpub.t_bme_publicdatadict ) a, crmpub.t_bme_languagelocaldisplay b where a.datadesc = b.keyindex and a.typeid = ‘CSP.BSF.BLC.FUNCID’ and a.dataid=t.FUNCID and b.msginfo is not null and rownum<2)||’;’||t.opertime||’;’||t.staffname||’;’||t.content||’;’||t.operip||’;’||t.keycontent from crmpub.t_bsf_operatelog t where trunc(t.opertime) = trunc(sysdate-1);

spool off;
/
[CRMDB2][/home/oracle]$

CREER UN DB_LINK DANS UN AUTRE SCHEMA

CREATE OR REPLACE PROCEDURE SVC_GEOMARKET.create_db_links_prc2
IS
BEGIN
EXECUTE IMMEDIATE ‘CREATE DATABASE LINK EDWP_SVC_GIS CONNECT TO SVC_BILNK IDENTIFIED BY P_xxx_d USING ”GIS”’;

END create_db_links_prc2;

exec svc_geomarket.create_db_links_prc2

DROP PROCEDURE svc_geomarket.create_db_links_prc

SUPPRIMER UN DB_LINK DANS UN AUTRE SCHEMA

NB: suppression du db_link TESTUSER. ”MYLINK”. A exécuter en tant que user ‘sys’
1)
DECLARE
l_sql CLOB :=
‘CREATE PROCEDURE TESTUSER.drop_db_links_prc
IS
BEGIN
FOR i IN (SELECT * FROM user_db_links where db_link=”MYLINK”)
LOOP
EXECUTE IMMEDIATE ”DROP DATABASE LINK ”||i.db_link;
END LOOP;
END;’;
begin
EXECUTE IMMEDIATE l_sql;
end;
/
2)
execute TESTUSER.drop_db_links_prc

DROP PROCEDURE KYC.drop_db_links_prc

KILL PROCESS IN A LOOP (ZOG)

ps -ef|grep oraclefd*|awk ‘{print $2}’ > kill.txt

for i in ‘cat kill.txt’ do
echo kill $i;
kill -9 $i;
done

COPY PASSWORD FILE IN ASM

srvctl config database -db ”

ASMCMD> pwget –asm
+DGP_01/ASM/PASSWORD/pwdasm.256.844043619

ASMCMD> ls +DGP_01/ASM/PASSWORD/pwdasm.256.844043619
pwdasm.256.844043619

ASMCMD> pwcopy –asm +DGP_01/ASM/PASSWORD/pwdasm.256.844043619 /tmp/pwdasm_bk
copying +DGP_01/ASM/PASSWORD/pwdasm.256.844043619 -> /tmp/pwdasm_bk

ASMCMD> pwget –asm
/tmp/pwdasm_bk

ASMCMD> pwset –asm +DGP_01/ASM/PASSWORD/pwdasm.256.844043619

ASMCMD> pwget –asm
+DGP_01/ASM/PASSWORD/pwdasm.256.844043619

Voir MO changement password file dans ASM

GENERER CLE POUR SSH AUTORISATION

$ssh-keygen -t dsa

[root@svr-tstoravm-01 ~]# systemctl stop firewalld
[root@svr-tstoravm-01 ~]# firewall-cmd –state
not running
[root@svr-tstoravm-01 ~]# systemctl disable firewalld.service
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@svr-tstoravm-01 ~]#
[root@svr-tstoravm-01 ~]#
[root@svr-tstoravm-01 ~]#
[root@svr-tstoravm-01 ~]# firewall-cmd –state
not running
[root@svr-tstoravm-01 ~]#

CREATE Pluggable database:

Se connecter au container :

SQL>CREATE PLUGGABLE DATABASE asset90 ADMIN USER pdb_asset90 IDENTIFIED BY * CREATE_FILE_DEST=’+DATA1′;

SQL>alter pluggable database ASSETCA open sid=’*’;

SQL>CREATE TABLESPACE USERS DATAFILE ‘+DATA1’ SIZE 4G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

MYSQL Database

1/BACKUP ITRAC MYSQL DATABASE
nohup mysqldump –databases itrac |gzip -9 > /udd1/mysqlbackups/mtn/itrac_14.05.2018.sql.gz &

2/Restoration de bd
mysql -u root -ptecmint rsyslog < rsyslog.sql

NB: rsyslog doit être une bd vide existante

3/Taille des bds dans Mysql

$mysql
Mysql>SELECT table_schema AS “Database”, SUM(data_length + index_length) / 1024 / 1024 AS “Size (MB)” FROM information_schema.TABLES GROUP BY table_schema;

root@itrac-db-1:~# ps -ef|grep mysql
root 7449 1 0 May14 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql 7491 7449 3 May14 ? 00:38:39 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/run/mysqld/mysqld.sock
root 7492 7449 0 May14 ? 00:00:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
root 9672 9660 0 09:40 pts/1 00:00:00 grep mysql
root@itrac-db-1:~#

Monter un iso pour installation de package

[root@SVR-TASDBTEST-01 database]# history|grep mount
77 mount -t iso9660 -o loop /dev/sr0 /mnt
101 history |grep mount
102 mount -t iso9660 -o loop /dev/sr0 /mnt
107 history|grep mount
109 history|grep mount
[root@SVR-TASDBTEST-01 database]# df -h /mnt
Filesystem Size Used Avail Use% Mounted on
/dev/loop0 3.8G 3.8G 0 100% /mnt
[root@SVR-TASDBTEST-01 database]#

Backup with exp using compression

$ mknod exp_facts_full.pipe p
$ gzip < exp_facts_full.pipe > exp_facts_full.dmp.gz &
[1] 1146
$ nohup exp exporter/exporter file=exp_facts_full.pipe log=exp_facts_full.log full=yes &

——- IMPORT

cd /home/oracle
# create a name pipe
mknod imp_disc_schema_scott.pipe p
# read the zip file and output to pipe
gunzip < imp_disc_schema_scott.dmp.gz > imp_disc_schema_scott.pipe &
# feed the pipe
imp file=imp_disc_schema_scott.pipe log=imp_disc_full.log fromuser=scott touser=scott1

select ‘insert into my_table values(”’||owner||”’,”’||table_name||”’,’||'(select count(*) from ‘||owner||’.’||table_name||’));’
from dba_tables where owner in (‘CBS’,’DSI_PRG’,’EXFSYS’,’FACTS’,’FILTER_GSM’,’GLOBAL_CONFIG’,’GLOBAL_GSM’,’GLOBAL_WCDMA’,’HSTX’,’HUAWEI_CDMA’,’HUAWEI_CORE’,’HUAWEI_DATA_GGSN’,
‘HUAWEI_DATA_SGSN’,’HUAWEI_DBA’,’HUAWEI_GSM’,’HUAWEI_RBSCELL_WCDMA’,’HUAWEI_RBS_WCDMA’,’HUAWEI_TRANSMISSION’,’HUAWEI_WCDMA’,’HUAWEI_WIMAX’,’JMORGANTI’,’MBOUDOUKHANE’,’MDDATA’,
‘MGMT_VIEW’,’MLUNGW_R’,’MTN_ADMIN’,’NMS_SNMP’,’NSN_TX_STP’,’SIEMENSBR10_GSM’,’SIEMENS_BR10′,’SIEMENS_GSM’,’SITE_GSM’,’SI_INFORMTN_SCHEMA’,’STATS_VIEW’,’WATCHDOG’)
group by owner,table_name;

–create table my_table (name varchar2(50),tabname varchar2(50), nb_rows integer);

ORACLE LONG RUNNING QUERIES
select * from
(
select
sid,username,sql_id,opname,
start_time,
target,
sofar,
totalwork,
units,
elapsed_seconds,
message
from
v$session_longops
order by start_time desc
)
where rownum <=1;

ORACLE DIRECT CONNEXION

sqlplus USER/PASSWORD@//hostName:port/SID

— Version plus générique sur une semaine (sysdate – 7) avec le temps exact

set lines 250 pages 50000
with Min_Snap_id as (select min(SNAP_ID) – 4 from dba_hist_snapshot where trunc(BEGIN_INTERVAL_TIME) = trunc(sysdate – &2)),
DB_Time as
(
select begin_snap, end_snap, begin_timestamp, inst, DBtime
from (
select begin_snap, end_snap, timestamp begin_timestamp, inst, trunc(a/1000000,0) DBtime from
(
select
e.snap_id end_snap,
lag(e.snap_id) over (order by e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.snap_id) timestamp,
s.instance_number inst,
e.value,
nvl(value-lag(value) over (order by e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl(‘1’,to_char(e.instance_number))
and stat_name = ‘DB time’
)
where begin_snap between (select * from Min_Snap_id) and 99999999999
and begin_snap=end_snap-1
order by timestamp
)),
DB_CPU as
(
select begin_snap, end_snap, begin_timestamp, inst, DBCPU
from (
select begin_snap, end_snap, timestamp begin_timestamp, inst, trunc(a/1000000,0) DBCPU from
(
select
e.snap_id end_snap,
lag(e.snap_id) over (order by e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.snap_id) timestamp,
s.instance_number inst,
e.value,
nvl(value-lag(value) over (order by e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl(‘1’,to_char(e.instance_number))
and stat_name = ‘DB CPU’
)
where begin_snap between (select * from Min_Snap_id) and 99999999999
and begin_snap=end_snap-1
order by timestamp
))
select T.begin_snap,T.end_snap,T.begin_timestamp SNAP_TIME, round(T.DBtime/60,0) “DBTIME(min)”, round(C.DBCPU/60,0) “DBCPU(min)”,
round(round(abs(extract(second from (T.begin_timestamp – lag(T.begin_timestamp) over(order by T.begin_snap)))
+ extract(minute from (T.begin_timestamp – lag(T.begin_timestamp) over(order by T.begin_snap)))*60
+ extract(hour from (T.begin_timestamp – lag(T.begin_timestamp) over(order by T.begin_snap)))*60*60
+ extract(day from (T.begin_timestamp – lag(T.begin_timestamp) over(order by T.begin_snap)))*24*60*60), 0)/60,0) “elapsed_time(s)”,
round(T.DBtime/(abs(extract(second from (T.begin_timestamp – lag(T.begin_timestamp) over(order by T.begin_snap)))
+ extract(minute from (T.begin_timestamp – lag(T.begin_timestamp) over(order by T.begin_snap)))*60
+ extract(hour from (T.begin_timestamp – lag(T.begin_timestamp) over(order by T.begin_snap)))*60*60
+ extract(day from (T.begin_timestamp – lag(T.begin_timestamp) over(order by T.begin_snap)))*24*60*60)),1) AAS
from DB_Time T, DB_CPU C
where T.begin_snap = C.begin_snap
and T.end_snap = C.end_snap
order by 1;

NBU configuration

 Créer un lien entre le fichier libobk.so et le fichier libobk.so64
$cd $ORACLE_HOME/lib
$ ln -s /usr/openv/netbackup/bin/libobk.so64 ./libobk.so

 Vérifier le lien créé
$ ls -ltrh libobk.so

Purge AUD$UNIFIED table
Requête pour faire un truncate de la table : AUD$UNIFIED

exec dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,use_last_arch_timestamp=>FALSE);

ASMCMD : copie via le réseau
asmcmd cp –port 1542 sys/password@xxx.xx.xxx.xx.+ASM1:+FRA1/orclprd/datafile/test.7961.964865921 +FRA1/test

USE TO DO

Share this: