alter user winmadmin default role NONE;
alter user winmadmin default role DBA, CONNECT, RESOURCE;

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

De incercat la listele care crapa din prea multe joinuri :
alter session set query_rewrite_enabled=false;
alter session set "_query_rewrite_vop_cleanup"=false;
alter session set "_complex_view_merging"=false;
alter session set optimizer_features_enable = "11.1.0.7";

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

Curatare Depoozite fantoma :
select 'DROP TABLE '||table_name||';' 
from user_tables 
where SubStr(table_name,1,2) IN ('J_','J0','J1','J2','J3','J4','J5','J6','J7','J8','J9')
  AND table_name NOT IN (SELECT tabla FROM joblistefin);

SELECT distinct e.file_id, f.file_name, e.owner
FROM dba_extents e left join dba_data_files f on f.file_id = e.file_id
where e.tablespace_name = 'WMDATA';

SELECT ROUND(s.bytes/1024/1024/1024,2) size_gb, s.owner, s.tablespace_name, s.segment_type, s.segment_name, l.table_name, l.column_name
FROM dba_segments s LEFT JOIN all_lobs l ON l.segment_name = s.segment_name AND s.segment_type = 'LOBSEGMENT'  
WHERE s.tablespace_name = 'WMDATA'
ORDER BY 1 desc;

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

-- detectare pozitionare firme
SELECT a.file_name, b.owner, trunc((nvl(hwmin,1)*c.value)/1024/1024) mbstart, ceil((nvl(hwmax,1)*c.value)/1024/1024) mbend
FROM dba_data_files a
  LEFT  JOIN (select file_id, owner, min(block_id) hwmin,  max(block_id+blocks-1) hwmax from dba_extents group by file_id, owner) b ON b.file_id = a.file_id
  CROSS JOIN (select value from v$parameter where name = 'db_block_size') c
WHERE a.tablespace_name IN ('WMDATA', 'WMINDX')
ORDER BY 1, 4 DESC 

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

-- detectare obiecte mari
select s.owner, s.segment_name, s.segment_type, round(Sum(s.bytes)/1024/1024/1024,2) SIZE_GB
from dba_segments s
WHERE s.tablespace_name = 'WMDATA'
group by s.owner, s.segment_name, s.segment_type
order by 4 DESC;

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

-- detectare lob-uri mari + script golire
SELECT s.owner, l.table_name LobTab, l.column_name LobCol, round(Sum(s.bytes)/1024/1024/1024,2) SIZE_GB,
  CASE 
    WHEN l.table_name IS NULL THEN NULL
    ELSE 'UPDATE '||s.owner||'.'||l.table_name||' SET '||l.column_name||' = empty_blob();'||Chr(10)||'ALTER TABLE '||s.owner||'.'||l.table_name||' MODIFY LOB ('||l.column_name||') (shrink space);'
  END SCRIPTGOLIRE
FROM dba_segments s LEFT JOIN all_lobs l ON l.segment_name = s.segment_name
WHERE s.tablespace_name = 'WMDATA' AND s.segment_type = 'LOBSEGMENT'
GROUP BY s.owner, s.segment_name, s.segment_type, l.table_name, l.column_name
ORDER BY 4 DESC;

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

-- golire lob si recuperare spatiu 
-- din scriptul anterior avem Owner, LobTab, LobCol
-- daca se ruleaza de pe winmadmin se va completa vUser := 'OWNER' 
-- daca esti conctat pe userul pe care se efectueaza curatenie vUser := ''
DECLARE
  vOwner  VARCHAR2(30) := '';
  vLobTab VARCHAR2(70) := 'DOCUMENTE'; --'MAILURI1';
  vLobCol VARCHAR2(30) := 'DOC';       --'ATTACHFILE';
BEGIN
  if vOwner IS NOT NULL
  THEN vLobTab := vOwner||'.'||vLobTab;
  END IF;
  
  EXECUTE IMMEDIATE 'UPDATE '||vLobTab||' SET '||vLobCol||' = empty_blob()';
  EXECUTE IMMEDIATE 'ALTER TABLE '||vLobTab||' MODIFY LOB ('||vLobCol||') (shrink space)';
END;

SELECT TABLE_NAME, column_name
FROM   user_lobs
WHERE  segment_name = (SELECT object_name
                       FROM   user_objects
                       WHERE  object_name = UPPER('&object_name')
                       AND    object_type = 'LOB');


alter tablespace temp-tbs shrink space;
alter tablespace YOUR_TEMP_TABLESPACE_NAME shrink space keep 256M;

Minimum size datafiles
    SELECT 'ALTER DATABASE DATAFILE '''||file_name||''' RESIZE '||ceil((nvl(hwm,1)*c.value)/1024/1024 + 5)||'M' changesize
    FROM dba_data_files a
      LEFT  JOIN (select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b ON b.file_id = a.file_id
      CROSS JOIN (select value from v$parameter where name = 'db_block_size') c

Verificare Open Cursor

SELECT  max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE  a.statistic# = b.statistic#  and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;


select c.SQL_ID, count(1) cnt, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id
and c.sid=&sid
GROUP BY c.SQL_ID, sql.sql_text
ORDER BY 2 desc;

Monitoring open cursors

I believe a lot of the confusion about open cursors vs. cached cursors comes from the names of the Oracle dynamic performance views used to monitor them. v$open_cursor shows cached cursors, not currently open cursors, by session. If you're wondering how many cursors a session has open, don't look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.

To monitor open cursors, query v$sesstat where name='opened cursors current'. This will give the number of currently opened cursors, by session:

--total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current';

If you're running several N-tiered applications with multiple webservers, you may find it useful to monitor open cursors by username and machine:

--total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
s.username, s.machine
from v$sesstat a, v$statname b, v$session s 
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current' 
group by s.username, s.machine
order by 1 desc;


Monitoring the session cursor cache

v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.

--session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' ;

You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID, and includes the first few characters of the statement and the sql_id, so you can actually tell what the cursors are for.

select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id  -- for 9i and earlier use: c.address=sql.address
and c.sid=&sid;



    SELECT 
      'ALTER '||CASE WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN 'PACKAGE' ELSE OBJECT_TYPE END||' '||OBJECT_NAME||' COMPILE;' text
    FROM USER_OBJECTS WHERE UPPER(STATUS) <> 'VALID'
    
UPDATE (
  SELECT ATTACHFILE 
  FROM PROF2014.MAILURI M INNER JOIN PROF2014.MAILURI1 M1 ON M1.CODMAIL = M.CODMAIL
  WHERE data < Add_Months(Trunc(SYSDATE,'mm'), - 3)
)
SET ATTACHFILE = empty_blob();

UPDATE (
  SELECT ATTACHFILE 
  FROM THPROF.MAILURI M INNER JOIN THPROF.MAILURI1 M1 ON M1.CODMAIL = M.CODMAIL
  WHERE data < Add_Months(Trunc(SYSDATE,'mm'), - 3)
)
SET ATTACHFILE = empty_blob();

with query as (
    select /*+ NO_MERGE MATERIALIZE */ 
        file_id, tablespace_name, max(block_id + blocks) highblock
    from  dba_extents
    group by  file_id, tablespace_name
)
select  'alter database datafile '|| q.file_id || ' resize ' || ceil ((q.highblock * t.block_size + t.block_size)/1024)  || 'K;' cmd
from query q, dba_tablespaces t
where q.tablespace_name = t.tablespace_name;

expdp parfile=Annie_Useri.par
expdp parfile=Annie_Firme.par

impdp parfile=Annie_Imp1.par

expdp system/system@dbgh schemas=VIVI3,CORNEL,DEMO2013,OLINT,XXX,ZZZ,YYY,DEMO2014,ABC,RTC11,ARAMIS2014,NOU,RALU,ANNIENOU,VIVI1,TESTE,STEF,LIVIU,ANNIE,CATALINA,BOGDAN,MASTER,RALUCA,LULU,GABI,VALI,VALI_WEB directory=WME_BACKUP dumpfile=FULL.PMP logfile=FULL.Log FLASHBACK_TIME=systimestamp
expdp system/system@dbgh full=Y directory=WME_BACKUP dumpfile=FULL.PMP logfile=FULL.Log FLASHBACK_TIME=systimestamp
impdp system/system@dbgh schemas=VIVI3,CORNEL,DEMO2013,OLINT,XXX,ZZZ,YYY,DEMO2014,ABC,RTC11,ARAMIS2014,NOU,RALU,ANNIENOU,VIVI1,TESTE,STEF,LIVIU,ANNIE,CATALINA,BOGDAN,MASTER,RALUCA,LULU,GABI,VALI,VALI_WEB directory=WME_BACKUP dumpfile=FULL.PMP logfile=FULL.LOG

SELECT 'expdp system/system@'||name||' full=Y directory=WME_BACKUP dumpfile=full_backup.pmp logfile=full_backup.Log flashback_scn='||current_scn  FROM v$database;

Save all grantee from original WinMadmin

SELECT string_agg(SCHEMA) FROM (SELECT Upper(SKEMAFIRMA) SCHEMA FROM firma UNION SELECT Upper(USERNAME) SCHEMA FROM utilizator ORDER BY 1)
SELECT string_agg(USERNAME) FROM (SELECT USERNAME FROM all_users u WHERE user_id BETWEEN 92 AND 10000 AND NOT EXISTS (SELECT * FROM WINMADMIN.FIRMA F WHERE Upper(F.SKEMAFIRMA) = U.USERNAME) ORDER BY 1)


CREATE BIGFILE TEMPORARY TABLESPACE WINMDB_TEMP TEMPFILE 'd:\OraData\wmeprel2\WINMDB_TEMP.DBF' SIZE 5M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED;
CREATE BIGFILE TABLESPACE WMDATA LOGGING DATAFILE 'd:\OraData\wmeprel2\WMDATA.DBF' SIZE 5M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE WMINDX LOGGING DATAFILE 'd:\OraData\wmeprel2\WMINDX.DBF' SIZE 5M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;


SELECT 'impdp system/system@'||name||' full=Y directory=WME_BACKUP dumpfile=full_backup.pmp logfile=full_restore.Log'  FROM v$database;

SELECT 'WINMADMIN' SCHEMA, 'impdp system/system@'||name||' SCHEMAS=WINMADMIN  directory=WME_BACKUP dumpfile=full_backup2.pmp logfile=restore_winmadmin.Log exclude=index' FROM v$database
UNION ALL
SELECT f.SKEMAFIRMA, 'impdp system/system@'||name||' SCHEMAS='||f.SKEMAFIRMA||'  directory=WME_BACKUP dumpfile=full_backup2.pmp logfile=restore_'||f.SKEMAFIRMA||'.Log exclude=index'
FROM v$database d CROSS JOIN winmadmin.FIRMA f
ORDER BY 1

Import Al Grantee from original WINMADMIN
Verificare de structuri
Inregitrare server mail


Blocaj la import (udi-00257) de verificat daca nu e plina flash recovery area
SELECT NAME, SPACE_LIMIT, SPACE_LIMIT/1024/1024/1024 SPACE_LIMIT_GB, SPACE_USED/1024/1024/1024 SPACE_USED_GB, SPACE_RECLAIMABLE, NUMBER_OF_FILES FROM V$RECOVERY_FILE_DEST;
SELECT NAME, SPACE_LIMIT, SPACE_USED, SPACE_RECLAIMABLE, NUMBER_OF_FILES FROM V$RECOVERY_FILE_DEST;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=214748364800 SCOPE=BOTH;


SELECT x.*,  space_usage_kbytes/1024 mb, space_usage_kbytes/1024/1024 gb
FROM V$SYSAUX_OCCUPANTS x
ORDER BY space_usage_kbytes DESC


SELECT df.tablespace_name, df.file_name, df.totalsize/1024/1024/1024 "TOTAL SIZE GB", fs.freespace/1024/1024/1024 "FREE SPACE GB"  
FROM (
  SELECT file_id, tablespace_name, file_name, sum(bytes) totalsize
  FROM dba_data_files
  GROUP BY file_id, tablespace_name, file_name
) df 
LEFT JOIN (
  SELECT file_id, sum(bytes) freespace
  FROM dba_free_space
  GROUP BY file_id
) fs ON fs.file_id = df.file_id


Mutare fisiere BD

ALTER SYSTEM SET control_files='E:\WME\ORADATA\WINMDB2\CONTROL01.CTL','E:\WME\ORADATA\WINMDB2\CONTROL02.CTL' SCOPE=SPFILE;
shutdown


copiere fisiere locatie noua

startup mount;

alter database rename file 'D:\WME\ORADATA\WINMDB2\SYSTEM01.DBF' to 'E:\WME\ORADATA\WINMDB2\SYSTEM01.DBF';
alter database rename file 'D:\WME\ORADATA\WINMDB2\SYSAUX01.DBF' to 'E:\WME\ORADATA\WINMDB2\SYSAUX01.DBF';
alter database rename file 'D:\WME\ORADATA\WINMDB2\UNDOTBS01.DBF' to 'E:\WME\ORADATA\WINMDB2\UNDOTBS01.DBF';
alter database rename file 'D:\WME\ORADATA\WINMDB2\USERS01.DBF' to 'E:\WME\ORADATA\WINMDB2\USERS01.DBF';
alter database rename file 'D:\WME\ORADATA\WINMDB2\WMDATA.DBF' to 'E:\WME\ORADATA\WINMDB2\WMDATA.DBF';
alter database rename file 'D:\WME\ORADATA\WINMDB2\WMINDX.DBF' to 'E:\WME\ORADATA\WINMDB2\WMINDX.DBF';
alter database rename file 'D:\WME\ORADATA\WINMDB2\TEMP01.DBF' to 'E:\WME\ORADATA\WINMDB2\TEMP01.DBF';
***alter database rename file 'D:\WME\ORADATA\WINMDB2\WINMDB_TEMP.DBF' to 'E:\WME\ORADATA\WINMDB2\WINMDB_TEMP.DBF';

alter database rename file 'D:\WME\ORADATA\WINMDB2\REDO01.LOG' to 'E:\WME\ORADATA\WINMDB2\REDO01.LOG';
alter database rename file 'D:\WME\ORADATA\WINMDB2\REDO02.LOG' to 'E:\WME\ORADATA\WINMDB2\REDO02.LOG';
alter database rename file 'D:\WME\ORADATA\WINMDB2\REDO03.LOG' to 'E:\WME\ORADATA\WINMDB2\REDO03.LOG';

shutdown;
startup;

***daca este cazul recreare temp:
DROP TABLESPACE WINMDB_TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE WINMDB_TEMP TEMPFILE 'E:\WME\ORADATA\WINMDB2\WINMDB_TEMP.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED;



TNSPING timpi foarte mari

de verificat in directorul aplicatiei oracle 
diag>.. >listener>trace>   > listener.log
Daca e foarte mare se poate dezactiva si de vazut daca se corecteaza (si asta ar insemna sa-l golim)
LSNRCTL> set Log_Status off

in caz contrar de vazut in log care e problema

Import firma eroare 
ORA-39213: Metadata processing is not available
Connectat ca SYSDBA
SQL> execute sys.dbms_metadata_util.load_stylesheets;



Eroare = ORA-00604: error occurred at recursive SQL level 1
ORA-01654: unable to extend index SYS.I_OBJ1 by 128 in tablespace SYSTEM


select file_name, bytes/1024/1024, autoextensible, maxbytes/1024/1024 from dba_data_files where tablespace_name='SYSTEM'
alter database datafile 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' autoextend on next 100m maxsize 2000m;


RESETARE PAROLE 

BEGIN
  FOR vrec IN (SELECT 'ALTER USER '||skemafirma||' IDENTIFIED BY '||skemafirma txt FROM FIRMA)
  LOOP
    EXECUTE IMMEDIATE vrec.txt;
  END LOOP;

  FOR vrec IN (SELECT 'ALTER USER '||username||' IDENTIFIED BY 1' txt FROM UTILIZATOR)
  LOOP
    EXECUTE IMMEDIATE vrec.txt;
  END LOOP;
END;

EXPDP winmadmin/wme2008@WinMDB2 FLASHBACK_TIME=systimestamp DIRECTORY=WME_BACKUP_WINMADMIN DUMPFILE=WINMADMIN_20131212_1507.pmp SCHEMAS=WINMADMIN LOGFILE=WINMADMIN_20131212_1507.lge

EXP winmadmin/1@DBGH FILE=C:\WMSAVE\WINMADMIN\WINMADMIN_20131212_1435.dmp GRANTS=N ROWS=Y COMPRESS=Y OWNER=WINMADMIN log=C:\WMSAVE\WINMADMIN\WINMADMIN_20131212_1435.lge

EXP winmadmin/wme2008@WinMDB2 FILE=C:\WMSAVE\BAU12\BAU12_20131212_1438.dmp GRANTS=N ROWS=Y COMPRESS=Y STATISTICS=NONE OWNER=BAU12 log=C:\WMSAVE\BAU12\BAU12_20131212_1438.lge
EXP winmadmin/wme2008@WinMDB2 FILE=C:\WMSAVE\BAU13\BAU13_20131212_1438.dmp GRANTS=N ROWS=Y COMPRESS=Y STATISTICS=NONE OWNER=BAU13 log=C:\WMSAVE\BAU13\BAU13_20131212_1438.lge
EXP winmadmin/wme2008@WinMDB2 FILE=C:\WMSAVE\TEST\TEST_20131212_1438.dmp GRANTS=N ROWS=Y COMPRESS=Y STATISTICS=NONE OWNER=TEST log=C:\WMSAVE\TEST\TEST_20131212_1438.lge

CREATE USER BBBB PROFILE "DEFAULT" IDENTIFIED BY BBBB DEFAULT TABLESPACE WMDATA TEMPORARY TABLESPACE WINMDB_TEMP ACCOUNT UNLOCK;

grant create session to BBBB;
GRANT RESOURCE to BBBB;

Oracle Provider for OLE DB 11.2.0.3 is not installed or registered correctly.

Open command prompt, if you are running on Windows 7,8,2008 with "Run As Administrator" privileges.
In the command window execute the following command to register the Oracle Provider for OLE DB : 
for 64-bit Oracle Client on 64-bit OS
   regsvr32 <Oracle64BitHome>\bin\OraOLEDB11.dll 
for 32-bit Oracle Client on 64-bit OS
   C:\Windows\SysWOW64\regsvr32 <Oracle32BitHome>\bin\OraOLEDB11.dll


SELECT TABLESPACE_NAME,SUM(BYTES/1024/1024) SIZE_IN_MEG
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME=UPPER(TRIM(:TS_NAME))
GROUP BY TABLESPACE_NAME ORDER BY SIZE_IN_MEG DESC;

SELECT TABLESPACE_NAME,SUM(BYTES/1024/1024) SIZE_IN_MEG
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(TRIM(:USERNAME))
GROUP BY TABLESPACE_NAME ORDER BY SIZE_IN_MEG DESC;

SELECT SEGMENT_TYPE ,SUM(BYTES/1024/1024) SIZE_IN_MEG
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE','INDEX')
  AND OWNER=UPPER(TRIM(:USERNAME))
GROUP BY


SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 MB
FROM 
  --DBA_SEGMENTS -- SPATIU OCUPAT
  DBA_FREE_SPACE -- SPATIU LIBER 
WHERE TABLESPACE_NAME='WMDATA'
GROUP BY TABLESPACE_NAME ORDER BY SIZE_IN_MEG DESC;

SELECT TABLESPACE_NAME, SUM(USED) USED_MB, SUM(FREE) FREE_MB
FROM (
  SELECT TABLESPACE_NAME, Sum(BYTES)/1024/1024 USED, 0 FREE
  FROM DBA_SEGMENTS
  GROUP BY TABLESPACE_NAME
  UNION ALL
  SELECT TABLESPACE_NAME, 0 USED, Sum(BYTES)/1024/1024 FREE
  FROM DBA_FREE_SPACE
  GROUP BY TABLESPACE_NAME
)
WHERE TABLESPACE_NAME='WMDATA'
GROUP BY TABLESPACE_NAME


Dimensiunne minima tablespace

select 
  file_name,
  ceil( (nvl(hwm,1)*c.value)/1024/1024 ) + 1 smallest,
  ceil( blocks*c.value/1024/1024) currsize,
  ceil( blocks*c.value/1024/1024) - ceil( (nvl(hwm,1)*c.value)/1024/1024 ) savings
from dba_data_files a,
  (select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b,
  (select value from v$parameter where name = 'db_block_size') c
where a.file_id = b.file_id(+) 
order by savings desc

ORA-28031 - SERVICE -- prea multe firme stergere firma si revocare rol ca dba (exemplu de interogare roles si stergere firma anverom)
select * from dba_role_privs where grantee='WINMADMIN' order by granted_role;
drop user anverom cascade;
REVOKE ROLE_ANVEROM_OBJ FROM WINMADMIN;

probleme cu blocuri corupte 

verificare blocuri corupte
dbv file=F:\ORADATA\WINMDB2\SYSAUX01.DBF

To recover specific data blocks:
    Obtain the datafile numbers and block numbers of the corrupted blocks.
    The easiest way to locate trace files and the alert log is to connect SQL*Plus to the target database and execute the following query:

    SELECT NAME, VALUE 
    FROM   V$DIAG_INFO;

    Start RMAN and connect to the target database, which must be mounted or open.
    Run the SHOW ALL command to make sure that the appropriate channels are preconfigured.
    Run the RECOVER ... BLOCK command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks.

    The following example recovers two blocks.
    RECOVER 
      DATAFILE 8 BLOCK 13 
      DATAFILE 2 BLOCK 19;

    You can also specify various options to control RMAN behavior. The following example indicates that only backups with tag mondayam will be used when searching for blocks. You could use the FROM BACKUPSET option to restrict the type of backup that RMAN searches, or EXCLUDE FLASHBACK LOG to restrict RMAN from searching the flashback logs.
    RECOVER 
      DATAFILE 8 BLOCK 13 
      DATAFILE 2 BLOCK 199
      FROM TAG mondayam;

Recovering All Blocks in V$DATABASE_BLOCK_CORRUPTION -> In this scenario, RMAN automatically recovers all blocks listed in the V$DATABASE_BLOCK_CORRUPTION view.
    SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
    Start RMAN and connect to the target database.
    RMAN> RECOVER CORRUPTION LIST;

probleme la poronire bd (daca e plina memoria flash)

RMAN -> 
connect target sys/sys;
startup nomount;
alter database mount;

delete archivelog all; 
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

crosscheck archivelog all;
alter database open;

De aruncat o privire la ;
How to clean old archive log without backup
you need 2 scripts (modify paths accordingly as per your setup):

1)From sqlplus:

connect / as sysdba
set serveroutput on;
spool C:\scripts\output_del_logs.txt

declare CURSOR cur IS
  SELECT a.name
  FROM v$asm_alias a, v$asm_file b
  where a.group_number = b.group_number and b.creation_date < sysdate-7 and a.file_number = b.file_number and b.type='ARCHIVELOG' and a.name like '%.arc'
  order by a.name;

  sqlstr VARCHAR2(250);

BEGIN
  FOR cur_rec IN cur LOOP
    sqlstr := 'alter diskgroup ASM_BACKUP drop file ''+ASM_BACKUP/DB1/' || cur_rec.name || '''';
    DBMS_OUTPUT.PUT_LINE(sqlstr);
    EXECUTE IMMEDIATE sqlstr;
  END LOOP;
END;
/

spool off;
exit

2)
From RMAN

run {
  crosscheck archivelog all;
  delete expired archivelog all;
}


probleme diferente character set: 

CMD ->

sqlplus / as sysdba;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE EE8MSWIN1250;
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP; 


sau

SQLPLUS /NOLOG
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE EE8MSWIN1250;
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT;


select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET')
SELECT USERENV ('language') FROM DUAL

in registri de setat NLS_LANG => ROMANIAN_ROMANIA.EE8MSWIN1250



PROBLEME WMDATA- NU E SUFICIENT SPATIu

SELECT file_name FROM dba_data_files;
select tablespace_name from dba_tablespaces;

in functie de care able space da pe afara
ALTER TABLESPACE WMDATA ADD DATAFILE 'cale\WMDATA0x.dbf'  SIZE 10G autoextend on;

ALTER TABLESPACE WMINDX ADD DATAFILE 'cale\WMINDX0x.dbf'  SIZE 10G autoextend on;

X - NR 1...

probleme table space

select tablespace_name, sum(bytes)/1024/1024 MB from dba_temp_files group by tablespace_name;
select tablespace_name, bytes/1024/1024 MB from dba_temp_files;
select tablespace_name, file_name, bytes/1024/1024 MB from dba_temp_files order by tablespace_name, file_name;

e bine sa dai un restart la server inainte de stergere ca sa fim siguri ca  nu mai useri cu obiecte temporare acolo.
1)DROP TABLESPACE WINMDB_TEMP INCLUDING CONTENTS AND DATAFILES;
dupa drop verifici daca l-a sters si de la nivelul sistemului de operare, pentru ca eu am observat ca nu il sterge tot timpu si de acolo, daca nu a reusit sa il stearga el automat il stergi tu manual. Cand incerci sa il stergi manual e posibil sa nu te lase sistemu de operare pentru ca oracle inca nu a notificat ca nu mai foloseste fisierul respectiv, in acest caz restart la server (serveru de oracle; serviciul restectiv) si apoi reincerci stergerea manuala
2)CREATE TEMPORARY TABLESPACE WINMDB_TEMP TEMPFILE 'D:\OraData\WINMDB2\WINMDB_TEMP.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED;

2)CREATE BIGFILE TEMPORARY TABLESPACE WINMDB_TEMP TEMPFILE 'E:\OraData\WMEPREL\WINMDB_TEMP.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED;
 LA MAX FILE SE POATE INCERCA SI 2T, 128G, 32M ...

de inlocuit calea fisierului din exemplul meu cu calea aferenta fiecarui caz particular.


Default Profile Values
Oracle 10g
PASSWORD_LIFE_TIME: UNLIMITED
PASSWORD_LOCK_TIME: UNLIMITED
PASSWORD_GRACE_TIME: UNLIMITED

Oracle 11g
PASSWORD_LIFE_TIME: 180
PASSWORD_LOCK_TIME: 1
PASSWORD_GRACE_TIME: 7

ALTER USER WMEAPPUSER IDENTIFIED BY WMEAPPUSER; 
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME  UNLIMITED; 
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS  UNLIMITED;


Parametrii responsabili cu securitatea in oracle (se pot modifica conectat ca SYS, SYSTEM sau chiar WINMADMIN)

PASSWORD_LIFE_TIME -> nr.zile de valabilitate a parolei. Default = 180
PASSWORD_GRACE_TIME -> nr.zile perioada de gratie dupa expirarea parolei (se permite autentificare cu mesaj). Default = 7
PASSWORD_REUSE_TIME -> nr.zile dupa care o parola poate fi reutilizata
PASSWORD_REUSE_MAX -> nr.de schimbari de parola dupa care o parola poate fi reutilizata
PASSWORD_LOCK_TIME -> nr.zile user blocat dupa incercari consecutive de logare esuate. Default = 1
FAILED_LOGIN_ATTEMPTS -> nr.maxim de incercari consecutive de logare esuate. Default = 10

Exemplu de modificare (a se schimba valorile in functie de necesitati)

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 45;
ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 5;
ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME 45;
ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX 3;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME 1;
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 3;


CREATE PROFILE app_user LIMIT 
  SESSIONS_PER_USER          UNLIMITED 
  CPU_PER_SESSION            UNLIMITED 
  CPU_PER_CALL               3000 
  CONNECT_TIME               45 
  LOGICAL_READS_PER_SESSION  DEFAULT 
  LOGICAL_READS_PER_CALL     1000 
  PRIVATE_SGA                15K
  COMPOSITE_LIMIT            5000000; 

CREATE PROFILE app_user2 LIMIT
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 60
  PASSWORD_REUSE_TIME 60
  PASSWORD_REUSE_MAX 5
  PASSWORD_VERIFY_FUNCTION verify_function
  PASSWORD_LOCK_TIME 1/24
  PASSWORD_GRACE_TIME 10;

ALTER USER SCOTT PROFILE APP_USER;


kill session

SET LINESIZE 100

COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       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';

ALTER SYSTEM KILL SESSION 'sid,serial#';
sau
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

probleme la pornire service (nu porneste ) 
stergere si creare service 
c:\> oradim -delete -sid winmdb2
c:\> oradim -new -sid winmdb2 -startmode auto 

probleme la verificare structuri
ALTER USER MURES TEMPORARY TABLESPACE WINMDB_TEMP;


ora-01172 recovery of thread file# 3 (in cazul de fata e vorba de UNDOTBS care se poate sterge si reface)

daca nu merge cu 
recover datafile 3

se poate incerca cu 
STARTUP MOUNT;
ALTER DATABASE RECOVER datafile 3 allow 1 corruption;
ATRER DATABASE OPEN;

select * from dba_data_files

show parameter undo
CREATE undo TABLESPACE undotbs2 DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA_WINMDB2\WINMDB2\UNDOTBS02.DBF' size 100M AUTOEXTEND ON NEXT 10M;
alter system set undo_tablespace=UNDOTBS2 scope=both;
drop tablespace undotbs1 including contents and datafiles;

repornire 
stergere manuala UNDOTBS01.DBF
si daca se vrea sa se revina la UNDOTBS01.DBF

CREATE undo TABLESPACE undotbs1 DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA_WINMDB2\WINMDB2\UNDOTBS01.DBF' size 100M AUTOEXTEND ON NEXT 10M;
alter system set undo_tablespace=UNDOTBS1 scope=both;
drop tablespace undotbs2 including contents and datafiles;

******************************** kcratr_nab_less_than_odr ********************************************************
// Stef 10.11.2015 - ultima data am aplicat option B si a fost cu succest 


ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],[290], [63878], [63882], [], [], [], [], [], [], []
shutdown

cold backup database files

startup mount
alter database recover until cancel using backup controlfile;
alter database recover continue default;
alter database recover cancel;
alter database open resetlogs;


 Alter database open fails with ORA-00600 kcratr_nab_less_than_odr
Db: 11.2.0.1

Symptoms
After Power Fail Alter database open fails with

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr]

Changes
Power failure
Cause
There was a power failure causing logical corruption in controlfile

Solution

Option a
------------

SQL>Startup mount ;

SQL>Show parameter control_files

Query 1
------------

sql>select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT'

Note down the name of the redo log

SQL>Shutdown abort ;

Take a OS Level back of the controlfile (This is to ensure we have a backup of current state of controlfile)

SQL>Startup mount ;

SQL>recover database using backup controlfile until cancel ;

Enter location of redo log shown as current in Query 1 when prompted for recovery

Hit Enter

SQL>Alter database open resetlogs ;

Option b
-----------


Recreate the controlfile using the Controlfile recreation script

With database in mount stage

rman target /

rman> spool log to '/tmp/rman.log';

Rman> list backup ;

Rman > exit

Keep this log handy

Go to sqlplus

SQL> Show parameter control_files

Keep this location handy.

SQL>oradebug setmypid

SQL>Alter session set tracefile_identifier='controlfilerecreate' ;

SQL>Alter database backup controlfile to trace ;

SQL>Oradebug tracefile_name ; --> This command will give the path and name of the trace file


Go to this location ,Open this trace file and select the controlfile recreation script with NO Resetlogs option


SQL>Shutdown immediate;


Rename the existing controlfile to _old ---> This is Important as we need to have a backup of existing controlfile since we plan to recreate it

SQL>Startup nomount

Now run the Controlfile recreation script with NO Resetlogs mode

SQL>Alter database open ;

For database version 10g and above

Once database is opened you can recatalog the rman backup information present in the list /tmp/rman.log using

Rman> Catalog start with '' ;

******************************** kcratr_nab_less_than_odr ********************************************************