/*
SAPSR3 un Z li tablolarının dml işlem yapılmış olanlarının istatistiklerinin toplanması
*/
declare
cursor c_tablo is
select u.table_name
from all_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = d.table_name
and d.owner = 'SAPSR3' and d.table_name like 'ZPPBOY%'
and (u.inserts > 1 or u.updates > 1 or u.deletes > 1)
order by d.last_analyzed;
sayi number;
begin
for r_tablo in c_tablo
loop
dbms_output.put_line('Tablo:'||r_tablo.table_name);
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=>r_tablo.table_name, partname=> NULL);
end loop;
end;
----------------------------------------
---tablo değişikliklerini listeleme
select u.TIMESTAMP,
d.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
percent
from all_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = d.table_name
and d.owner = 'SAPSR3'
and (u.inserts > 1 or u.updates > 1 or u.deletes > 1)
order by d.last_analyzed
/
------------------------
begin
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=> 'Z_table', partname=> NULL);
end;
begin
dbms_stats.gather_schema_stats(ownname=> 'SAPSR3' , cascade=> TRUE);
end;
-------------------------
Oracle Advisory BASIC TYPICAL ALL
-------------------------------------------
Buffer Cache Advice X X
MTTR Advice X X
Shared Pool Advice X X
Segment Level Statistics X X
PGA Advice X X
Timed Statistics X X
Timed OS Statistics X
Plan Execution Statistics X
col statistics_name for a30 head "Statistics Name"
col session_status for a10 head "Session|Status"
col system_status for a10 head "System|Status"
col activation_level for a10 head "Activation|Level"
col session_settable for a10 head "Session|Settable"
SELECT STATISTICS_NAME,
SESSION_STATUS,
SYSTEM_STATUS,
ACTIVATION_LEVEL,
SESSION_SETTABLE
FROM v$statistics_level
ORDER BY 1
/
Doc ID: 252597.1
Doc ID: 255452.1
17 Ağustos 2009 Pazartesi
13 Ağustos 2009 Perşembe
DATA GUARD
>shutdown immediate;
tüm database in backup ını alıyoruz.
-tnsnames.ora -primary
prim1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primaryip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim1.world)
)
)
stby1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbyip)(PORT = 1512))
)
(CONNECT_DATA =
(SERVICE_NAME = stby1.world)
)
)
---------
primary database i archive log alıyoruz.
-archive log list;
no archive log;
create pfile from spfile;
--İnit.ora’da:
--LOG_ARCHIVE_START=true -9.i için.
LOG_ARCHIVE_DEST_1='LOCATION=C:\vtyedek\archives MANDATORY REOPEN=30'
LOG_ARCHIVE_DEST_2='SERVICE=stby1 LGWR SYNC AFFIRM'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
LOG_ARCHIVE_FORMAT=arc_%t_%r_%s.arc
REMOTE_ARCHIVE_ENABLE=true
---------------
create spfile and
STARTUP mount
alter database archivelog;
alter database open;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\stbycf.f'; -- standby için controlfile
daha once aldıgımın yedeğin içine controlfile ı atıyoruz.
------------------------------
stndby databasei aldığımız backupı koyuyoruz.
--tnsnames stndby
stby1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stby1.world)
)
)
PRIM1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim1.world)
)
)
---------------
stndby init.ora
SERVICE_NAMES = stby1
CONTROL_FILES=standby.ctl - control file ları siliyoruz. tekrar düzenliyoruz.
LOG_ARCHIVE_START=true
LOCK_NAME_SPACE=stby1
FAL_SERVER=PRIM1
FAL_CLIENT=stby1
# Uncomment is filename conversion is needed--sid ler farklı ise
#DB_FILE_NAME_CONVERT=("/primary","/standby")
#LOG_FILE_NAME_CONVERT=("/primary","/standby")
STANDBY_ARCHIVE_DEST=C:\vtyedek\archives
LOG_ARCHIVE_DEST_1='LOCATION=C:\vtyedek\archives'
LOG_ARCHIVE_TRACE=127
LOG_ARCHIVE_FORMAT=arc_%t_%r_%s.arc
STANDBY_FILE_MANAGEMENT=auto
REMOTE_ARCHIVE_ENABLE=true
--stndby lisner.ora
SID_listener ara 12514 hatası için sid ekleyebiliriz.
--examp
(SID_DESC =
(SID_NAME = fer)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(GLOBAL_DBNAME = fer)
---
lisner ı değiştiriyorz
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1512))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 2481))
)
)
STANDBY_LISTENER = (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=stndbyip))
)
-----------------------
lsnrctl reload
lsnrctl stop
lsnrctl start
create spfile
STARTUP NOMOUNT
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
primary database;
ALTER DATABASE SET STANDBY DATABASE PROTECTED; illa standby a yolla demek.
hata verdi.
primary de test table oluştur.
commit
alter system switch logfile;
SQL> -- Cancel protected mode on primary
SQL> CONNECT sys/password@primary1 AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;
SQL>
SQL> -- Cancel recovery if necessary
SQL> CONNECT sys/password@standby1 AS SYSDBA
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
-------------------
SQL> -- Startup managed recovery
SQL> CONNECT sys/password@standby1 AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> -- Protect primary database
SQL> CONNECT sys/password@primary1 AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;
---------------------------------------
select log_mode,open_mode , database_role from v$database;
--------------
Activating A Standby Database
SQL> -- Cancel recovery if necessary
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
----------------
http://www.oracle-base.com/articles/9i/DataGuard.php
--------------------------
logical database;
pri-
CREATE TABLESPACE logmnrts DATAFILE ’C:\vtyedek\logmnrts.dbf’ SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE AS 'C:\stndby.ctl';
stnd-
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY new-db_name;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
ALTER DATABASE GUARD ALL;
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
select log_mode,open_mode , database_role from v$database;
logical standby
-----------------------------
-----------------------------------------ek not;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('C:\oracle\product\10.2.0\oradata\orcl\stdbyredo1.log') SIZE 50M;
cfgadm -al
format
luxadm -e port
luxadm -e dump_map
CREATE ROLLBACK SEGMENT rbs_dummy
TABLESPACE system
STORAGE (INITIAL 10k NEXT 10k MINEXTENTS 2);
select sequence#, to_char(2009-08-14 10:00,'YYYY-MM-DD HH24:MI') as first_time,
to_char(2009-08-14 14:00,'YYYY-MM-DD HH24:MI') as next_time, applied from v$archived_log order by sequence#;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/u02/oradata/amcpudb/archive2/primary/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=amcpudb_pri' scope=both;
alter system set log_archive_dest_1='LOCATION=/u02/oradata/amcpudb/archive1/standby/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=amcpudb_pri' scope=both;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END
FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
----------------------------------
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
------------pri
LOG_ARCHIVE_DEST_3=
'LOCATION=/arch2/chicago/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
-------------stand
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY new-db_name;
SQL> STARTUP MOUNT;
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/boston/
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
'SERVICE=chicago LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_3=
'LOCATION=/arch2/boston/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
----------------------------------------------------
--EXECUTE LOGMNR_D.SET_TABLESPACE 'logical_tblsp');
ALTER DATABASE GUARD ALL;
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
select log_mode,open_mode , database_role from v$database;
alter database add supplemental log data
( primary key, unique index) columns;
ed
SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG,
SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG
* FROM V$DATABASE
select tablespace_name from dba_tablespaces
where contents = 'TEMPORARY'
select sequence#, first_time, next_time, dict_begin,dict_end
from dba_logstdby_log
order by 1;
select owner,table_name from dba_logstdby_not_unique
where (owner,table_name) not in
(select distinct owner,table_name from dba_logstdby_unsupported)
and bad_column='Y';
LOG_ARCHIVE_DEST_3=
'LOCATION=C:\vtyedek\archives
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=PRIM1'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
alter system set UNDO_RETENTION=3600 scope=both;
------------------------
pri-
ALTER DATABASE FORCE LOGGING
SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME;
SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';
CREATE TABLESPACE logmnrts DATAFILE ’C:\vtyedek\logmnrts.dbf’ SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
ALTER DATABASE CREATE logical STANDBY CONTROLFILE AS 'c:\stbycf.f';
select guard_status from v$database
alter database guard none;
select max(sequence#) from v$log_history;
SELECT SEQUENCE#, FIRST_TIME, APPLIED
FROM DBA_LOGSTDBY_LOG
ORDER BY SEQUENCE#;
http://forums.oracle.com/forums/thread.jspa?messageID=3666921
https://metalink.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28bmDocID=404686.1&viewingMode=1143&from=BOOKMARK&bmDocType=PROBLEM&bmDocDsrc=KB&bmDocTitle=ORA-01031%20ORA-06512%20%3Cb%3EORA-16224%3C/b%3E%20EXP-00083%20While%20Doing%20Export%29%29
http://www.itpub.net/viewthread.php?tid=1200984
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10823/scenarios.htm
http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10823/manage_ls.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm
tüm database in backup ını alıyoruz.
-tnsnames.ora -primary
prim1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primaryip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim1.world)
)
)
stby1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbyip)(PORT = 1512))
)
(CONNECT_DATA =
(SERVICE_NAME = stby1.world)
)
)
---------
primary database i archive log alıyoruz.
-archive log list;
no archive log;
create pfile from spfile;
--İnit.ora’da:
--LOG_ARCHIVE_START=true -9.i için.
LOG_ARCHIVE_DEST_1='LOCATION=C:\vtyedek\archives MANDATORY REOPEN=30'
LOG_ARCHIVE_DEST_2='SERVICE=stby1 LGWR SYNC AFFIRM'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
LOG_ARCHIVE_FORMAT=arc_%t_%r_%s.arc
REMOTE_ARCHIVE_ENABLE=true
---------------
create spfile and
STARTUP mount
alter database archivelog;
alter database open;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\stbycf.f'; -- standby için controlfile
daha once aldıgımın yedeğin içine controlfile ı atıyoruz.
------------------------------
stndby databasei aldığımız backupı koyuyoruz.
--tnsnames stndby
stby1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stby1.world)
)
)
PRIM1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim1.world)
)
)
---------------
stndby init.ora
SERVICE_NAMES = stby1
CONTROL_FILES=standby.ctl - control file ları siliyoruz. tekrar düzenliyoruz.
LOG_ARCHIVE_START=true
LOCK_NAME_SPACE=stby1
FAL_SERVER=PRIM1
FAL_CLIENT=stby1
# Uncomment is filename conversion is needed--sid ler farklı ise
#DB_FILE_NAME_CONVERT=("/primary","/standby")
#LOG_FILE_NAME_CONVERT=("/primary","/standby")
STANDBY_ARCHIVE_DEST=C:\vtyedek\archives
LOG_ARCHIVE_DEST_1='LOCATION=C:\vtyedek\archives'
LOG_ARCHIVE_TRACE=127
LOG_ARCHIVE_FORMAT=arc_%t_%r_%s.arc
STANDBY_FILE_MANAGEMENT=auto
REMOTE_ARCHIVE_ENABLE=true
--stndby lisner.ora
SID_listener ara 12514 hatası için sid ekleyebiliriz.
--examp
(SID_DESC =
(SID_NAME = fer)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(GLOBAL_DBNAME = fer)
---
lisner ı değiştiriyorz
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1512))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 2481))
)
)
STANDBY_LISTENER = (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=stndbyip))
)
-----------------------
lsnrctl reload
lsnrctl stop
lsnrctl start
create spfile
STARTUP NOMOUNT
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
primary database;
ALTER DATABASE SET STANDBY DATABASE PROTECTED; illa standby a yolla demek.
hata verdi.
primary de test table oluştur.
commit
alter system switch logfile;
SQL> -- Cancel protected mode on primary
SQL> CONNECT sys/password@primary1 AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;
SQL>
SQL> -- Cancel recovery if necessary
SQL> CONNECT sys/password@standby1 AS SYSDBA
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
-------------------
SQL> -- Startup managed recovery
SQL> CONNECT sys/password@standby1 AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> -- Protect primary database
SQL> CONNECT sys/password@primary1 AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;
---------------------------------------
select log_mode,open_mode , database_role from v$database;
--------------
Activating A Standby Database
SQL> -- Cancel recovery if necessary
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
----------------
http://www.oracle-base.com/articles/9i/DataGuard.php
--------------------------
logical database;
pri-
CREATE TABLESPACE logmnrts DATAFILE ’C:\vtyedek\logmnrts.dbf’ SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE AS 'C:\stndby.ctl';
stnd-
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY new-db_name;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
ALTER DATABASE GUARD ALL;
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
select log_mode,open_mode , database_role from v$database;
logical standby
-----------------------------
-----------------------------------------ek not;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('C:\oracle\product\10.2.0\oradata\orcl\stdbyredo1.log') SIZE 50M;
cfgadm -al
format
luxadm -e port
luxadm -e dump_map
CREATE ROLLBACK SEGMENT rbs_dummy
TABLESPACE system
STORAGE (INITIAL 10k NEXT 10k MINEXTENTS 2);
select sequence#, to_char(2009-08-14 10:00,'YYYY-MM-DD HH24:MI') as first_time,
to_char(2009-08-14 14:00,'YYYY-MM-DD HH24:MI') as next_time, applied from v$archived_log order by sequence#;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/u02/oradata/amcpudb/archive2/primary/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=amcpudb_pri' scope=both;
alter system set log_archive_dest_1='LOCATION=/u02/oradata/amcpudb/archive1/standby/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=amcpudb_pri' scope=both;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END
FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
----------------------------------
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
------------pri
LOG_ARCHIVE_DEST_3=
'LOCATION=/arch2/chicago/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
-------------stand
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY new-db_name;
SQL> STARTUP MOUNT;
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/boston/
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
'SERVICE=chicago LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_3=
'LOCATION=/arch2/boston/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
----------------------------------------------------
--EXECUTE LOGMNR_D.SET_TABLESPACE 'logical_tblsp');
ALTER DATABASE GUARD ALL;
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
select log_mode,open_mode , database_role from v$database;
alter database add supplemental log data
( primary key, unique index) columns;
ed
SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG,
SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG
* FROM V$DATABASE
select tablespace_name from dba_tablespaces
where contents = 'TEMPORARY'
select sequence#, first_time, next_time, dict_begin,dict_end
from dba_logstdby_log
order by 1;
select owner,table_name from dba_logstdby_not_unique
where (owner,table_name) not in
(select distinct owner,table_name from dba_logstdby_unsupported)
and bad_column='Y';
LOG_ARCHIVE_DEST_3=
'LOCATION=C:\vtyedek\archives
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=PRIM1'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
alter system set UNDO_RETENTION=3600 scope=both;
------------------------
pri-
ALTER DATABASE FORCE LOGGING
SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME;
SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';
CREATE TABLESPACE logmnrts DATAFILE ’C:\vtyedek\logmnrts.dbf’ SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
ALTER DATABASE CREATE logical STANDBY CONTROLFILE AS 'c:\stbycf.f';
select guard_status from v$database
alter database guard none;
select max(sequence#) from v$log_history;
SELECT SEQUENCE#, FIRST_TIME, APPLIED
FROM DBA_LOGSTDBY_LOG
ORDER BY SEQUENCE#;
http://forums.oracle.com/forums/thread.jspa?messageID=3666921
https://metalink.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28bmDocID=404686.1&viewingMode=1143&from=BOOKMARK&bmDocType=PROBLEM&bmDocDsrc=KB&bmDocTitle=ORA-01031%20ORA-06512%20%3Cb%3EORA-16224%3C/b%3E%20EXP-00083%20While%20Doing%20Export%29%29
http://www.itpub.net/viewthread.php?tid=1200984
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10823/scenarios.htm
http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10823/manage_ls.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm
12 Ağustos 2009 Çarşamba
RMAN errors
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
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
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
Kaydol:
Kayıtlar (Atom)