alter user winmadmin default role NONE; alter user winmadmin default role DBA, CONNECT, RESOURCE; [INS-10002] Inventory initialization failed De adaugat in Registry [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE] "inst_loc"="C:\Program Files (x86)\Oracle\Inventory" 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; Pentru a putea compila fara erori pachetele din winmadmin si implicita a rezolva printre altere salvarea utilizand joburi pe baxe de date express edition trebuie lansate urmatoare granturi logati ca SYS AS SYSDBA: GRANT EXECUTE ON SYS.UTL_FILE TO PUBLIC; GRANT EXECUTE ON UTL_FILE TO PUBLIC; GRANT EXECUTE ON SYS.UTL_SMTP TO PUBLIC; GRANT EXECUTE ON UTL_SMTP TO PUBLIC; GRANT EXECUTE ON SYS.UTL_TCP TO PUBLIC; GRANT EXECUTE ON UTL_TCP TO PUBLIC; 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 \bin\OraOLEDB11.dll for 32-bit Oracle Client on 64-bit OS C:\Windows\SysWOW64\regsvr32 \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 SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE SCOPE = BOTH; 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 ********************************************************