Diagnosing Free List Contention
The V$SYSTEM_EVENT and V$WAITSTAT dynamic performance views are used to diagnose free list contention problems.
Select class,count,time
From v$waitstat
Where class = ‘segment header’;
Select event,total_waits
From v$system_event
Where event =’buffer busy waits’;
Identify the segment and determine the number of free list that currently exist for the segment identified by querying:
Select s.segment_name,s.segment_type,s.freelists,w.wait_time,w.seconds_in_wait,w.state
from dba_segments s, V$session_wait w
where w.event ='buffer busy waits'
and w.p1 = s.header_file
and w.p2 = s.header_block;
Resolving Free List Contention
In order to change the freelist storage just alters the segment:
Alter table xyz storage( freelists 2);
In oracle9i free list contention can be solved if you move the infected table into a tablespace with space segment management auto
Friday, April 13, 2007
Resolving Block migration and chaining
Detecting Migration and Chaining
There three ways in order to find if your database’s blocks are chained or migrated.
The first way is to create the chain_rows tables using the utlchain.sql script under the $ORACLE_HOME/rdbms/admin or call it @?/rdbms/admin/utlchain.sql. After that analyze the table using:
Analyze table xyz list chained rows;
The second way is to detected migrated or chained rows by checking the “table fetch continue row” statistic in V$SYSSTAT.
The third way is to analyze the table first and after that to use the following query
Select num_rows,chain_cnt,
(chain_cnt * 100)/num_rows as " Chained Percentage"
From dba_tables
where table_name=’XYZ’
Eliminating Migrated Rows
Export – Import
Export the table.
Drop or truncate the table.
Import the table.
Move table command
Alter table xyz move
Rebuild xyz’s tables.
Copying migrated rows
Find migraded rows using.
Copy migrated rows to new table.
Delete migrayed rows from original table.
Copy rows from new table to original table;
There three ways in order to find if your database’s blocks are chained or migrated.
The first way is to create the chain_rows tables using the utlchain.sql script under the $ORACLE_HOME/rdbms/admin or call it @?/rdbms/admin/utlchain.sql. After that analyze the table using:
Analyze table xyz list chained rows;
The second way is to detected migrated or chained rows by checking the “table fetch continue row” statistic in V$SYSSTAT.
The third way is to analyze the table first and after that to use the following query
Select num_rows,chain_cnt,
(chain_cnt * 100)/num_rows as " Chained Percentage"
From dba_tables
where table_name=’XYZ’
Eliminating Migrated Rows
Export – Import
Export the table.
Drop or truncate the table.
Import the table.
Move table command
Alter table xyz move
Rebuild xyz’s tables.
Copying migrated rows
Find migraded rows using.
Copy migrated rows to new table.
Delete migrayed rows from original table.
Copy rows from new table to original table;
Parallel Direct Load Insert DML
Example
create table table2 ( id number,name varchar2(10));
select degree from user_tables where table_name='TABLE2';
ALTER TABLE michael.table2 PARALLEL ( DEGREE 2 );
alter session enable parallel dml;
Insert /*+ Append parallel(table2,2) */ into table2
select * from table1;
truncate table table2 reuse storage
create table table2 ( id number,name varchar2(10));
select degree from user_tables where table_name='TABLE2';
ALTER TABLE michael.table2 PARALLEL ( DEGREE 2 );
alter session enable parallel dml;
Insert /*+ Append parallel(table2,2) */ into table2
select * from table1;
truncate table table2 reuse storage
XML IN- XML OUT in Oracle 9i
As DBA user create the xmluser just execute the following
SQL> create user xmluser identified by xmluser;
User created.
SQL> grant connect,resource to xmluser;
Grant succeeded.
SQL>
As xmluser create the following tables
SQL> create table employee
(
id number primary key,name varchar2(20),
regdate date default sysdate
);
Table created.
SQL> create table xml_out ( xml_data clob);
Table created.
The table employee contains the employee data and the table xml_out contains the query result from employee table.
Put few row in employee as follows
SQL> insert into employee(id,name) values(1,'Michael Georgiou');
1 row created.
SQL> insert into employee values(2,'Katerina Georgiou',sysdate+1);
1 row created.
SQL> insert into employee(id) values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace procedure load_xml_from_query is
2 begin
3 declare
4 qryCtx dbms_xmlgen.ctxHandle;
5 result CLOB;
6 begin
7 qryCtx := dbms_xmlgen.newContext('Select id,name,regdate from employee');
8 --Row header is the employee
9 dbms_xmlgen.setRowTag(qryCtx,'Employee');
10 result:= dbms_xmlgen.getXML(qryCtx);
11
12 insert into xml_out values(result);
13
14 dbms_xmlgen.closeContext(qryCtx);
15 end;
16 end;
17 /
Procedure created.
SQL> show errors;
No errors.
SQL> exec load_xml_from_query;
PL/SQL procedure successfully completed.
Now query the xml_out table to see the xml output.
SQL> set long 2000
SQL> set pagesize 80
SQL> select * from xml_out;
XML_DATA
--------------------------------------------------------------------------------
1
Michael Georgiou
22-MAR-06
2
Katerina Georgiou
23-MAR-06
3
22-MAR-06
As you can see the xml_data column contain your query in the xml format.
In order to transport the data from xml_data column is good idea to put it in a operating system file.
Create the an oracle directory as follows , login as DBA
Sql > create or replace directory XML_DIR as ‘C:\XML’;
Sql> grant read,write on directory XML_DIR to xmluser;
After that login as xmluser and run the following;
DECLARE
CURSOR xml_data IS SELECT * FROM xml_out;
file_handle utl_file.file_type;
BEGIN
file_handle := utl_file.fopen('XML_DIR','employee.xml','w');
FOR i IN xml_data LOOP
file_handle := utl_file.fopen('XML_DIR','employee.xml', 'a');
utl_file.put_line(file_handle,i.xml_data);
utl_file.fclose(file_handle);
END LOOP;
END;
/
If the above block runs successfully then a new file will be created under
c:\xml with the name employee.xml;
Loading method ( From xml file to table)
You need to load the data in employee.xml in employeeTest table
As xmluser create the following table
SQL> create table employeeTest
(
id number primary key,name varchar2(20),
regdate date default sysdate
);
Where the employee.xml contents is :
1
Michael Georgiou
22-MAR-06
2
Katerina Georgiou
23-MAR-06
3
22-MAR-06
The following code insert the above xml into employeeTest table
declare
indoc VARCHAR2(2000);
buf VARCHAR2(30000);
insCtx DBMS_XMLSave.ctxType;
rows number;
name VARCHAR2(20);
dlob CLOB := EMPTY_CLOB();
begin
dbms_lob.createtemporary( dlob, TRUE, 2 );
-- Change the directory to reflect yours
indoc :='C:\XML\employee.xml';
name := 'employeeTest';
xmldom.writetoClob(xmlparser.parse(indoc), dlob);
insCtx := DBMS_XMLSave.newContext(name); -- get the context handle
DBMS_XMLSave.setDateFormat(insCtx, 'dd-MMM-yy');
DBMS_XMLSave.setRowTag(insCtx, 'EMPLOYEE');
rows := DBMS_XMLSave.insertXML(insCtx,dlob); -- this inserts the document
dbms_output.put_line(to_char(rows) ' rows inserted');
DBMS_XMLSave.closeContext(insCtx);
End;
/
*** The EMPLOYEE is case sensitive
SQL> select * from employee;
ID NAME REGDATE
---------- -------------------- ---------
1 Michael Georgiou 22-MAR-06
2 Katerina Georgiou 23-MAR-06
3 22-MAR-06
References
+Oracle9i XML Database Developer’s Guide - Oracle XML DB
+ For generating XML from relational data see chapter 10 in particular:
-Chapter 10 Generating XML Data from the Database
+For loading XML into the database you can either store the data in a CLOB or an XMLTYPE column which is advised. See:
-Chapter 4 Using XMLType
Technical white papers about usage of XDB can also be found on the Technology Network.
http://www.oracle.com/technology/tech/xml/xmldb/index.html
How to Read XML File Stored in Clob Column and Extract Data to Different Table
Note:135352.1
XMLGEN API Has Been Deprecated from XDK 9.2
Note:220020.1
240097.1
DBMS_XMLSave.insertXML Fails With ORA-29532 ORA-06512
185257.1
SQL> create user xmluser identified by xmluser;
User created.
SQL> grant connect,resource to xmluser;
Grant succeeded.
SQL>
As xmluser create the following tables
SQL> create table employee
(
id number primary key,name varchar2(20),
regdate date default sysdate
);
Table created.
SQL> create table xml_out ( xml_data clob);
Table created.
The table employee contains the employee data and the table xml_out contains the query result from employee table.
Put few row in employee as follows
SQL> insert into employee(id,name) values(1,'Michael Georgiou');
1 row created.
SQL> insert into employee values(2,'Katerina Georgiou',sysdate+1);
1 row created.
SQL> insert into employee(id) values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace procedure load_xml_from_query is
2 begin
3 declare
4 qryCtx dbms_xmlgen.ctxHandle;
5 result CLOB;
6 begin
7 qryCtx := dbms_xmlgen.newContext('Select id,name,regdate from employee');
8 --Row header is the employee
9 dbms_xmlgen.setRowTag(qryCtx,'Employee');
10 result:= dbms_xmlgen.getXML(qryCtx);
11
12 insert into xml_out values(result);
13
14 dbms_xmlgen.closeContext(qryCtx);
15 end;
16 end;
17 /
Procedure created.
SQL> show errors;
No errors.
SQL> exec load_xml_from_query;
PL/SQL procedure successfully completed.
Now query the xml_out table to see the xml output.
SQL> set long 2000
SQL> set pagesize 80
SQL> select * from xml_out;
XML_DATA
--------------------------------------------------------------------------------
As you can see the xml_data column contain your query in the xml format.
In order to transport the data from xml_data column is good idea to put it in a operating system file.
Create the an oracle directory as follows , login as DBA
Sql > create or replace directory XML_DIR as ‘C:\XML’;
Sql> grant read,write on directory XML_DIR to xmluser;
After that login as xmluser and run the following;
DECLARE
CURSOR xml_data IS SELECT * FROM xml_out;
file_handle utl_file.file_type;
BEGIN
file_handle := utl_file.fopen('XML_DIR','employee.xml','w');
FOR i IN xml_data LOOP
file_handle := utl_file.fopen('XML_DIR','employee.xml', 'a');
utl_file.put_line(file_handle,i.xml_data);
utl_file.fclose(file_handle);
END LOOP;
END;
/
If the above block runs successfully then a new file will be created under
c:\xml with the name employee.xml;
Loading method ( From xml file to table)
You need to load the data in employee.xml in employeeTest table
As xmluser create the following table
SQL> create table employeeTest
(
id number primary key,name varchar2(20),
regdate date default sysdate
);
Where the employee.xml contents is :
The following code insert the above xml into employeeTest table
declare
indoc VARCHAR2(2000);
buf VARCHAR2(30000);
insCtx DBMS_XMLSave.ctxType;
rows number;
name VARCHAR2(20);
dlob CLOB := EMPTY_CLOB();
begin
dbms_lob.createtemporary( dlob, TRUE, 2 );
-- Change the directory to reflect yours
indoc :='C:\XML\employee.xml';
name := 'employeeTest';
xmldom.writetoClob(xmlparser.parse(indoc), dlob);
insCtx := DBMS_XMLSave.newContext(name); -- get the context handle
DBMS_XMLSave.setDateFormat(insCtx, 'dd-MMM-yy');
DBMS_XMLSave.setRowTag(insCtx, 'EMPLOYEE');
rows := DBMS_XMLSave.insertXML(insCtx,dlob); -- this inserts the document
dbms_output.put_line(to_char(rows) ' rows inserted');
DBMS_XMLSave.closeContext(insCtx);
End;
/
*** The EMPLOYEE is case sensitive
SQL> select * from employee;
ID NAME REGDATE
---------- -------------------- ---------
1 Michael Georgiou 22-MAR-06
2 Katerina Georgiou 23-MAR-06
3 22-MAR-06
References
+Oracle9i XML Database Developer’s Guide - Oracle XML DB
+ For generating XML from relational data see chapter 10 in particular:
-Chapter 10 Generating XML Data from the Database
+For loading XML into the database you can either store the data in a CLOB or an XMLTYPE column which is advised. See:
-Chapter 4 Using XMLType
Technical white papers about usage of XDB can also be found on the Technology Network.
http://www.oracle.com/technology/tech/xml/xmldb/index.html
How to Read XML File Stored in Clob Column and Extract Data to Different Table
Note:135352.1
XMLGEN API Has Been Deprecated from XDK 9.2
Note:220020.1
240097.1
DBMS_XMLSave.insertXML Fails With ORA-29532 ORA-06512
185257.1
Hiding sys password when you use sql loader and export utility
WINDOWS
=======
country.ctl
============
LOAD DATA
INFILE 'C:\sql_loader\country.data' "Str '#\r\n'"
INTO TABLE michael.country
fields terminated by ','
(countryName,countryCode)
country.data
==============
CYPRUS,CY,#
AFGHANISTAN,AF,#
ALBANIA,AL,#
ALGERIA,DZ,#
sqlldr '/ AS SYSDBA' control=product.ctl
UNIX
====
country.ctl
============
LOAD DATA
INFILE '/devvol/oracle9i/michaelg/SQL_LOADER/country.data' "Str '#'"
INTO TABLE michael.country
fields terminated by ','
(countryName,countryCode)
country.data
==============
CYPRUS,CY,#
AFGHANISTAN,AF,#
ALBANIA,AL,#
ALGERIA,DZ,#
sqlldr \'/ AS SYSDBA\' control=/devvol/oracle9i/michaelg/SQL_LOADER/country.ctl
Run the script as root user as follows:
# su - oracle9i -c "sqlldr \'/ AS SYSDBA\' control=/devvol/oracle9i/michaelg/SQL_LOADER/country.ctl"
Sun Microsystems Inc. SunOS 5.9 Generic May 2002
bash-2.05$ exit
exit
SQL*Loader: Release 9.2.0.6.0 - Production on Tue Jul 4 11:26:05 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
Hiding sys password when you use exp utility
bash-2.05$ exp "'/ as sysdba'" tables=michael.country file=test.dmp
Export: Release 9.2.0.6.0 - Production on Tue Jul 4 11:36:42 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in EL8ISO8859P7 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to MICHAEL
. . exporting table COUNTRY 4 rows exported
Export terminated successfully without warnings.
bash-2.05$
Run the script as root user as follows:
# su - oracle9i -c /devvol/oracle9i/michaelg/EXP/testEXP.sh
Sun Microsystems Inc. SunOS 5.9 Generic May 2002
bash-2.05$ exit
exit
/devvol/oracle9i/michaelg/EXP/testEXP.sh:
Export: Release 9.2.0.6.0 - Production on Tue Jul 4 11:58:39 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in EL8ISO8859P7 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to MICHAEL
. . exporting table COUNTRY 4 rows exported
Export terminated successfully without warnings.
#
=======
country.ctl
============
LOAD DATA
INFILE 'C:\sql_loader\country.data' "Str '#\r\n'"
INTO TABLE michael.country
fields terminated by ','
(countryName,countryCode)
country.data
==============
CYPRUS,CY,#
AFGHANISTAN,AF,#
ALBANIA,AL,#
ALGERIA,DZ,#
sqlldr '/ AS SYSDBA' control=product.ctl
UNIX
====
country.ctl
============
LOAD DATA
INFILE '/devvol/oracle9i/michaelg/SQL_LOADER/country.data' "Str '#'"
INTO TABLE michael.country
fields terminated by ','
(countryName,countryCode)
country.data
==============
CYPRUS,CY,#
AFGHANISTAN,AF,#
ALBANIA,AL,#
ALGERIA,DZ,#
sqlldr \'/ AS SYSDBA\' control=/devvol/oracle9i/michaelg/SQL_LOADER/country.ctl
Run the script as root user as follows:
# su - oracle9i -c "sqlldr \'/ AS SYSDBA\' control=/devvol/oracle9i/michaelg/SQL_LOADER/country.ctl"
Sun Microsystems Inc. SunOS 5.9 Generic May 2002
bash-2.05$ exit
exit
SQL*Loader: Release 9.2.0.6.0 - Production on Tue Jul 4 11:26:05 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
Hiding sys password when you use exp utility
bash-2.05$ exp "'/ as sysdba'" tables=michael.country file=test.dmp
Export: Release 9.2.0.6.0 - Production on Tue Jul 4 11:36:42 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in EL8ISO8859P7 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to MICHAEL
. . exporting table COUNTRY 4 rows exported
Export terminated successfully without warnings.
bash-2.05$
Run the script as root user as follows:
# su - oracle9i -c /devvol/oracle9i/michaelg/EXP/testEXP.sh
Sun Microsystems Inc. SunOS 5.9 Generic May 2002
bash-2.05$ exit
exit
/devvol/oracle9i/michaelg/EXP/testEXP.sh:
Export: Release 9.2.0.6.0 - Production on Tue Jul 4 11:58:39 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in EL8ISO8859P7 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to MICHAEL
. . exporting table COUNTRY 4 rows exported
Export terminated successfully without warnings.
#
User Manage backup 9i
The most import is to create the following oracle directory as sys user
1.sql> create or replace BACKUP_ORACLE_DIR
as ‘\path\to\directory’;
2. Decide the a destination for database’s datafiles
3. Database must be in archive log mode
4. The procedure has the followings inputs
Input 1 = ‘WIN’ for windows and ‘UNIX’ for unix platforms
Input 2 = destination for database’s datafiles
5. This backup strategy does not make backup any tablespace that its status is read only or offline as well as the temporary tablespace.
Use the sql>set serveroutput on; in order to catch any errors
exec createBackup('win','C:\backup');
/*
Michael Georgiou
Run the script as sysdba
*/
CREATE OR REPLACE PROCEDURE CreateBackup
(
os IN VARCHAR2,
copy_files_dest IN VARCHAR2
)
IS
BEGIN
DECLARE
directory_counter INTEGER :=0;
archive_counter INTEGER :=0;
os_command VARCHAR2(10):='cp';
counter INTEGER :=0;
--------------------------------
-- Which tablespaces ---------
CURSOR TS_NAME IS
SELECT tablespace_name
FROM dba_tablespaces
WHERE status NOT IN ('READ ONLY','OFFLINE')
AND CONTENTS <> 'TEMPORARY';
---------------------------------
-- Find tablespaces datafiles---
CURSOR DF_NAME(ts_nm VARCHAR2) IS
SELECT file_name
FROM dba_data_files
WHERE tablespace_name = ts_nm;
---------------------------------
file_handle Utl_File.file_type;
BEGIN
Dbms_Output.ENABLE(1000);
SELECT COUNT(*) INTO directory_counter
FROM dba_directories
WHERE directory_name='BACKUP_ORACLE_DIR';
SELECT COUNT(*) INTO archive_counter
FROM v$database
WHERE log_mode='ARCHIVELOG';
IF UPPER(os)='WIN' THEN
os_command := 'xcopy /y';
ELSE
os_command := 'cp';
END IF;
IF archive_counter = 1 THEN
counter :=1;
ELSE
Dbms_Output.put_line('ERROR:');
Dbms_Output.put_line('Media recovery is not enabled');
counter:=0;
END IF;
IF directory_counter = 1 THEN
counter:=1;
ELSE
Dbms_Output.put_line('ERROR:');
Dbms_Output.put_line('The Oracle Directory BACKUP_ORACLE_DIR does not exit');
counter:=0;
END IF;
IF counter=1 THEN
file_handle := Utl_File.fopen( 'BACKUP_ORACLE_DIR', 'HotBackup.sql', 'w');
FOR TS IN TS_NAME LOOP
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'alter tablespace 'TS.tablespace_name' begin backup;');
Utl_File.fclose(file_handle);
FOR DF IN DF_NAME(TS.tablespace_name) LOOP
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'HOST 'os_command' 'DF.file_name' 'copy_files_dest);
Utl_File.fclose(file_handle);
END LOOP;
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'alter tablespace 'TS.tablespace_name' end backup;');
Utl_File.fclose(file_handle);
END LOOP;
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'Alter system archive log current;');
Utl_File.put_line(file_handle,'exit');
Utl_File.fclose(file_handle);
END IF;
END;
END;
/
show errors;
1.sql> create or replace BACKUP_ORACLE_DIR
as ‘\path\to\directory’;
2. Decide the a destination for database’s datafiles
3. Database must be in archive log mode
4. The procedure has the followings inputs
Input 1 = ‘WIN’ for windows and ‘UNIX’ for unix platforms
Input 2 = destination for database’s datafiles
5. This backup strategy does not make backup any tablespace that its status is read only or offline as well as the temporary tablespace.
Use the sql>set serveroutput on; in order to catch any errors
exec createBackup('win','C:\backup');
/*
Michael Georgiou
Run the script as sysdba
*/
CREATE OR REPLACE PROCEDURE CreateBackup
(
os IN VARCHAR2,
copy_files_dest IN VARCHAR2
)
IS
BEGIN
DECLARE
directory_counter INTEGER :=0;
archive_counter INTEGER :=0;
os_command VARCHAR2(10):='cp';
counter INTEGER :=0;
--------------------------------
-- Which tablespaces ---------
CURSOR TS_NAME IS
SELECT tablespace_name
FROM dba_tablespaces
WHERE status NOT IN ('READ ONLY','OFFLINE')
AND CONTENTS <> 'TEMPORARY';
---------------------------------
-- Find tablespaces datafiles---
CURSOR DF_NAME(ts_nm VARCHAR2) IS
SELECT file_name
FROM dba_data_files
WHERE tablespace_name = ts_nm;
---------------------------------
file_handle Utl_File.file_type;
BEGIN
Dbms_Output.ENABLE(1000);
SELECT COUNT(*) INTO directory_counter
FROM dba_directories
WHERE directory_name='BACKUP_ORACLE_DIR';
SELECT COUNT(*) INTO archive_counter
FROM v$database
WHERE log_mode='ARCHIVELOG';
IF UPPER(os)='WIN' THEN
os_command := 'xcopy /y';
ELSE
os_command := 'cp';
END IF;
IF archive_counter = 1 THEN
counter :=1;
ELSE
Dbms_Output.put_line('ERROR:');
Dbms_Output.put_line('Media recovery is not enabled');
counter:=0;
END IF;
IF directory_counter = 1 THEN
counter:=1;
ELSE
Dbms_Output.put_line('ERROR:');
Dbms_Output.put_line('The Oracle Directory BACKUP_ORACLE_DIR does not exit');
counter:=0;
END IF;
IF counter=1 THEN
file_handle := Utl_File.fopen( 'BACKUP_ORACLE_DIR', 'HotBackup.sql', 'w');
FOR TS IN TS_NAME LOOP
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'alter tablespace 'TS.tablespace_name' begin backup;');
Utl_File.fclose(file_handle);
FOR DF IN DF_NAME(TS.tablespace_name) LOOP
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'HOST 'os_command' 'DF.file_name' 'copy_files_dest);
Utl_File.fclose(file_handle);
END LOOP;
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'alter tablespace 'TS.tablespace_name' end backup;');
Utl_File.fclose(file_handle);
END LOOP;
file_handle := Utl_File.fopen('BACKUP_ORACLE_DIR', 'HotBackup.sql', 'a');
Utl_File.put_line(file_handle,'Alter system archive log current;');
Utl_File.put_line(file_handle,'exit');
Utl_File.fclose(file_handle);
END IF;
END;
END;
/
show errors;
Recover database in Oracle9+
Document Scope
This document describes some important scenarios for recovering oracle9i database for a specific failure using complete and incomplete recovery
Document contents
==================
Scenario 1
Recovery in NOARCHIVELOG mode with out redo log files backups.
Scenario 2
Loosing users tablespace (because of media failure) and need to recover it in new location.( The database is open)
Scenario 3
Loosing all control files
Scenario 4
Loosing undo tablespace
Scenario 5
Loosing System tablespace
Scenario 6
Recover from user error. The table xyz was dropped at 11:00 am.
Scenario 7
Loss of inactive online redo log
Scenario 8
Loss of current online redo log
Scenario 9
Recover a lost datafile with no backup but you have all the archives from datafile creation.
Tips
====
Before the Recover command verify whether any offile datafiles exist and place then online, because any offline files may be unrecoverable after recovery.
SQL> select * from V$recover_file;
Ensure the log_archive_format to much your archives if not during recovery use the command
Sql>alter system log_archive_format=your_format scope=memory i.e in police enviroment is arch_%s.arc for
Example arch_88909.arc.
To change the archive destination for new location
Sql>alter system archive log start to ‘/backup/archives/day0’
To get a picture of the archives and current redo logs
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 130 **ARCHIVED
Next log sequence to archive 131 **WAIT TO BE ARCHIVED
Current log sequence 131
SQL> select status,archived,sequence# from v$log;
STATUS ARC SEQUENCE#
---------------- --- ----------
CURRENT NO 131
INACTIVE YES 130
Sql>select sequence#,to_char(first_time,'DD-MON HH24:MI') from v$log_history
Or
Sql> select archived,sequence# from v$archived_log order by sequence# desc
Scenario 1
Recovery in NOARCHIVELOG mode with out redo log files backups.
Sql > shutdown immediate;
Restore all datafiles and control files from your backup
Sql> startup mount;
Sql>Recover database until cancel using backup controlfile;
Sql>Alter database open resetlogs;
Scenario 2
Loosing users tablespace (because of media failure) and need to recover it in new location.( The database is open)
SQL> alter tablespace users offline;
Restore from backup into new location
SQL> alter tablespace users
rename datafile 'C:\ORACLE\ORADATA\TARGET01\USERS01.
DBF' to 'C:\ORACLE\ORADATA\TARGET01\disk2\USERS01.DBF';
SQL> recover tablespace users
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter tablespace users online;
Tablespace altered.
Scenario 3
Loosing all control files
If and only if current redo is available
1. create the controlfile from your saved scripts
2. sql>recover database
3. sql>alter system archive log all
4. sql>alter database open ;
If and only if current redo is not available
1. create the controlfile from your saved scripts
2. sql>recover database using backup control file;
3. sql>alter database open resetlogs
Scenario 4
Loosing undo tablespace
1. Restore undotbs1 from backup
2. Mount the database
3. sql>Recover database
4. Apply archives and redo logs
5. sql>Alter database open;
Scenario 5
Loosing System tablespace
*******Use the instruction on step 4
Scenario 6
Recover from user error. The table xyz was dropped at 11:00 am.
1. Sql>shutdown immediate;
2. Sql>Restore datafiles and controlfiles
3. Sql>recover database until time ‘2006-12-30 10:59:00’ using backup controlfile
4. apply archives
5. Sql>alter database open resetlogs
6. backup database
Scenario 7
Loss of inactive online redo log
1. Drop inactive online redo log and create a new one.
2. Sql>alter database drop logfile group 3
3. Sql>alter database add logfile group 3 ( ‘/u01/../redo03a.dbf’,’/u02/../redo3b.dbf’) size 5M
Scenario 8
Loss of current online redo log
1. Restore datafiles
2. Startup mount
3. sql>Recover database until cancel
4. Apply archived
5. sql>Alter database open resetlogs
6. Backup database
Scenario 9
Recover a lost datafile with no backup but you have all the archives from datafile creation.
Mount database
Drop datafile
Sql>alter database create datafile ‘/u01/../user02.dbf’
Sql>recover database
Apply all archives
Sql>alter database open;
This document describes some important scenarios for recovering oracle9i database for a specific failure using complete and incomplete recovery
Document contents
==================
Scenario 1
Recovery in NOARCHIVELOG mode with out redo log files backups.
Scenario 2
Loosing users tablespace (because of media failure) and need to recover it in new location.( The database is open)
Scenario 3
Loosing all control files
Scenario 4
Loosing undo tablespace
Scenario 5
Loosing System tablespace
Scenario 6
Recover from user error. The table xyz was dropped at 11:00 am.
Scenario 7
Loss of inactive online redo log
Scenario 8
Loss of current online redo log
Scenario 9
Recover a lost datafile with no backup but you have all the archives from datafile creation.
Tips
====
Before the Recover command verify whether any offile datafiles exist and place then online, because any offline files may be unrecoverable after recovery.
SQL> select * from V$recover_file;
Ensure the log_archive_format to much your archives if not during recovery use the command
Sql>alter system log_archive_format=your_format scope=memory i.e in police enviroment is arch_%s.arc for
Example arch_88909.arc.
To change the archive destination for new location
Sql>alter system archive log start to ‘/backup/archives/day0’
To get a picture of the archives and current redo logs
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 130 **ARCHIVED
Next log sequence to archive 131 **WAIT TO BE ARCHIVED
Current log sequence 131
SQL> select status,archived,sequence# from v$log;
STATUS ARC SEQUENCE#
---------------- --- ----------
CURRENT NO 131
INACTIVE YES 130
Sql>select sequence#,to_char(first_time,'DD-MON HH24:MI') from v$log_history
Or
Sql> select archived,sequence# from v$archived_log order by sequence# desc
Scenario 1
Recovery in NOARCHIVELOG mode with out redo log files backups.
Sql > shutdown immediate;
Restore all datafiles and control files from your backup
Sql> startup mount;
Sql>Recover database until cancel using backup controlfile;
Sql>Alter database open resetlogs;
Scenario 2
Loosing users tablespace (because of media failure) and need to recover it in new location.( The database is open)
SQL> alter tablespace users offline;
Restore from backup into new location
SQL> alter tablespace users
rename datafile 'C:\ORACLE\ORADATA\TARGET01\USERS01.
DBF' to 'C:\ORACLE\ORADATA\TARGET01\disk2\USERS01.DBF';
SQL> recover tablespace users
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter tablespace users online;
Tablespace altered.
Scenario 3
Loosing all control files
If and only if current redo is available
1. create the controlfile from your saved scripts
2. sql>recover database
3. sql>alter system archive log all
4. sql>alter database open ;
If and only if current redo is not available
1. create the controlfile from your saved scripts
2. sql>recover database using backup control file;
3. sql>alter database open resetlogs
Scenario 4
Loosing undo tablespace
1. Restore undotbs1 from backup
2. Mount the database
3. sql>Recover database
4. Apply archives and redo logs
5. sql>Alter database open;
Scenario 5
Loosing System tablespace
*******Use the instruction on step 4
Scenario 6
Recover from user error. The table xyz was dropped at 11:00 am.
1. Sql>shutdown immediate;
2. Sql>Restore datafiles and controlfiles
3. Sql>recover database until time ‘2006-12-30 10:59:00’ using backup controlfile
4. apply archives
5. Sql>alter database open resetlogs
6. backup database
Scenario 7
Loss of inactive online redo log
1. Drop inactive online redo log and create a new one.
2. Sql>alter database drop logfile group 3
3. Sql>alter database add logfile group 3 ( ‘/u01/../redo03a.dbf’,’/u02/../redo3b.dbf’) size 5M
Scenario 8
Loss of current online redo log
1. Restore datafiles
2. Startup mount
3. sql>Recover database until cancel
4. Apply archived
5. sql>Alter database open resetlogs
6. Backup database
Scenario 9
Recover a lost datafile with no backup but you have all the archives from datafile creation.
Mount database
Drop datafile
Sql>alter database create datafile ‘/u01/../user02.dbf’
Sql>recover database
Apply all archives
Sql>alter database open;
External Tables in Oracle 10g
/*********************************************************************
UNLOAD -> Populate external table via normal table
**********************************************************************/
create table ext_product
(
PRODUCTID,
DESCRIPTION,
PRODUCTNAME
)
organization external
(
type oracle_datapump
default directory dir1
location ('product.dat')
)
as
select PRODUCTID,
DESCRIPTION,
PRODUCTNAME
from product;
/*********************************************************************
Create external table base on existing dump file
**********************************************************************/
create table ext_product_2
(
PRODUCTID number(10),
DESCRIPTION varchar2(500),
PRODUCTNAME varchar2(30)
)
organization external
(
type oracle_datapump
default directory dir1
location ('product.dat')
)
/
/*********************************************************************
SQL-LOADER EXAMPLE
**********************************************************************/
create table ext_product_3
(
PRODUCTID number(10),
DESCRIPTION varchar2(500),
PRODUCTNAME varchar2(30)
)
organization external
(
type oracle_loader
default directory dir1
access parameters ( records delimited by newline fields terminated by ',')
location ('product3.dat')
)
reject limit unlimited
/
/*********************************************************************
Projected columns examples
**********************************************************************/
create table pro1
(
ID number(10),
S_ID number(10),
NAME varchar2(30)
)
organization external
(
type oracle_loader
default directory dir1
access parameters ( records delimited by newline fields terminated by ',')
location ('pro1.dat')
)
reject limit unlimited
/
/****** Pro1 data **************
100,1,Hardisk 7200A
101,2,Hardisk 7200B
102,3,Hardisk 7200C
103,11111111111111111111111,Hardisk 7200D
SQL> select * from pro1;
ID S_ID NAME
---------- ---------- ----------------------------
100 1 Hardisk 7200A
101 2 Hardisk 7200B
102 3 Hardisk 7200C
SQL> select count(id) from pro1;
COUNT(ID)
----------
3
SQL> select count(s_id) from pro1;
COUNT(S_ID)
-----------
3
SQL> alter table pro1 project column referenced;
Table altered.
SQL> select count(id) from pro1;
COUNT(ID)
----------
4
SQL> select count(id),name from pro1
2 group by name;
COUNT(ID) NAME
---------- ------------------------------
1 Hardisk 7200B
1 Hardisk 7200A
1 Hardisk 7200C
1 Hardisk 7200D
SQL> select count(s_id) from pro1;
COUNT(S_ID)
-----------
3
SQL>
UNLOAD -> Populate external table via normal table
**********************************************************************/
create table ext_product
(
PRODUCTID,
DESCRIPTION,
PRODUCTNAME
)
organization external
(
type oracle_datapump
default directory dir1
location ('product.dat')
)
as
select PRODUCTID,
DESCRIPTION,
PRODUCTNAME
from product;
/*********************************************************************
Create external table base on existing dump file
**********************************************************************/
create table ext_product_2
(
PRODUCTID number(10),
DESCRIPTION varchar2(500),
PRODUCTNAME varchar2(30)
)
organization external
(
type oracle_datapump
default directory dir1
location ('product.dat')
)
/
/*********************************************************************
SQL-LOADER EXAMPLE
**********************************************************************/
create table ext_product_3
(
PRODUCTID number(10),
DESCRIPTION varchar2(500),
PRODUCTNAME varchar2(30)
)
organization external
(
type oracle_loader
default directory dir1
access parameters ( records delimited by newline fields terminated by ',')
location ('product3.dat')
)
reject limit unlimited
/
/*********************************************************************
Projected columns examples
**********************************************************************/
create table pro1
(
ID number(10),
S_ID number(10),
NAME varchar2(30)
)
organization external
(
type oracle_loader
default directory dir1
access parameters ( records delimited by newline fields terminated by ',')
location ('pro1.dat')
)
reject limit unlimited
/
/****** Pro1 data **************
100,1,Hardisk 7200A
101,2,Hardisk 7200B
102,3,Hardisk 7200C
103,11111111111111111111111,Hardisk 7200D
SQL> select * from pro1;
ID S_ID NAME
---------- ---------- ----------------------------
100 1 Hardisk 7200A
101 2 Hardisk 7200B
102 3 Hardisk 7200C
SQL> select count(id) from pro1;
COUNT(ID)
----------
3
SQL> select count(s_id) from pro1;
COUNT(S_ID)
-----------
3
SQL> alter table pro1 project column referenced;
Table altered.
SQL> select count(id) from pro1;
COUNT(ID)
----------
4
SQL> select count(id),name from pro1
2 group by name;
COUNT(ID) NAME
---------- ------------------------------
1 Hardisk 7200B
1 Hardisk 7200A
1 Hardisk 7200C
1 Hardisk 7200D
SQL> select count(s_id) from pro1;
COUNT(S_ID)
-----------
3
SQL>
Working with Lobs using Java
Prepare Database
=============
conn system/oracle
create directory IMG as 'C:\IMG';
-- I already add the oracle.gif and oraclelogo.gif files inside a c:\IMG
CREATE TABLE lob_table (filename VARCHAR2(128),blobdata BLOB);
CREATE OR REPLACE PROCEDURE LoadFILEIntoBLOB (myfilename IN VARCHAR2) IS
out_blob BLOB;
in_file BFILE := BFILENAME('IMG', myfilename);
blob_length INTEGER;
BEGIN
-- Obtain the size of the blob file
DBMS_LOB.FILEOPEN(in_file, DBMS_LOB.FILE_READONLY);
blob_length:=DBMS_LOB.GETLENGTH(in_file);
DBMS_LOB.FILECLOSE(in_file);
-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to out_blob.
INSERT INTO lob_table VALUES (myfilename, EMPTY_BLOB())
RETURNING blobdata INTO out_blob;
-- Load the image into the database as a BLOB
DBMS_LOB.OPEN(in_file, DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(out_blob, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(out_blob, in_file, blob_length);
-- Close handles to blob and file
DBMS_LOB.CLOSE(out_blob);
DBMS_LOB.CLOSE(in_file);
COMMIT;
END;
/
exec LoadFileIntoBlob('oracle.gif');
--Ensure that picture in Loaded
SELECT filename,DBMS_LOB.GETLENGTH(blobdata)
lob_table;
FILENAME DBMS_LOB.GETLENGTH(BLOBDATA)
------------- ---------------------------------------------
oracle.gif 7868
Get the photo from JSP
<%
ServletOutputStream out1 = response.getOutputStream();
JDBConnection con = new JDBConnection();
Blob photo = null;
String query = " Select blobdata from lob_table ";
String sysdate= "";
try
{
PreparedStatement pst = con.conn.prepareStatement(query);
ResultSet rst = pst.executeQuery();
if (rst.next())
{
photo = rst.getBlob(1);
}
response.setContentType("image/gif");
InputStream in = photo.getBinaryStream();
int length = (int) photo.length();
int bufferSize = 1024;
byte[] buffer = new byte[bufferSize];
while ((length = in.read(buffer)) != -1)
{
out1.write(buffer, 0, length);
}
in.close();
out1.flush();
con.conn.close();
}
catch(SQLException e)
{
throw new Exception(""+e.getMessage());
}
catch(Exception e)
{
throw new Exception(""+e.getMessage());
}
%>
Get the photo from Servlet
package project1;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.IOException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import oracle.jdbc.driver.OracleResultSet;
public class DisplayPhoto extends HttpServlet {
private static final String CONTENT_TYPE = "text/html; charset=windows-1252";
public void init(ServletConfig config) throws ServletException {
super.init(config);
}
public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setContentType(CONTENT_TYPE);
// PrintWriter out = response.getWriter();
ServletOutputStream out = response.getOutputStream();
//out.println("");
//out.println("DisplayPhoto ");
//out.println("");
JDBConnection con = new JDBConnection();
Blob photo = null;
String query = " Select blobdata from lob_table ";
String sysdate= "";
try
{
PreparedStatement pst = con.conn.prepareStatement(query);
ResultSet rst = pst.executeQuery();
if (rst.next())
{
photo = rst.getBlob(1);
}
response.setContentType("image/gif");
InputStream in = photo.getBinaryStream();
int length = (int) photo.length();
int bufferSize = 1024;
byte[] buffer = new byte[bufferSize];
while ((length = in.read(buffer)) != -1)
{
//System.out.println("writing " + length + " bytes");
out.write(buffer, 0, length);
}
in.close();
out.flush();
con.conn.close();
}
catch(SQLException e)
{
throw new ServletException(""+e.getMessage());
}
catch(Exception e)
{
throw new ServletException(""+e.getMessage());
}
//out.println("
//out.println("");
//out.close();
}
}
package project1;
import java.awt.*;
import java.sql.*;
import java.awt.event.*;
import oracle.sql.*;
import oracle.jdbc.*;
import java.lang.*;
public class JDBConnection
{
public Connection conn=null;
public Statement stm=null;
public JDBConnection()
{
try
{
registerDB();
}
catch(Exception e)
{
System.out.println("Error...");
}
}
public void registerDB()throws Exception
{
try
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
conn=DriverManager.getConnection
("jdbc:oracle:thin:@michaelgeorgiou:1521:orcl","system","oracle");
stm = conn.createStatement();
}
catch (SQLException e)
{
throw new Exception("Sql error: "+e.getMessage());
}
}
}
=============
conn system/oracle
create directory IMG as 'C:\IMG';
-- I already add the oracle.gif and oraclelogo.gif files inside a c:\IMG
CREATE TABLE lob_table (filename VARCHAR2(128),blobdata BLOB);
CREATE OR REPLACE PROCEDURE LoadFILEIntoBLOB (myfilename IN VARCHAR2) IS
out_blob BLOB;
in_file BFILE := BFILENAME('IMG', myfilename);
blob_length INTEGER;
BEGIN
-- Obtain the size of the blob file
DBMS_LOB.FILEOPEN(in_file, DBMS_LOB.FILE_READONLY);
blob_length:=DBMS_LOB.GETLENGTH(in_file);
DBMS_LOB.FILECLOSE(in_file);
-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to out_blob.
INSERT INTO lob_table VALUES (myfilename, EMPTY_BLOB())
RETURNING blobdata INTO out_blob;
-- Load the image into the database as a BLOB
DBMS_LOB.OPEN(in_file, DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(out_blob, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(out_blob, in_file, blob_length);
-- Close handles to blob and file
DBMS_LOB.CLOSE(out_blob);
DBMS_LOB.CLOSE(in_file);
COMMIT;
END;
/
exec LoadFileIntoBlob('oracle.gif');
--Ensure that picture in Loaded
SELECT filename,DBMS_LOB.GETLENGTH(blobdata)
lob_table;
FILENAME DBMS_LOB.GETLENGTH(BLOBDATA)
------------- ---------------------------------------------
oracle.gif 7868
Get the photo from JSP
<%
ServletOutputStream out1 = response.getOutputStream();
JDBConnection con = new JDBConnection();
Blob photo = null;
String query = " Select blobdata from lob_table ";
String sysdate= "";
try
{
PreparedStatement pst = con.conn.prepareStatement(query);
ResultSet rst = pst.executeQuery();
if (rst.next())
{
photo = rst.getBlob(1);
}
response.setContentType("image/gif");
InputStream in = photo.getBinaryStream();
int length = (int) photo.length();
int bufferSize = 1024;
byte[] buffer = new byte[bufferSize];
while ((length = in.read(buffer)) != -1)
{
out1.write(buffer, 0, length);
}
in.close();
out1.flush();
con.conn.close();
}
catch(SQLException e)
{
throw new Exception(""+e.getMessage());
}
catch(Exception e)
{
throw new Exception(""+e.getMessage());
}
%>
Get the photo from Servlet
package project1;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.IOException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import oracle.jdbc.driver.OracleResultSet;
public class DisplayPhoto extends HttpServlet {
private static final String CONTENT_TYPE = "text/html; charset=windows-1252";
public void init(ServletConfig config) throws ServletException {
super.init(config);
}
public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setContentType(CONTENT_TYPE);
// PrintWriter out = response.getWriter();
ServletOutputStream out = response.getOutputStream();
//out.println("");
//out.println("
//out.println("");
JDBConnection con = new JDBConnection();
Blob photo = null;
String query = " Select blobdata from lob_table ";
String sysdate= "";
try
{
PreparedStatement pst = con.conn.prepareStatement(query);
ResultSet rst = pst.executeQuery();
if (rst.next())
{
photo = rst.getBlob(1);
}
response.setContentType("image/gif");
InputStream in = photo.getBinaryStream();
int length = (int) photo.length();
int bufferSize = 1024;
byte[] buffer = new byte[bufferSize];
while ((length = in.read(buffer)) != -1)
{
//System.out.println("writing " + length + " bytes");
out.write(buffer, 0, length);
}
in.close();
out.flush();
con.conn.close();
}
catch(SQLException e)
{
throw new ServletException(""+e.getMessage());
}
catch(Exception e)
{
throw new ServletException(""+e.getMessage());
}
//out.println("
Date: "+sysdate+"
");//out.println("");
//out.close();
}
}
package project1;
import java.awt.*;
import java.sql.*;
import java.awt.event.*;
import oracle.sql.*;
import oracle.jdbc.*;
import java.lang.*;
public class JDBConnection
{
public Connection conn=null;
public Statement stm=null;
public JDBConnection()
{
try
{
registerDB();
}
catch(Exception e)
{
System.out.println("Error...");
}
}
public void registerDB()throws Exception
{
try
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
conn=DriverManager.getConnection
("jdbc:oracle:thin:@michaelgeorgiou:1521:orcl","system","oracle");
stm = conn.createStatement();
}
catch (SQLException e)
{
throw new Exception("Sql error: "+e.getMessage());
}
}
}
Configure RMAN repository
Oracle Version 10.2.0.1
Tested on Windows XP
Create catalog mandatory steps:
1. Create tablespace
2. Create owner
3. Grant privileges
4. Create Catalog
5. Connect to the database
6. Register the database
Step2
=====
create user rman identified by rman
default tablespace RMAN
quota unlimited on RMAN;
Step3
=====
grant recovery_catalog_owner to rman;
grant connect, resource to rman;
Step4
=====
C:\>rman catalog rman/rman@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 8 12:40:03 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> create catalog tablespace "RMAN";
recovery catalog created
C:\>rman target sys/oracle@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 8 14:49:10 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1144457296)
RMAN> connect catalog rman/rman@rman
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Tested on Windows XP
Create catalog mandatory steps:
1. Create tablespace
2. Create owner
3. Grant privileges
4. Create Catalog
5. Connect to the database
6. Register the database
Step2
=====
create user rman identified by rman
default tablespace RMAN
quota unlimited on RMAN;
Step3
=====
grant recovery_catalog_owner to rman;
grant connect, resource to rman;
Step4
=====
C:\>rman catalog rman/rman@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 8 12:40:03 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> create catalog tablespace "RMAN";
recovery catalog created
C:\>rman target sys/oracle@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 8 14:49:10 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1144457296)
RMAN> connect catalog rman/rman@rman
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Create Oracle Database manul on Linux
First we need to create the init.ora and password file.
File: initINST_DB10G.ora
====================
sga_target = 300M
log_buffer = 1048576
pga_aggregate_target=15M
remote_login_passwordfile='EXCLUSIVE'
control_files = '/home/oracle/DB10G/control01.ctl'
db_block_size = 8192
db_name = DB10G
instance_name = INST_DB10G
db_domain = domain.local
background_dump_dest = '/home/oracle/DB10G/bdump'
user_dump_dest = '/home/oracle/DB10G/udump'
core_dump_dest = '/home/oracle/DB10G/bdump'
log_archive_dest_1 = 'location=/home/oracle/DB10G/arch'
log_archive_format = %t_%s_%r.dbf
log_archive_max_processes = 2
processes = 100
sessions = 115
open_cursors = 500
transactions = 132
db_writer_processes = 2
db_file_multiblock_read_count = 32
db_recovery_file_dest = '/home/oracle/DB10G/flash_recovery_area'
db_recovery_file_dest_size=3G
undo_managment=auto
undo_tablespace=UNDO1
File:orapwINST_DB10G.ora
=====================
Create the file using the orapwd command as follows:
orapwd file=orapwINST_DB10G password=oracle entries=3;
Start the instance
[oracle@server4 dbs]$ORACLE_SID=INST_DB10G
[oracle@server4 dbs]$export ORACLE_SID
[oracle@server4 dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 22 12:18:19 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219160 bytes
Variable Size 96470440 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
CREATE DATABASE DB10G
MAXDATAFILES 100
MAXLOGFILES 6
MAXLOGHISTORY 100
MAXINSTANCES 1
ARCHIVELOG
LOGFILE
GROUP 1 ('/home/oracle/DB10G/redo01.dbf') size 10M,
GROUP 2 ('/home/oracle/DB10G/redo02.dbf') size 10M
DATAFILE '/home/oracle/DB10G/system01.dbf' size 512M AUTOEXTEND ON MAXSIZE UNLIMITED
SYSAUX DATAFILE '/home/oracle/DB10G/sysaux01.dbf' size 512M AUTOEXTEND ON MAXSIZE 1024M
DEFAULT TEMPORARY TABLESPACE TEMP tempfile '/home/oracle/DB10G/temp01.tmp' size 128M AUTOEXTEND OFF
UNDO TABLESPACE UNDO1 DATAFILE '/home/oracle/DB10G/undo101.dbf' size 128M AUTOEXTEND ON
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8;
Create database Dictionary
As sys
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc
@?/rdbms/admin/utlrp
connect system/manager
@?/sqlplus/admin/pupbld
Enabling Flashback Database
startup mount
alter database flashback on;
alter system set db_flashback_retention_target=60 scope=memory; --1 hour flashback
Install Enterprise manager
[oracle@server4 install]$ emca -config dbcontrol db -repos create
STARTED EMCA at Mar 23, 2007 10:36:44 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: INST_DB10G
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Install java
@?/javavm/install/initjvm.sql
Install XDB
@?/rdbms/admin/catqm.sql change_on_install XDB TEMP
@?/rdbms/admin/catxdbj.sql
create bigfile tablespace EXAMPLE
datafile '/home/oracle/DB10G/example.dbf' size 512M
autoextend on maxsize unlimited
segment space management auto;
SQL> select comp_name from dba_registry;
COMP_NAME
------------------------------------------------
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle Enterprise Manager
Oracle XML Database
File: initINST_DB10G.ora
====================
sga_target = 300M
log_buffer = 1048576
pga_aggregate_target=15M
remote_login_passwordfile='EXCLUSIVE'
control_files = '/home/oracle/DB10G/control01.ctl'
db_block_size = 8192
db_name = DB10G
instance_name = INST_DB10G
db_domain = domain.local
background_dump_dest = '/home/oracle/DB10G/bdump'
user_dump_dest = '/home/oracle/DB10G/udump'
core_dump_dest = '/home/oracle/DB10G/bdump'
log_archive_dest_1 = 'location=/home/oracle/DB10G/arch'
log_archive_format = %t_%s_%r.dbf
log_archive_max_processes = 2
processes = 100
sessions = 115
open_cursors = 500
transactions = 132
db_writer_processes = 2
db_file_multiblock_read_count = 32
db_recovery_file_dest = '/home/oracle/DB10G/flash_recovery_area'
db_recovery_file_dest_size=3G
undo_managment=auto
undo_tablespace=UNDO1
File:orapwINST_DB10G.ora
=====================
Create the file using the orapwd command as follows:
orapwd file=orapwINST_DB10G password=oracle entries=3;
Start the instance
[oracle@server4 dbs]$ORACLE_SID=INST_DB10G
[oracle@server4 dbs]$export ORACLE_SID
[oracle@server4 dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 22 12:18:19 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219160 bytes
Variable Size 96470440 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
CREATE DATABASE DB10G
MAXDATAFILES 100
MAXLOGFILES 6
MAXLOGHISTORY 100
MAXINSTANCES 1
ARCHIVELOG
LOGFILE
GROUP 1 ('/home/oracle/DB10G/redo01.dbf') size 10M,
GROUP 2 ('/home/oracle/DB10G/redo02.dbf') size 10M
DATAFILE '/home/oracle/DB10G/system01.dbf' size 512M AUTOEXTEND ON MAXSIZE UNLIMITED
SYSAUX DATAFILE '/home/oracle/DB10G/sysaux01.dbf' size 512M AUTOEXTEND ON MAXSIZE 1024M
DEFAULT TEMPORARY TABLESPACE TEMP tempfile '/home/oracle/DB10G/temp01.tmp' size 128M AUTOEXTEND OFF
UNDO TABLESPACE UNDO1 DATAFILE '/home/oracle/DB10G/undo101.dbf' size 128M AUTOEXTEND ON
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8;
Create database Dictionary
As sys
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc
@?/rdbms/admin/utlrp
connect system/manager
@?/sqlplus/admin/pupbld
Enabling Flashback Database
startup mount
alter database flashback on;
alter system set db_flashback_retention_target=60 scope=memory; --1 hour flashback
Install Enterprise manager
[oracle@server4 install]$ emca -config dbcontrol db -repos create
STARTED EMCA at Mar 23, 2007 10:36:44 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: INST_DB10G
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Install java
@?/javavm/install/initjvm.sql
Install XDB
@?/rdbms/admin/catqm.sql change_on_install XDB TEMP
@?/rdbms/admin/catxdbj.sql
create bigfile tablespace EXAMPLE
datafile '/home/oracle/DB10G/example.dbf' size 512M
autoextend on maxsize unlimited
segment space management auto;
SQL> select comp_name from dba_registry;
COMP_NAME
------------------------------------------------
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle Enterprise Manager
Oracle XML Database
Subscribe to:
Posts (Atom)