Title: Generating CREATE USER DDL Statements Author:Ted Martin, a database administrator in Ottawa, Ontario, Canada. These scripts will generate SQL DDL statements related to the creation of user accounts. The types of statements generated are as follows: 1. CREATE USER and ALTER USER...QUOTA x ON [tabspace] (GENUSER.SQL) 2. CREATE role (GENROLE.SQL) 3. GRANT [role|priv] TO user (GRANTPRIV.SQL) All three scripts ask for execution parameters. If you leave such a parameter blank, the script will generate for all. The exception is the prompt for the output filename. Source/Text/Comments REM REM PROGRAM-ID : GENUSER.SQL REM WRITTEN BY : Ted Martin REM DATE WRITTEN : 26-AUG-1998 REM clear screen PROMPT GENUSER.SQL Generates CREATE USER commands PROMPT PROMPT Includes ALTER USER...QUOTA x ON tabspace commands PROMPT accept uname prompt 'Enter User Name : ' accept outfile prompt ' Output filename : ' col username noprint col lne newline set heading off pagesize 0 verify off feedback off spool &&outfile..gen prompt genuser.log prompt set term on echo off prompt prompt Creating User Accounts... prompt set term off echo on SELECT username, 'CREATE USER '||username||' '|| DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY', 'IDENTIFIED BY '''||password||''' ') lne, 'DEFAULT TABLESPACE '||default_tablespace lne, 'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne FROM DBA_USERS WHERE USERNAME LIKE UPPER('%&&uname%') OR UPPER('&&uname') IS NULL ORDER BY USERNAME; prompt set term on echo off prompt prompt Granting Tablespace Quotas... prompt set term off echo on SELECT username, 'ALTER USER '||username||' QUOTA '|| DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K') ||' ON TABLESPACE '||tablespace_name||';' lne FROM DBA_TS_QUOTAS WHERE USERNAME LIKE UPPER('%&&uname%') OR UPPER('&&uname') IS NULL ORDER BY USERNAME; spool off PROMPT PROMPT File &&outfile..GEN generated. Please review before using PROMPT EXIT ============================================================= REM REM PROGRAM-ID : GENROLE.SQL REM WRITTEN BY : Ted Martin REM DATE WRITTEN : 6-APR-1996 REM set term on echo off linesize 132 pagesize 0 heading off set verify off clear screen prompt GENROLE.SQL V1.0 Generate CREATE ROLE statements prompt prompt accept rname prompt ' Grant Role : ' accept outfile prompt ' Output filename : ' set feedback off pagesize 0 heading off col lne newline spool &&outfile..gen prompt prompt Run Parameters prompt prompt . . Role = &&rname prompt spool &&outfile..log prompt set term on echo off feedback on select 'CREATE ROLE '||role||';' lne from dba_roles where role like UPPER('%&&rname%') and role not in ('CONNECT', 'RESOURCE', 'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE') ORDER BY ROLE; prompt spool off prompt exit spool off prompt Script &&outfile..gen ready. Review before using it. exit ===================================================== REM REM PROGRAM-ID : GRANTPRIVS.SQL REM WRITTEN BY : Ted Martin REM DATE WRITTEN : 26-AUG-1998 REM clear screen set term on echo off linesize 132 pagesize 0 heading off set verify off prompt GRANTPRIVS.SQL Generate Existing GRANT role/priv statements prompt prompt Handles both Roles and System Privs. Excludes SYS and SYSTEM accounts prompt accept rname prompt ' Grant Priv : ' accept towner prompt ' To User : ' accept outfile prompt ' Output filename : ' set feedback off verify off spool &&outfile..gen prompt prompt Run Parameters prompt prompt . . Priv = &&rname prompt prompt . . User = &&towner prompt spool &&outfile..log prompt set term on echo on feedback on col grantee noprint col granted_priv noprint select grantee, granted_role granted_priv, 'GRANT '||granted_role||' to '||grantee|| DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';') from dba_role_privs where (granted_role like upper('%&&rname%') or '&&rname' IS NULL) or (grantee like upper('%&&towner%') or '&&towner' is null) and grantee not in ('SYS', 'SYSTEM') UNION select grantee, privilege granted_priv, 'GRANT '||privilege||' to '||grantee|| DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';') from dba_sys_privs where (privilege like upper('%&&rname%') or '&&rname' IS NULL) or (grantee like upper('%&&towner%') or '&&towner' is null) and grantee not in ('SYS', 'SYSTEM') order by 1, 2; prompt spool off prompt exit spool off prompt Script &&outfile..gen ready. Review before using it. exit
Script:Generating CREATE USER DDL Statements
Script:Diagnostic ORA-01000 maximum open cursors exceeded
以下脚本可以用于诊断ORA-01000打开游标过多错误:
set linesize 140 pagesize 1400 select to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits, to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses, to_char(100 * hard / calls, '999990.00') || '%' hard_parses from ( select value calls from v$sysstat where name = 'parse count (total)' ), ( select value hard from v$sysstat where name = 'parse count (hard)' ), ( select value sess from v$sysstat where name = 'session cursor cache hits' ) / select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%cursor ca%' / select sum(a.value), b.name,a.sid from v$sesstat a, v$statname b where a.statistic# = b.statistic# and b.name = 'opened cursors current' group by rollup (b.name,a.sid) order by 1 / 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' order by 1 / select sid, count(*) from v$open_cursor group by sid order by 2 / Exec DBMS_WORKLOAD_REPOSITORY.create_snapshot(); exec dbms_lock.sleep(300); Exec DBMS_WORKLOAD_REPOSITORY.create_snapshot(); @?/rdbms/admin/awrrpt upload the awr report or select dbms_workload_repository.awr_report_text(l_dbid => dbid, l_inst_num => instance_number, l_bid => mid - 1, l_eid => mid) from (select vd.dbid, vi.instance_number, mid from v$database vd, v$instance vi, (select max(snap_id) mid from dba_hist_snapshot dhs)) /
Oracle Event 10357 and 10351
[oracle@rh2 ~]$ oerr ora 10357 10357, 00000, "turn on debug information for direct path" // *Cause: // *Action: turn on debug information for direct path The cause of this issue was identified as unpublished Bug 9650718 In the bug, it was found that during cleanup from a direct path load, qesmm context was freed without the pointer to the context being set to zero. Because the pointer was non zero, there was code that tried to use the context. To help in diagnosing the issue, the following was used: This command will enable tracing for direct path operations in the server: ALTER SYSTEM SET EVENTS 'TRACE [DIRPATH_LOAD] DISK=high'; This command will enable tracing for the I/O component of direct path load: alter system set events '10357 trace name context forever, level 12'; klmalp: heap=0x7f9bd14efea8 size=72 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eab98 klmalp: heap=0x7f9bd14efea8 size=224 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eabe0 klmalp: heap=0x7f9bd14efea8 size=336 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eacc0 klmalp: heap=0x7f9bd14efea8 size=80 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eae10 klmalp: heap=0x7f9bd14efea8 size=8168 comment=kdobjrbb mem=0x7f9bd14edbc0 klmalp: heap=0x7f9bd14efea8 size=1150 comment=ktbbhs:kdbh mem=0x7f9bd14eb230 klmalp: heap=0x7f9bd14efea8 size=432 comment=kcbl_structure_instance mem=0x7f9bd14eb6b0 klmalp: heap=0x7f9bd14efea8 size=64 comment=kllcqc:kllcq mem=0x7f9bd14eb860 klmalp: heap=0x7f9bd14efea8 size=1312 comment=kllcqc:kllcqslt mem=0x7f9bd14ecb40 kcblin: lbs=0x7f9bd14eb6b0 flg=104 slt=(nil) cnt=2 sz=262144 st_obj=0x8b0cc8c0 fb=0 kcblin: state objects are: Call=8b0cc8c0, Current Call=8b0cc8c0, Session=8b082f18, Proc=8bae46c8 kdblil2<-klclil1r<-qerltFRop<-qercoRop<-kdstf0000101km<-kdsttgr<-qertbFetch<-qercoFetch<-rwsfcd<-qerltFetch <-insdlexe<-insExecStmtExecIniEngine<-insexe<-opiexe<-kpoal8< -opiodr<-ttcpip<-opitsk<-opiino<-opiodr klmalp: heap=0x7f9bd14efea8 size=48 comment=kdblix:klixllkey mem=0x7f9bd14ed060 klmalp: heap=0x7f9bd14efea8 size=528 comment=kcblinlm mem=0x7f9bd14ed090 kcblnb: lbs=d14eb6b0, slt=0, d=10dd2a9, nwr=0, cnt=0 klmalp: heap=0x7f9bd14efea8 size=262656 comment=kllcqgf:kllsltba mem=0x7f9bd135de00 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2aa, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ab, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ac, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ad, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ae, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2af, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b0, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b1, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b2, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b3, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b4, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b5, nwr=0, cnt=0 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b6, nwr=0, cnt=0 kcblcow: lbs=d14eb6b0, nslots=2, nwr=1 kcblco:lbs=d14eb6b0 slt=d14ecb40 typ=1 afn=4 blk=905897 d=10dd2a9 cnt=17 buf=d135de00 rq=d14ecc18fob=898375c8 kcblco ret: lbs=d14eb6b0 slt=d14ecb40 type=1 afn=4 blk=905897 cnt=17 buf=d135de00 rq=d14ecc18 fob=898375c8, wait=0, more=-783370504, wrc=0 kcblcow: slt=d14ecb40 dba=10dd2a9, sz=32, blks=23, st=1, idx=0 kcblio: lbs=d14eb6b0 slt=d14ecdd0 nslt=2, d=10dd2c0, st=2, fbon=0, flg=104 kcblio: Logging lbs=d14eb6b0 slt=d14ecdd0 nslt=2, d=10dd2c0, st=2 kcblgr: lbs=d14eb6b0 slt=d14ecdd0, rd=10dd2c0, flg=104, blks=32 kcbldio:lbs=d14eb6b0 flg=104 slt=d14ecdd0 typ=1 async=16 afn=4 blk=905920 cnt=20 buf=d13ade00 rq=d14ecea8 fob=0 blks=32 st=2 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e1, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e2, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e3, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e4, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e5, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e6, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e7, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e8, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e9, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ea, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2eb, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ec, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ed, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ee, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ef, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f0, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f1, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f2, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f3, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f4, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f5, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f6, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f7, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f8, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f9, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fa, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fb, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fc, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fd, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fe, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ff, nwr=1, cnt=23 kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd300, nwr=1, cnt=23 [oracle@rh2 ~]$ oerr ora 10351 10351, 00000, "size of slots" // *Cause: // *Action: sets the size of slots to use // *Comment: a slot is a unit of I/O and this factor controls the size // *Comment: of the IO. alter session set events '10351 trace name context forever, level 128'; level 128 -> direct path write max block 128 I generated a new run of the big testcase with event 10357, Patch 4417285 applied, manual workarea_size_policy, sort_area_size=50000000, db_file_multiblock_read_count=16 and event 10351 with level 128. I tried it with disk_asynch_io=TRUE and FALSE just to be certain this is not something related to the async. In the trace files I see something very peculiar. The slots size is 128 as expected and I see many writes of 128 blocks but not all of them are and they look like the they come in clusters. A few 128 writes, then a lot smaller of different sizes but mainly less than 16 blocks and then another cluster of big ones and so on. kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14 kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14 kcblcow: dba=100c991, sz=128, blks=1, st=3, idx=15 kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14 kcblcow: dba=100c991, sz=128, blks=1, st=3, idx=15 kcblcow: dba=100c990, sz=128, blks=1, st=3, idx=0 kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1 kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1 kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2 kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1 kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2 kcblcow: dba=100ca3a, sz=128, blks=1, st=3, idx=3 kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2 kcblcow: dba=100ca3a, sz=128, blks=1, st=3, idx=3 kcblcow: dba=100ca39, sz=128, blks=1, st=3, idx=4 but it is possible that there are other factor out of our control that forces Oracle to stop adding blocks to the slot and write small batches. In conclusion, in order to have the least ammount of direct operations and have the maximum possible read/write batches these are the parameters to set : alter session set events '10351 trace name context forever, level 128'; alter session set workarea_size_policy=manual; alter session set sort_area_size=50000000;
Audit Logon above 9i
1. Enable audit. Set the parameter to
audit_trail=db (or db,extended)
2. Restart the database instance to enable the audit settings.
3. Set up audit for session:
audit session whenever successful;
4. After a relevant period of time, check the DBA_AUDIT_SESSION view, in the documentation
LOGOFF_PREAD Physical reads for the session
LOGOFF_LWRITE Logical writes for the session
SESSION_CPU Amount of CPU time used by each Oracle session
A query example:
select username,sum(logoff_lread) "TOTAL READS",
sum(logoff_pread) "TOTAL PHYS READS",
sum(logoff_lwrite) "TOTAL WRITES",
sum(session_cpu) "TOTAL CPU",
sum(logoff_pread)/count(*) "READS/SESSIO",
sum(logoff_lwrite)/count(*) "PHYS_READS/SESSION",
sum(logoff_lwrite)/count(*) "WRITES/SESSION",
sum(session_cpu)/count(*) "CPU/SESSION"
from dba_audit_session group by username;
The range of values can be restricted using the TIMESTAMP and/or LOGOFF_TIME columns (which are the logon and logoff interval ends) to have the results for a specific period of time.
[oracle@rh2 ~]$ [oracle@rh2 ~]$ sqlplus maclean/fdsfds SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 8 12:48:05 2009 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ORA-01017: invalid username/password; logon denied select * from dba_audit_session OS_USERNAME -------------------------------------------------------------------------------------------------------------------------------------------- USERNAME ------------------------------ USERHOST -------------------------------------------------------------------------------------------------------------------------------- TERMINAL -------------------------------------------------------------------------------------------------------------------------------------------- TIMESTAMP ACTION_NAME LOGOFF_TI LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE LOGOFF_DLOCK SESSIONID --------- ---------------------------- --------- ------------ ------------ ------------- ---------------------------------------- ---------- RETURNCODE CLIENT_ID SESSION_CPU ---------- ---------------------------------------------------------------- ----------- EXTENDED_TIMESTAMP PROXY_SESSIONID GLOBAL_UID INSTANCE_NUMBER --------------------------------------------------------------------------- --------------- -------------------------------- --------------- OS_PROCESS ---------------- oracle MACLEAN rh2.oracle.com pts/0 08-JUL-09 LOGOFF 08-JUL-09 655 51 16 0 960800 0 9 08-JUL-09 12.45.42.813460 PM +08:00 0 6159 oracle MACLEAN rh2.oracle.com pts/0 08-JUL-09 LOGON 960801 1017 08-JUL-09 12.46.17.938293 PM +08:00 0 6168 oracle MACLEAN rh2.oracle.com pts/0 08-JUL-09 LOGON 960802 1017 08-JUL-09 12.48.05.234442 PM +08:00 0 6176 oracle MACLEAN rh2.oracle.com pts/0 08-JUL-09 LOGON 960803 0 08-JUL-09 12.48.40.687569 PM +08:00 0 6181
手动递增SCN号的几种方法:How to increase System Change Number by manual
手动递增SCN号的几种方法
除去下面几种,还有一种方法直接修改 实例的Global Lamport SCN,在SGA中由kcsgscn变量存储,对于一个实例来说这是唯一的源SCN,所有其他的SCN均由这个source scn所驱动。 这种递增方式是直接用oradebug 修改该Global Lamport SCN kcsgscn
如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 : 13764045638 QQ号:47079569 邮箱:service@parnassusdata.com
SQL> oradebug setmypid
Statement processed.
SQL> select to_char(current_scn,’XXXXXXXXXXXX’) from v$database;
TO_CHAR(CURRE
————-
D3E1E
SQL>
SQL> oradebug DUMPvar SGA kcsgscn
kcslf kcsgscn_ [060012658, 060012688) = 000D3E1E 00000000 00000000 00000000 0000162D 00000000 00000000 00000000 00000000 00000000 60012338 00000000
ORADEBUG POKE 0x060012658 4 0xfffff
poke 命令的语法
<address> <length> <value>” allows you to modify a given region of memory (length of memory is limited to size of scalar C types)
SQL> select current_scn from v$database;
CURRENT_SCN
———–
1048583
SQL> select to_char(current_scn,’XXXXXXXXXXXX’) from v$database;
TO_CHAR(CURRE
————-
10000B
SQL> oradebug DUMPvar SGA kcsgscn
kcslf kcsgscn_ [060012658, 060012688) = 00100010 00000000 00000000 00000000 0000004E 00000000 00000000 00000000 00000000 00000000 60012338 00000000
How to jump SCN by manual , this could be a problem:
1. We can bump up the SCN by using the procedure from Note: 386830.1
Bump the system SCN on the primary database to fix any metadata index corruptions for both primary and physical standby databases. Set the following parameters in the init.ora and restart the database in restricted mode to bump the system SCN of the primary database. For Real Application Clusters, perform the steps on only one node of the cluster. Init.ora syntax: *._allow_error_simulation = TRUE *._smu_debug_mode = 268435456 If using an spfile, Oracle recommends creating a temporary init.ora using the CREATE PFILE SQL command. SQL> create pfile='/tmp/initTMP.ora' from spfile=''; Then add the parameters to this temporary file. To use the temporary init.ora file when starting the instance, include the 'PFILE' clause with the STARTUP SQL command i.e. SQL> startup restrict pfile= WARNING: These parameters should only be used for this fix and must be removed immediately afterwards in step 2. If the above parameters are used through multiple database restarts, a complete database rebuild will be required. To know the system SCN has been bumped, monitor the instance's alert.log for the following message: advance SCN to wrap base xxxx Where xxx represents the new wrap SCN.
2.EVENT: ADJUST_SCN – Quick Reference (Doc ID 30681.1)
WORKAROUND: ----------- Searched in webiv (ora-1555, ora-604 see note:1063408.6 ) suggests workaround is to adjust serial number using event Will try : (30681.1) alter session set events 'IMMEDIATE trace name ADJUST_SCN level 1'; ora-600 [2256][0][1073741824][1][293672646] ERROR: ORA-600 [2256][a][b][c][d][e] VERSIONS: versions 7.3.X, 8.0.X, 8.1.X DESCRIPTION: This exception indicates that you attempted to ADJUST_SCN but the level supplied would be less that the current SCN. ARGUMENTS: a. Requested SCN WRAP b. Requested SCN BASE c. Current SCN WRAP d. Current SCN BASE = [293672646] *4 = 1174690584 will try level 2 ora-600 2256[0][2147483648][1][293672646] level 3 ora-600 2256[0][3221225472][1][293672646] Now, we increase the SCN on DST7 by using the ADJUST_SCN event - (Note: do not use this event outside of your test environment) set the following hidden parameter in init.ora on DST7 database and bounce the database. _allow_error_simulation=true Now, the SCN is increased by doing - alter system set events 'immediate trace name adjust_scn level '; where translates to (n*0x40000000) as the target SCN value. ie. n = 1 will set the SCN as 0x0000.40000000. For the testcase pick a value that is larger than the current SCN on both databases by atleast 100000 SCNs (our earlier SCN adjust threshold value). alter system set events 'immediate trace name adjust_scn level 2'; select to_char(current_scn, 'xxxxxxxxxxxx') from v$database;
3.Note 552438.1 How To Adjust the SCN using parameter _MINIMUM_GIGA_SCN
Parameter: MINIMUM_GIGA_SCN ~~~~~~~~~~~~~~~~~~~~~~~~~~~ @Oracle8i: HIDDEN @Identifier: kcmmsn @Versions: See <IVERS.MINIMUM_GIGA_SCN> Values: Related: Description: Minimum SCN to start with in 2^30 units ~~~~~~~~~~~~ Articles: <Event:ADJUST_SCN> Overview of Init.Ora Parameter Reference notes
终极方案:
使用bbed修改datafile header实现修改scn
主要是修改system01.dbf datafile header kcvfh.kcvfhckp.kcvcpscn
helpbkup_us@oracle.com helpkern_us@oracle.com SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production 1* select name,CHECKPOINT_CHANGE# from v$datafile SQL> / /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf 3905523 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_undotbs1_9f2flf52_.dbf 3905523 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_sysaux_9f2flf2v_.dbf 3905523 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_users_9f2flf5g_.dbf 3905523 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_example_9f2fmfto_.dbf 3905523 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_youchuan_9f2fqwr4_.dbf 3905523 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_guobao_9f2fwkkt_.dbf 3905523 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_tsoa_d_bjx6j57v_.dbf 3905523 SQL> select to_char(3905523,'XXXXXXXXXXXXX') from dual; TO_CHAR(390552 -------------- 3B97F3 SQL> oradebug tracefile_name /s01/oracle/product/10.2.0/db_1/admin/MACLEAN1/udump/maclean1_ora_9468.trc [oracle@vrh8 ~]$ cp /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf.bak [oracle@vrh8 ~]$ bbed filename=/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf password=blockedit BBED: Release 2.0.0.0.0 - Limited Production on Tue Mar 24 16:50:39 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set mode edit MODE Edit BBED> set blocksize 8192 BLOCKSIZE 8192 BBED> set block 1 BLOCK# 1 BBED> map File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0) Block: 1 Dba:0x00000000 ------------------------------------------------------------ Data File Header struct kcvfh, 676 bytes @0 ub4 tailchk @8188 BBED> p kcvfh struct kcvfh, 676 bytes @0 struct kcvfhbfh, 20 bytes @0 ub1 type_kcbh @0 0x0b ub1 frmt_kcbh @1 0xa2 ub1 spare1_kcbh @2 0x00 ub1 spare2_kcbh @3 0x00 ub4 rdba_kcbh @4 0x00400001 ub4 bas_kcbh @8 0x00000000 ub2 wrp_kcbh @12 0x0000 ub1 seq_kcbh @14 0x01 ub1 flg_kcbh @15 0x04 (KCBHFCKV) ub2 chkval_kcbh @16 0x4fc6 ub2 spare3_kcbh @18 0x0000 struct kcvfhhdr, 76 bytes @20 ub4 kccfhswv @20 0x00000000 ub4 kccfhcvn @24 0x0a200100 ub4 kccfhdbi @28 0x157f2927 text kccfhdbn[0] @32 M text kccfhdbn[1] @33 A text kccfhdbn[2] @34 C text kccfhdbn[3] @35 L text kccfhdbn[4] @36 E text kccfhdbn[5] @37 A text kccfhdbn[6] @38 N text kccfhdbn[7] @39 1 ub4 kccfhcsq @40 0x00000675 ub4 kccfhfsz @44 0x00010900 s_blkz kccfhbsz @48 0x00 ub2 kccfhfno @52 0x0001 ub2 kccfhtyp @54 0x0003 ub4 kccfhacid @56 0x00000000 ub4 kccfhcks @60 0x00000000 text kccfhtag[0] @64 text kccfhtag[1] @65 text kccfhtag[2] @66 text kccfhtag[3] @67 text kccfhtag[4] @68 text kccfhtag[5] @69 text kccfhtag[6] @70 text kccfhtag[7] @71 text kccfhtag[8] @72 text kccfhtag[9] @73 text kccfhtag[10] @74 text kccfhtag[11] @75 text kccfhtag[12] @76 text kccfhtag[13] @77 text kccfhtag[14] @78 text kccfhtag[15] @79 text kccfhtag[16] @80 text kccfhtag[17] @81 text kccfhtag[18] @82 text kccfhtag[19] @83 text kccfhtag[20] @84 text kccfhtag[21] @85 text kccfhtag[22] @86 text kccfhtag[23] @87 text kccfhtag[24] @88 text kccfhtag[25] @89 text kccfhtag[26] @90 text kccfhtag[27] @91 text kccfhtag[28] @92 text kccfhtag[29] @93 text kccfhtag[30] @94 text kccfhtag[31] @95 ub4 kcvfhrdb @96 0x00400179 struct kcvfhcrs, 8 bytes @100 ub4 kscnbas @100 0x00000008 ub2 kscnwrp @104 0x0000 ub4 kcvfhcrt @108 0x221e01bf ub4 kcvfhrlc @112 0x31dd7868 struct kcvfhrls, 8 bytes @116 ub4 kscnbas @116 0x00080634 ub2 kscnwrp @120 0x0000 ub4 kcvfhbti @124 0x00000000 struct kcvfhbsc, 8 bytes @128 ub4 kscnbas @128 0x00000000 ub2 kscnwrp @132 0x0000 ub2 kcvfhbth @136 0x0000 ub2 kcvfhsta @138 0x2000 (NONE) struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x003b97f3 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x342a9561 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000052 ub4 kcrbabno @504 0x00013fc0 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 ub4 kcvfhcpc @140 0x00000087 ub4 kcvfhrts @144 0x3427f1a8 ub4 kcvfhccc @148 0x00000086 struct kcvfhbcp, 36 bytes @152 struct kcvcpscn, 8 bytes @152 ub4 kscnbas @152 0x00000000 ub2 kscnwrp @156 0x0000 ub4 kcvcptim @160 0x00000000 ub2 kcvcpthr @164 0x0000 union u, 12 bytes @168 struct kcvcprba, 12 bytes @168 ub4 kcrbaseq @168 0x00000000 ub4 kcrbabno @172 0x00000000 ub2 kcrbabof @176 0x0000 ub1 kcvcpetb[0] @180 0x00 ub1 kcvcpetb[1] @181 0x00 ub1 kcvcpetb[2] @182 0x00 ub1 kcvcpetb[3] @183 0x00 ub1 kcvcpetb[4] @184 0x00 ub1 kcvcpetb[5] @185 0x00 ub1 kcvcpetb[6] @186 0x00 ub1 kcvcpetb[7] @187 0x00 ub4 kcvfhbhz @312 0x00000000 struct kcvfhxcd, 16 bytes @316 ub4 space_kcvmxcd[0] @316 0x00000000 ub4 space_kcvmxcd[1] @320 0x00000000 ub4 space_kcvmxcd[2] @324 0x00000000 ub4 space_kcvmxcd[3] @328 0x00000000 word kcvfhtsn @332 0 ub2 kcvfhtln @336 0x0006 text kcvfhtnm[0] @338 S text kcvfhtnm[1] @339 Y text kcvfhtnm[2] @340 S text kcvfhtnm[3] @341 T text kcvfhtnm[4] @342 E text kcvfhtnm[5] @343 M text kcvfhtnm[6] @344 text kcvfhtnm[7] @345 text kcvfhtnm[8] @346 text kcvfhtnm[9] @347 text kcvfhtnm[10] @348 text kcvfhtnm[11] @349 text kcvfhtnm[12] @350 text kcvfhtnm[13] @351 text kcvfhtnm[14] @352 text kcvfhtnm[15] @353 text kcvfhtnm[16] @354 text kcvfhtnm[17] @355 text kcvfhtnm[18] @356 text kcvfhtnm[19] @357 text kcvfhtnm[20] @358 text kcvfhtnm[21] @359 text kcvfhtnm[22] @360 text kcvfhtnm[23] @361 text kcvfhtnm[24] @362 text kcvfhtnm[25] @363 text kcvfhtnm[26] @364 text kcvfhtnm[27] @365 text kcvfhtnm[28] @366 text kcvfhtnm[29] @367 ub4 kcvfhrfn @368 0x00000001 struct kcvfhrfs, 8 bytes @372 ub4 kscnbas @372 0x00000000 ub2 kscnwrp @376 0x0000 ub4 kcvfhrft @380 0x00000000 struct kcvfhafs, 8 bytes @384 ub4 kscnbas @384 0x00000000 ub2 kscnwrp @388 0x0000 ub4 kcvfhbbc @392 0x00000000 ub4 kcvfhncb @396 0x00000000 ub4 kcvfhmcb @400 0x00000000 ub4 kcvfhlcb @404 0x00000000 ub4 kcvfhbcs @408 0x00000000 ub2 kcvfhofb @412 0x000a ub2 kcvfhnfb @414 0x000a ub4 kcvfhprc @416 0x221e01a8 struct kcvfhprs, 8 bytes @420 ub4 kscnbas @420 0x00000001 ub2 kscnwrp @424 0x0000 struct kcvfhprfs, 8 bytes @428 ub4 kscnbas @428 0x00000000 ub2 kscnwrp @432 0x0000 ub4 kcvfhtrt @444 0x00000000 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x003b97f3 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x342a9561 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000052 ub4 kcrbabno @504 0x00013fc0 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 BBED> set offset 484 OFFSET 484 BBED> modify /x 0xF397 File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0) Block: 1 Offsets: 484 to 995 Dba:0x00000000 ------------------------------------------------------------------------ f397f4f3 00000000 61952a34 01000000 52000000 c03f0100 1000a865 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000 00000000 02004000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> set offset 486 OFFSET 486 BBED> modify /x 0x4B00 File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0) Block: 1 Offsets: 486 to 997 Dba:0x00000000 ------------------------------------------------------------------------ 4b000000 00006195 2a340100 00005200 0000c03f 01001000 a8650200 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000d00 0d000d00 01000000 00000000 00000000 00000200 40000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x004b97f3 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x342a9561 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000052 ub4 kcrbabno @504 0x00013fc0 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 BBED> sum Check value for File 0, Block 1: current = 0x4fc6, required = 0x4fb6 BBED> sum apply Check value for File 0, Block 1: current = 0x4fb6, required = 0x4fb6 BBED> verify DBVERIFY - Verification starting FILE = /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf BLOCK = 1 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 SQL> select file#,name,checkpoint_change# from v$datafile; 1 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf 3905523 2 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_undotbs1_9f2flf52_.dbf 3905523 3 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_sysaux_9f2flf2v_.dbf 3905523 4 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_users_9f2flf5g_.dbf 3905523 5 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_example_9f2fmfto_.dbf 3905523 6 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_youchuan_9f2fqwr4_.dbf 3905523 7 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_guobao_9f2fwkkt_.dbf 3905523 8 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_tsoa_d_bjx6j57v_.dbf 3905523 8 rows selected. SQL> select file#,FUZZY,CHECKPOINT_CHANGE# from v$datafile_header; 1 NO 4954099 2 NO 3905523 3 NO 3905523 4 NO 3905523 5 NO 3905523 6 NO 3905523 7 NO 3905523 8 NO 3905523 8 rows selected. SQL> alter database open; Database altered. SQL> select file#,FUZZY,CHECKPOINT_CHANGE# from v$datafile_header; 1 YES 4954100 2 YES 4954100 3 YES 4954100 4 YES 4954100 5 YES 4954100 6 YES 4954100 7 YES 4954100 8 YES 4954100 8 rows selected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 591396864 bytes Fixed Size 2022536 bytes Variable Size 180355960 bytes Database Buffers 402653184 bytes Redo Buffers 6365184 bytes Database mounted. Database opened.
ORA-4030 PGA Usage Diagnostic Script
REM Locate the top PGA user set lines 75 set pages 999 set serveroutput on spool topuser.out select * from gv$version; declare a1 number; a2 number; a3 varchar2(30); a4 varchar2(30); a5 number; a6 number; a7 number; a8 number; blankline varchar2(70); cursor code is select pid, spid, substr(username,1,20) "USER" , substr(program,1,30) "Program", PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM from v$process where pga_alloc_mem= (select max(pga_alloc_mem) from v$process where program not like '%LGWR%'); begin blankline:=chr(13); open code; fetch code into a1, a2, a3, a4, a5, a6, a7, a8; dbms_output.put_line(blankline); dbms_output.put_line(' Top PGA User'); dbms_output.put_line(blankline); dbms_output.put_line('PID: '||a1||' '||'SPID: '||a2); dbms_output.put_line('User Info: '||a3); dbms_output.put_line('Program: '||a4); dbms_output.put_line('PGA Used: '||a5); dbms_output.put_line('PGA Allocated: '||a6); dbms_output.put_line('PGA Freeable: '||a7); dbms_output.put_line('Maximum PGA: '||a8); end; / set lines 132 col value format 999,999,999,999,999 select * from v$pgastat; spool off REM REM Investigate memory from the database side REM col TTL format 999,999,999,999 heading "Total Memory" break on report compute sum on report of TTL select bytes TTL from v$sgainfo where name='Maximum SGA Size' union select value from v$pgastat where name='total PGA allocated' / set lines 132 set pages 999 spool workareaoverview.out REM overview of PGA usage col name format a40 head "Name" col value format 999,999,999 head "Total" col unit format a10 head "Units" col pga_size format a25 head "PGA Size" col optimal_executions format 999,999,999,999 head "Optimal" col onepass_executions format 999,999,999,999 head "One-Pass" col multipasses_executions format 999,999,999,999 head "Multi-Pass" col optimal_count format 999,999,999,999 head "Optimal Count" col optimal_perc format 999 head "Optimal|PCT" col onepass_count format 999,999,999,999 head "One-Pass Count" col onepass_perc format 999 head "One|PCT" col multipass_count format 999,999,999,999 head "Multi-Pass Count" col multipass_perc format 999 head "Multi|PCT" col sid format 999,999 Head "SID" col operation format a30 head "Operation" col esize format 999,999,999 head "Expected Size" col mem format 999,999,999 head "Actual Mem" col "MAX MEM" format 999,999,999 head "Maximum Mem" col pass format 999,999 head "Passes" col tsize format 999,999,999,999,999 head "Temporary|Segment Size" spool workareaoverview.out SELECT name, decode(unit, 'bytes', trunc(value/1024/1024), value) value , decode(unit, 'bytes', 'MBytes', unit) unit FROM V$PGASTAT / REM Review workarea buckets to see how efficient memory is utilized REM Ideal to see OPTIMAL EXECUTIONS vs. ONE-PASS and Multi-PASS select case when low_optimal_size < 1024*1024 then to_char(low_optimal_size/1024,'999999') || 'kb 0 order by low_optimal_size / REM Review workarea buckets as percentages overall REM this script assuming 64K optimal size SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc, onepass_count, round(onepass_count*100/total, 2) onepass_perc, multipass_count, round(multipass_count*100/total, 2) multipass_perc FROM (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total, sum(OPTIMAL_EXECUTIONS) optimal_count, sum(ONEPASS_EXECUTIONS) onepass_count, sum(MULTIPASSES_EXECUTIONS) multipass_count FROM v$sql_workarea_histogram WHERE low_optimal_size > 64*1024) / REM Review current activity in Work Areas SELECT to_number(decode(SID, 65535, NULL, SID)) sid, operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE, trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM", NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE FROM V$SQL_WORKAREA_ACTIVE ORDER BY 1,2 / alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; select sysdate from dual; select * from v$pgastat; col time form a30 col name form a30 select a.BEGIN_INTERVAL_TIME time, b.* from DBA_HIST_SNAPSHOT a, DBA_HIST_PGASTAT b where a.SNAP_ID=b.SNAP_ID and b.name='total PGA allocated' order by a.BEGIN_INTERVAL_TIME desc / show parameter parallel_execution_message_size show parameter memory show parameter pga spool off clear col #collect AIX info ls -al $ORACLE_HOME/bin/oracle >> /tmp/support.txt oslevel -s whoami >> /tmp/support.txt ulimit -a >> /tmp/support.txt svmon -O unit=MB >> /tmp/support.txt /usr/sbin/lsps -a >> /tmp/support.txt /usr/sbin/lsattr -HE -l sys0 -a realmem >> /tmp/support.txt ipcs -m >> /tmp/support.txt opatch lsinventory -detail #collect Linux info arch cat /etc/issue whoami ulimit -a df -h /dev/shm ipcs -ma cat /etc/sysctl.conf cat /proc/meminfo cat /proc/swaps cat /proc/vmstat opatch lsinventory -detail
Oracle内部错误ORA-600:[1112]
以下为ORA-600[1112]内部错误的相关诊断信息:
ERROR: ORA-600 [1112] [a] [b] [c] [d] [e] VERSIONS: versions 7.3 to 9.2 DESCRIPTION: ORA-600 [1112] is getting raised while trying to add a row cache enqueue to a transaction state object during lookup of the default tablespace number during table creation. FUNCTIONALITY: STATE OBJECT MANAGEMENT IMPACT: PROCESS FAILURE NON CORRUPTIVE - No underlying data corruption. Bug 2489130 - OERI:1112 can occur while dumping PROCESSSTATE informatio (Doc ID 2489130.8) Bug 4126973: ORA-600[504] AND ORA-600[1112] OCCURED WHEN GETTING "ERRORSTACK" Base Bug 2489130 Bug 3954753: ORA-600 [1112] AND SESSION CRASH The cause for the ORA-00600 [1112] appears due to Bug 2489130 This error can occur on dumping of process state which is what occurred here. The primary issue is the WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! This then triggers a system state and process state to be dumped due to nature of the problem. The ORA-00600 [1112] gets dumped out when process state is done. Stack for trace very similar to Bug 2489130 and this is only known bug on 9.2 like this with a fix. A fix for bug 2489130 is included in the 9.2.0.7 patchset. Recommend applying 9.2.0.8 patchset to have this and other bug fixes. This would only prevent the ORA-00600 [1112] from occurring on state dumps. The primary row cache issue still to be investigated by performance team.
How many LMS processes for Oracle Rac 9i?
Question:
How many LMS processes for Oracle 9i Rac?
Can you check if you have increased the CPU in the machine during the upgrade.
Also please let me know if you have changed any underscore parameter.
Do you have some formula for calculate LMS processes and Num of CPU ?? Let say 2 CPU for 1 LMS process ??
Answer:
This can go dynamically as and when required and the # of startup of lms is also controlled by
_lm_lms,_lm_max_lms _lm_min_lms , later 2 seems to be for dynamic control of this numbers.
These parameters should not be set manually.
Also the no. of lms process should be one less than the number of CPU on the node.
Tune Very Large Hash Join
set timing on; alter session set workarea_size_policy=MANUAL; alter session set workarea_size_policy=MANUAL; alter session set db_file_multiblock_read_count=512; alter session set db_file_multiblock_read_count=512; alter session set events '10351 trace name context forever, level 128'; alter session set hash_area_size=524288000; alter session set hash_area_size=524288000; alter session set "_hash_multiblock_io_count"=128; alter session set "_hash_multiblock_io_count"=128; alter session enable parallel query; select /*+ pq_distribute(a hash,hash) parallel(a) parallel(b) */ column1,column2.... from source_tab a, driving_tab b where condition ; --PQ_DISTRIBUTE(tab,out,in) How to distribute rows from tab in a PQ --(out/in may be HASH/NONE/BROADCAST/PARTITION)
unique index vs non-unique index
Question:
What is between between “unique index vs non-unique index”. Which one is faster. The customer using non-unique and sql is getting delay If we change tp non-unique. Is it work ?
Answer:
Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.
Oracle recommends that unique indexes be created explicitly, using CREATE UNIQUE INDEX. Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index.
It is just that in a unique index, the rowid is not considered “part of the key” and in a non-unique index “the rowid is considered part of the key”.
From Performance point of view:
The optimizer can look at an index that is unique and check, if you use “where x =:x and y = :y and ….” I’m going to get ONE row back, I can cost that much better”
If the index is non-unique, the optimizer will perform , index range scan, he is going to get 0..N rows back” and it’ll cost it differently.
So, a unique index will affect the generated plan — it is more information for the optimizer to grab onto.
If the data must be UNIQUE, you should use a UNIQUE constraint – not an index. We will take care of the index for you. If the constraint is not deferrable, we’ll create a unique index for you. If the constraint is deferrable — we’ll use a non-unique index.
Non-Unique indexes have various “overheads” when compared to Unique Indexes
Will examine two key differences today:
- Extra byte required per index row entry
- Index requires additional consistent reads and latch gets
Reading a Non-Unique Index is more expensive in terms of consistent reads and latches.