Env: Oracle 10g
1. Create a table called ORDERS as follows:
create table orders (
order_number,
create_date,
assign_date,
close_date,
region_id )
as select
object_id,
created,
last_ddl_time,
last_ddl_time +7,
mod(object_id,8)
from dba_objects
/
scott@ORCL> select count(*) from orders;
COUNT(*)
----------
50377
2. Create a non-pipelined table function:
CREATE TYPE order_date_o AS object (
order_number number,
date_type char(1), -- O, A, C
year number,
quarter number,
month number
)
/
create type order_date_t as table of order_date_o
/
CREATE OR REPLACE FUNCTION date_parse ( p_curs SYS_REFCURS
RETURN order_date_t AS
v_order_rec orders%ROWTYPE;
v_ret_val order_date_t := order_date_t( );
BEGIN
-- for every order in the cursor ...
LOOP
FETCH p_curs INTO v_order_rec;
EXIT WHEN p_curs%NOTFOUND;
-- extend the array by 3 and populate with component
-- orders creation, assignment and close date
v_ret_val.EXTEND(3);
v_ret_val(v_ret_val.LAST-2) :=
order_date_o(v_order_rec.order_number,
'O',
TO_CHAR(v_order_rec.create_date,'YYYY'),
TO_CHAR(v_order_rec.create_date,'Q'),
TO_CHAR(v_order_rec.create_date,'MM'));
v_ret_val(v_ret_val.LAST - 1) :=
order_date_o(v_order_rec.order_number,
'A',
TO_CHAR(v_order_rec.assign_date,'YYYY'),
TO_CHAR(v_order_rec.assign_date,'Q'),
TO_CHAR(v_order_rec.assign_date,'MM'));
v_ret_val(v_ret_val.LAST) :=
order_date_o(v_order_rec.order_number,
'C',
TO_CHAR(v_order_rec.close_date,'YYYY'),
TO_CHAR(v_order_rec.close_date,'Q'),
TO_CHAR(v_order_rec.close_date,'MM'));
END LOOP;
RETURN(v_ret_val);
END;
/
3. Create a pipelined table function
CREATE OR REPLACE FUNCTION date_parse_pl ( p_curs SYS_REFCURSOR )
RETURN order_date_t
PIPELINED AS
v_order_rec orders%ROWTYPE;
BEGIN
-- for every order in the cursor...
LOOP
FETCH p_curs INTO v_order_rec;
EXIT WHEN p_curs%NOTFOUND;
-- pipe out the components of the orders open date
PIPE ROW(order_date_o(v_order_rec.order_number,
'O',
TO_CHAR(v_order_rec.create_date,'YYYY'),
TO_CHAR(v_order_rec.create_date,'Q'),
TO_CHAR(v_order_rec.create_date,'MM')));
-- pipe out the components of the orders assign date
PIPE ROW(order_date_o(v_order_rec.order_number,
'A',
TO_CHAR(v_order_rec.assign_date,'YYYY'),
TO_CHAR(v_order_rec.assign_date,'Q'),
TO_CHAR(v_order_rec.assign_date,'MM')));
-- pipe out the components of the orders close date
PIPE ROW(order_date_o(v_order_rec.order_number,
'C',
TO_CHAR(v_order_rec.close_date,'YYYY'),
TO_CHAR(v_order_rec.close_date,'Q'),
TO_CHAR(v_order_rec.close_date,'MM')));
END LOOP; -- every order in the cursor
RETURN;
END;
/
4. Comparing of the execution time for the following two statments in different session::
(a)
SELECT *
FROM TABLE(date_parse(CURSOR(SELECT * FROM orders)))
WHERE ROWNUM <=10; scott@ORCL> /
ORDER_NUMBER D YEAR QUARTER MONTH
------------ - ---------- ---------- ----------
20 O 2005 3 8
20 A 2005 3 8
20 C 2005 3 9
44 O 2005 3 8
44 A 2005 3 8
44 C 2005 3 9
28 O 2005 3 8
28 A 2005 3 8
28 C 2005 3 9
15 O 2005 3 8
10 rows selected.
Elapsed: 00:00:07.51
(b)
/* test pipelined table function */
SELECT *
FROM TABLE(date_parse_pl(CURSOR(SELECT * FROM orders)))
WHERE ROWNUM <=10; scott@ORCL> @date_parse_pl.tst
ORDER_NUMBER D YEAR QUARTER MONTH
------------ - ---------- ---------- ----------
20 O 2005 3 8
20 A 2005 3 8
20 C 2005 3 9
44 O 2005 3 8
44 A 2005 3 8
44 C 2005 3 9
28 O 2005 3 8
28 A 2005 3 8
28 C 2005 3 9
15 O 2005 3 8
10 rows selected.
Elapsed: 00:00:00.04
0.04 vs 7.51
~
Friday, July 27, 2007
Table function - pipleined vs non-pipelined
Sunday, July 22, 2007
SQLPLUS sample login.sql
--- login.sql ---
define_editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) '@' substr( global_name, 1, decode( dot, 0,
length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name
);
set sqlprompt '&gname> '
set termout on
define_editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) '@' substr( global_name, 1, decode( dot, 0,
length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name
);
set sqlprompt '&gname> '
set termout on
~
---- conn.sql ---
set termout off
connect &1
@login
set termout on
Thursday, July 19, 2007
A simple test of table function
scott@TOY10G> ho cat rowset.sql
CREATE TYPE rowset_o AS OBJECT ( col1 NUMBER,
col2 VARCHAR2(30));
/
CREATE TYPE rowset_t AS TABLE OF rowset_o;
/
CREATE OR REPLACE FUNCTION simple RETURN rowset_t AS
v_rowset rowset_t := rowset_t( );
BEGIN
v_rowset.EXTEND(3);
v_rowset(1) := rowset_o(1,'Value 1');
v_rowset(2) := rowset_o(2,'Value 2');
v_rowset(3) := rowset_o(3,'Value 3');
RETURN(v_rowset);
END;
/
scott@TOY10G> select * from table(simple);
COL1 COL2
---------- ------------------------------
1 Value 1
2 Value 2
3 Value 3
scott@TOY10G> select col2 from table(simple);
COL2
------------------------------
Value 1
Value 2
Value 3
CREATE TYPE rowset_o AS OBJECT ( col1 NUMBER,
col2 VARCHAR2(30));
/
CREATE TYPE rowset_t AS TABLE OF rowset_o;
/
CREATE OR REPLACE FUNCTION simple RETURN rowset_t AS
v_rowset rowset_t := rowset_t( );
BEGIN
v_rowset.EXTEND(3);
v_rowset(1) := rowset_o(1,'Value 1');
v_rowset(2) := rowset_o(2,'Value 2');
v_rowset(3) := rowset_o(3,'Value 3');
RETURN(v_rowset);
END;
/
scott@TOY10G> select * from table(simple);
COL1 COL2
---------- ------------------------------
1 Value 1
2 Value 2
3 Value 3
scott@TOY10G> select col2 from table(simple);
COL2
------------------------------
Value 1
Value 2
Value 3
Cursor reuse in PL/SQL
==> Lower case or upper case makes difference !
nbk9lsj@GEN3QB> ho cat cursor_in_sharedpool_8i.sql
-- pre Oracle 10gR1
colum sql_text format a30
SELECT hash_value,
address,
sql_text,
parse_calls,
executions
FROM v$sql
WHERE INSTR(UPPER(sql_text),'ALL_OBJECTS') > 0
AND INSTR(UPPER(sql_text),'SQL_TEXT') = 0
AND command_type = 3;
nbk9lsj@GEN3QB> @cursor_in_sharedpool_8i.sql
HASH_VALUE ADDRESS SQL_TEXT PARSE_CALLS
EXECUTIONS
---------- -------- ------------------------------ -----------
----------
2893531678 D4C259A4 select count(*) from all_objec 5
5
ts
1481688868 D4BDECC8 select count(*) from All_objec 3
3
ts
nbk9lsj@GEN3QB> select count(*) from all_objects;
COUNT(*)
----------
11306
nbk9lsj@GEN3QB> select count(*) from All_objects;
COUNT(*)
----------
11306
nbk9lsj@GEN3QB> select count(*) from all_Objects;
COUNT(*)
----------
11306
nbk9lsj@GEN3QB> @cursor_in_sharedpool_8i.sql
HASH_VALUE ADDRESS SQL_TEXT PARSE_CALLS
EXECUTIONS
---------- -------- ------------------------------ -----------
----------
2893531678 D4C259A4 select count(*) from all_objec 6
6
ts
3230163941 D4BB7348 select count(*) from all_Objec 1
1
ts
1481688868 D4BDECC8 select count(*) from All_objec 4
4
ts
==> Cursor in PL/SQL is reused. PL/SQL compiler makes it all upper
case.
nbk9lsj@GEN3QB> ho cat simple_demo_pro.sql
CREATE OR REPLACE PROCEDURE simple_demo AS
CURSOR one IS
select count(*) from all_objects;
CURSOR two IS
select count(*) from All_objects;
CURSOR three IS
select count(*) from all_Objects;
v_count number;
BEGIN
-- open and close correct cursor
OPEN one;
FETCH one INTO v_count;
CLOSE one;
OPEN two;
FETCH two INTO v_count;
CLOSE two;
OPEN three;
FETCH three INTO v_count;
CLOSE three;
END;
nbk9lsj@GEN3QB> exec simple_demo;
PL/SQL procedure successfully completed.
nbk9lsj@GEN3QB> @cursor_in_sharedpool_8i.sql
HASH_VALUE ADDRESS SQL_TEXT PARSE_CALLS
EXECUTIONS
---------- -------- ------------------------------ -----------
----------
3365040921 D4C02F74 SELECT COUNT(*) FROM ALL_OBJ 3
3
ECTS
2893531678 D4C259A4 select count(*) from all_objec 0
6
ts
3230163941 D4BB7348 select count(*) from all_Objec 0
1
ts
1481688868 D4BDECC8 select count(*) from All_objec 0
4
ts
note: why PA
nbk9lsj@GEN3QB> exec simple_demo;
PL/SQL procedure successfully completed.
nbk9lsj@GEN3QB> exec simple_demo;
PL/SQL procedure successfully completed.
nbk9lsj@GEN3QB> exec simple_demo;
PL/SQL procedure successfully completed.
nbk9lsj@GEN3QB> @cursor_in_sharedpool_8i.sql
HASH_VALUE ADDRESS SQL_TEXT PARSE_CALLS
EXECUTIONS
---------- -------- ------------------------------ -----------
----------
3365040921 D4C02F74 SELECT COUNT(*) FROM ALL_OBJ 3
12
ECTS
2893531678 D4C259A4 select count(*) from all_objec 0
6
ts
3230163941 D4BB7348 select count(*) from all_Objec 0
1
ts
1481688868 D4BDECC8 select count(*) from All_objec 0
4
ts
Wednesday, July 18, 2007
How to Set, Remove and Show Diagnostic Events
Environment: 8.1.7.4
The following steps demonstrate how to set, remove and show diagnostic events:
1. Connect to a test database TESTDB as sysdba
------------------------------------------------
testserver> /oracle/admin/TESTDB/udump [TESTDB] sqlplus "/ as sysdba"
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jul 18 08:18:44 2007
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
2. Issue the following command and view the generated trc file
----------------------------------------------------------------
sys@TESTDB> oradebug setmypid
Statement processed.
sys@TESTDB> oradebug dump events 4
Statement processed.
sys@TESTDB> oradebug tracefile_name
/oracle/admin/TESTDB/udump/testdb_ora_10751.trc
sys@TESTDB> ho ls -lh /oracle/admin/TESTDB/udump/testdb_ora_10751.trc
-rw-r----- 1 oracle dba 624 Jul 18 08:19
/oracle/admin/TESTDB/udump/testdb_ora_10751.trc
sys@TESTDB> ho cat /oracle/admin/TESTDB/udump/testdb_ora_10751.trc
/oracle/admin/TESTDB/udump/testdb_ora_10751.trc
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
ORACLE_HOME = /oracle/product/8.1.7.4
System name: SunOS
Node name: testserver
Release: 5.9
Version: Generic_118558-38
Machine: sun4u
Instance name: TESTDB
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 10751, image: oracle@testserver (TNS V1-V3)
*** 2007-07-18 08:19:15.111
*** SESSION ID:(16.6455) 2007-07-18 08:19:15.110
Dump event group for level SYSTEM
TC Addr Evt#(b10) Action TR Addr Arm Life
Note: there are nothing listed at the bottom of the file currently. --
No any kind of events are set
3. Set 1003 event
------------------
sys@TESTDB> alter system set events '1003 trace name errorstack level
10'
2 ;
System altered.
4. Dump and view the trc file again
---------------------------------------
sys@TESTDB> oradebug dump events 4
Statement processed.
sys@TESTDB> oradebug tracefile_name
/oracle/admin/TESTDB/udump/testdb_ora_10751.trc
sys@TESTDB> ho cat /oracle/admin/TESTDB/udump/testdb_ora_10751.trc
/oracle/admin/TESTDB/udump/testdb_ora_10751.trc
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
ORACLE_HOME = /oracle/product/8.1.7.4
System name: SunOS
Node name: testserver
Release: 5.9
Version: Generic_118558-38
Machine: sun4u
Instance name: TESTDB
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 10751, image: oracle@testserver (TNS V1-V3)
*** 2007-07-18 08:19:15.111
*** SESSION ID:(16.6455) 2007-07-18 08:19:15.110
Dump event group for level SYSTEM
TC Addr Evt#(b10) Action TR Addr Arm Life
*** 2007-07-18 08:21:57.394
Dump event group for level SYSTEM
TC Addr Evt#(b10) Action TR Addr Arm Life
D79F50B0 1003 1 d79f50c8 0 0
TR Name TR level TR address TR arm TR
life TR type
ERRORSTACK 10 ffffffff 1 2
0
Note: under 8:21, there is a 1003 entry show up
5. Remove the 1003 event
-------------------------
sys@TESTDB> ALTER SYSTEM SET EVENTS '1003 TRACE NAME ERRORSTACK OFF';
System altered.
6. Dump and view trc file
-------------------------
sys@TESTDB> oradebug dump events 4
Statement processed.
sys@TESTDB> ho ls -lh /oracle/admin/TESTDB/udump/testdb_ora_10751.trc
-rw-r----- 1 oracle dba 1.0K Jul 18 08:24
/oracle/admin/TESTDB/udump/testdb_ora_10751.trc
sys@TESTDB> ho cat /oracle/admin/TESTDB/udump/testdb_ora_10751.trc
/oracle/admin/TESTDB/udump/testdb_ora_10751.trc
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
ORACLE_HOME = /oracle/product/8.1.7.4
System name: SunOS
Node name: testserver
Release: 5.9
Version: Generic_118558-38
Machine: sun4u
Instance name: TESTDB
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 10751, image: oracle@testserver (TNS V1-V3)
*** 2007-07-18 08:19:15.111
*** SESSION ID:(16.6455) 2007-07-18 08:19:15.110
Dump event group for level SYSTEM
TC Addr Evt#(b10) Action TR Addr Arm Life
*** 2007-07-18 08:21:57.394
Dump event group for level SYSTEM
TC Addr Evt#(b10) Action TR Addr Arm Life
D79F50B0 1003 1 d79f50c8 0 0
TR Name TR level TR address TR arm TR
life TR type
ERRORSTACK 10 ffffffff 1 2
0
*** 2007-07-18 08:24:58.949
Dump event group for level SYSTEM
TC Addr Evt#(b10) Action TR Addr Arm Life
Note: event 1003 not presented after 8:24
Sunday, July 15, 2007
Do We have Clusters or IOT?
In Tom kytes's book "Effective Oracle By Design", it has been mentioned
that Steve Adams(http://www.ixora.com.au/) said, "If a schema has no IOTs
or clusters, that is a good indication that no thought has been given to
the matter of optimizing data access."
that Steve Adams(http://www.ixora.com.au/) said, "If a schema has no IOTs
or clusters, that is a good indication that no thought has been given to
the matter of optimizing data access."
Checking our production DB, find only SYS schema has clustered tables.
1 select cluster_name, owner, table_name from dba_tables where
cluster_name is not null
2* order by cluster_name
system@PRDP> /
CLUSTER_NAME OWNER TABLE_NAME
------------------------------ ------------------------------
--------------------------
C_COBJ# SYS CDEF$
C_COBJ# SYS CCOL$
C_FILE#_BLOCK# SYS SEG$
C_FILE#_BLOCK# SYS UET$
C_MLOG# SYS MLOG$
C_MLOG# SYS SLOG$
C_OBJ# SYS ICOL$
C_OBJ# SYS COL$
C_OBJ# SYS TAB$
C_OBJ# SYS ICOLDEP$
C_OBJ# SYS REFCON$
C_OBJ# SYS LIBRARY$
C_OBJ# SYS NTAB$
C_OBJ# SYS TYPE_MISC$
C_OBJ# SYS LOB$
C_OBJ# SYS VIEWTRCOL$
C_OBJ# SYS ATTRCOL$
C_OBJ# SYS COLTYPE$
C_OBJ# SYS IND$
C_OBJ# SYS CLU$
C_OBJ#_INTCOL# SYS HISTGRM$
C_RG# SYS RGROUP$
C_RG# SYS RGCHILD$
C_TOID_VERSION# SYS TYPE$
C_TOID_VERSION# SYS
COLLECTION$
C_TOID_VERSION# SYS ATTRIBUTE$
C_TOID_VERSION# SYS PARAMETER$
C_TOID_VERSION# SYS RESULT$
C_TOID_VERSION# SYS METHOD$
C_TS# SYS FET$
C_TS# SYS TS$
C_USER# SYS USER$
C_USER# SYS TSQ$
No IOT tables either:
1 select iot_name, owner, table_name from dba_tables where iot_name
is not null
2* order by iot_name
system@PRDP> /
no rows selected
Wednesday, July 11, 2007
RMAN - Testing of Recovery through the RESETLOGS point
Oracle 10 g new feature - Backups prior to the use of RESETLOGS can be
used to recover database to the point after the RESETLOGS
used to recover database to the point after the RESETLOGS
Overview of test steps
======================
1. Make a new backup or have a good whole database backup available.
2. Force the redo log activity to archive logs.
3. Validate the log sequence number to perform your incomplete recovery,
for which you use the RESETLOGS clause.
4. Shut down the database and delete the USERS01.DBF file to cause a
recovery situation.
5. Start up the database in MOUNT mode and use RMAN to perform an
incomplete recovery using the SET UNTIL SEQUENCE command.
6. When the incomplete recovery is complete, use the RESETLOGS clause to
open the database.
7. To recover through the RESETLOGS, you need to simulate more database
activity and force this activity to the archive logs. We will be using
the USERS tablespace.
8. Shut down the database and delete the USERS01.DBF to cause another
recovery situation.
9. Start up the database in MOUNT mode and use RMAN to perform a
complete recovery.
10. When the recovery is complete, just use an ALTER DATABASE OPEN
command.
11. Finally, view the V$LOG_HISTORY table and validate that the database
activity is available in the database.
Steps
=====
1. Perform a backup
2. Force all the redo log information to archive logs by executing ALTER
SYSTEM SWITCH LOGFILE:
sys@TOY10G> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G
Oldest online log sequence 36
Next log sequence to archive 38
Current log sequence 38
sys@TOY10G> alter system switch logfile;
System altered.
sys@TOY10G> alter system switch logfile;
System altered.
sys@TOY10G> alter system switch logfile;
System altered.
sys@TOY10G> alter system switch logfile;
System altered.
sys@TOY10G> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G
Oldest online log sequence 40
Next log sequence to archive 42
Current log sequence 42
sys@TOY10G>
3. Verify the redo log sequence and thread number in the V$LOG_HISTORY
table so that we can perform incomplete recovery to a redo log sequence:
sys@TOY10G> select * from v$log_history;
RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME
NEXT_CHANGE#
---------- ---------- ---------- ---------- -------------
-------------------- ------------
RESETLOGS_CHANGE# RESETLOGS_TIME
----------------- --------------------
229 622072816 1 229 1484411 08-MAY-2007
21:00:22 1487280
1 06-APR-2007 12:51:52
230 622072832 1 230 1487280 08-MAY-2007
22:00:16 1488399
1 06-APR-2007 12:51:52
231 622073883 1 231 1488399 08-MAY-2007
22:00:32 1490706
1 06-APR-2007 12:51:52
... .........
516 627639754 1 37 2875462 11-JUL-2007
00:00:58 2887413
2703078 06-JUL-2007 14:45:59
517 627639885 1 38 2887413 11-JUL-2007
08:22:34 2887469
2703078 06-JUL-2007 14:45:59
518 627639889 1 39 2887469 11-JUL-2007
08:24:45 2887471
2703078 06-JUL-2007 14:45:59
519 627639896 1 40 2887471 11-JUL-2007
08:24:49 2887477
2703078 06-JUL-2007 14:45:59
520 627639901 1 41 2887477 11-JUL-2007
08:24:56 2887479
2703078 06-JUL-2007 14:45:59
4. Simulate a failure by shutting down the database and deleting the
USERS01.DBF
sys@TOY10G> select name from v$datafile;
NAME
------------------------------------------------------------------------
----------------------------
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/system01.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/undotbs01.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/sysaux01.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/users01.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/indx01.dbf
sys@TOY10G> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TOY10G> ho rm
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/users01.dbf
sys@TOY10G>
5. Begin the recovery process by starting the database in MOUNT mode
6. Perform an incomplete recovery using the SET UNTIL SEQUENCE clause
RMAN> connect target
connected to target database: TOY10G (DBID=3330944552, not open)
RMAN> run
2> {
3> set until sequence 39 thread 1;
4> restore database;
5> recover database;
6> }
executing command: SET until clause
using target database control file instead of recovery catalog
Starting restore at 11-JUL-2007 09:01:52
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=45 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/system01.dbf
restoring datafile 00002 to
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/undotbs01.dbf
restoring datafile 00003 to
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/sysaux01.dbf
restoring datafile 00004 to
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/users01.dbf
restoring datafile 00005 to
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/indx01.dbf
channel ORA_DISK_1: reading from backup piece
/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_6276397
44_22_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece
handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_
627639744_22_1.dbf tag=TAG20070711T082223
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 11-JUL-2007 09:02:11
Starting recover at 11-JUL-2007 09:02:11
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 37 is already on disk as file
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_37_62723075
9.arc
archive log thread 1 sequence 38 is already on disk as file
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_38_62723075
9.arc
archive log
filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_37
_627230759.arc thread=1 sequence=37
archive log
filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_38
_627230759.arc thread=1 sequence=38
media recovery complete, elapsed time: 00:00:03
Finished recover at 11-JUL-2007 09:02:16
RMAN>
RMAN> alter database open resetlogs;
database opened
RMAN>
7. Simulate database activity by creating a table T1 and forcing this
activity to the
archived redo logs:
scott@TOY10G> create table t5(c char(20));
Table created.
scott@TOY10G> conn / as sysdba
Connected.
sys@TOY10G> alter system switch logfile;
System altered.
sys@TOY10G> ;
1* alter system switch logfile
sys@TOY10G> /
System altered.
sys@TOY10G> /
System altered.
sys@TOY10G> /
System altered.
sys@TOY10G>
8. Shut down the database and simulate a database failure by deleting
the
USERS01.DBF file:
9. Start the database in MOUNT mode and then perform a complete recovery
in
RMAN:
RMAN> connect target
connected to target database: TOY10G (DBID=3330944552, not open)
RMAN> run
2> {
3> restore database;
4> recover database;
5> }
Starting restore at 11-JUL-2007 09:22:37
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/system01.dbf
restoring datafile 00002 to
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/undotbs01.dbf
restoring datafile 00003 to
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/sysaux01.dbf
restoring datafile 00004 to
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/users01.dbf
restoring datafile 00005 to
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/indx01.dbf
channel ORA_DISK_1: reading from backup piece
/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_6276397
44_22_1.dbf
channel ORA_DISK_1: restored backup piece 1
piece
handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_
627639744_22_1.dbf tag=TAG20070711T082223
channel ORA_DISK_1: restore complete, elapsed time: 00:00:09
Finished restore at 11-JUL-2007 09:22:48
Starting recover at 11-JUL-2007 09:22:48
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 37 is already on disk as file
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_37_62723075
9.arc
archive log thread 1 sequence 38 is already on disk as file
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_38_62723075
9.arc
archive log thread 1 sequence 1 is already on disk as file
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_1_627642245
.arc
archive log thread 1 sequence 2 is already on disk as file
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_2_627642245
.arc
archive log thread 1 sequence 3 is already on disk as file
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_3_627642245
.arc
archive log thread 1 sequence 4 is already on disk as file
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_4_627642245
.arc
archive log
filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_37
_627230759.arc thread=1 sequence=37
archive log
filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_38
_627230759.arc thread=1 sequence=38
archive log
filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_1_
627642245.arc thread=1 sequence=1
archive log
filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G/arch_1_2_
627642245.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:03
Finished recover at 11-JUL-2007 09:22:52
RMAN> alter database open;
database opened
RMAN>
10. Validate that you have recovered through the last RESETLOGS by
verifying that the
current V$LOG_HISTORY table
516 627639754 1 37 2875462 11-JUL-2007
00:00:58 2887413
2703078 06-JUL-2007 14:45:59
517 627639885 1 38 2887413 11-JUL-2007
08:22:34 2887469
2703078 06-JUL-2007 14:45:59
518 627639889 1 39 2887469 11-JUL-2007
08:24:45 2887471
2703078 06-JUL-2007 14:45:59
519 627639896 1 40 2887471 11-JUL-2007
08:24:49 2887477
2703078 06-JUL-2007 14:45:59
520 627639901 1 41 2887477 11-JUL-2007
08:24:56 2887479
2703078 06-JUL-2007 14:45:59
521 627642522 1 1 2887470 11-JUL-2007
09:04:05 2887674
2887470 11-JUL-2007 09:04:05
522 627642526 1 2 2887674 11-JUL-2007
09:08:42 2887676
2887470 11-JUL-2007 09:04:05
523 627642530 1 3 2887676 11-JUL-2007
09:08:46 2887679
2887470 11-JUL-2007 09:04:05
524 627642535 1 4 2887679 11-JUL-2007
09:08:50 2887682
2887470 11-JUL-2007 09:04:05
Tuesday, July 10, 2007
Replication - Moving/Re-organize replication system tables
========================================================================
= Moving/Re-organize replication system tables for an existing
replicated database =
========================================================================
Reference
=========
Note:1037317.6
Issues
======
(Seen in GENP as an example)
1. Some DEF$ table in SYSTEM tablespace
system@GENP> select table_name, tablespace_name from user_tables where
table_name like 'DEF$%';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEF$_AQCALL GEN_REP_DATA
DEF$_AQERROR GEN_REP_DATA
DEF$_CALLDEST GEN_REP_DATA
DEF$_DEFAULTDEST GEN_REP_DATA
DEF$_DESTINATION GEN_REP_DATA
DEF$_ERROR GEN_REP_DATA
DEF$_LOB SYSTEM
DEF$_ORIGIN GEN_REP_DATA
DEF$_PROPAGATOR SYSTEM
DEF$_PUSHED_TRANSACTIONS SYSTEM
DEF$_TEMP$LOB SYSTEM
2. DEF$_AQCALL grows too large (HWM too high)
SEGMENT_NAME size(M)
-------------------------------------------------- ----------
DEF$_AQCALL 5403.625
SYS_LOB0000002581C00003$$ 3241
SYS_C00120258 411.5
DEF$_TRANORDER 282.4375
DEF$_CALLDEST_PRIMARY 7.625
Prepare
========
Before starting this procedure, perform the following:
1) Make sure all objects owned by SYS and SYSTEM are valid
2) Backup of the database
Procedure
=========
1) Make sure queue is clear
select count(*) from deftran;
select count(*) from deftrandest;
select count(*) from defcalldest;
select count(*) from deferror;
2) Quiesce your environment to guarantee no new transactions are created
during this process.
3) Perform a TABLE level export
exp system/manager file=repexp.dmp
TABLES=(DEF$_ERROR,
REPCAT$_REPSCHEMA,
DEF$_DESTINATION,
DEF$_CALLDEST,
DEF$_DEFAULTDEST,
DEF$_LOB,
DEF$_TEMP$LOB,
DEF$_ORIGIN,
DEF$_PUSHED_TRANSACTIONS,
DEF$_PROPAGATOR)
LOG=<path>/exprep.log
Note: DEF$_PROPAGATOR added by Denis
= Moving/Re-organize replication system tables for an existing
replicated database =
========================================================================
Reference
=========
Note:1037317.6
Issues
======
(Seen in GENP as an example)
1. Some DEF$ table in SYSTEM tablespace
system@GENP> select table_name, tablespace_name from user_tables where
table_name like 'DEF$%';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEF$_AQCALL GEN_REP_DATA
DEF$_AQERROR GEN_REP_DATA
DEF$_CALLDEST GEN_REP_DATA
DEF$_DEFAULTDEST GEN_REP_DATA
DEF$_DESTINATION GEN_REP_DATA
DEF$_ERROR GEN_REP_DATA
DEF$_LOB SYSTEM
DEF$_ORIGIN GEN_REP_DATA
DEF$_PROPAGATOR SYSTEM
DEF$_PUSHED_TRANSACTIONS SYSTEM
DEF$_TEMP$LOB SYSTEM
2. DEF$_AQCALL grows too large (HWM too high)
SEGMENT_NAME size(M)
-------------------------------------------------- ----------
DEF$_AQCALL 5403.625
SYS_LOB0000002581C00003$$ 3241
SYS_C00120258 411.5
DEF$_TRANORDER 282.4375
DEF$_CALLDEST_PRIMARY 7.625
Prepare
========
Before starting this procedure, perform the following:
1) Make sure all objects owned by SYS and SYSTEM are valid
2) Backup of the database
Procedure
=========
1) Make sure queue is clear
select count(*) from deftran;
select count(*) from deftrandest;
select count(*) from defcalldest;
select count(*) from deferror;
2) Quiesce your environment to guarantee no new transactions are created
during this process.
3) Perform a TABLE level export
exp system/manager file=repexp.dmp
TABLES=(DEF$_ERROR,
REPCAT$_REPSCHEMA,
DEF$_DESTINATION,
DEF$_CALLDEST,
DEF$_DEFAULTDEST,
DEF$_LOB,
DEF$_TEMP$LOB,
DEF$_ORIGIN,
DEF$_PUSHED_TRANSACTIONS,
DEF$_PROPAGATOR)
LOG=<path>/exprep.log
Note: DEF$_PROPAGATOR added by Denis
4) Connect to SQL*Plus as SYSTEM. Drop the following tables in the order
specified below:
drop table system.DEF$_CALLDEST;
drop table system.DEF$_DEFAULTDEST;
drop table system.DEF$_ERROR;
drop table system.DEF$_ORIGIN;
drop table system.REPCAT$_REPSCHEMA;
drop table system.DEF$_DESTINATION;
drop table system.DEF$_LOB;
drop table system.DEF$_TEMP$LOB;
drop table system.DEF$_PUSHED_TRANSACTIONS;
drop table system.DEF$_PROPAGATOR;
5) Connect to SQL*Plus as SYSTEM or REPADMIN and issue:
execute dbms_aqadm.drop_queue_table('SYSTEM.DEF$_AQCALL',TRUE);
execute dbms_aqadm.drop_queue_table('SYSTEM.DEF$_AQERROR',TRUE);
6) Connect to SQL*Plus as SYS and issue:
alter user system default tablespace gen_rep_data
quota unlimited on gen_rep_data;
alter user system quota 0 on system;
revoke unlimited tablespace from system;
7) Connect to SQL*Plus as SYS and run the script "catdefrt.sql" located
in
the "$ORACLE_HOME/rdbms/admin" directory. This recreates all of the
def$_ tables in the new tablespace, as well as associated queues and
indexes.
@?/rdbms/admin/catdefrt
8) Import the tables in the order listed from your export file from step
3
above.
DEF$_ERROR
REPCAT$_REPSCHEMA
DEF$_DESTINATION
DEF$_CALLDEST
DEF$_DEFAULTDEST
DEF$_LOB
DEF$_TEMP$LOB
DEF$_ORIGIN
DEF$_PUSHED_TRANSACTIONS
DEF$_PROPAGATOR
9) After you have imported all replication tables, verify that the
tables are
contained in the new tablespace.
connect system/<password>;
select TABLE_NAME, TABLESPACE_NAME from USER_TABLES;
10) Change tablespace privs
alter user system default tablespace system quota unlimited on
system;
grant unlimited tablespace to system;
11) Check the dba_objects view for invalid objects. If there are, run
utlrp.sql
connected as sysdba.
select count(*) from dba_objects where status = 'INVALID';
count(*)
--------------------
32 <---- (You may see a count of 42 in Oracle9i
9.x)
If count(*) is greater than zero, run as SYS:
@$ORACLE_HOME/rdbms/admin/utlrp.sql
This should recompile the relocated objects. If problems persist see
Note 106206.1 which addresses ORA-4068 errors from Oracle packages.
SQLPLUS CHR(n) function - generate linefeed
Problem:
========
Want to create sql statement with the format as follows:
========
Want to create sql statement with the format as follows:
UPDATE cf_contact_function_type
SET description = 'DFT30'
WHERE contact_function_type_code = 'DCF'
/
Solution:
=========
Using CHR(10) to provide linefeed character
select
'UPDATE cf_contact_function_type' chr(10)
' SET description = ''' description '''' chr(10)
' WHERE contact_function_type_code = ''' contact_function_type_code
'''' chr(10)
'/'
from cf_contact_function_type;
Example output:
repadmin@GENQB> select
2 'UPDATE cf_contact_function_type' chr(10)
3 ' SET description = ''' description '''' chr(10)
4 ' WHERE contact_function_type_code = '''
contact_function_type_code ''''
5 '/'
6 from cf_contact_function_type;
'UPDATECF_CONTACT_FUNCTION_TYPE'CHR(10)'SETDESCRIPTION='''DESCRIPT
ION''''CHR
------------------------------------------------------------------------
--------------
UPDATE cf_contact_function_type
SET description = 'Accounts Payable'
WHERE contact_function_type_code = 'ATP'
/
UPDATE cf_contact_function_type
SET description = 'After Hours'
WHERE contact_function_type_code = 'AFH'
/
UPDATE cf_contact_function_type
SET description = 'Billing'
WHERE contact_function_type_code = 'BLG'
/
UPDATE cf_contact_function_type
SET description = 'Bookkeeper'
WHERE contact_function_type_code = 'BKE'
/
UPDATE cf_contact_function_type
SET description = 'Controller'
WHERE contact_function_type_code = 'CON'
/
UPDATE cf_contact_function_type
SET description = 'Legal'
WHERE contact_function_type_code = 'LEG'
/
UPDATE cf_contact_function_type
SET description = 'Manager'
WHERE contact_function_type_code = 'MGR'
/
Friday, July 06, 2007
Exercise - Using the control file autobackup to recover from the lost of all control files - Oracle 10g
Environment: Oracle 10gR2
Summary of the testing steps
1. First, configure RMAN to perform a control file autobackup:
2. Next, perform a backup with the control file autobackup enabled:
3. Next, simulate the missing control files by deleting all the control files.
4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount.
5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to
6. Next, restore the control file from backup:
7. Next, mount the database and begin to recover the database:
8. Finally, open the database with RESETLOGS option for normal operations:
Detailed steps:
1. First, configure RMAN to perform a control file autobackup:
RMAN> connect target
connected to target database: TOY10G (DBID=3330944552)
RMAN> configure controlfile autobackup on;
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
RMAN> show all;
RMAN>
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_%t_%s_%p.dbf';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/snapcf_TOY10G.f';
RMAN>
2. Next, perform a backup with the control file autobackup enabled:
RMAN> run
2> {
3> backup database;
4> backup (archivelog all);
5> }
Starting backup at 06-JUL-2007 14:19:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/system01.dbf
input datafile fno=00003 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/sysaux01.dbf
input datafile fno=00004 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/users01.dbf
input datafile fno=00005 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/indx01.dbf
input datafile fno=00002 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:15
channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:22
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229154_17_1.dbf tag=TAG20070706T141914 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 06-JUL-2007 14:19:22
Starting backup at 06-JUL-2007 14:19:25
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=478 recid=1 stamp=627229166
channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:27
channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229166_18_1.dbf tag=TAG20070706T141926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 06-JUL-2007 14:19:28
Starting Control File Autobackup at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp comment=NONE
Finished Control File Autobackup at 06-JUL-2007 14:19:30
RMAN>
3. Next, simulate the missing control files by deleting all the control files. (The database will need to be shut down to perform this simulated failure.)
sys@TOY10G> select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf
after shutdown, do:
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf
4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount:
idle> conn / as sysdba
Connected to an idle instance.
idle> startup nomount pfile='/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/pfile/initTOY10G.ora';
ORACLE instance started.
5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to, because the control file contains this information and failure causes the control file to be unavailable.
RMAN> connect target
connected to target database: TOY10G (not mounted)
RMAN> set dbid 3330944552
executing command: SET DBID
RMAN>
6. Next, restore the control file from backup:
RMAN> restore controlfile from autobackup;
Starting restore at 06-JUL-2007 14:35:11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
recovery area destination: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G
database name (or database unique name) used for search: TOY10G
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf
Finished restore at 06-JUL-2007 14:35:18
RMAN>
7. Next, mount the database and begin to recover the database:
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 06-JUL-2007 14:44:20
Starting implicit crosscheck backup at 06-JUL-2007 14:44:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 06-JUL-2007 14:44:22
Starting implicit crosscheck copy at 06-JUL-2007 14:44:22
using channel ORA_DISK_1
Crosschecked 5 objects
Finished implicit crosscheck copy at 06-JUL-2007 14:44:22
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
File Name: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 479 is already on disk as file /ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log
archive log filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log thread=1 sequence=479
media recovery complete, elapsed time: 00:00:03
Finished recover at 06-JUL-2007 14:44:28
RMAN>
8. Finally, open the database with RESETLOGS option for normal operations:
RMAN> alter database open resetlogs;
database opened
RMAN>
Summary of the testing steps
1. First, configure RMAN to perform a control file autobackup:
2. Next, perform a backup with the control file autobackup enabled:
3. Next, simulate the missing control files by deleting all the control files.
4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount.
5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to
6. Next, restore the control file from backup:
7. Next, mount the database and begin to recover the database:
8. Finally, open the database with RESETLOGS option for normal operations:
Detailed steps:
1. First, configure RMAN to perform a control file autobackup:
RMAN> connect target
connected to target database: TOY10G (DBID=3330944552)
RMAN> configure controlfile autobackup on;
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
RMAN> show all;
RMAN>
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_%t_%s_%p.dbf';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/snapcf_TOY10G.f';
RMAN>
2. Next, perform a backup with the control file autobackup enabled:
RMAN> run
2> {
3> backup database;
4> backup (archivelog all);
5> }
Starting backup at 06-JUL-2007 14:19:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/system01.dbf
input datafile fno=00003 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/sysaux01.dbf
input datafile fno=00004 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/users01.dbf
input datafile fno=00005 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/indx01.dbf
input datafile fno=00002 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:15
channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:22
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229154_17_1.dbf tag=TAG20070706T141914 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 06-JUL-2007 14:19:22
Starting backup at 06-JUL-2007 14:19:25
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=478 recid=1 stamp=627229166
channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:27
channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229166_18_1.dbf tag=TAG20070706T141926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 06-JUL-2007 14:19:28
Starting Control File Autobackup at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp comment=NONE
Finished Control File Autobackup at 06-JUL-2007 14:19:30
RMAN>
3. Next, simulate the missing control files by deleting all the control files. (The database will need to be shut down to perform this simulated failure.)
sys@TOY10G> select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf
after shutdown, do:
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf
4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount:
idle> conn / as sysdba
Connected to an idle instance.
idle> startup nomount pfile='/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/pfile/initTOY10G.ora';
ORACLE instance started.
5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to, because the control file contains this information and failure causes the control file to be unavailable.
RMAN> connect target
connected to target database: TOY10G (not mounted)
RMAN> set dbid 3330944552
executing command: SET DBID
RMAN>
6. Next, restore the control file from backup:
RMAN> restore controlfile from autobackup;
Starting restore at 06-JUL-2007 14:35:11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
recovery area destination: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G
database name (or database unique name) used for search: TOY10G
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf
Finished restore at 06-JUL-2007 14:35:18
RMAN>
7. Next, mount the database and begin to recover the database:
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 06-JUL-2007 14:44:20
Starting implicit crosscheck backup at 06-JUL-2007 14:44:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 06-JUL-2007 14:44:22
Starting implicit crosscheck copy at 06-JUL-2007 14:44:22
using channel ORA_DISK_1
Crosschecked 5 objects
Finished implicit crosscheck copy at 06-JUL-2007 14:44:22
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
File Name: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 479 is already on disk as file /ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log
archive log filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log thread=1 sequence=479
media recovery complete, elapsed time: 00:00:03
Finished recover at 06-JUL-2007 14:44:28
RMAN>
8. Finally, open the database with RESETLOGS option for normal operations:
RMAN> alter database open resetlogs;
database opened
RMAN>
Exercise - Switch the database archiving mode from NOARCHIVELOG to ARCHIVELOG
Exercise - Switch the database archiving mode from NOARCHIVELOG to
ARCHIVELOG
--------------------------------------------------------------------
Environment: Oracle 10g
ARCHIVELOG
--------------------------------------------------------------------
Environment: Oracle 10g
1. Shut down the database instance.
sys@TOY10G> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TOY10G>
An open database must first be closed and any associated instances shut
down
before you can switch the database archiving mode. You cannot change the
mode from
ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery.
2. Back up the database.
Before making any major change to a database, always back up the
database to protect
against any problems. This will be your final backup of the database in
NOARCHIVELOG
mode and can be used if something goes wrong during the change to
ARCHIVELOG mode.
3. Edit the initialization parameter file to include the initialization
parameters
that specify the destinations for the archive log files.
log_archive_dest_1 =
"LOCATION=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G"
log_archive_dest_state_1 = enable
LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc
4. Start a new instance and mount, but do not open, the database.
idle> conn / as sysdba
Connected to an idle instance.
idle> startup mount
pfile='/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/pfile/initTO
Y10G.ora';
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 2029392 bytes
Variable Size 92276912 bytes
Database Buffers 167772160 bytes
Redo Buffers 6356992 bytes
Database mounted.
To enable or disable archiving, the database must be mounted but not
open.
4. Change the database archiving mode. Then open the database for normal
operations.
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
idle> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G
Oldest online log sequence 476
Next log sequence to archive 478
Current log sequence 478
5. Shut down the database.
SHUTDOWN IMMEDIATE
6. Back up the database.
Changing the database archiving mode updates the control file. After
changing the database archiving mode, you must back up all of your
database files and control file. Any previous backup is no longer usable
because it was taken in NOARCHIVELOG mode.
Thursday, July 05, 2007
Link - Oracle Replication FAQ
<<faqrepl.url>>
The message is ready to be sent with the following file or link
attachments:
Shortcut to: http://orafaq.com/faqrepl.htm#DEFERR
Note: To protect against computer viruses, e-mail programs may prevent
sending or receiving certain types of file attachments. Check your
e-mail security settings to determine how attachments are handled.
RMAN: How to delete backup of archivelog in catalog after deleting by OS command
========================================================================
==
How to delete backup of archivelog in catalog after deleting by OS
command
========================================================================
==
Environment: Oracle 8i
==
How to delete backup of archivelog in catalog after deleting by OS
command
========================================================================
==
Environment: Oracle 8i
1. Resync catalog if necessary
2. Query RC_BACKUP_SET
2.1 Log into catalog db as catalog user:
sqlplus rman/xxx@catp92a
2.2 Generate RMAN command to crosscheck backupset of archivelog for next
step. (dbid represents GENB database)
------------------------------------------------------------------------
--
select 'change backupset ' || bs_key || ' crosscheck;' || ' # ' ||
completion_time
from rc_backup_set
where db_id=3427423174
and completion_time > sysdate - 7
and completion_time < sysdate - 1
and backup_type='L'
/
Sample Output:
change backupset 19565786 crosscheck; # 28-JUN-2007 19:56:06
change backupset 19565787 crosscheck; # 28-JUN-2007 19:56:06
change backupset 19676247 crosscheck; # 03-JUL-2007 21:02:26
change backupset 19676248 crosscheck; # 03-JUL-2007 21:04:26
change backupset 19676249 crosscheck; # 03-JUL-2007 21:06:04
change backupset 19676250 crosscheck; # 03-JUL-2007 21:07:43
change backupset 19676251 crosscheck; # 03-JUL-2007 21:09:02
change backupset 19676252 crosscheck; # 03-JUL-2007 21:10:18
change backupset 19676253 crosscheck; # 03-JUL-2007 21:11:32
change backupset 19676254 crosscheck; # 03-JUL-2007 21:12:44
change backupset 19676259 crosscheck; # 04-JUL-2007 21:00:52
3. Cross check the backupset of archivelog
rman target / catalog rman/xxx@catp92a
allocate channel for maintenance type disk;
execute the command obtained in step 2
4. Delete expired backup of archivelog;
rman target / catalog rman/xxx@catp92a
Example syntax to delete expired backup of archivelog
-----------------------------------------------------
delete expired backup of archivelog from time '28-JUN-2007' until
time '04-JUL-2007';
Note: If it just hang there for more than 10 min, ctrl-c then take
following action:
Action:
~~~~~~
4.1 Connect without catalog:
rman target / nocatalog
then issue:
RMAN> allocate channel for maintenance type disk;
RMAN> delete expired backup of archivelog from time '28-JUN-2007'
until
time '04-JUL-2007';
4.2 Connect to target and catalog databases issue:
RMAN> resync catalog;
Tuesday, July 03, 2007
Experience errors in a test database refreshed from production
MYDBP is a production database (8i) with replication and in archivelog mode. MYDB3S is a test databe, intend to be standalone and in noarchivelog mode. I created database MYDB3S from RMAN backup of MYDBP using RMAN duplicate command. However, I have made two mistakes:
1. Dropped replication package, resulting in some triggers invalid
In some triggers, there is a condition check:
IF DBMS_REPUTIL.FROM_REMOTE
THEN
RETURN;
END IF;
While I executed the $ORACLE_HOME/rdbms/admin/catrepr.sql to remove replication catalog views, packages, etc, I dropped the DBMS_REPUTIL package among other things.
Solution: re-install replciation catalog views and package by issue:
$ORACLE_HOME/rdbms/admin/catrep.sql
2. Failed to turn off archivelog mode
I only set archive_log_start=false in the init.ora. I should have issued
'alter system noarchivelog' in the mount mode too to really turn off the archivelog mode
1. Dropped replication package, resulting in some triggers invalid
In some triggers, there is a condition check:
IF DBMS_REPUTIL.FROM_REMOTE
THEN
RETURN;
END IF;
While I executed the $ORACLE_HOME/rdbms/admin/catrepr.sql to remove replication catalog views, packages, etc, I dropped the DBMS_REPUTIL package among other things.
Solution: re-install replciation catalog views and package by issue:
$ORACLE_HOME/rdbms/admin/catrep.sql
2. Failed to turn off archivelog mode
I only set archive_log_start=false in the init.ora. I should have issued
'alter system noarchivelog' in the mount mode too to really turn off the archivelog mode
Subscribe to:
Posts (Atom)
