http://updates.oracle.com/download/2617419.html
http://updates.oracle.com/download/4898608.html
Monday, December 17, 2007
Tuesday, October 9, 2007
Using the DBMS_METADATA to extract DLL from table and Index
SQL> set long 4000
SQL> select dbms_metadata.get_DDL( 'TABLE','EMP','HR') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','HR')
-----------------------------------------------------------------------------
CREATE TABLE "HR"."EMP"
( "ID" NUMBER(10,0),
"NAME" VARCHAR2(30),
"EMAIL" VARCHAR2(100),
"SALARY" NUMBER(10,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
SQL> select dbms_metadata.get_DDL( 'INDEX','EMP_EMP_ID_PK','HR') from dual;
DBMS_METADATA.GET_DDL('INDEX','EMP_EMP_ID_PK','HR')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")
SQL> select dbms_metadata.get_DDL( 'TABLE','EMP','HR') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','HR')
-----------------------------------------------------------------------------
CREATE TABLE "HR"."EMP"
( "ID" NUMBER(10,0),
"NAME" VARCHAR2(30),
"EMAIL" VARCHAR2(100),
"SALARY" NUMBER(10,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
SQL> select dbms_metadata.get_DDL( 'INDEX','EMP_EMP_ID_PK','HR') from dual;
DBMS_METADATA.GET_DDL('INDEX','EMP_EMP_ID_PK','HR')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")
Tuesday, October 2, 2007
Add second listener on a database in different port
Document Scope: This document describes how to create and add a second listener
Document Type: How to
Last Update: 14-11-2006
References: http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14212/listenercfg.htm#sthref946
Metalink doc:
Created: 14-11-2006
Database: 10.2.0.1
Platform: Solaris SPARC 64Bit
Application Server: none
Author: Michael Georgiou
Comments: Also covers and dbconsole issues , because the dbconsole is configure to listener to LISTENER on port 1521.
=================================================================================
Contents
I need a second listener because the node that I worked has two oracle software one for oracle9i and for oracle10g. I leave the listener LISTENER for the oracle9i software and I create a new one LISTERNER10G for oracle10g software.
I create the second listener via netca and I statically configure the database sid via netmgr .After that I made the following changes in init.ora and tnsnames.ora file.
tnsnames.ora
LISTENER10G=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.40.250)(PORT=1526)))
alter system set LOCAL_LISTENER= LISTENER10G scope=both;
Some very importand database parameters are the following.
service_names=DB_SISII
db_name=DB_SISII
instance_name=DBSISII
Note that when I create the database I set database name as DB_SISII and SID as DBSISII
Lsnrctl>set current_listener LISTENER10G
Lsnrctl>services
Service "DB_SISII" has 2 instance(s).
Instance "DBSISII", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "DBSISII", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
I can access the database via the following tnsnames.ora entry
DBSISII =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.250)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = DB_SISII)
)
)
Some issues exist for database console and may be able to be solved by:
/home/oracle10g/product/10.2.0/netudevsrv1_DBSISII/sysman/emd
Edit the target.xml
/home/oracle10g/product/10.2.0/netudevsrv1_DBSISII/sysman/config
Edit the following line in emoms.properties
oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=netudevsrv1)(PORT
\=1526)))(CONNECT_DATA\=(SERVICE_NAME\=DB_SISII)))
emctl stop dbconsole
emctl start dbconsole
Document Type: How to
Last Update: 14-11-2006
References: http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14212/listenercfg.htm#sthref946
Metalink doc:
Created: 14-11-2006
Database: 10.2.0.1
Platform: Solaris SPARC 64Bit
Application Server: none
Author: Michael Georgiou
Comments: Also covers and dbconsole issues , because the dbconsole is configure to listener to LISTENER on port 1521.
=================================================================================
Contents
I need a second listener because the node that I worked has two oracle software one for oracle9i and for oracle10g. I leave the listener LISTENER for the oracle9i software and I create a new one LISTERNER10G for oracle10g software.
I create the second listener via netca and I statically configure the database sid via netmgr .After that I made the following changes in init
tnsnames.ora
LISTENER10G=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.40.250)(PORT=1526)))
alter system set LOCAL_LISTENER= LISTENER10G scope=both;
Some very importand database parameters are the following.
service_names=DB_SISII
db_name=DB_SISII
instance_name=DBSISII
Note that when I create the database I set database name as DB_SISII and SID as DBSISII
Lsnrctl>set current_listener LISTENER10G
Lsnrctl>services
Service "DB_SISII" has 2 instance(s).
Instance "DBSISII", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "DBSISII", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
I can access the database via the following tnsnames.ora entry
DBSISII =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.250)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = DB_SISII)
)
)
Some issues exist for database console and may be able to be solved by:
/home/oracle10g/product/10.2.0/netudevsrv1_DBSISII/sysman/emd
Edit the target.xml
/home/oracle10g/product/10.2.0/netudevsrv1_DBSISII/sysman/config
Edit the following line in emoms.properties
oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=netudevsrv1)(PORT
\=1526)))(CONNECT_DATA\=(SERVICE_NAME\=DB_SISII)))
emctl stop dbconsole
emctl start dbconsole
Tuesday, September 18, 2007
Connect to a remote database using the TWO_TASK enviroment
Some programs , for example COBOL , access the database in the same machine
using the enviroment variable ORACLE_SID. What happen if the COBOL program uses a
users that is authenticate by OS , and these programs access local database as described in users's profile enviromrnt which is the ORACLE_SID.How can you tell that the database is not in the same machine , but in a remote machine .The TWO_TASK enviroment variable helps as to achive this .
Add the following lines in your tnsnames.ora
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = remotedb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
I am login in my unix client machine , and i will set the TWO_TASK env as follows:
$ TWO_TASK=orcl
$ export TWO_TASK
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 18 09:58:03 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/xyxz as sysdba
Connected.
SQL> select name from v$database;
NAME
---------
ORCL
The ORCL database is my remote database.
Reference
=============
http://mennan.kagitkalem.com/ORACLESIDAndTWOTASKEnvironmentVariablesForUnixToConnectOracle.aspx
using the enviroment variable ORACLE_SID. What happen if the COBOL program uses a
users that is authenticate by OS , and these programs access local database as described in users's profile enviromrnt which is the ORACLE_SID.How can you tell that the database is not in the same machine , but in a remote machine .The TWO_TASK enviroment variable helps as to achive this .
Add the following lines in your tnsnames.ora
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = remotedb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
I am login in my unix client machine , and i will set the TWO_TASK env as follows:
$ TWO_TASK=orcl
$ export TWO_TASK
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 18 09:58:03 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/xyxz as sysdba
Connected.
SQL> select name from v$database;
NAME
---------
ORCL
The ORCL database is my remote database.
Reference
=============
http://mennan.kagitkalem.com/ORACLESIDAndTWOTASKEnvironmentVariablesForUnixToConnectOracle.aspx
Friday, August 24, 2007
Recover Database using RMAN
I use the following script to backup my database , with out catalog
Backup
======
run
{
allocate channel ch1 type disk;
backup incremental level 0 as backupset tag=full_backup (database);
sql 'alter system archive log current';
release channel ch1;
allocate channel ch1 type disk;
backup archivelog all tag=archives_backup
delete all input;
release channel ch1;
}
delete noprompt obsolete;
restore validate database;
Restore
=======
RMAN>shutdown immediate
RAMN>connect target sys/oracle
RMAN>startup mouunt
RMAN>
RUN
{
allocate channel ch1 type disk;
sql "alter session set nls_date_format=''MM-DD-YYYY HH24:MI:SS''";
set until time '08-24-2007 10:47:00';
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
release channel ch1;
}
Backup
======
run
{
allocate channel ch1 type disk;
backup incremental level 0 as backupset tag=full_backup (database);
sql 'alter system archive log current';
release channel ch1;
allocate channel ch1 type disk;
backup archivelog all tag=archives_backup
delete all input;
release channel ch1;
}
delete noprompt obsolete;
restore validate database;
Restore
=======
RMAN>shutdown immediate
RAMN>connect target sys/oracle
RMAN>startup mouunt
RMAN>
RUN
{
allocate channel ch1 type disk;
sql "alter session set nls_date_format=''MM-DD-YYYY HH24:MI:SS''";
set until time '08-24-2007 10:47:00';
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
release channel ch1;
}
Tuesday, August 21, 2007
Install Oracle 11g on Redhat Linux 5 ( kernel 2.6.18 )
Redhat installation
====================
During the installation I choose the installation for "Developer"
I used 2.5G of swap space.
After the installation create the oracle user , and assign to it the dba group
As root installed the package below ( the --installed flag indicate that the package is alredy , installed during the "development installation"
Packages for Red Hat Enterprise Linux 5.0
binutils-2.17.50.0.6-2.el5 --installed
compat-libstdc++-33-3.2.3-61 --2
elfutils-libelf-0.125-3.el5 --installed
elfutils-libelf-devel-0.125 --2
glibc-2.5-12 --installed
glibc-common-2.5-12 --installed
glibc-devel-2.5-12 --installed
gcc-4.1.1-52 --installed
gcc-c++-4.1.1-52 --installed
libaio-0.3.106 ---installed
libaio-devel-0.3.106 -- installed
libgcc-4.1.1-52 --installed
libstdc++-4.1.1 --installed
libstdc++-devel-4.1.1-52.e15 --2
make-3.81-1.1 --installed
sysstat-7.0.0 --3
unixODBC-2.2.11 --2
unixODBC-devel-2.2.11 --2
edit the /etc/sysctl.conf file
fs.file-max = 512 * PROCESSES
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
Add the following lines to the /etc/security/limits.conf file:
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:
session required /lib/security/pam_limits.so
session required pam_limits.so
Restart the server , and run the runinstaller, the rest it is like in 10g expect some , security issue.
Ensure that the SElinux is disable, or it setup correctly.
For futher info , just send email askMichaelgeorgiou@gmail.com
References:Oracle 11g Installation Guide, for Linux
Setup estimation including redhat installation about 1hour
====================
During the installation I choose the installation for "Developer"
I used 2.5G of swap space.
After the installation create the oracle user , and assign to it the dba group
As root installed the package below ( the --installed flag indicate that the package is alredy , installed during the "development installation"
Packages for Red Hat Enterprise Linux 5.0
binutils-2.17.50.0.6-2.el5 --installed
compat-libstdc++-33-3.2.3-61 --2
elfutils-libelf-0.125-3.el5 --installed
elfutils-libelf-devel-0.125 --2
glibc-2.5-12 --installed
glibc-common-2.5-12 --installed
glibc-devel-2.5-12 --installed
gcc-4.1.1-52 --installed
gcc-c++-4.1.1-52 --installed
libaio-0.3.106 ---installed
libaio-devel-0.3.106 -- installed
libgcc-4.1.1-52 --installed
libstdc++-4.1.1 --installed
libstdc++-devel-4.1.1-52.e15 --2
make-3.81-1.1 --installed
sysstat-7.0.0 --3
unixODBC-2.2.11 --2
unixODBC-devel-2.2.11 --2
edit the /etc/sysctl.conf file
fs.file-max = 512 * PROCESSES
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
Add the following lines to the /etc/security/limits.conf file:
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:
session required /lib/security/pam_limits.so
session required pam_limits.so
Restart the server , and run the runinstaller, the rest it is like in 10g expect some , security issue.
Ensure that the SElinux is disable, or it setup correctly.
For futher info , just send email askMichaelgeorgiou@gmail.com
References:Oracle 11g Installation Guide, for Linux
Setup estimation including redhat installation about 1hour
Wednesday, June 27, 2007
Manage Oracle Report Server ( 10.1.2.0.2 Windows)
The scenario , assume you have 2 report servers on host1 and host2 on windows 2003
To see he status of the application server , Login as remote desktop on Host[1..2], open a CMD terminal and
write the following command
opmnctl status
dcm-daemon | dcm-daemon | 3012 | Alive
HTTP_Server | HTTP_Server | 4660 | Alive
WebCache | WebCache | 1440 | Alive
WebCache | WebCacheAdmin | 2968 | Alive
OC4J | home | 1464 | Alive
OC4J | OC4J_BI_Forms | 4428 | Alive
OC4J | OC4J_BI_Forms | 2260 | Alive
reportServer2 | ReportsServer | 4720 | Alive
ALIVE for all the components
To see the status and availability of report servers ,open a CMD terminal and
write the following command
rwdiag -findall
Broadcast mechanism used to locate servers
------------------------------------------
Channel address = 228.5.6.7
Channel port = 14021
(1) Name = reportserver2 : Type = server : Host = host1.domain.local
(2) Name = reportserver1 : Type = server : Host = host2.domain.local
If the above is TRUE and some report server is not working open a CMD terminal and
write the following command ( The example below restart the reportServer2)
opmnctl stopproc ias-component=reportServer2 process-type=ReportsServer
opmnctl startproc ias-component=reportServer2 process-type=ReportsServer
To restart the application server components open a CMD terminal and write the following commands with the following order:
dcmctl stop
opmnctl stopall
dcmctl start
opmnctl startall
Verify if working
opmnctl status
rwdiag -findall
In order to see the reports' server job use the following URL for each server
http://host1.domain.local/reports/rwservlet/showjobs?server=reportServer2
http://host2.domain.local/reports/rwservlet/showjobs?server=reportServer1
*
To see he status of the application server , Login as remote desktop on Host[1..2], open a CMD terminal and
write the following command
opmnctl status
dcm-daemon | dcm-daemon | 3012 | Alive
HTTP_Server | HTTP_Server | 4660 | Alive
WebCache | WebCache | 1440 | Alive
WebCache | WebCacheAdmin | 2968 | Alive
OC4J | home | 1464 | Alive
OC4J | OC4J_BI_Forms | 4428 | Alive
OC4J | OC4J_BI_Forms | 2260 | Alive
reportServer2 | ReportsServer | 4720 | Alive
ALIVE for all the components
To see the status and availability of report servers ,open a CMD terminal and
write the following command
rwdiag -findall
Broadcast mechanism used to locate servers
------------------------------------------
Channel address = 228.5.6.7
Channel port = 14021
(1) Name = reportserver2 : Type = server : Host = host1.domain.local
(2) Name = reportserver1 : Type = server : Host = host2.domain.local
If the above is TRUE and some report server is not working open a CMD terminal and
write the following command ( The example below restart the reportServer2)
opmnctl stopproc ias-component=reportServer2 process-type=ReportsServer
opmnctl startproc ias-component=reportServer2 process-type=ReportsServer
To restart the application server components open a CMD terminal and write the following commands with the following order:
dcmctl stop
opmnctl stopall
dcmctl start
opmnctl startall
Verify if working
opmnctl status
rwdiag -findall
In order to see the reports' server job use the following URL for each server
http://host1.domain.local/reports/rwservlet/showjobs?server=reportServer2
http://host2.domain.local/reports/rwservlet/showjobs?server=reportServer1
*
Sunday, May 20, 2007
RMAN basics
RMAN backup and Recovery (Basics)
=================================
This doc aims to provide a basic solutions for the following targets :
1. Make an incremental 1 database backup using a tag
2. Backup control files and spfile.
3. Checks backups
4. Restore the control file and spfile
5. Restore a database at a point in time
6. Clonning a database from RMAN
7. Backup RMAN catalog
8. Rman commands
First learn how to connect
=================================
C:\>rman target / catalog rman/rman@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 19 22:49:16 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1150787593)
connected to recovery catalog database
Make an incremental 1 database backup using a tag
===========================================
C:\>set oracle_sid=orcl
C:\rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 14 12:28:56 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
Register the database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Backup database
resync catalog;
run
{
allocate channel ch1 type disk;
backup incremental level 1 as backupset tag=orcl_L1 (database);
sql 'alter system archive log current';
release channel ch1;
allocate channel ch1 type disk;
backup
archivelog all
delete all input;
release channel ch1;
}
Backup Control file and Spfile
===========================================
You can enable the auto backup feature , that auto backup control files and spfiles . These files are included in the datafile’s backup set.
Rman>configure auto backup on
When you list your backup set you can see
…
Control File Included: Ckp SCN: 147650 Ckp time: 19-MAY-07
SPFILE Included: Modification time: 19-MAY-07
….
Check your backup
===========================================
The following command you can help to monitor your backup
RMAN>backup validate batabase;
The above command check the data file’s location , and its physical and logical corruption status. If some error occurred the view V$database_block_corruption is updated by RMAN.
RMAN>List backup [ tag=your tag name]
RMAN>List backupset;
RMAN>List copy;
RMAN>List backup summary;
RMAN>crosscheck backup;
Example:
RMAN> List backupset tag=orcl_l1;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1410 Incr 1 56.00K DISK 00:00:25 19-MAY-07
BP Key: 1414 Status: AVAILABLE Compressed: NO Tag: ORCL_L1
Piece Name: C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_19\O1_MF_NNND1_ORCL_L1_34Y49O4D_.BKP
List of Datafiles in backup set 1410
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\SYSTEM01.DBF
2 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\UNDOTBS01.DBF
3 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\SYSAUX01.DBF
4 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\USERS01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1411 Incr 1 6.80M DISK 00:00:03 19-MAY-07
BP Key: 1415 Status: AVAILABLE Compressed: NO Tag: ORCL_L1
Piece Name: C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_19\O1_MF_NCSN1_ORCL_L1_34Y4BJHN_.BKP
Control File Included: Ckp SCN: 147650 Ckp time: 19-MAY-07
SPFILE Included: Modification time: 19-MAY-07
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1486 Incr 1 6.44M DISK 00:00:27 19-MAY-07
BP Key: 1489 Status: AVAILABLE Compressed: NO Tag: ORCL_L1
Piece Name: C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_19\O1_MF_NNND1_ORCL_L1_34YKTZTC_.BKP
List of Datafiles in backup set 1486
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\SYSTEM01.DBF
2 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\UNDOTBS01.DBF
3 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\SYSAUX01.DBF
4 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\USERS01.DBF
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1369 B 1 A DISK 19-MAY-07 1 1 NO TAG20070519T175846
1370 B 1 A DISK 19-MAY-07 1 1 NO TAG20070519T175846
1394 B A A DISK 19-MAY-07 1 1 NO TAG20070519T180010
1410 B 1 A DISK 19-MAY-07 1 1 NO ORCL_L1
1411 B 1 A DISK 19-MAY-07 1 1 NO ORCL_L1
1443 B A A DISK 19-MAY-07 1 1 NO TAG20070519T180148
1486 B 1 A DISK 19-MAY-07 1 1 NO ORCL_L1
1499 B F A DISK 19-MAY-07 1 1 NO TAG20070519T215253
1534 B A A DISK 19-MAY-07 1 1 NO TAG20070519T215309
1547 B F A DISK 19-MAY-07 1 1 NO TAG20070519T215314
Restore spfile and control files
===========================================
Restore spfile
C:\>rman target / catalog rman/rman@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 19 23:12:49 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> startup
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\MY_DATABASE_1023\DATABASE\INITORCL.ORA'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/19/2007 23:13:06
ORA-01103: database name 'ORCL' in control file is not 'DUMMY'
RMAN> set dbid 1150787593
executing command: SET DBID
database name is "ORCL" and DBID is 1150787593
RMAN> restore spfile;
Starting restore at 19-MAY-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=C:\MY_DATABASE_1023\DATABASE\SPFILEORCL.ORA
channel ORA_DISK_1: reading from backup piece C:\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2007_05_19\O1_MF_S_623026
4YOKL8M_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2007_05_19\O1_MF_S_623026528_34YOKL8M_.BKP tag=TAG20070519
28
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 19-MAY-07
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 167772160 bytes
Fixed Size 1246852 bytes
Variable Size 62916988 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
Restore control files
RMAN> startup
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/19/2007 23:19:18
ORA-00205: error in identifying control file, check alert log for more info
RMAN> set dbid 1150787593;
executing command: SET DBID
database name is "ORCL" and DBID is 1150787593
RMAN> restore controlfile;
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
RMAN> recover database;
Starting recover at 19-MAY-07
Starting implicit crosscheck backup at 19-MAY-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 13 objects
Finished implicit crosscheck backup at 19-MAY-07
Starting implicit crosscheck copy at 19-MAY-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 19-MAY-07
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: C:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2007_05_19\O1_MF_1_90_34YOKWQ8_.ARC
File Name: C:\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2007_05_19\O1_MF_S_623026528_34YOKL8M_.BKP
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 90 is already on disk as file C:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2007_05
_1_90_34YOKWQ8_.ARC
archive log thread 1 sequence 91 is already on disk as file C:\ORADATA\ORCL\REDO01.LOG
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2007_05_19\O1_MF_1_90_34YOKWQ8_.ARC thread=1 s
0
archive log filename=C:\ORADATA\ORCL\REDO01.LOG thread=1 sequence=91
media recovery complete, elapsed time: 00:00:02
Finished recover at 19-MAY-07
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1281 1282 ORCL 1150787593 PARENT 1 19-MAY-07
1281 2410 ORCL 1150787593 CURRENT 153553 19-MAY-07
Cloning database with RMAN
===========================================
Terms
Source database: The original database
Auxiliary database: The new database ( after cloning)
Steps
Create the password and init files
Set you listener to manual register both source and auxiliary database
Create a tns entry in tnsnames.ora for the auxiliary database
Test the tns entry
Backup ( LEVEL 0 ) the source database
Open nomount the auxiliary database and EXIT the session
Connect to RMAN using the auxiliary database
Clone the database
Step1
=====
C:\>oradim -NEW -SID ORCL2 -STARTMODE AUTO -SHUTMODE IMMEDIATE
Instance created.
C:\>orapwd file=C:\My_Database_1023\database\pwdorcl2.ora password=oracle entries=5
Step 2
======
C:\>tnsping orcl2
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-MAY-2007 00:30:50
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\My_Database_1023\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = michaelgeorgiou.domain.local)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl2)))
OK (130 msec)
Step 5
=======
resync catalog;
run
{
allocate channel ch1 type disk;
backup as backupset tag=orcl_L1 (database);
sql 'alter system archive log current';
release channel ch1;
allocate channel ch1 type disk;
backup
archivelog all
delete all input;
release channel ch1;
}
Step 7
=======
set ORACLE_SID=ORCL –-your target or source database
rman target / nocatalog auxiliary sys/oracle@orcl2 –orcl2 is the aux db
Step 8
=======
run
{
allocate auxiliary channel aux1 type disk;
set newname for datafile 1 to 'C:\oradata\ORCL2\SYSTEM01.DBF';
set newname for datafile 2 to 'C:\oradata\ORCL2\UNDOTBS01.DBF';
set newname for datafile 3 to 'C:\oradata\ORCL2\SYSAUX01.DBF';
set newname for datafile 4 to 'C:\oradata\ORCL2\USERS01.DBF';
duplicate target database to 'ORCL2' NOFILENAMECHECK;
}
RMAN responses as follows:
RMAN> run
2> {
3> allocate auxiliary channel aux1 type disk;
4> set newname for datafile 1 to 'C:\oradata\ORCL2\SYSTEM01.DBF';
5> set newname for datafile 2 to 'C:\oradata\ORCL2\UNDOTBS01.DBF';
6> set newname for datafile 3 to 'C:\oradata\ORCL2\SYSAUX01.DBF';
7> set newname for datafile 4 to 'C:\oradata\ORCL2\USERS01.DBF';
8> duplicate target database to 'ORCL2' NOFILENAMECHECK;
9> }
allocated channel: aux1
channel aux1: sid=155 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 20-MAY-07
contents of Memory Script:
{
set until scn 188601;
set newname for datafile 1 to
"C:\ORADATA\ORCL2\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\ORADATA\ORCL2\UNDOTBS01.DBF";
set newname for datafile 3 to
"C:\ORADATA\ORCL2\SYSAUX01.DBF";
set newname for datafile 4 to
"C:\ORADATA\ORCL2\USERS01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-MAY-07
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORADATA\ORCL2\SYSTEM01.DBF
restoring datafile 00002 to C:\ORADATA\ORCL2\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORADATA\ORCL2\SYSAUX01.DBF
restoring datafile 00004 to C:\ORADATA\ORCL2\USERS01.DBF
channel aux1: reading from backup piece C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_NNNDF_ORCL_L1_35
HN_.BKP
channel aux1: restored backup piece 1
piece handle=C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_NNNDF_ORCL_L1_3515NZHN_.BKP tag=ORCL_L1
channel aux1: restore complete, elapsed time: 00:00:55
Finished restore at 20-MAY-07
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 5 M ,
GROUP 2 SIZE 5 M ,
GROUP 3 SIZE 5 M
DATAFILE
'C:\ORADATA\ORCL2\SYSTEM01.DBF'
CHARACTER SET UTF8
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=623108765 filename=C:\ORADATA\ORCL2\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=623108765 filename=C:\ORADATA\ORCL2\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=623108765 filename=C:\ORADATA\ORCL2\USERS01.DBF
contents of Memory Script:
{
set until scn 188601;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 20-MAY-07
starting media recovery
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=14
channel aux1: restoring archive log
archive log thread=1 sequence=15
channel aux1: reading from backup piece C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_ANNNN_TAG2007052
4354_3515QDDP_.BKP
channel aux1: restored backup piece 1
piece handle=C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_ANNNN_TAG20070520T214354_3515QDDP_.BKP tag=
0070520T214354
channel aux1: restore complete, elapsed time: 00:00:03
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_14_3515VMG5_.ARC thread=1 seque
14
channel clone_default: deleting archive log(s)
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_14_3515VMG5_.ARC recid=2 stamp=
08772
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_15_3515VMM1_.ARC thread=1 seque
15
channel clone_default: deleting archive log(s)
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_15_3515VMM1_.ARC recid=1 stamp=
08771
media recovery complete, elapsed time: 00:00:02
Finished recover at 20-MAY-07
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1246852 bytes
Variable Size 62916988 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 5 M ,
GROUP 2 SIZE 5 M ,
GROUP 3 SIZE 5 M
DATAFILE
'C:\ORADATA\ORCL2\SYSTEM01.DBF'
CHARACTER SET UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"C:\ORADATA\ORCL\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "C:\ORADATA\ORCL2\UNDOTBS01.DBF";
catalog clone datafilecopy "C:\ORADATA\ORCL2\SYSAUX01.DBF";
catalog clone datafilecopy "C:\ORADATA\ORCL2\USERS01.DBF";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to C:\ORADATA\ORCL\TEMP01.DBF in control file
cataloged datafile copy
datafile copy filename=C:\ORADATA\ORCL2\UNDOTBS01.DBF recid=1 stamp=623109564
cataloged datafile copy
datafile copy filename=C:\ORADATA\ORCL2\SYSAUX01.DBF recid=2 stamp=623109564
cataloged datafile copy
datafile copy filename=C:\ORADATA\ORCL2\USERS01.DBF recid=3 stamp=623109565
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=623109564 filename=C:\ORADATA\ORCL2\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=623109564 filename=C:\ORADATA\ORCL2\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=623109565 filename=C:\ORADATA\ORCL2\USERS01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20-MAY-07
Backup RMAN catalog
=================================
Create an offline database backup , after your RMAN backups.
=================================
This doc aims to provide a basic solutions for the following targets :
1. Make an incremental 1 database backup using a tag
2. Backup control files and spfile.
3. Checks backups
4. Restore the control file and spfile
5. Restore a database at a point in time
6. Clonning a database from RMAN
7. Backup RMAN catalog
8. Rman commands
First learn how to connect
=================================
C:\>rman target / catalog rman/rman@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 19 22:49:16 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1150787593)
connected to recovery catalog database
Make an incremental 1 database backup using a tag
===========================================
C:\>set oracle_sid=orcl
C:\rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 14 12:28:56 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
Register the database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Backup database
resync catalog;
run
{
allocate channel ch1 type disk;
backup incremental level 1 as backupset tag=orcl_L1 (database);
sql 'alter system archive log current';
release channel ch1;
allocate channel ch1 type disk;
backup
archivelog all
delete all input;
release channel ch1;
}
Backup Control file and Spfile
===========================================
You can enable the auto backup feature , that auto backup control files and spfiles . These files are included in the datafile’s backup set.
Rman>configure auto backup on
When you list your backup set you can see
…
Control File Included: Ckp SCN: 147650 Ckp time: 19-MAY-07
SPFILE Included: Modification time: 19-MAY-07
….
Check your backup
===========================================
The following command you can help to monitor your backup
RMAN>backup validate batabase;
The above command check the data file’s location , and its physical and logical corruption status. If some error occurred the view V$database_block_corruption is updated by RMAN.
RMAN>List backup [ tag=your tag name]
RMAN>List backupset;
RMAN>List copy;
RMAN>List backup summary;
RMAN>crosscheck backup;
Example:
RMAN> List backupset tag=orcl_l1;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1410 Incr 1 56.00K DISK 00:00:25 19-MAY-07
BP Key: 1414 Status: AVAILABLE Compressed: NO Tag: ORCL_L1
Piece Name: C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_19\O1_MF_NNND1_ORCL_L1_34Y49O4D_.BKP
List of Datafiles in backup set 1410
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\SYSTEM01.DBF
2 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\UNDOTBS01.DBF
3 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\SYSAUX01.DBF
4 1 Incr 147640 19-MAY-07 C:\ORADATA\ORCL\USERS01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1411 Incr 1 6.80M DISK 00:00:03 19-MAY-07
BP Key: 1415 Status: AVAILABLE Compressed: NO Tag: ORCL_L1
Piece Name: C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_19\O1_MF_NCSN1_ORCL_L1_34Y4BJHN_.BKP
Control File Included: Ckp SCN: 147650 Ckp time: 19-MAY-07
SPFILE Included: Modification time: 19-MAY-07
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1486 Incr 1 6.44M DISK 00:00:27 19-MAY-07
BP Key: 1489 Status: AVAILABLE Compressed: NO Tag: ORCL_L1
Piece Name: C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_19\O1_MF_NNND1_ORCL_L1_34YKTZTC_.BKP
List of Datafiles in backup set 1486
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\SYSTEM01.DBF
2 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\UNDOTBS01.DBF
3 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\SYSAUX01.DBF
4 1 Incr 150555 19-MAY-07 C:\ORADATA\ORCL\USERS01.DBF
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1369 B 1 A DISK 19-MAY-07 1 1 NO TAG20070519T175846
1370 B 1 A DISK 19-MAY-07 1 1 NO TAG20070519T175846
1394 B A A DISK 19-MAY-07 1 1 NO TAG20070519T180010
1410 B 1 A DISK 19-MAY-07 1 1 NO ORCL_L1
1411 B 1 A DISK 19-MAY-07 1 1 NO ORCL_L1
1443 B A A DISK 19-MAY-07 1 1 NO TAG20070519T180148
1486 B 1 A DISK 19-MAY-07 1 1 NO ORCL_L1
1499 B F A DISK 19-MAY-07 1 1 NO TAG20070519T215253
1534 B A A DISK 19-MAY-07 1 1 NO TAG20070519T215309
1547 B F A DISK 19-MAY-07 1 1 NO TAG20070519T215314
Restore spfile and control files
===========================================
Restore spfile
C:\>rman target / catalog rman/rman@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 19 23:12:49 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> startup
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\MY_DATABASE_1023\DATABASE\INITORCL.ORA'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/19/2007 23:13:06
ORA-01103: database name 'ORCL' in control file is not 'DUMMY'
RMAN> set dbid 1150787593
executing command: SET DBID
database name is "ORCL" and DBID is 1150787593
RMAN> restore spfile;
Starting restore at 19-MAY-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=C:\MY_DATABASE_1023\DATABASE\SPFILEORCL.ORA
channel ORA_DISK_1: reading from backup piece C:\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2007_05_19\O1_MF_S_623026
4YOKL8M_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2007_05_19\O1_MF_S_623026528_34YOKL8M_.BKP tag=TAG20070519
28
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 19-MAY-07
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 167772160 bytes
Fixed Size 1246852 bytes
Variable Size 62916988 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
Restore control files
RMAN> startup
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/19/2007 23:19:18
ORA-00205: error in identifying control file, check alert log for more info
RMAN> set dbid 1150787593;
executing command: SET DBID
database name is "ORCL" and DBID is 1150787593
RMAN> restore controlfile;
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
RMAN> recover database;
Starting recover at 19-MAY-07
Starting implicit crosscheck backup at 19-MAY-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 13 objects
Finished implicit crosscheck backup at 19-MAY-07
Starting implicit crosscheck copy at 19-MAY-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 19-MAY-07
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: C:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2007_05_19\O1_MF_1_90_34YOKWQ8_.ARC
File Name: C:\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2007_05_19\O1_MF_S_623026528_34YOKL8M_.BKP
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 90 is already on disk as file C:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2007_05
_1_90_34YOKWQ8_.ARC
archive log thread 1 sequence 91 is already on disk as file C:\ORADATA\ORCL\REDO01.LOG
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2007_05_19\O1_MF_1_90_34YOKWQ8_.ARC thread=1 s
0
archive log filename=C:\ORADATA\ORCL\REDO01.LOG thread=1 sequence=91
media recovery complete, elapsed time: 00:00:02
Finished recover at 19-MAY-07
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1281 1282 ORCL 1150787593 PARENT 1 19-MAY-07
1281 2410 ORCL 1150787593 CURRENT 153553 19-MAY-07
Cloning database with RMAN
===========================================
Terms
Source database: The original database
Auxiliary database: The new database ( after cloning)
Steps
Create the password and init files
Set you listener to manual register both source and auxiliary database
Create a tns entry in tnsnames.ora for the auxiliary database
Test the tns entry
Backup ( LEVEL 0 ) the source database
Open nomount the auxiliary database and EXIT the session
Connect to RMAN using the auxiliary database
Clone the database
Step1
=====
C:\>oradim -NEW -SID ORCL2 -STARTMODE AUTO -SHUTMODE IMMEDIATE
Instance created.
C:\>orapwd file=C:\My_Database_1023\database\pwdorcl2.ora password=oracle entries=5
Step 2
======
C:\>tnsping orcl2
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-MAY-2007 00:30:50
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\My_Database_1023\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = michaelgeorgiou.domain.local)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl2)))
OK (130 msec)
Step 5
=======
resync catalog;
run
{
allocate channel ch1 type disk;
backup as backupset tag=orcl_L1 (database);
sql 'alter system archive log current';
release channel ch1;
allocate channel ch1 type disk;
backup
archivelog all
delete all input;
release channel ch1;
}
Step 7
=======
set ORACLE_SID=ORCL –-your target or source database
rman target / nocatalog auxiliary sys/oracle@orcl2 –orcl2 is the aux db
Step 8
=======
run
{
allocate auxiliary channel aux1 type disk;
set newname for datafile 1 to 'C:\oradata\ORCL2\SYSTEM01.DBF';
set newname for datafile 2 to 'C:\oradata\ORCL2\UNDOTBS01.DBF';
set newname for datafile 3 to 'C:\oradata\ORCL2\SYSAUX01.DBF';
set newname for datafile 4 to 'C:\oradata\ORCL2\USERS01.DBF';
duplicate target database to 'ORCL2' NOFILENAMECHECK;
}
RMAN responses as follows:
RMAN> run
2> {
3> allocate auxiliary channel aux1 type disk;
4> set newname for datafile 1 to 'C:\oradata\ORCL2\SYSTEM01.DBF';
5> set newname for datafile 2 to 'C:\oradata\ORCL2\UNDOTBS01.DBF';
6> set newname for datafile 3 to 'C:\oradata\ORCL2\SYSAUX01.DBF';
7> set newname for datafile 4 to 'C:\oradata\ORCL2\USERS01.DBF';
8> duplicate target database to 'ORCL2' NOFILENAMECHECK;
9> }
allocated channel: aux1
channel aux1: sid=155 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 20-MAY-07
contents of Memory Script:
{
set until scn 188601;
set newname for datafile 1 to
"C:\ORADATA\ORCL2\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\ORADATA\ORCL2\UNDOTBS01.DBF";
set newname for datafile 3 to
"C:\ORADATA\ORCL2\SYSAUX01.DBF";
set newname for datafile 4 to
"C:\ORADATA\ORCL2\USERS01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-MAY-07
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORADATA\ORCL2\SYSTEM01.DBF
restoring datafile 00002 to C:\ORADATA\ORCL2\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORADATA\ORCL2\SYSAUX01.DBF
restoring datafile 00004 to C:\ORADATA\ORCL2\USERS01.DBF
channel aux1: reading from backup piece C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_NNNDF_ORCL_L1_35
HN_.BKP
channel aux1: restored backup piece 1
piece handle=C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_NNNDF_ORCL_L1_3515NZHN_.BKP tag=ORCL_L1
channel aux1: restore complete, elapsed time: 00:00:55
Finished restore at 20-MAY-07
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 5 M ,
GROUP 2 SIZE 5 M ,
GROUP 3 SIZE 5 M
DATAFILE
'C:\ORADATA\ORCL2\SYSTEM01.DBF'
CHARACTER SET UTF8
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=623108765 filename=C:\ORADATA\ORCL2\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=623108765 filename=C:\ORADATA\ORCL2\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=623108765 filename=C:\ORADATA\ORCL2\USERS01.DBF
contents of Memory Script:
{
set until scn 188601;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 20-MAY-07
starting media recovery
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=14
channel aux1: restoring archive log
archive log thread=1 sequence=15
channel aux1: reading from backup piece C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_ANNNN_TAG2007052
4354_3515QDDP_.BKP
channel aux1: restored backup piece 1
piece handle=C:\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2007_05_20\O1_MF_ANNNN_TAG20070520T214354_3515QDDP_.BKP tag=
0070520T214354
channel aux1: restore complete, elapsed time: 00:00:03
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_14_3515VMG5_.ARC thread=1 seque
14
channel clone_default: deleting archive log(s)
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_14_3515VMG5_.ARC recid=2 stamp=
08772
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_15_3515VMM1_.ARC thread=1 seque
15
channel clone_default: deleting archive log(s)
archive log filename=C:\FLASH_RECOVERY_AREA\ORCL2\ARCHIVELOG\2007_05_20\O1_MF_1_15_3515VMM1_.ARC recid=1 stamp=
08771
media recovery complete, elapsed time: 00:00:02
Finished recover at 20-MAY-07
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1246852 bytes
Variable Size 62916988 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 5 M ,
GROUP 2 SIZE 5 M ,
GROUP 3 SIZE 5 M
DATAFILE
'C:\ORADATA\ORCL2\SYSTEM01.DBF'
CHARACTER SET UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"C:\ORADATA\ORCL\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "C:\ORADATA\ORCL2\UNDOTBS01.DBF";
catalog clone datafilecopy "C:\ORADATA\ORCL2\SYSAUX01.DBF";
catalog clone datafilecopy "C:\ORADATA\ORCL2\USERS01.DBF";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to C:\ORADATA\ORCL\TEMP01.DBF in control file
cataloged datafile copy
datafile copy filename=C:\ORADATA\ORCL2\UNDOTBS01.DBF recid=1 stamp=623109564
cataloged datafile copy
datafile copy filename=C:\ORADATA\ORCL2\SYSAUX01.DBF recid=2 stamp=623109564
cataloged datafile copy
datafile copy filename=C:\ORADATA\ORCL2\USERS01.DBF recid=3 stamp=623109565
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=623109564 filename=C:\ORADATA\ORCL2\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=623109564 filename=C:\ORADATA\ORCL2\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=623109565 filename=C:\ORADATA\ORCL2\USERS01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20-MAY-07
Backup RMAN catalog
=================================
Create an offline database backup , after your RMAN backups.
Friday, April 13, 2007
Resolving Free List Contention
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
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
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)