RMAN>shutdown abort;
RMAN>startup nomount;
RMAN>restore database;
ORA-01507
RMAN>alter database mount;
RMAN>restore database;
RMAN>recover database;
RMAN-00571
RMAN-00569
RMAN-00571
RMAN-03002
RMAN-06053 onemli
RMAN-06025
RMAN-06053 için;
RMAN>shutdown abort;
RMAN>startup nomount;
RMAN>restore controlfile;
RMAN-06563 -- Doc ID: 291432.1 --Note 22080.1
RMAN>show all;
tum parametreler default olmuş
RMAN>alter database mount;
RMAN>alter database open;
--
RMAN target /
RMAN>startup nomount
RMAN> run {
> allocate channel t1 type DISK
> restore controlfile from autobackup;
> sql 'alter database mount';
> restore database;
> switch datafile all;
> recover database;
> alter database open resetlogs;
> }
http://www.orafaq.com/forum/t/141370/2/
-----------------------------------
cold backup
>startup;
ORA-01157
ORA-01110
12 Ağustos 2009 Çarşamba
11 Ağustos 2009 Salı
ORA-00600: internal error code, arguments: [kqlfFillBindData:1]
http://updates.oracle.com/ARULink/PatchDetails/process_form?aru=9130568&patch_password=&no_header=0
Subject: ORA-00600[kqlfFillBindData:1] Raised
Doc ID: 428018.1 Type: PROBLEM
Modified Date: 23-MAY-2008 Status: MODERATED
In this Document
Symptoms
Cause
Solution
References
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.2.0
This problem can occur on any platform.
Symptoms
The following errors are seen in the alert log:
ORA-00600: internal error code, arguments: [kqlfFillBindData:1]
The following errors may also occur:
ORA-00600: internal error code, arguments: [729]
ORA-00600: internal error code, arguments: [1234]
The stack trace may look like the following:
ksedst ksedmp ksfdmp kgerinv kgeasnmierr kqlfFillBindData kqlffc kqlfgo kgligo
Cause
This issue is caused by Bug 5638146 ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2.
Solution
To resolve this issue, you may choose any one of these options:
1. Apply the 10.2.0.4 patchset which was not available at the time of this writing (Apr 2007).
2. Check MetaLink for backport Patch 5638146 for your operating system and database release.
3. Use the workaround:
Use a smaller number of bind variables in the query.
References
Bug 5638146 - ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
------------------------------------------
SQL ;
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kqlfFillBindData:1], [669], [468], [], [], [], [], []
Current SQL statement for this session:
insert into wrh$_sqlstat (snap_id, dbid, instance_number, sql_id, plan_hash_value, optimizer_cost, optimizer_mode, optimizer_env_hash_value, sharable_mem, loaded_versions, version_count, module, action, sql_profile, force_matching_signature, parsing_schema_id, parsing_schema_name, fetches_total, fetches_delta, end_of_fetch_count_total, end_of_fetch_count_delta, sorts_total, sorts_delta, executions_total, executions_delta, px_servers_execs_total, px_servers_execs_delta, loads_total, loads_delta, invalidations_total, invalidations_delta, parse_calls_total, parse_calls_delta, disk_reads_total, disk_reads_delta, buffer_gets_total, buffer_gets_delta, rows_processed_total, rows_processed_delta, cpu_time_total, cpu_time_delta, elapsed_time_total, elapsed_time_delta, iowait_total, iowait_delta, clwait_total, clwait_delta, apwait_total, apwait_delta, ccwait_total, ccwait_delta, direct_writes_total, direct_writes_delta, plsexec_time_total, plsexec_time_delta, javexec_time_total, javexec_time_delta, bind_data, flag) SELECT /*+ ordered use_nl(sql) index(sql kglobt03) */ :snap_id, :dbid, :instance_number, kglobt03, kglobt30, kglobtn0, decode(kglobt32, 0, 'NONE', 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE', 4, 'CHOOSE', 'UNKNOWN'), kglobcceh, kglobhs0+kglobhs1+kglobhs2+kglobhs3+ kglobhs4+kglobhs5+kglobhs6, kglobclc, kglobccc, kglobts0, kglobts1, kglobts3, kglobt49, kglobt18, kglobts4, kglobt04, kglobdft, kglobt35, kglobdef, kglobt01, kglobdso, kglobt05, kglobdex, kglobt48, kglobdpx, kglhdldc, kglobdld, kglhdivc, kglobdiv, kglobt12, kglobdps, kglobt13, kglobddr, kglobt14, kglobdbf, kglobt15, kglobdro, kglobt06, kglobdcp, kglobt07, kglobdel, kglobwui, kglobdui, kglobwcl, kglobdcl, kglobwap, kglobdap, kglobwcc, kglobdcc, kglobwdw, kglobddw, kglobt42, kglobdpl, kglobt43, kglobdjv, kglobcbca, NULL FROM X$KEWRSQLIDTAB sie, X$KGLCURSOR_CHILD_SQLIDPH sql WHERE sie.sqlid_kewrsie = sql.kglobt03
----- Call Stack Trace -----
---------------------------------
Bug No: 5638146
Filed 01-NOV-2006 Updated 01-APR-2009
Product Oracle Server - Enterprise Edition Product Version 10.2.0.2.0
Platform AIX5L Based Systems (64-bit) Platform Version No Data
Database Version 10.2.0.2.0 Affects Platforms Generic
Severity Severe Loss of Service Status Development to Q/A
Base Bug N/A Fixed in Product Version 11.1.0.0
Problem statement:
ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
*** 11/01/06 03:38 pm ***
PLEASE SEE TESTCASE AT *** RSPOWELL 02/19/07 05:12 am ***
*** 11/08/06 04:43 pm *** (CHG: Sta->16)
*** 11/08/06 04:43 pm ***
*** 11/09/06 03:05 am *** (CHG: Asg->NEW OWNER)
*** 11/09/06 03:39 am *** (CHG: Sta->10)
*** 11/09/06 03:39 am ***
*** 12/01/06 03:53 pm *** (CHG: Sta->16)
*** 12/01/06 03:53 pm ***
*** 12/01/06 03:56 pm ***
*** 12/04/06 01:12 am *** (CHG: Sta->10)
*** 12/04/06 01:12 am ***
The CTAS has to be taken when the problem ORA-600 occurs
so we can look at the content in relation to the trace
to see if we can see what may have caused the problem.
Do you have the trace matching the time when this CTAS
was done so we can try to cross reference.
*** 12/19/06 03:57 pm *** (CHG: Sta->16)
*** 12/19/06 03:57 pm ***
*** 12/20/06 03:42 am *** (CHG: Sta->10)
*** 12/20/06 03:42 am ***
*** 01/19/07 10:31 am *** (CHG: Sta->16)
*** 01/19/07 10:31 am ***
*** 01/22/07 09:41 am *** (CHG: Sta->10)
*** 01/22/07 09:41 am ***
*** 01/22/07 02:25 pm *** (CHG: Sta->16)
*** 01/22/07 02:25 pm ***
*** 01/23/07 02:37 am *** (CHG: Sta->10)
*** 01/23/07 02:37 am ***
I suspect "c" may raise questions. Something like
this may help catch the bad SQL_ID:
.
drop table xx_bug;
create table xx_bug (
SQL_ID VARCHAR2(13),
ADDR RAW(8)
);
declare
bnd raw(2000);
begin
for R in (select addr, KGLOBT03 from X$KGLCURSOR_CHILD_SQLIDPH)
loop
insert into xx_bug values(R.KGLOBT03, R.addr);
commit;
begin
-- See if this SQL_ID bind info throws ORA-600
select max(KGLOBCBCA) into bnd from X$KGLCURSOR_CHILD_SQLIDPH
where KGLOBT03=R.KGLOBT03;
exception
when no_data_found then
null; -- This is ok as row gone now
end;
-- If we got here we did not ORA-600. Delete the row.
delete from xx_bug;
commit;
end loop;
end;
/
set pages 1000
select * from xx_bug;
select * from X$KGLCURSOR_CHILD_SQLIDPH where KGLOBT03='&id';
.
.
This will scan X$KGLCURSOR_CHILD_SQLIDPH and for each
row insert the SQL_ID into xx_bug. It then tries to get
the bind info for that X$KGLCURSOR_CHILD_SQLIDPH sql_id .
If it succeeds this is not the bad row so it deletes from
xx_bug. If the row has gone it deletes the xx_bug row.
If the row throws ORA-600 the SQL_ID will be left in
xx_bug.
.
The select after the PLSQL block should confirm if that
is the problem SQL_ID. As much info as possible about that
SQL_ID would then be helpful, including all the events+dumps
listed.
.
*** 02/16/07 03:30 pm *** (CHG: Sta->16)
*** 02/16/07 03:30 pm ***
*** 02/19/07 05:12 am *** (CHG: Sta->11 SubComp->LIB CACHE)
*** 02/19/07 05:12 am ***
.
BDE Screening
~~~~~~~~~~~~~
Testcase
~~~~~~~~~~~~~~~~~
.
Files: BDETC.tar.Z (containing setup.sql, tc.sql)
.
Steps:
sqlplus /nolog @setup
Create a user TC, a table TST and then issues a SQL
statement which uses a large number of bind variables
(5480 binds) . The statement is run few times to ensure it
is cached in the SGA.
.
sqlplus /nolog @tc
Runs a select of KGLOBCBCA from X$KGLCURSOR_CHILD_SQLIDPH
^
ORA-600 [kqlfFillBindData:1], [1200], [636]
.
Reproduced
~~~~~~~~~~
Reproduced in 10.2.0.2
Reproduced in RDBMS_MAIN_LINUX_070216
.
.
Workaround/s
~~~~~~~~~~~~
Do not use > 5461 bind variables in a single SQL
.
.
*** 02/19/07 05:12 am *** (CHG: Asg->NEW OWNER)
*** 02/19/07 05:12 am ***
*** 02/21/07 05:00 am *** (CHG: DevPri->2)
*** 02/21/07 05:00 am *** (CHG: Confirmed Flag->Y)
*** 02/21/07 05:00 am *** (CHG: Asg->NEW OWNER)
*** 02/21/07 05:00 am ***
*** 02/21/07 05:00 am *** (CHG: Asg->NEW OWNER)
*** 02/23/07 07:00 am *** ESCALATED
*** 02/23/07 07:04 am ***
*** 02/24/07 08:36 am *** (CHG: Asg->NEW OWNER)
*** 02/24/07 08:36 am ***
*** 03/02/07 03:14 pm ***
*** 03/07/07 06:18 pm ***
*** 03/20/07 05:44 am ***
*** 03/20/07 09:37 am ***
*** 03/28/07 10:22 am ***
*** 04/03/07 07:49 am ***
*** 04/04/07 06:40 pm ***
REDISCOVERY INFORMATION:
If 'ORA-00600 [kqlfFillBindData:1]' is raised and there are
thousands of bind variables in the SQL statement, then you
may be encountering this particular problem.
WORKAROUND:
Try to reduce the number of bind variables.
RELEASE NOTES:
]]'ORA-00600 [kqlfFillBindData:1]' is no longer raised against
]]a SQL statement containing thousands of bind variables.
*** 04/04/07 06:47 pm *** (CHG: Fixed->11.1.0.0)
*** 04/04/07 06:47 pm *** (CHG: Sta->80)
*** 04/05/07 06:14 am ***
*** 04/05/07 08:43 am ***
*** 04/05/07 12:58 pm ***
*** 04/05/07 01:25 pm ***
*** 04/05/07 01:26 pm ***
*** 04/05/07 01:26 pm ***
*** 04/06/07 04:42 am ***
*** 04/06/07 04:04 pm ***
*** 04/08/07 11:28 am ***
*** 04/09/07 08:58 am ***
*** 04/09/07 03:19 pm ***
*** 04/10/07 07:09 am ***
*** 04/10/07 01:17 pm ***
*** 04/11/07 03:11 pm ***
*** 04/12/07 01:53 pm ***
*** 04/12/07 07:30 pm ***
*** 04/13/07 10:30 am ***
*** 04/15/07 04:31 pm ***
*** 04/15/07 04:32 pm ***
*** 04/15/07 04:32 pm ***
*** 04/15/07 04:34 pm ***
*** 04/15/07 04:35 pm ***
*** 04/15/07 04:36 pm ***
*** 04/15/07 04:36 pm ***
*** 04/15/07 04:37 pm ***
*** 04/15/07 04:37 pm ***
*** 04/16/07 07:22 am ***
*** 04/18/07 02:24 am ***
*** 04/18/07 03:50 am ***
*** 04/19/07 12:20 am ***
*** 04/19/07 04:22 am ***
*** 04/19/07 05:18 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:22 am ***
*** 04/19/07 05:28 am ***
*** 04/19/07 10:51 am ***
*** 04/23/07 02:50 am ***
*** 04/23/07 02:52 am ***
*** 04/24/07 05:59 am ***
*** 05/14/07 03:21 pm ***
*** 05/16/07 03:10 am ***
*** 05/16/07 11:27 pm ***
*** 05/18/07 03:26 am ***
*** 05/18/07 03:28 am ***
*** 05/18/07 04:02 am ***
*** 05/18/07 04:04 am ***
*** 05/18/07 04:13 am ***
*** 05/18/07 06:31 am ***
*** 05/25/07 06:54 pm ***
*** 05/29/07 07:47 pm ***
*** 06/01/07 01:33 pm ***
bug 5638146
*** 06/26/07 03:22 am ***
*** 06/28/07 01:08 am ***
*** 06/28/07 01:18 am ***
*** 07/30/07 02:02 am ***
*** 08/08/07 01:07 pm ***
*** 08/08/07 01:08 pm ***
*** 08/23/07 11:59 am ***
*** 08/29/07 02:08 am ***
*** 08/30/07 12:18 pm ***
*** 08/30/07 12:18 pm ***
*** 09/01/07 03:16 am ***
*** 09/07/07 07:05 am ***
*** 09/07/07 07:07 am ***
*** 10/26/07 01:01 am ***
*** 11/09/07 03:08 am ***
*** 11/09/07 03:20 am ***
*** 11/09/07 12:07 pm ***
*** 11/09/07 12:09 pm ***
*** 11/09/07 09:29 pm ***
*** 11/09/07 09:31 pm ***
*** 11/13/07 01:50 pm ***
*** 11/18/07 03:25 pm ***
*** 11/19/07 11:48 pm ***
*** 11/19/07 11:50 pm ***
*** 05/07/08 01:17 am ***
*** 05/07/08 02:42 am ***
*** 05/07/08 02:42 am ***
*** 12/17/08 04:42 pm ***
*** 12/24/08 05:19 am ***
*** 03/17/09 03:25 pm ***
*** 03/17/09 05:57 pm ***
*** 04/01/09 11:08 am ***
.
----------------------------------
README for 5638146
Patch Details
#
# WARNING: Failure to carefully read and understand these requirements may
# result in your applying a patch that can cause your Oracle Server to
# malfunction, including interruption of service and/or loss of data.
#
# If you do not meet all of the following requirements, please log an
# iTAR, so that an Oracle Support Analyst may review your situation. The
# Oracle analyst will help you determine if this patch is suitable for you
# to apply to your system. We recommend that you avoid applying any
# temporary patch unless directed by an Oracle Support Analyst who has
# reviewed your system and determined that it is applicable.
#
# Requirements:
#
# - You must have located this patch via a Bug Database entry
# and have the exact symptoms described in the bug entry.
#
# - Your system configuration (Oracle Server version and patch
# level, OS Version) must exactly match those in the bug
# database entry - You must have NO OTHER PATCHES installed on
# your Oracle Server since the latest patch set (or base release
# x.y.z if you have no patch sets installed).
#
# - [Oracle 9.2.0.2 & above] You must have Perl 5.00503 (or later)
# installed under the ORACLE_HOME, or elsewhere within the host
# environment. OPatch is no longer included in patches as of 9.2.0.2.
# Refer to the following link for details on Perl and OPatch:
# http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=189489.1
#
# - [IBM AIX O/S & Java patches for Oracle 9.2]
# In order to apply java class updates to IBM AIX based systems using
# java_131, you must update your java if you are running a version prior
# to Service Refresh build date 20030630a. This is
# necessary to fix IBM Defect#60472.
#
# To identify which java build date you are on, enter the following
# command ;
#
# > $ORACLE_HOME/jdk/bin/java -fullversion
# ... example response ...
# java full version "J2RE 1.3.1 IBM AIX build ca131-20030630a"
#
# The string ends in the date format YYYYMMDD or YYYYMMDDa where 'a'
# indicates an updated release to the original build. You should always
# apply the latest AIX Java SDK 1.3.1 Service Update available from IBM.
# As a minimum, the above service refresh can be found under
# APAR IY47055. The signature for the updated JVM is ca131-20030630a.
# Information on the latest available fixes, as well as how to apply
# the APARs to your AIX systems, is available at the IBM Java site.
#
# If you are running AIX 5L, you can safely ignore any comment against
# the APAR that says (AIXV43 only). The APAR is applicable to
# both AIX 4.3 and AIX 5L.
#
# Once you have updated your java installation you need to copy these
# updated files to Oracle's copies in $ORACLE_HOME/jdk.
# As the Oracle owner, simply issue the following commands;
#
# > cd /usr/java131
# > cp -fpR * $ORACLE_HOME/jdk
#
#
# If you do NOT meet these requirements, or are not certain that you meet
# these requirements, please log an iTAR requesting assistance with this
# patch and Support will make a determination about whether you should
# apply this patch.
#
#-------------------------------------------------------------------------
# Interim Patch for Base Bugs: 5638146
#-------------------------------------------------------------------------
#
# DATE: Wed Apr 18 22:32:51 2007
# -------------------------------
# Platform Patch for : AIX5L Based Systems (64-bit)
# Product Version # : 10.2.0.2
# Product Patched : RDBMS
#
# Bugs Fixed by this patch:
# -------------------------
# 5638146:ORA-600[KQLFFILLBINDDATA 1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
#
# Patch Installation Instructions:
# --------------------------------
# To apply the patch, unzip the PSE container file:
#
# p5638146_10202_AIX64-5L.zip
#
# Set your current directory to the directory where the patch
# is located:
#
# % cd 5638146
# On AIX platforms only for 10201 Patches :
#
# Ensure environment variable OBJECT_MODE is set to 32_64
# e.g.
#
# % setenv OBJECT_MODE 32_64
# or
# $ export OBJECT_MODE=32_64
#
# Ensure that the directory containing the opatch script appears in
# your $PATH; then enter the following command:
#
# % opatch apply
#
# Patch Special Instructions:
# ---------------------------
# Make sure all instances running under the ORACLE_HOME being patched
# are cleanly shutdown before installing this patch. Also ensure that
# the tool used to terminate the instance(s) has exited cleanly.
#
# If the Oracle inventory is not setup correctly this utility will
# fail. To check accessibility to the inventory you can use the
# command
#
# % opatch lsinventory
#
# If you have any problems installing this PSE or are not sure
# about inventory setup please call Oracle support.
#
# Patch Deinstallation Instructions:
# ----------------------------------
# Use the following command:
#
# % cd 5638146
# % opatch rollback -id 5638146
#
----------------------------------
ora600_sapnote_0001033315
Bugs-5638146
Subject: ORA-00600[kqlfFillBindData:1] Raised
Doc ID: 428018.1 Type: PROBLEM
Modified Date: 23-MAY-2008 Status: MODERATED
In this Document
Symptoms
Cause
Solution
References
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.2.0
This problem can occur on any platform.
Symptoms
The following errors are seen in the alert log:
ORA-00600: internal error code, arguments: [kqlfFillBindData:1]
The following errors may also occur:
ORA-00600: internal error code, arguments: [729]
ORA-00600: internal error code, arguments: [1234]
The stack trace may look like the following:
ksedst ksedmp ksfdmp kgerinv kgeasnmierr kqlfFillBindData kqlffc kqlfgo kgligo
Cause
This issue is caused by Bug 5638146 ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2.
Solution
To resolve this issue, you may choose any one of these options:
1. Apply the 10.2.0.4 patchset which was not available at the time of this writing (Apr 2007).
2. Check MetaLink for backport Patch 5638146 for your operating system and database release.
3. Use the workaround:
Use a smaller number of bind variables in the query.
References
Bug 5638146 - ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
------------------------------------------
SQL ;
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kqlfFillBindData:1], [669], [468], [], [], [], [], []
Current SQL statement for this session:
insert into wrh$_sqlstat (snap_id, dbid, instance_number, sql_id, plan_hash_value, optimizer_cost, optimizer_mode, optimizer_env_hash_value, sharable_mem, loaded_versions, version_count, module, action, sql_profile, force_matching_signature, parsing_schema_id, parsing_schema_name, fetches_total, fetches_delta, end_of_fetch_count_total, end_of_fetch_count_delta, sorts_total, sorts_delta, executions_total, executions_delta, px_servers_execs_total, px_servers_execs_delta, loads_total, loads_delta, invalidations_total, invalidations_delta, parse_calls_total, parse_calls_delta, disk_reads_total, disk_reads_delta, buffer_gets_total, buffer_gets_delta, rows_processed_total, rows_processed_delta, cpu_time_total, cpu_time_delta, elapsed_time_total, elapsed_time_delta, iowait_total, iowait_delta, clwait_total, clwait_delta, apwait_total, apwait_delta, ccwait_total, ccwait_delta, direct_writes_total, direct_writes_delta, plsexec_time_total, plsexec_time_delta, javexec_time_total, javexec_time_delta, bind_data, flag) SELECT /*+ ordered use_nl(sql) index(sql kglobt03) */ :snap_id, :dbid, :instance_number, kglobt03, kglobt30, kglobtn0, decode(kglobt32, 0, 'NONE', 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE', 4, 'CHOOSE', 'UNKNOWN'), kglobcceh, kglobhs0+kglobhs1+kglobhs2+kglobhs3+ kglobhs4+kglobhs5+kglobhs6, kglobclc, kglobccc, kglobts0, kglobts1, kglobts3, kglobt49, kglobt18, kglobts4, kglobt04, kglobdft, kglobt35, kglobdef, kglobt01, kglobdso, kglobt05, kglobdex, kglobt48, kglobdpx, kglhdldc, kglobdld, kglhdivc, kglobdiv, kglobt12, kglobdps, kglobt13, kglobddr, kglobt14, kglobdbf, kglobt15, kglobdro, kglobt06, kglobdcp, kglobt07, kglobdel, kglobwui, kglobdui, kglobwcl, kglobdcl, kglobwap, kglobdap, kglobwcc, kglobdcc, kglobwdw, kglobddw, kglobt42, kglobdpl, kglobt43, kglobdjv, kglobcbca, NULL FROM X$KEWRSQLIDTAB sie, X$KGLCURSOR_CHILD_SQLIDPH sql WHERE sie.sqlid_kewrsie = sql.kglobt03
----- Call Stack Trace -----
---------------------------------
Bug No: 5638146
Filed 01-NOV-2006 Updated 01-APR-2009
Product Oracle Server - Enterprise Edition Product Version 10.2.0.2.0
Platform AIX5L Based Systems (64-bit) Platform Version No Data
Database Version 10.2.0.2.0 Affects Platforms Generic
Severity Severe Loss of Service Status Development to Q/A
Base Bug N/A Fixed in Product Version 11.1.0.0
Problem statement:
ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
*** 11/01/06 03:38 pm ***
PLEASE SEE TESTCASE AT *** RSPOWELL 02/19/07 05:12 am ***
*** 11/08/06 04:43 pm *** (CHG: Sta->16)
*** 11/08/06 04:43 pm ***
*** 11/09/06 03:05 am *** (CHG: Asg->NEW OWNER)
*** 11/09/06 03:39 am *** (CHG: Sta->10)
*** 11/09/06 03:39 am ***
*** 12/01/06 03:53 pm *** (CHG: Sta->16)
*** 12/01/06 03:53 pm ***
*** 12/01/06 03:56 pm ***
*** 12/04/06 01:12 am *** (CHG: Sta->10)
*** 12/04/06 01:12 am ***
The CTAS has to be taken when the problem ORA-600 occurs
so we can look at the content in relation to the trace
to see if we can see what may have caused the problem.
Do you have the trace matching the time when this CTAS
was done so we can try to cross reference.
*** 12/19/06 03:57 pm *** (CHG: Sta->16)
*** 12/19/06 03:57 pm ***
*** 12/20/06 03:42 am *** (CHG: Sta->10)
*** 12/20/06 03:42 am ***
*** 01/19/07 10:31 am *** (CHG: Sta->16)
*** 01/19/07 10:31 am ***
*** 01/22/07 09:41 am *** (CHG: Sta->10)
*** 01/22/07 09:41 am ***
*** 01/22/07 02:25 pm *** (CHG: Sta->16)
*** 01/22/07 02:25 pm ***
*** 01/23/07 02:37 am *** (CHG: Sta->10)
*** 01/23/07 02:37 am ***
I suspect "c" may raise questions. Something like
this may help catch the bad SQL_ID:
.
drop table xx_bug;
create table xx_bug (
SQL_ID VARCHAR2(13),
ADDR RAW(8)
);
declare
bnd raw(2000);
begin
for R in (select addr, KGLOBT03 from X$KGLCURSOR_CHILD_SQLIDPH)
loop
insert into xx_bug values(R.KGLOBT03, R.addr);
commit;
begin
-- See if this SQL_ID bind info throws ORA-600
select max(KGLOBCBCA) into bnd from X$KGLCURSOR_CHILD_SQLIDPH
where KGLOBT03=R.KGLOBT03;
exception
when no_data_found then
null; -- This is ok as row gone now
end;
-- If we got here we did not ORA-600. Delete the row.
delete from xx_bug;
commit;
end loop;
end;
/
set pages 1000
select * from xx_bug;
select * from X$KGLCURSOR_CHILD_SQLIDPH where KGLOBT03='&id';
.
.
This will scan X$KGLCURSOR_CHILD_SQLIDPH and for each
row insert the SQL_ID into xx_bug. It then tries to get
the bind info for that X$KGLCURSOR_CHILD_SQLIDPH sql_id .
If it succeeds this is not the bad row so it deletes from
xx_bug. If the row has gone it deletes the xx_bug row.
If the row throws ORA-600 the SQL_ID will be left in
xx_bug.
.
The select after the PLSQL block should confirm if that
is the problem SQL_ID. As much info as possible about that
SQL_ID would then be helpful, including all the events+dumps
listed.
.
*** 02/16/07 03:30 pm *** (CHG: Sta->16)
*** 02/16/07 03:30 pm ***
*** 02/19/07 05:12 am *** (CHG: Sta->11 SubComp->LIB CACHE)
*** 02/19/07 05:12 am ***
.
BDE Screening
~~~~~~~~~~~~~
Testcase
~~~~~~~~~~~~~~~~~
.
Files: BDETC.tar.Z (containing setup.sql, tc.sql)
.
Steps:
sqlplus /nolog @setup
Create a user TC, a table TST and then issues a SQL
statement which uses a large number of bind variables
(5480 binds) . The statement is run few times to ensure it
is cached in the SGA.
.
sqlplus /nolog @tc
Runs a select of KGLOBCBCA from X$KGLCURSOR_CHILD_SQLIDPH
^
ORA-600 [kqlfFillBindData:1], [1200], [636]
.
Reproduced
~~~~~~~~~~
Reproduced in 10.2.0.2
Reproduced in RDBMS_MAIN_LINUX_070216
.
.
Workaround/s
~~~~~~~~~~~~
Do not use > 5461 bind variables in a single SQL
.
.
*** 02/19/07 05:12 am *** (CHG: Asg->NEW OWNER)
*** 02/19/07 05:12 am ***
*** 02/21/07 05:00 am *** (CHG: DevPri->2)
*** 02/21/07 05:00 am *** (CHG: Confirmed Flag->Y)
*** 02/21/07 05:00 am *** (CHG: Asg->NEW OWNER)
*** 02/21/07 05:00 am ***
*** 02/21/07 05:00 am *** (CHG: Asg->NEW OWNER)
*** 02/23/07 07:00 am *** ESCALATED
*** 02/23/07 07:04 am ***
*** 02/24/07 08:36 am *** (CHG: Asg->NEW OWNER)
*** 02/24/07 08:36 am ***
*** 03/02/07 03:14 pm ***
*** 03/07/07 06:18 pm ***
*** 03/20/07 05:44 am ***
*** 03/20/07 09:37 am ***
*** 03/28/07 10:22 am ***
*** 04/03/07 07:49 am ***
*** 04/04/07 06:40 pm ***
REDISCOVERY INFORMATION:
If 'ORA-00600 [kqlfFillBindData:1]' is raised and there are
thousands of bind variables in the SQL statement, then you
may be encountering this particular problem.
WORKAROUND:
Try to reduce the number of bind variables.
RELEASE NOTES:
]]'ORA-00600 [kqlfFillBindData:1]' is no longer raised against
]]a SQL statement containing thousands of bind variables.
*** 04/04/07 06:47 pm *** (CHG: Fixed->11.1.0.0)
*** 04/04/07 06:47 pm *** (CHG: Sta->80)
*** 04/05/07 06:14 am ***
*** 04/05/07 08:43 am ***
*** 04/05/07 12:58 pm ***
*** 04/05/07 01:25 pm ***
*** 04/05/07 01:26 pm ***
*** 04/05/07 01:26 pm ***
*** 04/06/07 04:42 am ***
*** 04/06/07 04:04 pm ***
*** 04/08/07 11:28 am ***
*** 04/09/07 08:58 am ***
*** 04/09/07 03:19 pm ***
*** 04/10/07 07:09 am ***
*** 04/10/07 01:17 pm ***
*** 04/11/07 03:11 pm ***
*** 04/12/07 01:53 pm ***
*** 04/12/07 07:30 pm ***
*** 04/13/07 10:30 am ***
*** 04/15/07 04:31 pm ***
*** 04/15/07 04:32 pm ***
*** 04/15/07 04:32 pm ***
*** 04/15/07 04:34 pm ***
*** 04/15/07 04:35 pm ***
*** 04/15/07 04:36 pm ***
*** 04/15/07 04:36 pm ***
*** 04/15/07 04:37 pm ***
*** 04/15/07 04:37 pm ***
*** 04/16/07 07:22 am ***
*** 04/18/07 02:24 am ***
*** 04/18/07 03:50 am ***
*** 04/19/07 12:20 am ***
*** 04/19/07 04:22 am ***
*** 04/19/07 05:18 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:22 am ***
*** 04/19/07 05:28 am ***
*** 04/19/07 10:51 am ***
*** 04/23/07 02:50 am ***
*** 04/23/07 02:52 am ***
*** 04/24/07 05:59 am ***
*** 05/14/07 03:21 pm ***
*** 05/16/07 03:10 am ***
*** 05/16/07 11:27 pm ***
*** 05/18/07 03:26 am ***
*** 05/18/07 03:28 am ***
*** 05/18/07 04:02 am ***
*** 05/18/07 04:04 am ***
*** 05/18/07 04:13 am ***
*** 05/18/07 06:31 am ***
*** 05/25/07 06:54 pm ***
*** 05/29/07 07:47 pm ***
*** 06/01/07 01:33 pm ***
bug 5638146
*** 06/26/07 03:22 am ***
*** 06/28/07 01:08 am ***
*** 06/28/07 01:18 am ***
*** 07/30/07 02:02 am ***
*** 08/08/07 01:07 pm ***
*** 08/08/07 01:08 pm ***
*** 08/23/07 11:59 am ***
*** 08/29/07 02:08 am ***
*** 08/30/07 12:18 pm ***
*** 08/30/07 12:18 pm ***
*** 09/01/07 03:16 am ***
*** 09/07/07 07:05 am ***
*** 09/07/07 07:07 am ***
*** 10/26/07 01:01 am ***
*** 11/09/07 03:08 am ***
*** 11/09/07 03:20 am ***
*** 11/09/07 12:07 pm ***
*** 11/09/07 12:09 pm ***
*** 11/09/07 09:29 pm ***
*** 11/09/07 09:31 pm ***
*** 11/13/07 01:50 pm ***
*** 11/18/07 03:25 pm ***
*** 11/19/07 11:48 pm ***
*** 11/19/07 11:50 pm ***
*** 05/07/08 01:17 am ***
*** 05/07/08 02:42 am ***
*** 05/07/08 02:42 am ***
*** 12/17/08 04:42 pm ***
*** 12/24/08 05:19 am ***
*** 03/17/09 03:25 pm ***
*** 03/17/09 05:57 pm ***
*** 04/01/09 11:08 am ***
.
----------------------------------
README for 5638146
Patch Details
#
# WARNING: Failure to carefully read and understand these requirements may
# result in your applying a patch that can cause your Oracle Server to
# malfunction, including interruption of service and/or loss of data.
#
# If you do not meet all of the following requirements, please log an
# iTAR, so that an Oracle Support Analyst may review your situation. The
# Oracle analyst will help you determine if this patch is suitable for you
# to apply to your system. We recommend that you avoid applying any
# temporary patch unless directed by an Oracle Support Analyst who has
# reviewed your system and determined that it is applicable.
#
# Requirements:
#
# - You must have located this patch via a Bug Database entry
# and have the exact symptoms described in the bug entry.
#
# - Your system configuration (Oracle Server version and patch
# level, OS Version) must exactly match those in the bug
# database entry - You must have NO OTHER PATCHES installed on
# your Oracle Server since the latest patch set (or base release
# x.y.z if you have no patch sets installed).
#
# - [Oracle 9.2.0.2 & above] You must have Perl 5.00503 (or later)
# installed under the ORACLE_HOME, or elsewhere within the host
# environment. OPatch is no longer included in patches as of 9.2.0.2.
# Refer to the following link for details on Perl and OPatch:
# http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=189489.1
#
# - [IBM AIX O/S & Java patches for Oracle 9.2]
# In order to apply java class updates to IBM AIX based systems using
# java_131, you must update your java if you are running a version prior
# to Service Refresh build date 20030630a. This is
# necessary to fix IBM Defect#60472.
#
# To identify which java build date you are on, enter the following
# command ;
#
# > $ORACLE_HOME/jdk/bin/java -fullversion
# ... example response ...
# java full version "J2RE 1.3.1 IBM AIX build ca131-20030630a"
#
# The string ends in the date format YYYYMMDD or YYYYMMDDa where 'a'
# indicates an updated release to the original build. You should always
# apply the latest AIX Java SDK 1.3.1 Service Update available from IBM.
# As a minimum, the above service refresh can be found under
# APAR IY47055. The signature for the updated JVM is ca131-20030630a.
# Information on the latest available fixes, as well as how to apply
# the APARs to your AIX systems, is available at the IBM Java site.
#
# If you are running AIX 5L, you can safely ignore any comment against
# the APAR that says (AIXV43 only). The APAR is applicable to
# both AIX 4.3 and AIX 5L.
#
# Once you have updated your java installation you need to copy these
# updated files to Oracle's copies in $ORACLE_HOME/jdk.
# As the Oracle owner, simply issue the following commands;
#
# > cd /usr/java131
# > cp -fpR * $ORACLE_HOME/jdk
#
#
# If you do NOT meet these requirements, or are not certain that you meet
# these requirements, please log an iTAR requesting assistance with this
# patch and Support will make a determination about whether you should
# apply this patch.
#
#-------------------------------------------------------------------------
# Interim Patch for Base Bugs: 5638146
#-------------------------------------------------------------------------
#
# DATE: Wed Apr 18 22:32:51 2007
# -------------------------------
# Platform Patch for : AIX5L Based Systems (64-bit)
# Product Version # : 10.2.0.2
# Product Patched : RDBMS
#
# Bugs Fixed by this patch:
# -------------------------
# 5638146:ORA-600[KQLFFILLBINDDATA 1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
#
# Patch Installation Instructions:
# --------------------------------
# To apply the patch, unzip the PSE container file:
#
# p5638146_10202_AIX64-5L.zip
#
# Set your current directory to the directory where the patch
# is located:
#
# % cd 5638146
# On AIX platforms only for 10201 Patches :
#
# Ensure environment variable OBJECT_MODE is set to 32_64
# e.g.
#
# % setenv OBJECT_MODE 32_64
# or
# $ export OBJECT_MODE=32_64
#
# Ensure that the directory containing the opatch script appears in
# your $PATH; then enter the following command:
#
# % opatch apply
#
# Patch Special Instructions:
# ---------------------------
# Make sure all instances running under the ORACLE_HOME being patched
# are cleanly shutdown before installing this patch. Also ensure that
# the tool used to terminate the instance(s) has exited cleanly.
#
# If the Oracle inventory is not setup correctly this utility will
# fail. To check accessibility to the inventory you can use the
# command
#
# % opatch lsinventory
#
# If you have any problems installing this PSE or are not sure
# about inventory setup please call Oracle support.
#
# Patch Deinstallation Instructions:
# ----------------------------------
# Use the following command:
#
# % cd 5638146
# % opatch rollback -id 5638146
#
----------------------------------
ora600_sapnote_0001033315
Bugs-5638146
5 Ağustos 2009 Çarşamba
RMAN Recovery -2
create tablespace;
CREATE SMALLFILE TABLESPACE "EXAMPLE" DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\example' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
CREATE SMALLFILE TABLESPACE "EXAMPLE" DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\example' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
----------------------------
create user;
CREATE USER "HR" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "EXAMPLE" ACCOUNT UNLOCK
GRANT "CONNECT" TO "HR"
----------------------------
create table;
CREATE TABLE "HR"."TB_REHBER" ( "AD" VARCHAR2(20), "SOYAD" VARCHAR2(20))
Failed to commit: ORA-01950: no privileges on tablespace 'EXAMPLE'
ALTER USER "HR" QUOTA UNLIMITED ON "EXAMPLE" --create etmek için
GRANT CREATE TABLE TO "HR"
GRANT ALTER TABLESPACE TO "HR"
DROP TABLE "HR"."TB_REHBER" CASCADE CONSTRAINTS
REVOKE ALTER TABLESPACE FROM "HR"
REVOKE CREATE TABLE FROM "HR"
---------------------------------
CREATE SMALLFILE TABLESPACE "EXAMPLE" DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\example' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
CREATE SMALLFILE TABLESPACE "EXAMPLE" DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\example' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
----------------------------
create user;
CREATE USER "HR" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "EXAMPLE" ACCOUNT UNLOCK
GRANT "CONNECT" TO "HR"
----------------------------
create table;
CREATE TABLE "HR"."TB_REHBER" ( "AD" VARCHAR2(20), "SOYAD" VARCHAR2(20))
Failed to commit: ORA-01950: no privileges on tablespace 'EXAMPLE'
ALTER USER "HR" QUOTA UNLIMITED ON "EXAMPLE" --create etmek için
GRANT CREATE TABLE TO "HR"
GRANT ALTER TABLESPACE TO "HR"
DROP TABLE "HR"."TB_REHBER" CASCADE CONSTRAINTS
REVOKE ALTER TABLESPACE FROM "HR"
REVOKE CREATE TABLE FROM "HR"
---------------------------------
RMAN Recovery
select table_name, tablespace_name from user_tables;
example tablespace siliyoruz.
select * from job_history;
hata verecektir.
bu konuda kullıcılardan ekran görüntüsü şart!!!
çalışan veritabanında illa yedekten donulmesi şart değil. test sunucuya gidip restore edip
hangi zaman aralığında veri kaybolduysa export edip appli sunucuya import edebiliriz.
tablespace düşürdüğümüz için sunucuyu kapatmaya gerek yok.
DataFile Recover Etmek:
1) hr ile bağlanıp employees tablosunu sorgulayalım:
sqlplus hr/hr@orcl
set pagesize 5000;
select * from employees;
2) Enterprise Manager ile employees tablosunun tablespace’ini önce offline yapıp sonra düşürelim.
3) SQL Plus’da 1.sıradaki sorguyu tekrar çalıştırıp employees tablosunun gittiğini görelim. Hangi datafile’ın eksik olduğunu numarasıyla birlikte söyleyecek.
4) datafile’ı rman ile recover edelim:
rman target sys/oracle@orcl;
restore datafile 5;
recover datafile 5;
RMAN> SQL ‘alter tablespace example online’;
Bu adımları tablespace üzerinden de yapabiliriz:
rman target sys/sarar@orcl;
restore tablespace EXAMPLE;
recover tablespace EXAMPLE;
RMAN> SQL ‘alter tablespace example online’;
5) Datafile’ın gelip gelmediğini görmek için 1.sıradaki sorguyu tekrar çalıştıralım.
Database Recover Etmek:
6) 1. sırdaki sorguyu çalıştırın.
7) shutdown abort komutu ile veritabanını kapatın.
oradata\orcl altındaki sonu *.dbf ile biten tüm dosyaları silin.
veritabanını startup mount komutuyla (RMAN> startup mount ) açın.
8) tüm veritabanını recover edelim:
rman target sys/oracle@orcl
restore database;
recover database;
alter database open;
9) 1. işlemdeki sorguyu çalıştırın:
INCOMPLETE RECOVERY:
Online redo logların olduğu hard disk göçerse, archived loglardan birkaç tanesi ya da hepsi kayıpsa, control file’ı kaybettiyseniz incomplete recovery yapmanız gerekir:
10) 1. sıradaki işlemi yapınız.
11) sys olarak bağlanıp dbid’yi alalım. Bu dbid’yi bir yere not edelim, çünkü control file’ı sileceğiz ve bu dbid ile geri dönebileceğiz.
Connect sys/oracle@orcl as sysdba
SELECT dbid
FROM v$database;
12) shutdown abort komutu ile veritabanını kapatın.
oradata\orcl altındaki tüm dosyaları silin.
13) 1.sıradaki işlemi çalıştırmaya ya da veritabanını açmaya çalışın.
14) RMAN’de aşağıdaki komutları yazın:
DOS> RMAN
SET DBID X;
CONNECT TARGET SYS/oracle@orcl;
STARTUP NOMOUNT;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\vtyedek\%F';
RESTORE CONTROLFILE FROM AUTOBACKUP;
PWD dosyasını d:\oracle\ora92\database altına kopyala
NLS_LANG SERVERLA Aynı olsun.
Not: Bir kaldırdığın backuptan tekrar kaldıramazsın.(O yüzden recovery yapmadan önce yedeğin yedeğini al). Veritabanını kaldırdığın backup geçersiz olacak. O yüzden ayağa kalktıktan sonra backup almakta fayda var.
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
Online redo loglarda kayıp olduğu için sonunda RMAN hataları oluşacak.
15) Online redo loglar kayıp olduğu için veritabanını onlar olmadan ve sequence’i sıfırlayarak açıyoruz.
RMAN> ALTER DATABASE OPEN RESETLOGS;
16) 1. sıradaki işlemi yapın :)
17) 4-5 kere switch yaparak biraz archive log üretelim.
alter system switch logfile;
/
/
/
/
18) rman’e bağlanıp backup ihtiyacı olan dosyalara bakalım.
rman target sys/oracle@orcl
RMAN> report need backup;
19) rman de yedek alalım.
rman> backup database plus archivelog;
20) eski dosyaları silelim.
rman> report obsolete;
rman> delete obsolete;
delete noprompt obsolete;
alter database backup controlfile to trace;
example tablespace siliyoruz.
select * from job_history;
hata verecektir.
bu konuda kullıcılardan ekran görüntüsü şart!!!
çalışan veritabanında illa yedekten donulmesi şart değil. test sunucuya gidip restore edip
hangi zaman aralığında veri kaybolduysa export edip appli sunucuya import edebiliriz.
tablespace düşürdüğümüz için sunucuyu kapatmaya gerek yok.
DataFile Recover Etmek:
1) hr ile bağlanıp employees tablosunu sorgulayalım:
sqlplus hr/hr@orcl
set pagesize 5000;
select * from employees;
2) Enterprise Manager ile employees tablosunun tablespace’ini önce offline yapıp sonra düşürelim.
3) SQL Plus’da 1.sıradaki sorguyu tekrar çalıştırıp employees tablosunun gittiğini görelim. Hangi datafile’ın eksik olduğunu numarasıyla birlikte söyleyecek.
4) datafile’ı rman ile recover edelim:
rman target sys/oracle@orcl;
restore datafile 5;
recover datafile 5;
RMAN> SQL ‘alter tablespace example online’;
Bu adımları tablespace üzerinden de yapabiliriz:
rman target sys/sarar@orcl;
restore tablespace EXAMPLE;
recover tablespace EXAMPLE;
RMAN> SQL ‘alter tablespace example online’;
5) Datafile’ın gelip gelmediğini görmek için 1.sıradaki sorguyu tekrar çalıştıralım.
Database Recover Etmek:
6) 1. sırdaki sorguyu çalıştırın.
7) shutdown abort komutu ile veritabanını kapatın.
oradata\orcl altındaki sonu *.dbf ile biten tüm dosyaları silin.
veritabanını startup mount komutuyla (RMAN> startup mount ) açın.
8) tüm veritabanını recover edelim:
rman target sys/oracle@orcl
restore database;
recover database;
alter database open;
9) 1. işlemdeki sorguyu çalıştırın:
INCOMPLETE RECOVERY:
Online redo logların olduğu hard disk göçerse, archived loglardan birkaç tanesi ya da hepsi kayıpsa, control file’ı kaybettiyseniz incomplete recovery yapmanız gerekir:
10) 1. sıradaki işlemi yapınız.
11) sys olarak bağlanıp dbid’yi alalım. Bu dbid’yi bir yere not edelim, çünkü control file’ı sileceğiz ve bu dbid ile geri dönebileceğiz.
Connect sys/oracle@orcl as sysdba
SELECT dbid
FROM v$database;
12) shutdown abort komutu ile veritabanını kapatın.
oradata\orcl altındaki tüm dosyaları silin.
13) 1.sıradaki işlemi çalıştırmaya ya da veritabanını açmaya çalışın.
14) RMAN’de aşağıdaki komutları yazın:
DOS> RMAN
SET DBID X;
CONNECT TARGET SYS/oracle@orcl;
STARTUP NOMOUNT;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\vtyedek\%F';
RESTORE CONTROLFILE FROM AUTOBACKUP;
PWD dosyasını d:\oracle\ora92\database altına kopyala
NLS_LANG SERVERLA Aynı olsun.
Not: Bir kaldırdığın backuptan tekrar kaldıramazsın.(O yüzden recovery yapmadan önce yedeğin yedeğini al). Veritabanını kaldırdığın backup geçersiz olacak. O yüzden ayağa kalktıktan sonra backup almakta fayda var.
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
Online redo loglarda kayıp olduğu için sonunda RMAN hataları oluşacak.
15) Online redo loglar kayıp olduğu için veritabanını onlar olmadan ve sequence’i sıfırlayarak açıyoruz.
RMAN> ALTER DATABASE OPEN RESETLOGS;
16) 1. sıradaki işlemi yapın :)
17) 4-5 kere switch yaparak biraz archive log üretelim.
alter system switch logfile;
/
/
/
/
18) rman’e bağlanıp backup ihtiyacı olan dosyalara bakalım.
rman target sys/oracle@orcl
RMAN> report need backup;
19) rman de yedek alalım.
rman> backup database plus archivelog;
20) eski dosyaları silelim.
rman> report obsolete;
rman> delete obsolete;
delete noprompt obsolete;
alter database backup controlfile to trace;
Check Database - 2
select value
from v$parameter
where name = 'background_dump_dest';
http://abakalidis.blogspot.com/2007/11/where-is-oracle-database-alert-log.html
--------------------------
SAPDBA: Error - opening alert log P:\oracle\QAS\saptrace\background\QASalrt.log
However Oracle Log is actually at the place Q:\oracle\QAS\817\RDBMS\trace\qasALRT.LOG
from v$parameter
where name = 'background_dump_dest';
http://abakalidis.blogspot.com/2007/11/where-is-oracle-database-alert-log.html
--------------------------
SAPDBA: Error - opening alert log P:\oracle\QAS\saptrace\background\QASalrt.log
However Oracle Log is actually at the place Q:\oracle\QAS\817\RDBMS\trace\qasALRT.LOG
Compute Statistics
table için;
begin
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=> 'MARG', partname=> NULL);
end;
tablespace için;
begin
dbms_stats.gather_schema_stats(ownname=> 'SAPSR3' , cascade=> TRUE);
end;
http://blog.csdn.net/guozhenblog/category/260868.aspx
begin
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=> 'MARG', partname=> NULL);
end;
tablespace için;
begin
dbms_stats.gather_schema_stats(ownname=> 'SAPSR3' , cascade=> TRUE);
end;
http://blog.csdn.net/guozhenblog/category/260868.aspx
Check Database
Oracle Database Administration
Oracle Database Information Page
* Oracle Database Recovery
* Oracle Database Backup
* Oracle Database Migration
* Oracle Database Monitoring Scripts:
o User
o Tablespaces
o Rollback Segment
o Redo Log
o Tables and Indexes
o Coalesce
o FreeList
o Database Buffer
o Extents
o Shared Pool Size
o Session Statstics
* Oracle Database Archiving
* Oracle Stored Procedures
* Y2K Inventory Database
* Other Issues
Oracle Database Recovery
Implementing a Restored and Recovery Strategy
Phase I - Steps for Diagnosing a Problem
1. Determine if the database instance is available and the database is open.
2. Attempt to start the instance and open the database.
3. Shut down the instance if problems occur while starting it or opening the database.
4. Check the trace files for possible problems.
5. Check the alert_SID.log file for the possible problems.
6. Determine the appropriate recovery method by asking the following questions for each scenario:
1. Which recovery operations are available?
1. Complete Recovery
Closed Database Recovery
Open Database, Offline Tablespace Recovery
Open Database, Offline Tablespace, Individual Datafile Recovery
2. Incomplete Media Recovery
Cancel Based Recovery
Time Based Recovery
Change Based Recovery
2. Which recovery operations are appropriate for the particular problem?
3. Are disaster recovery procedures in place?
4. What need to be restored to proceed with recovery?
Phase II - Restore Appropriate Files
1. Determine which file(s) to restore
2. Determine what state the instance and database must be in to perform the recovery.
Phase III - Recover Database
1. Perform the appropriate method of recovery
Phase IV - Backup Database
1. Determine if another full offline backup is required.
Time Based Recovery
This is used to recover the database up to a specific point in time:
* When data is lost; check the alert file for the approximate time of the error.
* When part of a non-mirrored online redo log becomes corrupt and the approximate time of the corruption is known
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup(except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
8. svrmgrl> alter database open resetlogs;
9. svrmgrl> shutdown
10. svrmgrl> startup
11. Do an offline backup
Recently, I had to recover a database using time based recovery but due to some corrupted nightly backup files, I was not able to do a normal recovery. The error message was:
SVRMGR> recover database until time 1998-06-03:10:18:00
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/dbase/u04/oradata/PRD/temp01PRD.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
The way to get around this is:
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup (except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> select * from v$recover_file: it displayed 3 datafile that are not recoverable:
* /dbase/u04/oradata/PRD/temp01PRD.dbf
* /dbase/u04/oradata/PRD/ndx01BUD.dbf
* /dbase/u04/oradata/PRD/ndx01HLP.dbf
8. svrmgrl> alter database datafile '/dbase/u04/oradata/PRD/temp01PRD.dbf' offline drop; repeat for the other 2 files.
9. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
10. svrmgrl> alter database open resetlogs;
11. svrmgrl> drop tablespace TEMP including contents; svrmgrl> drop tablespace BUD_NDX including contents cascade contraints;
12. Create new tablespaces.
13. Create new indexes.
14. Shutdown the database.
15. Startup the database.
16. Do an offline backup.
Oracle Database Migration
Two ways to move a database:
1. For a small database.
1. Do an Export.
2. Create a new empty database.
3. Do an Import.
2. For a large database (in the old system):
1. svrmgrl>alter database backup controlfile to trace.
2. Take a Cold Backup.
For a large database (in the new system):
3. Restore on the new system (all the datafiles and redo log files). Modified file name if necessary.
4. Create New initSID file.
5. Modify the trace file from step 1 to reflect changes in file names and change the database name if desired.
6. Set the ORACLE_SID to the new SID.
7. Run the sql from step 1
8. Open the database.
Oracle Database Backup
3 ways of doing database backup:
1. Full Offline(Cold) Backup
2. Full Online(Hot) Backup - archiving must be on
3. Export Utility Backup
Oracle Database Archiving
To Turn Archive Log On
1. Modified the initSID.ora file to uncomment these 3 lines:
log_archive_start = true
log_archive_dest = disk$rdbms:[oracle.archive]
log_archive_format = "%S.arc"
2. Goto svrmgrl
svrmgrl> connect internal
svrmgrl> startup mount pfile=initSID.ora
svrmgrl> alter database archivelog;
svrmgrl> alter database open
Oracle Stored Procedure
To Create a Procedure
in sqlplus
sql> create procedure scott.sal_incr
sql> (v_empno IN NUMBER,
sql> v_incr IN NUMBER)
sql> as begin
sql> update scott.emp
sql> set sal = sal + v_incr
sql> where empno = v_empno;
sql> end;
To Execute a Procedure
in sqlplus
sql> exec sal_incr(v_empno, v_incr);
To look for User Procedure
in sqlplus
sql> select * from user_objects where object_type = 'PROCEDURE';
To look at Procedure Code
in sqlplus
sql> select text from user_source where name = 'PROCEDURE_NAME';
Oracle Database Monitoring Scripts
Check for overextended tables or indexes
select substr(owner,1,10) "Owner",
substr(segment_name,1,10) "SegName",
segment_type "SegType",
substr(tablespace_name,1,10) "Tablespace",
substr(extents,1,4) "#Ext",
max_extents "Max",
initial_extent "InitExt",
next_extent "NextExt"
from sys.dba_segments
where extents > 5
and (segment_type = 'TABLE'
or segment_type = 'ROLLBACK'
or segment_type = 'INDEX')
and owner like '%DBA'
order by owner, extents desc, segment_name;
Check for full tablespace
select substr(owner,1,10) "Owner",
substr(segment_name,1,14) "SegName",
next_extent "NextExt",
substr(tablespace_name,1,14) "Tablespace"
from dba_segments ds
where next_extent >
(select max(bytes)
from dba_free_space
where tablespace_name=ds.tablespace_name);
Check for tablespace fragmentation
select TABLESPACE_NAME,
count(*) FREE_SEGMENTS
from dba_free_space
group by TABLESPACE_NAME
having count(*) > 10;
Check for free space by tablespace monitoring
select substr(b.file_id,1,3) "FID",
substr(b.file_name,23,16) "Filename",
substr(b.tablespace_name,1,10) "Tablespace",
b.bytes "# Bytes",
(b.bytes - sum(nvl(a.bytes,0))) "# Used",
sum(nvl(a.bytes,0)) "# Free",
(sum(nvl(a.bytes,0))/(b.bytes)) * 100 "% Free"
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id(+) = b.file_id
group by b.tablespace_name, b.file_id, b.file_name, b.bytes
order by b.tablespace_name;
Tablespace Disk Usages
select tablespace_name,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by tablespace_name;
select tablespace_name,
sum(bytes) "Bytes Alloc"
from dba_data_files
group by tablespace_name;
Check for redo log contention
select name,
gets,
misses,
sleeps,
immediate_gets,
immediate_misses
from v$latch
where name in ('redo allocation', 'redo copy');
Check for rollback segments contention
select substr(r.name,1,10) "Rollback",
s.gets,
s.waits,
round (100 * s.waits/s.gets) "%Cont"
from v$rollstat s, v$rollname r
where s.usn = r.usn
and round (100 * s.waits/s.gets) > 1;
Check for rollback segments monitoring
select substr(name,1,10) "Rollback",
optsize,
shrinks,
aveshrink,
wraps,
extends,
rssize,
writes
from v$rollstat s, v$rollname r
where s.usn = r.usn;
select substr(segment_name,1,14),
substr(name,1,36),
substr(file_id,1,6),
bytes,
substr(tablespace_name,1,10)
from dba_rollback_segs,
v$datafile
where v$datafile.file# = dba_rollback_segs.file_id;
Check for transaction per rollback segments monitoring
select r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal ts
from V$LOCK l, V$SESSION s, V$ROLLNAME r
where l.sid =s.sid(+)
and trunc(l.ID1/65536) = r.usn
and l.type = 'TX'
and l.lmode = 6
order by r.name;
Check for user that use SYSTEM tablespace for work area
select substr(username,1,14) "User",
user_id "User ID",
substr(default_tablespace,1,14) "Default TSpace",
substr(temporary_tablespace,1,14) "Temp TSpace"
from dba_users
where temporary_tablespace = 'SYSTEM';
Check user privileges
select *
from sys.dba_role_privs
where GRANTEE like '%';
select * from sys.dba_sys_privs
where GRANTEE like '%';
select * from sys.dba_tab_privs
where GRANTEE like '%';
select username,
program,
sid,
serial#
from v$session;
User Disk Usages
select owner,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by owner;
Check for tablespace coalesce
select tablespace_name, percent_blocks_coalesced
from dba_free_space_coalesced
order by percent_blocks_coalesced;
alter tablespace XXX coalesce;
select * from dba_free_space
where tablespace_name = 'XXX';
Check for db_buffer
select name,
value
from v$sysstat
where name in ('consistent gets', 'db block gets', 'physical reads');
select * from dba_ts_quotas
where BYTES = MAX_BYTES;
Check for Freelist
select * from v$waitstat;
Check for segments running out of extents
select a.owner,
table_name "table",
a.tablespace_name "tablespace",
'T' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_tables a,
sys.dba_segments b
where table_name = segment_name
and a.max_extents - b.extents <= 10
union
select a.owner,
index_name "index",
a.tablespace_name "tablespace",
'I' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_indexes a,
sys.dba_segments b
where index_name = segment_name
and a.max_extents - b.extents <= 10;
Resize datafiles
alter database datafile 'XXX' resize 30M;
Collect session statstics
select username,
to_char(sysdate - (HSECS - s.value)/(3600*100*24),
'DD-MM-YYYY HH24:MI:SS')
from v$sesstst S,
v$session SE,
v$timer
where SE.SID = S.SID
and statistic#=14
and username is not null;
Shared Pool Information
select sum(pins) pins,
sum(reloads) reloads
from v$librarycache;
select sum(gets) gets,
sum(getmisses) getmisses
from v$rowcache;
Oracle Database Other Issues
Connection
To restrict other users to the database.
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
http://www.its.monash.edu.au/staff/systems/oracle/technical/ss_oracle.html
http://decipherinfosys.wordpress.com/2007/03/20/checking-database-parameters-in-oracle/
http://help.sap.com/saphelp_nw70/helpdata/EN/b0/a42100eb7ec5478ebf2c846e2912e9/frameset.htm
Oracle Database Information Page
* Oracle Database Recovery
* Oracle Database Backup
* Oracle Database Migration
* Oracle Database Monitoring Scripts:
o User
o Tablespaces
o Rollback Segment
o Redo Log
o Tables and Indexes
o Coalesce
o FreeList
o Database Buffer
o Extents
o Shared Pool Size
o Session Statstics
* Oracle Database Archiving
* Oracle Stored Procedures
* Y2K Inventory Database
* Other Issues
Oracle Database Recovery
Implementing a Restored and Recovery Strategy
Phase I - Steps for Diagnosing a Problem
1. Determine if the database instance is available and the database is open.
2. Attempt to start the instance and open the database.
3. Shut down the instance if problems occur while starting it or opening the database.
4. Check the trace files for possible problems.
5. Check the alert_SID.log file for the possible problems.
6. Determine the appropriate recovery method by asking the following questions for each scenario:
1. Which recovery operations are available?
1. Complete Recovery
Closed Database Recovery
Open Database, Offline Tablespace Recovery
Open Database, Offline Tablespace, Individual Datafile Recovery
2. Incomplete Media Recovery
Cancel Based Recovery
Time Based Recovery
Change Based Recovery
2. Which recovery operations are appropriate for the particular problem?
3. Are disaster recovery procedures in place?
4. What need to be restored to proceed with recovery?
Phase II - Restore Appropriate Files
1. Determine which file(s) to restore
2. Determine what state the instance and database must be in to perform the recovery.
Phase III - Recover Database
1. Perform the appropriate method of recovery
Phase IV - Backup Database
1. Determine if another full offline backup is required.
Time Based Recovery
This is used to recover the database up to a specific point in time:
* When data is lost; check the alert file for the approximate time of the error.
* When part of a non-mirrored online redo log becomes corrupt and the approximate time of the corruption is known
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup(except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
8. svrmgrl> alter database open resetlogs;
9. svrmgrl> shutdown
10. svrmgrl> startup
11. Do an offline backup
Recently, I had to recover a database using time based recovery but due to some corrupted nightly backup files, I was not able to do a normal recovery. The error message was:
SVRMGR> recover database until time 1998-06-03:10:18:00
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/dbase/u04/oradata/PRD/temp01PRD.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
The way to get around this is:
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup (except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> select * from v$recover_file: it displayed 3 datafile that are not recoverable:
* /dbase/u04/oradata/PRD/temp01PRD.dbf
* /dbase/u04/oradata/PRD/ndx01BUD.dbf
* /dbase/u04/oradata/PRD/ndx01HLP.dbf
8. svrmgrl> alter database datafile '/dbase/u04/oradata/PRD/temp01PRD.dbf' offline drop; repeat for the other 2 files.
9. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
10. svrmgrl> alter database open resetlogs;
11. svrmgrl> drop tablespace TEMP including contents; svrmgrl> drop tablespace BUD_NDX including contents cascade contraints;
12. Create new tablespaces.
13. Create new indexes.
14. Shutdown the database.
15. Startup the database.
16. Do an offline backup.
Oracle Database Migration
Two ways to move a database:
1. For a small database.
1. Do an Export.
2. Create a new empty database.
3. Do an Import.
2. For a large database (in the old system):
1. svrmgrl>alter database backup controlfile to trace.
2. Take a Cold Backup.
For a large database (in the new system):
3. Restore on the new system (all the datafiles and redo log files). Modified file name if necessary.
4. Create New initSID file.
5. Modify the trace file from step 1 to reflect changes in file names and change the database name if desired.
6. Set the ORACLE_SID to the new SID.
7. Run the sql from step 1
8. Open the database.
Oracle Database Backup
3 ways of doing database backup:
1. Full Offline(Cold) Backup
2. Full Online(Hot) Backup - archiving must be on
3. Export Utility Backup
Oracle Database Archiving
To Turn Archive Log On
1. Modified the initSID.ora file to uncomment these 3 lines:
log_archive_start = true
log_archive_dest = disk$rdbms:[oracle.archive]
log_archive_format = "%S.arc"
2. Goto svrmgrl
svrmgrl> connect internal
svrmgrl> startup mount pfile=initSID.ora
svrmgrl> alter database archivelog;
svrmgrl> alter database open
Oracle Stored Procedure
To Create a Procedure
in sqlplus
sql> create procedure scott.sal_incr
sql> (v_empno IN NUMBER,
sql> v_incr IN NUMBER)
sql> as begin
sql> update scott.emp
sql> set sal = sal + v_incr
sql> where empno = v_empno;
sql> end;
To Execute a Procedure
in sqlplus
sql> exec sal_incr(v_empno, v_incr);
To look for User Procedure
in sqlplus
sql> select * from user_objects where object_type = 'PROCEDURE';
To look at Procedure Code
in sqlplus
sql> select text from user_source where name = 'PROCEDURE_NAME';
Oracle Database Monitoring Scripts
Check for overextended tables or indexes
select substr(owner,1,10) "Owner",
substr(segment_name,1,10) "SegName",
segment_type "SegType",
substr(tablespace_name,1,10) "Tablespace",
substr(extents,1,4) "#Ext",
max_extents "Max",
initial_extent "InitExt",
next_extent "NextExt"
from sys.dba_segments
where extents > 5
and (segment_type = 'TABLE'
or segment_type = 'ROLLBACK'
or segment_type = 'INDEX')
and owner like '%DBA'
order by owner, extents desc, segment_name;
Check for full tablespace
select substr(owner,1,10) "Owner",
substr(segment_name,1,14) "SegName",
next_extent "NextExt",
substr(tablespace_name,1,14) "Tablespace"
from dba_segments ds
where next_extent >
(select max(bytes)
from dba_free_space
where tablespace_name=ds.tablespace_name);
Check for tablespace fragmentation
select TABLESPACE_NAME,
count(*) FREE_SEGMENTS
from dba_free_space
group by TABLESPACE_NAME
having count(*) > 10;
Check for free space by tablespace monitoring
select substr(b.file_id,1,3) "FID",
substr(b.file_name,23,16) "Filename",
substr(b.tablespace_name,1,10) "Tablespace",
b.bytes "# Bytes",
(b.bytes - sum(nvl(a.bytes,0))) "# Used",
sum(nvl(a.bytes,0)) "# Free",
(sum(nvl(a.bytes,0))/(b.bytes)) * 100 "% Free"
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id(+) = b.file_id
group by b.tablespace_name, b.file_id, b.file_name, b.bytes
order by b.tablespace_name;
Tablespace Disk Usages
select tablespace_name,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by tablespace_name;
select tablespace_name,
sum(bytes) "Bytes Alloc"
from dba_data_files
group by tablespace_name;
Check for redo log contention
select name,
gets,
misses,
sleeps,
immediate_gets,
immediate_misses
from v$latch
where name in ('redo allocation', 'redo copy');
Check for rollback segments contention
select substr(r.name,1,10) "Rollback",
s.gets,
s.waits,
round (100 * s.waits/s.gets) "%Cont"
from v$rollstat s, v$rollname r
where s.usn = r.usn
and round (100 * s.waits/s.gets) > 1;
Check for rollback segments monitoring
select substr(name,1,10) "Rollback",
optsize,
shrinks,
aveshrink,
wraps,
extends,
rssize,
writes
from v$rollstat s, v$rollname r
where s.usn = r.usn;
select substr(segment_name,1,14),
substr(name,1,36),
substr(file_id,1,6),
bytes,
substr(tablespace_name,1,10)
from dba_rollback_segs,
v$datafile
where v$datafile.file# = dba_rollback_segs.file_id;
Check for transaction per rollback segments monitoring
select r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal ts
from V$LOCK l, V$SESSION s, V$ROLLNAME r
where l.sid =s.sid(+)
and trunc(l.ID1/65536) = r.usn
and l.type = 'TX'
and l.lmode = 6
order by r.name;
Check for user that use SYSTEM tablespace for work area
select substr(username,1,14) "User",
user_id "User ID",
substr(default_tablespace,1,14) "Default TSpace",
substr(temporary_tablespace,1,14) "Temp TSpace"
from dba_users
where temporary_tablespace = 'SYSTEM';
Check user privileges
select *
from sys.dba_role_privs
where GRANTEE like '%';
select * from sys.dba_sys_privs
where GRANTEE like '%';
select * from sys.dba_tab_privs
where GRANTEE like '%';
select username,
program,
sid,
serial#
from v$session;
User Disk Usages
select owner,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by owner;
Check for tablespace coalesce
select tablespace_name, percent_blocks_coalesced
from dba_free_space_coalesced
order by percent_blocks_coalesced;
alter tablespace XXX coalesce;
select * from dba_free_space
where tablespace_name = 'XXX';
Check for db_buffer
select name,
value
from v$sysstat
where name in ('consistent gets', 'db block gets', 'physical reads');
select * from dba_ts_quotas
where BYTES = MAX_BYTES;
Check for Freelist
select * from v$waitstat;
Check for segments running out of extents
select a.owner,
table_name "table",
a.tablespace_name "tablespace",
'T' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_tables a,
sys.dba_segments b
where table_name = segment_name
and a.max_extents - b.extents <= 10
union
select a.owner,
index_name "index",
a.tablespace_name "tablespace",
'I' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_indexes a,
sys.dba_segments b
where index_name = segment_name
and a.max_extents - b.extents <= 10;
Resize datafiles
alter database datafile 'XXX' resize 30M;
Collect session statstics
select username,
to_char(sysdate - (HSECS - s.value)/(3600*100*24),
'DD-MM-YYYY HH24:MI:SS')
from v$sesstst S,
v$session SE,
v$timer
where SE.SID = S.SID
and statistic#=14
and username is not null;
Shared Pool Information
select sum(pins) pins,
sum(reloads) reloads
from v$librarycache;
select sum(gets) gets,
sum(getmisses) getmisses
from v$rowcache;
Oracle Database Other Issues
Connection
To restrict other users to the database.
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
http://www.its.monash.edu.au/staff/systems/oracle/technical/ss_oracle.html
http://decipherinfosys.wordpress.com/2007/03/20/checking-database-parameters-in-oracle/
http://help.sap.com/saphelp_nw70/helpdata/EN/b0/a42100eb7ec5478ebf2c846e2912e9/frameset.htm
Kaydol:
Kayıtlar (Atom)