Script:Generating CREATE USER DDL Statements

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: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_LREAD Logical reads for the session
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.

沪ICP备14014813号-2

沪公网安备 31010802001379号