Let, our database name is LIST_MYDB and listener for this database is LIST_MYDB.
If needed doe below:
open "listener.ora" and put ADMIN_RESTRICTIONS_l[istener_name]=off and RELOAD [listnere_name]"
then proceed as below:
1. stop logging
$ lsnrctl
LSNRCTL> set CURRENT_LISTENER LIST_MYDB
Current Listener is LIST_MYDB
LSNRCTL> set Log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1533)))
LIST_MYDB parameter "log_status" set to OFF
The command completed successfully
2. move the current log
$ mv list_mydb.log mv list_mydb.log_old
3. start logging
$ lsnrctl
LSNRCTL> set CURRENT_LISTENER LIST_MYDB
LSNRCTL> set Log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1533)))
LIST_MYDB parameter "log_status" set to ON
The command completed successfully
LSNRCTL> exit
If forgot to stop logging [step 1] then logging may stop automatically.
In this case after completing step 2, do as step 1 and then step 3.
Now do below if needed:
open "listener.ora" and put ADMIN_RESTRICTIONS_l[istener_name]=on and RELOAD [listnere_name]"
Saturday, June 18, 2011
Wednesday, June 15, 2011
RMAN Scripts to Create Backup Pieces to Multiple Disk LOcations and delete obsolete
In order to take backup of datafiles and archivelogs to more than one locations below scripts can be used:
run {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/data01/oradata/RMAN_BACKUP/LOC1/%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/data02/oradata/RMAN_BACKUP/LOC2/%U';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/data02/oradata/RMAN_BACKUP/LOC2/%F';
backup incremental level 0 database;
release channel disk1;
release channel disk2;
sql 'alter system archive log current';
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/data01/oradata/RMAN_BACKUP/LOC1/LOG_%t_%s_%p_%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/data02/oradata/RMAN_BACKUP/LOC2/LOG_%t_%s_%p_%U';
backup archivelog all DELETE INPUT;
release channel disk1;
release channel disk2;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
}
We have faced below error while "delete obsolete":
RMAN> delete obsolete
RMAN-06091: no channel allocated for maintenance (of an appropriate type)
Problem was, I was trying to delete backups, which are on both disk and tape, so we need to allocate proper channel tape/disk:
Tape:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
crosscheck backupset;
delete obsolete;
}
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
crosscheck backupset;
delete noprompt backup of archivelog until time 'sysdate-7';
}
Disk:
run {
allocate channel 'dev_0' type disk;
crosscheck backupset;
delete obsolete;
}
run {
allocate channel 'dev_0' type disk;
crosscheck backupset;
delete noprompt backup of archivelog until time 'sysdate-7';
}
run {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/data01/oradata/RMAN_BACKUP/LOC1/%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/data02/oradata/RMAN_BACKUP/LOC2/%U';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/data02/oradata/RMAN_BACKUP/LOC2/%F';
backup incremental level 0 database;
release channel disk1;
release channel disk2;
sql 'alter system archive log current';
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/data01/oradata/RMAN_BACKUP/LOC1/LOG_%t_%s_%p_%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/data02/oradata/RMAN_BACKUP/LOC2/LOG_%t_%s_%p_%U';
backup archivelog all DELETE INPUT;
release channel disk1;
release channel disk2;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
}
We have faced below error while "delete obsolete":
RMAN> delete obsolete
RMAN-06091: no channel allocated for maintenance (of an appropriate type)
Problem was, I was trying to delete backups, which are on both disk and tape, so we need to allocate proper channel tape/disk:
Tape:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
crosscheck backupset;
delete obsolete;
}
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
crosscheck backupset;
delete noprompt backup of archivelog until time 'sysdate-7';
}
Disk:
run {
allocate channel 'dev_0' type disk;
crosscheck backupset;
delete obsolete;
}
run {
allocate channel 'dev_0' type disk;
crosscheck backupset;
delete noprompt backup of archivelog until time 'sysdate-7';
}
Oracle Database Disaster Recovery using Disk Based RMAN Backup
Scenario:
- Database name is MYDB
- Production [source] machine is completely out of order and inaccessible
- Have scheduled RMAN Backup on remote storage disks
- RMAN Backups are taken with "CONTROLFILE AUTOBACKUP ON;" configuration of RMAN
[if "CONTROLFILE AUTOBACKUP OFF;" then we need to create a pfile manually]
- All the needed RMAN backup pieces have been identified
- Oracle SW on the target machine has been installed
- Below two mount points have been mounted on the target machine and on which RMAN backup pieces are kept:
/data01/oradata/RMAN_RESTORE/LOC1
/data02/oradata/RMAN_RESTORE/LOC2
- Latest AUTOBACKUPed CONTROLFILE has been identified [ c-[DBID]-[YYYYMMDD]-[2 digit HEX_SEQUENCE for the day],expl:c-2169732094-20110614-0d ] and kept on /data02/oradata/RMAN_RESTORE/LOC2
- Now have to restore and recover the MYDB database
- For all the steps below, please export ORACLE_HOME & ORACLE_SID properly and use oracle user
[If you are on windows create a oracle DB service for MYDB as below on new machine before proceeding:
Please run the "Command Prompt" right click and "run as administrator" if current user is not "administrator"
oradim -new -sid MYDB -startmode m
]
1. Restore the spfile:
$ export ORACLE_SID=MYDB
rman> startup nomount
rman> restore spfile from '/data02/oradata/RMAN_RESTORE/LOC2/c-2169732094-20110614-0d';
- In above command "c-2169732094-20110614-0d" is the latest autobackuped controlfile.
- Spfile is now restored in the default location [$ORACLE_HOME/dbs/spfileMYDB].
- We may need to edit the spfile to create pfile file, because some some parameter may needed to be chaged an startup with pfile
rman> shutdown immediate
$ rman target /
rman> startup nomount [or startup nomount pfile='PFILE_LOCATION']
2. Restore controlfile and mount the database
- To avoid below error we need to create related directory locations or change control file location:
ORA-19504: failed to create file "/data01/oradata/MYDB/cntl/control02.ctl"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
rman> restore controlfile from '/data02/oradata/RMAN_RESTORE/LOC2/c-2169732094-20110614-0d';
- On above, this is the same file from which spfile has been restored.
rman> startup mount [or force]
3. Check needed configuration
- Check latest scn as no redo log file available
SQL> select group#, first_change#, status, archived from v$log order by FIRST_CHANGE# desc;
GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
1 44958649 CURRENT NO ---<<<< this is the target SCN
3 44958631 ACTIVE YES
2 44958342 ACTIVE YES
- optionally plan for change the datafile location
- use below command to find current datafile location then plan for new location:
rman> REPORT SCHEMA;
-we can use below set of commands to crosscheck whether we have all the needed backup pieces:
RMAN> list backup;
RMAN> list backup of database completed after [date];
RMAN> list backup of database completed between [start date] and [end date];
4. Register the calalog location [to identify the backup pieces from the disk locations]
- for each location where RMAN backup piece is kept please run " catalog start with" as below:
rman> catalog start with '/data01/oradata/RMAN_RESTORE/LOC1';
rman> catalog start with '/data02/oradata/RMAN_RESTORE/LOC2';
5. restore the database
- For similar location of the datafiles & tempfiles use below command:
rman> restore database;
- For changed locations [step 3.] of the datafiles & tempfiles use below command canbe used:
rman> run {
SET NEWNAME FOR DATAFILE 1 TO '[new location for datafile 1]';
SET NEWNAME FOR DATAFILE 5 TO '[new location for datafile 5]';
SET NEWNAME FOR DATAFILE 15 TO '[new location for datafile 15]';
SET NEWNAME FOR TEMPFILE 1 TO '[new location for tempfile 1]';
allocate channel 'dev_0' type disk;
restore database;
SWITCH DATAFILE ALL;
release channel 'dev_0';
}
6. Recover and open the database to the SCN identified at step 3:
rman> recover database until scn 44958649;
rman> alter database open resetlogs;
7. create a password file and give an entry in oratab file
$ orapwd file=$ORACLE_HOME/dbs/orapwMYDB password=password entries=5
[ for windows:$ orapwd file=$ORACLE_HOME/database/orapwMYDB password=password entries=5 ]
$ vi /etc/oratab
MYDB:/oracle/orabase/product/11.2.0/dbhome_1:N
Relater links:
http://web.njit.edu/info/oracle/DOC/backup.102/b14191/rcmcatdb002.htm
http://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ
http://repettas.wordpress.com/2008/11/01/rman-restore-and-recover-of-a-database-when-the-repository-and-spfileinitora-files-are-lost/
- Database name is MYDB
- Production [source] machine is completely out of order and inaccessible
- Have scheduled RMAN Backup on remote storage disks
- RMAN Backups are taken with "CONTROLFILE AUTOBACKUP ON;" configuration of RMAN
[if "CONTROLFILE AUTOBACKUP OFF;" then we need to create a pfile manually]
- All the needed RMAN backup pieces have been identified
- Oracle SW on the target machine has been installed
- Below two mount points have been mounted on the target machine and on which RMAN backup pieces are kept:
/data01/oradata/RMAN_RESTORE/LOC1
/data02/oradata/RMAN_RESTORE/LOC2
- Latest AUTOBACKUPed CONTROLFILE has been identified [ c-[DBID]-[YYYYMMDD]-[2 digit HEX_SEQUENCE for the day],expl:c-2169732094-20110614-0d ] and kept on /data02/oradata/RMAN_RESTORE/LOC2
- Now have to restore and recover the MYDB database
- For all the steps below, please export ORACLE_HOME & ORACLE_SID properly and use oracle user
[If you are on windows create a oracle DB service for MYDB as below on new machine before proceeding:
Please run the "Command Prompt" right click and "run as administrator" if current user is not "administrator"
oradim -new -sid MYDB -startmode m
]
1. Restore the spfile:
$ export ORACLE_SID=MYDB
rman> startup nomount
rman> restore spfile from '/data02/oradata/RMAN_RESTORE/LOC2/c-2169732094-20110614-0d';
- In above command "c-2169732094-20110614-0d" is the latest autobackuped controlfile.
- Spfile is now restored in the default location [$ORACLE_HOME/dbs/spfileMYDB].
- We may need to edit the spfile to create pfile file, because some some parameter may needed to be chaged an startup with pfile
rman> shutdown immediate
$ rman target /
rman> startup nomount [or startup nomount pfile='PFILE_LOCATION']
2. Restore controlfile and mount the database
- To avoid below error we need to create related directory locations or change control file location:
ORA-19504: failed to create file "/data01/oradata/MYDB/cntl/control02.ctl"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
rman> restore controlfile from '/data02/oradata/RMAN_RESTORE/LOC2/c-2169732094-20110614-0d';
- On above, this is the same file from which spfile has been restored.
rman> startup mount [or force]
3. Check needed configuration
- Check latest scn as no redo log file available
SQL> select group#, first_change#, status, archived from v$log order by FIRST_CHANGE# desc;
GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
1 44958649 CURRENT NO ---<<<< this is the target SCN
3 44958631 ACTIVE YES
2 44958342 ACTIVE YES
- optionally plan for change the datafile location
- use below command to find current datafile location then plan for new location:
rman> REPORT SCHEMA;
-we can use below set of commands to crosscheck whether we have all the needed backup pieces:
RMAN> list backup;
RMAN> list backup of database completed after [date];
RMAN> list backup of database completed between [start date] and [end date];
4. Register the calalog location [to identify the backup pieces from the disk locations]
- for each location where RMAN backup piece is kept please run " catalog start with" as below:
rman> catalog start with '/data01/oradata/RMAN_RESTORE/LOC1';
rman> catalog start with '/data02/oradata/RMAN_RESTORE/LOC2';
5. restore the database
- For similar location of the datafiles & tempfiles use below command:
rman> restore database;
- For changed locations [step 3.] of the datafiles & tempfiles use below command canbe used:
rman> run {
SET NEWNAME FOR DATAFILE 1 TO '[new location for datafile 1]';
SET NEWNAME FOR DATAFILE 5 TO '[new location for datafile 5]';
SET NEWNAME FOR DATAFILE 15 TO '[new location for datafile 15]';
SET NEWNAME FOR TEMPFILE 1 TO '[new location for tempfile 1]';
allocate channel 'dev_0' type disk;
restore database;
SWITCH DATAFILE ALL;
release channel 'dev_0';
}
6. Recover and open the database to the SCN identified at step 3:
rman> recover database until scn 44958649;
rman> alter database open resetlogs;
7. create a password file and give an entry in oratab file
$ orapwd file=$ORACLE_HOME/dbs/orapwMYDB password=password entries=5
[ for windows:$ orapwd file=$ORACLE_HOME/database/orapwMYDB password=password entries=5 ]
$ vi /etc/oratab
MYDB:/oracle/orabase/product/11.2.0/dbhome_1:N
Relater links:
http://web.njit.edu/info/oracle/DOC/backup.102/b14191/rcmcatdb002.htm
http://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ
http://repettas.wordpress.com/2008/11/01/rman-restore-and-recover-of-a-database-when-the-repository-and-spfileinitora-files-are-lost/
Tuesday, June 14, 2011
Disaster Recovery using HP Data Protector and Oracle RMAN
In our case we have used below components:
- HP Data Protector Version 6.2
- Source & Destination System: RHEL5.5
- Source & Destination Database: Oracle 11.2.0.1
Scenario:
- Our production [source] machine is completely out of order and inaccessible
- We have scheduled RMAN Backup [incremental] on TAPE which is configured with HPDP 6.2
- RMAN Backups are taken with "CONTROLFILE AUTOBACKUP ON;" configuration of RMAN
[if "CONTROLFILE AUTOBACKUP OFF;" then we need to create a pfile manually]
- Our Database name is MYDB and Backup Specifications names for this DB starts with MYDB_*
- Machine of Cell Manager is also completely damaged
- We have backup of HPDP iDB on tape
- So, currently we only have Tapes containing HPDP iDB latest backup & RMAN backup
So we need to do following:
1. Configure a new Cell Manager
2. Scan load tape information for the Tape containing DP iDB backup
3. import iDB from the backup on tape
4. Scan Tapes containing RMAN backup
5. Configure a new machine with same OS and Connectivity with new Cell Manager
6. Create the partitions locations as much as similar to the old machine as possible
7. Install Oracle SW only of same version [version means consideration 32bit and 64bit]
8. Create a LISTENER listening on default port
9. Install and configure HP DP 6.2 Client on the target DB machine [Install HP Data Protector 6.2 Client [and use in RMAN]]
10. Restore and Recover the Oracle Database named MYDB on new machine
Now we are going to discuss step 10 in details:
[If you are on windows create a oracle DB service for MYDB as below on new machine before proceeding:
Please run the "Command Prompt" right click and "run as administrator" if current user is not "administrator"
oradim -new -sid MYDB -startmode m
]
For all the below steps, make sure that desired ORACLE_HOME is exported correctly
a. Collection configuration information:
- identify the backup spec that contains latest backup in our case [NODE1_MYDB_INCR1]
- from the latest rman backup session of DP [internal database>sessions>properties>message] collect below to parameter:
dbid 4261461017;
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=MYDB,OB2BARLIST=NODE1_MYDB_INCR1)';
- identify and crosscheck needed media from session messages using lines like below:
Initializing new medium: "Default File_6"
Loading medium from slot [1709a8c054df607235202050011.fd] to device [Test_01_Writer0]
b. Restore spfile & controlfile backedup by "CONTROLFILE AUTOBACKUP" from Tape using below steps:
$ export ORACLE_SID=MYDB
$ rman target /
connected to target database (not started)
[ignore above, because we do not have any Database now]
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle1/orabase/product/11.2.0/dbhome_1/dbs/initMYDB.ora'
--ignore above, because we donot have s/pfile and DUMMY DB is needed now
RMAN>
set dbid 4261461017;
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=MYDB,OB2BARLIST=NODE1_MYDB_INCR1)';
restore spfile from autobackup;
restore controlfile from autobackup;
release channel 'dev_0';
}
allocated channel: dev_0
channel dev_0: SID=67 device type=SBT_TAPE
channel dev_0: Data Protector A.06.20/370
Starting restore at 12-JUN-11
channel dev_0: looking for AUTOBACKUP on day: 20110612
channel dev_0: AUTOBACKUP found: c-2169732094-20110612-01
channel dev_0: restoring spfile from AUTOBACKUP c-2169732094-20110612-01
channel dev_0: SPFILE restore from AUTOBACKUP complete
Finished restore at 12-JUN-11
Starting restore at 12-JUN-11
channel dev_0: looking for AUTOBACKUP on day: 20110612
channel dev_0: AUTOBACKUP found: c-2169732094-20110612-01
channel dev_0: restoring control file from AUTOBACKUP c-2169732094-20110612-01
channel dev_0: control file restore from AUTOBACKUP complete
output file name=/oracle1/orabase/product/11.2.0/dbhome_1/dbs/cntrlMYDB.dbf --<< control file restored location
Finished restore at 12-JUN-11
released channel: dev_0
[controlfile and spfile will be restored to the default location $ORACLE_HOME/dbs]
c. Then create a pfile by editing the restored spfile [change the location and other parameters]
d. shutdown the Database and start the database using the newly created pfile:
$ export ORACLE_SID=MYDB
$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup nomount pfile='[location of the newly created pfile]'
e. Change the locations for controlfile or copy controlfiles to the desired location mentioned in the in pfile and mount the Database
[or we could create the pfile with the modified "control_files" parameter]
SQL> show parameter control_files
control_files string /system1/oradata/MYDB/cntl/c
ontrol01.ctl, /data01/oradata/
MYDB/cntl/control02.ctl
SQL>! cp $ORACLE_HOME/dbs/cntrlMYDB.dbf /system1/oradata/MYDB/cntl/control01.ctl
SQL>! cp $ORACLE_HOME/dbs/cntrlMYDB.dbf /data01/oradata/MYDB/cntl/control02.ctl
SQL> alter database mount
f. after mount we can restore it from DP or using rman as below,
"tape identification and loading unloading will be handled by DP, please monitor session on DP"
$ export ORACLE_SID=MYDB
$ target /
RMAN> run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
restore database;
}
[Here only SBT_LIBRARY parms is needed, as rest of the info is now in restored control files.]
If you find error like below then try to create same locations or restore to new location:
ORA-19504: failed to create file "......."
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Use below command to identify missing datafiles and original locations:
RMAN> VALIDATE DATABASE;
RMAN> REPORT SCHEMA;
if same location is create then:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
restore datafile 15;
release channel 'dev_0';
}
if new location is needed then:
run {
SET NEWNAME FOR DATAFILE 1 TO '[new location for datafile 1]';
SET NEWNAME FOR DATAFILE 5 TO '[new location for datafile 5]';
SET NEWNAME FOR DATAFILE 15 TO '[new location for datafile 15]';
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
restore datafile 1;
restore datafile 5;
restore datafile 15;
SWITCH DATAFILE ALL;
release channel 'dev_0';
}
g. Collect the latest SCN [as redo logs are also lost] which will be user to recover.
This will eliminate the porbability of RMAN-06054 & RMAN-06556
$ export ORACLE_SID=MYDB
SQL> select group#, first_change#, status, archived from v$log order by FIRST_CHANGE# desc;
GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
2 968291 CURRENT NO ---<<< target SCN, SCN just after the latest archived redolog
1 968224 ACTIVE YES
3 963378 INACTIVE YES
i. Recover Database to the latest SCN
$ export ORACLE_SID=MYDB
$ rman target /
RMAN> run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
recover database until scn 968291;
}
[Here only SBT_LIBRARY parms is needed, as rest of the info is now in restored control files.]
j. Open database using "resetlogs" option [as redo logs are lost]
RMAN> alter database open resetlogs;
k. now create password file to enable remore sysdba login
$ export ORACLE_SID=MYDB
$ orapwd file=$ORACLE_HOME/dbs/orapw password=oracle entries=5
l. create spfile and restart database
$ export ORACLE_SID=MYDB
$ sqlplus / as sysdba
SQL> create spfile from pfile='[location of the newly created pfile]'
SQL> shutdown immediate
SQL> startup
m. Check alert log for error and take a level 0 backup using DP
n. optionally enter a new entry for the database in "/etc/oratab", needed for configuration [dbca]
If there is a 32bit and 64bit difference between source and destination database, we may encounter below error on step "10.j" :
--------------------------------------------------------------------------------------
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56327]
ORA-06553: PLS-801: internal error [56327]
--------------------------------------------------------------------------------------
Problem is that, backup originally was done from Oracle 64bit, but restored to Oracle 32bit.
Please use "MOS NOTE [ID 62290.1] — Changing between 32-bit and 64-bit Word Sizes" to resolve this issue.
Related Links:
http://www.orafusion.com/art_rman3.htm
http://yurijs.wordpress.com/2010/01/27/backup-issue/
ttp://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmcomre.htm
For HPDP Related Config:
http://razib2020.blogspot.com/2010/07/installation-of-dp-cell-manager-purging.html
- HP Data Protector Version 6.2
- Source & Destination System: RHEL5.5
- Source & Destination Database: Oracle 11.2.0.1
Scenario:
- Our production [source] machine is completely out of order and inaccessible
- We have scheduled RMAN Backup [incremental] on TAPE which is configured with HPDP 6.2
- RMAN Backups are taken with "CONTROLFILE AUTOBACKUP ON;" configuration of RMAN
[if "CONTROLFILE AUTOBACKUP OFF;" then we need to create a pfile manually]
- Our Database name is MYDB and Backup Specifications names for this DB starts with MYDB_*
- Machine of Cell Manager is also completely damaged
- We have backup of HPDP iDB on tape
- So, currently we only have Tapes containing HPDP iDB latest backup & RMAN backup
So we need to do following:
1. Configure a new Cell Manager
2. Scan load tape information for the Tape containing DP iDB backup
3. import iDB from the backup on tape
4. Scan Tapes containing RMAN backup
5. Configure a new machine with same OS and Connectivity with new Cell Manager
6. Create the partitions locations as much as similar to the old machine as possible
7. Install Oracle SW only of same version [version means consideration 32bit and 64bit]
8. Create a LISTENER listening on default port
9. Install and configure HP DP 6.2 Client on the target DB machine [Install HP Data Protector 6.2 Client [and use in RMAN]]
10. Restore and Recover the Oracle Database named MYDB on new machine
Now we are going to discuss step 10 in details:
[If you are on windows create a oracle DB service for MYDB as below on new machine before proceeding:
Please run the "Command Prompt" right click and "run as administrator" if current user is not "administrator"
oradim -new -sid MYDB -startmode m
]
For all the below steps, make sure that desired ORACLE_HOME is exported correctly
a. Collection configuration information:
- identify the backup spec that contains latest backup in our case [NODE1_MYDB_INCR1]
- from the latest rman backup session of DP [internal database>sessions>properties>message] collect below to parameter:
dbid 4261461017;
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=MYDB,OB2BARLIST=NODE1_MYDB_INCR1)';
- identify and crosscheck needed media from session messages using lines like below:
Initializing new medium: "Default File_6"
Loading medium from slot [1709a8c054df607235202050011.fd] to device [Test_01_Writer0]
b. Restore spfile & controlfile backedup by "CONTROLFILE AUTOBACKUP" from Tape using below steps:
$ export ORACLE_SID=MYDB
$ rman target /
connected to target database (not started)
[ignore above, because we do not have any Database now]
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle1/orabase/product/11.2.0/dbhome_1/dbs/initMYDB.ora'
--ignore above, because we donot have s/pfile and DUMMY DB is needed now
RMAN>
set dbid 4261461017;
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=MYDB,OB2BARLIST=NODE1_MYDB_INCR1)';
restore spfile from autobackup;
restore controlfile from autobackup;
release channel 'dev_0';
}
allocated channel: dev_0
channel dev_0: SID=67 device type=SBT_TAPE
channel dev_0: Data Protector A.06.20/370
Starting restore at 12-JUN-11
channel dev_0: looking for AUTOBACKUP on day: 20110612
channel dev_0: AUTOBACKUP found: c-2169732094-20110612-01
channel dev_0: restoring spfile from AUTOBACKUP c-2169732094-20110612-01
channel dev_0: SPFILE restore from AUTOBACKUP complete
Finished restore at 12-JUN-11
Starting restore at 12-JUN-11
channel dev_0: looking for AUTOBACKUP on day: 20110612
channel dev_0: AUTOBACKUP found: c-2169732094-20110612-01
channel dev_0: restoring control file from AUTOBACKUP c-2169732094-20110612-01
channel dev_0: control file restore from AUTOBACKUP complete
output file name=/oracle1/orabase/product/11.2.0/dbhome_1/dbs/cntrlMYDB.dbf --<< control file restored location
Finished restore at 12-JUN-11
released channel: dev_0
[controlfile and spfile will be restored to the default location $ORACLE_HOME/dbs]
c. Then create a pfile by editing the restored spfile [change the location and other parameters]
d. shutdown the Database and start the database using the newly created pfile:
$ export ORACLE_SID=MYDB
$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup nomount pfile='[location of the newly created pfile]'
e. Change the locations for controlfile or copy controlfiles to the desired location mentioned in the in pfile and mount the Database
[or we could create the pfile with the modified "control_files" parameter]
SQL> show parameter control_files
control_files string /system1/oradata/MYDB/cntl/c
ontrol01.ctl, /data01/oradata/
MYDB/cntl/control02.ctl
SQL>! cp $ORACLE_HOME/dbs/cntrlMYDB.dbf /system1/oradata/MYDB/cntl/control01.ctl
SQL>! cp $ORACLE_HOME/dbs/cntrlMYDB.dbf /data01/oradata/MYDB/cntl/control02.ctl
SQL> alter database mount
f. after mount we can restore it from DP or using rman as below,
"tape identification and loading unloading will be handled by DP, please monitor session on DP"
$ export ORACLE_SID=MYDB
$ target /
RMAN> run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
restore database;
}
[Here only SBT_LIBRARY parms is needed, as rest of the info is now in restored control files.]
If you find error like below then try to create same locations or restore to new location:
ORA-19504: failed to create file "......."
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Use below command to identify missing datafiles and original locations:
RMAN> VALIDATE DATABASE;
RMAN> REPORT SCHEMA;
if same location is create then:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
restore datafile 15;
release channel 'dev_0';
}
if new location is needed then:
run {
SET NEWNAME FOR DATAFILE 1 TO '[new location for datafile 1]';
SET NEWNAME FOR DATAFILE 5 TO '[new location for datafile 5]';
SET NEWNAME FOR DATAFILE 15 TO '[new location for datafile 15]';
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
restore datafile 1;
restore datafile 5;
restore datafile 15;
SWITCH DATAFILE ALL;
release channel 'dev_0';
}
g. Collect the latest SCN [as redo logs are also lost] which will be user to recover.
This will eliminate the porbability of RMAN-06054 & RMAN-06556
$ export ORACLE_SID=MYDB
SQL> select group#, first_change#, status, archived from v$log order by FIRST_CHANGE# desc;
GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
2 968291 CURRENT NO ---<<< target SCN, SCN just after the latest archived redolog
1 968224 ACTIVE YES
3 963378 INACTIVE YES
i. Recover Database to the latest SCN
$ export ORACLE_SID=MYDB
$ rman target /
RMAN> run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so';
recover database until scn 968291;
}
[Here only SBT_LIBRARY parms is needed, as rest of the info is now in restored control files.]
j. Open database using "resetlogs" option [as redo logs are lost]
RMAN> alter database open resetlogs;
k. now create password file to enable remore sysdba login
$ export ORACLE_SID=MYDB
$ orapwd file=$ORACLE_HOME/dbs/orapw
l. create spfile and restart database
$ export ORACLE_SID=MYDB
$ sqlplus / as sysdba
SQL> create spfile from pfile='[location of the newly created pfile]'
SQL> shutdown immediate
SQL> startup
m. Check alert log for error and take a level 0 backup using DP
n. optionally enter a new entry for the database in "/etc/oratab", needed for configuration [dbca]
If there is a 32bit and 64bit difference between source and destination database, we may encounter below error on step "10.j" :
--------------------------------------------------------------------------------------
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56327]
ORA-06553: PLS-801: internal error [56327]
--------------------------------------------------------------------------------------
Problem is that, backup originally was done from Oracle 64bit, but restored to Oracle 32bit.
Please use "MOS NOTE [ID 62290.1] — Changing between 32-bit and 64-bit Word Sizes" to resolve this issue.
Related Links:
http://www.orafusion.com/art_rman3.htm
http://yurijs.wordpress.com/2010/01/27/backup-issue/
ttp://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmcomre.htm
For HPDP Related Config:
http://razib2020.blogspot.com/2010/07/installation-of-dp-cell-manager-purging.html
Install HP Data Protecctor 6.2 Client [and use in RMAN]
We have followed below steps while installing HP Data Protecctor 6.2 Client:
For windows:
1. Add hostname and IP of target machine [client] & DP Cell Manager in hosts file of the target machine like below:
on windows C:\Windows\System32\drivers\etc\hosts
on linux /etc/hosts
192.168.81.209 DP_CLIENT_HOSTNAME.domain.com DP_CLIENT_HOSTNAME
192.168.9.23 DP_CELL_MANAGER.domain.com DP_CELL_MANAGER
2. unzip the HPDP sw dump
on windows change the locaion to: ESD_HP_DP_6.2_for_Windows_and_Media_Operation_TD586_15000_01\TD586-15000-01\WINDOWS_OTHER\i386
- Double Click setup --<<< use "run as administrator" or install using Administrator user
- select installation type: cleint
- Enter: Cell manager hostname [as entered in the hosts file] and "check response"
- From component list select: Disk Agent, Media Agent & oracle integration [under Integration]
- start
- Finish
Now locate the "orasbt.dll" which is needed in RMAN command.
So, RMAN Command on window may look as below:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=D:\OmniBack\bin\orasbt.dll';
restore database;
}
For Linux/Unix:
We need HPUX distribution of HPDP to install on both HPUX and linux.
Run below set of commands as "root":
$ tar -zxvf ESD_HP_DP_6.2_for_HP_UX_PA_TD586_15001_01.tar.gz
$ cd TD586-15001-01/LOCAL_INSTALL/
$ ./omnisetup.sh -server DP_CELL_MANAGER.domain.com -install da,ma,oracle8
# here as DP_CELL_MANAGER.domain.com is entered in the hosts file
Now locate the "libob2oracle8.so" [for x64 machine libob2oracle8_64bit.so] which is needed in RMAN command:
So, RMAN Command on window may look as below:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8.so';
restore database;
}
Thank to :http://saifur-shaon.blogspot.com/2011/01/dml-over-dblink-ora-01031.html
For windows:
1. Add hostname and IP of target machine [client] & DP Cell Manager in hosts file of the target machine like below:
on windows C:\Windows\System32\drivers\etc\hosts
on linux /etc/hosts
192.168.81.209 DP_CLIENT_HOSTNAME.domain.com DP_CLIENT_HOSTNAME
192.168.9.23 DP_CELL_MANAGER.domain.com DP_CELL_MANAGER
2. unzip the HPDP sw dump
on windows change the locaion to: ESD_HP_DP_6.2_for_Windows_and_Media_Operation_TD586_15000_01\TD586-15000-01\WINDOWS_OTHER\i386
- Double Click setup --<<< use "run as administrator" or install using Administrator user
- select installation type: cleint
- Enter: Cell manager hostname [as entered in the hosts file] and "check response"
- From component list select: Disk Agent, Media Agent & oracle integration [under Integration]
- start
- Finish
Now locate the "orasbt.dll" which is needed in RMAN command.
So, RMAN Command on window may look as below:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=D:\OmniBack\bin\orasbt.dll';
restore database;
}
For Linux/Unix:
We need HPUX distribution of HPDP to install on both HPUX and linux.
Run below set of commands as "root":
$ tar -zxvf ESD_HP_DP_6.2_for_HP_UX_PA_TD586_15001_01.tar.gz
$ cd TD586-15001-01/LOCAL_INSTALL/
$ ./omnisetup.sh -server DP_CELL_MANAGER.domain.com -install da,ma,oracle8
# here as DP_CELL_MANAGER.domain.com is entered in the hosts file
Now locate the "libob2oracle8.so" [for x64 machine libob2oracle8_64bit.so] which is needed in RMAN command:
So, RMAN Command on window may look as below:
run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8.so';
restore database;
}
Thank to :http://saifur-shaon.blogspot.com/2011/01/dml-over-dblink-ora-01031.html
Monday, June 6, 2011
Install Oracle Apex 4 on Oracle Database 11.2
Our target was to Install Apex with below details:
Apex version: 4.0.2
Install Option: Full Development, we need this installation for development
HTTP Server: "Application Express Listener" "Standalone Mode", want to avoid installation of extra application server
Database: Oracle 11.2 Enterprise
Java: jdk1.6.0_10 [for apex listener]
Below is the prepared action plan I have prepared according to the docs provided with Apex:
1. Requirements Check list:
1.1 DB: >= 10.2.0.3
1.2 SHARED_POOL_SIZE='100M'
1.3 Disk Space Requirement
- Oracle Application Express software files on the file system: 1 GB if using full download (apex_4.0.2.zip).
- Oracle Application Express tablespace: 185 MB
- SYSTEM tablespace: 100 MB
- Oracle Application Express tablespace for each additional language (other than English) installed: 75 MB
1.4 Oracle XML DB Requirement
1.5 Oracle Text Requirement
1.6 HTTP Server Requirements
- We will go for Oracle "Application Express Listener" because:
a. seperation from DB [Embaded PL/SQL gateway, runs in the database as part of the XML DB HTTP Protocol Listener]
b. no need of extra plagin [Oracle HTTP Server, uses the mod_plsql plug-in to communicate with the Oracle Application Express engine within the Oracle database]
2. Install appex:
2.1 Download Oracle Appex and upzip to the desired location[http://www.oracle.com/technology/products/database/application_express/download.html]
2.2 Remove the password complexity from the default profile of DB
2.3 Create tablespace for appex [TBS_APEX]
2.4 for full development installation go to unzipped folder of appex and use below:
SQL> @apexins tablespace_apex tablespace_files tablespace_temp images
tablespace_apex: is the name of the tablespace for the Oracle Application Express application user.
tablespace_files: is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp: is the name of the temporary tablespace.
images: is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/
for our case we used below:
SQL> conn / as sysdba
SQL> @apexins TBS_APEX TBS_APEX TEMP /i/
When Oracle Application Express installs it creates three new database accounts:
APEX_040000 - The account that owns the Oracle Application Express schema and metadata.
FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
APEX_PUBLIC_USER - The minimally privileged account used for Oracle Application Express configuration with Oracle HTTP Server and mod_plsql or Oracle Application Express Listener.
2.5 Change the Password for the ADMIN Account [When prompted enter a password for the ADMIN account]
SQL> conn / as sysdba
SQL> @apxchpwd
2.6 unlock APEX_PUBLIC_USER and change its password
SQL> conn / as sysdba
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY new_password
3. install appex listener:
3.1 Download Oracle Application Express Listener and unzip to the desired location [http://www.oracle.com/technology/products/database/application_express/html/apex_listener_download.html]
3.2 for installation we choose "Standalone Mode" as we donot need any exra components
3.4 start appex listener[we are using jdk1.6.0_10, failed to run using jre 1.3 & 1.5]:
> "C:\Program Files\Java\jdk1.6.0_10\bin\java" –jar -Dapex.images=[apex home]/images [appex listener home]\apex.war
-When prompted, specify the location of the folder containing the static resources used by Oracle Application Express
we used the images location created in the unziped apex folder:..../apex/images
- when prompt for username of "APEX Listener Administrator" we kept the default:[adminlistener] and gave the password [prompt only for the first run]
- when prompt for username of "APEX Listener Manager" we kept the default:[managerlistener] and gave the password [prompt only for the first run]
then go to "http://[ip or hostname]:[port, default is 8080]/apex/listenerConfigure" and configure APEX Listener:just give the connection details and apply
for more configuration refer to docs comes with APEX Listener
3.5 Enable Network Services in Oracle Database 11g:
- Failing to grant these privileges results in issues with:
a. Sending outbound mail in Oracle Application Express.Users can call methods from the APEX_MAIL package, but issues arise when sending outbound email.
b. Using Web services in Oracle Application Express.
c. PDF/report printing.
d. Searching for content in online Help (that is, using the Find link).
3.6 Granting Connect Privileges
The following example demonstrates how to grant connect privileges to any host for the APEX_040000 database user[as sysdba]:
-----------------------BEGIN SCRIPT-------------------------------------
DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040000
-- the "connect" privilege if APEX_040000 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
-- Before checking the privilege, ensure that the ACL is valid
-- (for example, does not contain stale references to dropped users).
-- If it does, the following exception will be raised:
--
-- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
--
SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;
DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'APEX_040000', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
-----------------------------END SCRIPT----------------------------------
3.7 Enable Indexing of Online Help in Oracle Database 11gR2 and Higher:
- Run the following command:
SQL > conn / as sysdba
SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';
This returns either NULL or the database role which is granted the ability to use an Oracle Text URL datastore.
- If no value is returned by above, then create a new database role as shown in the following example:
SQL> CREATE ROLE APEX_URL_DATASTORE_ROLE;
SQL> GRANT APEX_URL_DATASTORE_ROLE to APEX_040000;
- use the Oracle Text API to grant permission database role with the following statement:
SQL> EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');
4. JOB_QUEUE_PROCESSES:
4.1 JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs.
4.2 In Oracle Application Express release 4.0, transactional support and SQL scripts require jobs
4.3 use below to check and set value for job_queue_processes:
SQL> SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = [number];
5. Creating a Workspace Manually:
5.1 go to "http://hostname:port/apex/apex_admin" and login using "admin" [if will prompt for passwrod change, change it according to the rule]
5.2 Manage Workspaces > Create Workspace
- enter a workspace name and description and click Next >
- For Re-use existing schema, select No
Neter a schema name & password and quota
Click Next >
- Enter the Workspace administrator information and click Next >
- Create Workspace
6. Creating Oracle Application Express Users:6.1 go to "http://hostname:port/apex/apex_admin" and login using "admin"
6.2 create user > enter necessary info > create
7. Logging in to Your Workspace:
7.1 goto "http://hostname:port/apex"
7.2 enter the following:
Workspace field - Enter the name of your workspace
Username field - Enter your user name
Password field - Enter your case-sensitive password
7.3 login
Apex version: 4.0.2
Install Option: Full Development, we need this installation for development
HTTP Server: "Application Express Listener" "Standalone Mode", want to avoid installation of extra application server
Database: Oracle 11.2 Enterprise
Java: jdk1.6.0_10 [for apex listener]
Below is the prepared action plan I have prepared according to the docs provided with Apex:
1. Requirements Check list:
1.1 DB: >= 10.2.0.3
1.2 SHARED_POOL_SIZE='100M'
1.3 Disk Space Requirement
- Oracle Application Express software files on the file system: 1 GB if using full download (apex_4.0.2.zip).
- Oracle Application Express tablespace: 185 MB
- SYSTEM tablespace: 100 MB
- Oracle Application Express tablespace for each additional language (other than English) installed: 75 MB
1.4 Oracle XML DB Requirement
1.5 Oracle Text Requirement
1.6 HTTP Server Requirements
- We will go for Oracle "Application Express Listener" because:
a. seperation from DB [Embaded PL/SQL gateway, runs in the database as part of the XML DB HTTP Protocol Listener]
b. no need of extra plagin [Oracle HTTP Server, uses the mod_plsql plug-in to communicate with the Oracle Application Express engine within the Oracle database]
2. Install appex:
2.1 Download Oracle Appex and upzip to the desired location[http://www.oracle.com/technology/products/database/application_express/download.html]
2.2 Remove the password complexity from the default profile of DB
2.3 Create tablespace for appex [TBS_APEX]
2.4 for full development installation go to unzipped folder of appex and use below:
SQL> @apexins tablespace_apex tablespace_files tablespace_temp images
tablespace_apex: is the name of the tablespace for the Oracle Application Express application user.
tablespace_files: is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp: is the name of the temporary tablespace.
images: is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/
for our case we used below:
SQL> conn / as sysdba
SQL> @apexins TBS_APEX TBS_APEX TEMP /i/
When Oracle Application Express installs it creates three new database accounts:
APEX_040000 - The account that owns the Oracle Application Express schema and metadata.
FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
APEX_PUBLIC_USER - The minimally privileged account used for Oracle Application Express configuration with Oracle HTTP Server and mod_plsql or Oracle Application Express Listener.
2.5 Change the Password for the ADMIN Account [When prompted enter a password for the ADMIN account]
SQL> conn / as sysdba
SQL> @apxchpwd
2.6 unlock APEX_PUBLIC_USER and change its password
SQL> conn / as sysdba
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY new_password
3. install appex listener:
3.1 Download Oracle Application Express Listener and unzip to the desired location [http://www.oracle.com/technology/products/database/application_express/html/apex_listener_download.html]
3.2 for installation we choose "Standalone Mode" as we donot need any exra components
3.4 start appex listener[we are using jdk1.6.0_10, failed to run using jre 1.3 & 1.5]:
> "C:\Program Files\Java\jdk1.6.0_10\bin\java" –jar -Dapex.images=[apex home]/images [appex listener home]\apex.war
-When prompted, specify the location of the folder containing the static resources used by Oracle Application Express
we used the images location created in the unziped apex folder:..../apex/images
- when prompt for username of "APEX Listener Administrator" we kept the default:[adminlistener] and gave the password [prompt only for the first run]
- when prompt for username of "APEX Listener Manager" we kept the default:[managerlistener] and gave the password [prompt only for the first run]
then go to "http://[ip or hostname]:[port, default is 8080]/apex/listenerConfigure" and configure APEX Listener:just give the connection details and apply
for more configuration refer to docs comes with APEX Listener
3.5 Enable Network Services in Oracle Database 11g:
- Failing to grant these privileges results in issues with:
a. Sending outbound mail in Oracle Application Express.Users can call methods from the APEX_MAIL package, but issues arise when sending outbound email.
b. Using Web services in Oracle Application Express.
c. PDF/report printing.
d. Searching for content in online Help (that is, using the Find link).
3.6 Granting Connect Privileges
The following example demonstrates how to grant connect privileges to any host for the APEX_040000 database user[as sysdba]:
-----------------------BEGIN SCRIPT-------------------------------------
DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040000
-- the "connect" privilege if APEX_040000 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
-- Before checking the privilege, ensure that the ACL is valid
-- (for example, does not contain stale references to dropped users).
-- If it does, the following exception will be raised:
--
-- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
--
SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;
DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'APEX_040000', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
-----------------------------END SCRIPT----------------------------------
3.7 Enable Indexing of Online Help in Oracle Database 11gR2 and Higher:
- Run the following command:
SQL > conn / as sysdba
SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';
This returns either NULL or the database role which is granted the ability to use an Oracle Text URL datastore.
- If no value is returned by above, then create a new database role as shown in the following example:
SQL> CREATE ROLE APEX_URL_DATASTORE_ROLE;
SQL> GRANT APEX_URL_DATASTORE_ROLE to APEX_040000;
- use the Oracle Text API to grant permission database role with the following statement:
SQL> EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');
4. JOB_QUEUE_PROCESSES:
4.1 JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs.
4.2 In Oracle Application Express release 4.0, transactional support and SQL scripts require jobs
4.3 use below to check and set value for job_queue_processes:
SQL> SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = [number];
5. Creating a Workspace Manually:
5.1 go to "http://hostname:port/apex/apex_admin" and login using "admin" [if will prompt for passwrod change, change it according to the rule]
5.2 Manage Workspaces > Create Workspace
- enter a workspace name and description and click Next >
- For Re-use existing schema, select No
Neter a schema name & password and quota
Click Next >
- Enter the Workspace administrator information and click Next >
- Create Workspace
6. Creating Oracle Application Express Users:6.1 go to "http://hostname:port/apex/apex_admin" and login using "admin"
6.2 create user > enter necessary info > create
7. Logging in to Your Workspace:
7.1 goto "http://hostname:port/apex"
7.2 enter the following:
Workspace field - Enter the name of your workspace
Username field - Enter your user name
Password field - Enter your case-sensitive password
7.3 login
Saturday, June 4, 2011
ORA-00020
Last week we confronted ORA-00020. One of our development team complained that, whenever they were trying to connect to the DB (using JDBC) they were getting "ORA-12537: TNS: connection closed".
At the beginning we thought that it should be a problem related to network connectivity.
At first, we looked at the alert log and found several "ORA-00020", then tried to connect to the DB locally "as sysdba" got the error:
SQL> conn / as sysdba
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
At this point oracle had reached to maximum limit for processes and was unable to create a new server process to serve a new session. But this error [ORA-00020] is visible only form a local connection [bypassing listener] like above and using connections via listener may show TNS error like "ORA-12537".
We also, have to remember that this is not an ORA-00018 [maximum session] error and there is no one to one relationship between number of session and number of process. But opening too many sessions from an application may be one of the causes of "ORA-00020".
In order to solve the issue we followed below steps:
- If there is any existing connection with privilege to kill oracle session then use it to kill some sessions
- If above is not true then follow below:
1. from OS use below commane to find oracle processes serving oracle DB sessions:
$ ps -ef|grep "oracle[DB_SID] (LOCAL=NO)" [ example "oracleORCL (LOCAL=NO)" for SID with ORCL ]
2. $kill - 9 some_process_id_from_above to reduce number of oracle processes to below limit, so that a new connection can be made.
3. Immediately try "conn / as sysdba"
4. Find the application/user, which may induced the problem by opening too many connections using below query:
"select count(*),username,program from v$session group by username,program"
5. Then do necessary action [ like kill session from DB,increase value parameter "process" etc]
In our case, we have found one application opening too many connections.
For more information please below:
MOS NOTE ID: 825045.1, ID 316916.1
http://www.experts-exchange.com/Database/Oracle/Q_20015348.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:4026792891308559::::P11_QUESTION_ID:5671284058977
At the beginning we thought that it should be a problem related to network connectivity.
At first, we looked at the alert log and found several "ORA-00020", then tried to connect to the DB locally "as sysdba" got the error:
SQL> conn / as sysdba
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
At this point oracle had reached to maximum limit for processes and was unable to create a new server process to serve a new session. But this error [ORA-00020] is visible only form a local connection [bypassing listener] like above and using connections via listener may show TNS error like "ORA-12537".
We also, have to remember that this is not an ORA-00018 [maximum session] error and there is no one to one relationship between number of session and number of process. But opening too many sessions from an application may be one of the causes of "ORA-00020".
In order to solve the issue we followed below steps:
- If there is any existing connection with privilege to kill oracle session then use it to kill some sessions
- If above is not true then follow below:
1. from OS use below commane to find oracle processes serving oracle DB sessions:
$ ps -ef|grep "oracle[DB_SID] (LOCAL=NO)" [ example "oracleORCL (LOCAL=NO)" for SID with ORCL ]
2. $kill - 9 some_process_id_from_above to reduce number of oracle processes to below limit, so that a new connection can be made.
3. Immediately try "conn / as sysdba"
4. Find the application/user, which may induced the problem by opening too many connections using below query:
"select count(*),username,program from v$session group by username,program"
5. Then do necessary action [ like kill session from DB,increase value parameter "process" etc]
In our case, we have found one application opening too many connections.
For more information please below:
MOS NOTE ID: 825045.1, ID 316916.1
http://www.experts-exchange.com/Database/Oracle/Q_20015348.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:4026792891308559::::P11_QUESTION_ID:5671284058977
Thursday, June 2, 2011
Using Partition Exchange in Datawarehouse Environment
Our business needs:
- current data (3 months) should be on faster filesystem [RAID 1+0]
- older than 2 months should be on slower filesystem RAID 5
- older than 1 year data should be taken to Tape
So we have planed as below:
1. Current data should be kept on a Partitioned table [daily ranged partition]
So, that - each day partition will not going to contain garbage data[other day data]
- ETL processing and application design becomes simple
- tables can be managed easily.
2. Daily partition should be created on a seperate tablespace [or 10 day data will be kept on single tablespace]
3. Older partitions will be converted to stand alone tables and converted to TTS along with their tablespaces
4. Older partitions will be dropped from the current partitioned table
5. Newly created TTS with converted standalone tables will be removed from the PROD DB and moved to the slower filesystem
6. Finally these standalone tables will be reattached to the ARCHIVE database [as standalone table in partitioned view or as a partition of a table]
We have several indexes on the current partitioned table and we wanted to check the scrnerios specially related to converting a partition to a table.
So, we went through below steps:
1. create a date ranged partition
CREATE TABLE part_tab (id NUMBER, names varchar2(10) ,dates DATE)
PARTITION BY RANGE (dates)
(PARTITION PART_01_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-02', 'YYYY-MM-DD')),
PARTITION PART_02_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-03', 'YYYY-MM-DD')),
PARTITION PART_03_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-04', 'YYYY-MM-DD')),
PARTITION PART_04_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-05', 'YYYY-MM-DD')),
PARTITION PART_05_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-06', 'YYYY-MM-DD')),
PARTITION PART_MAX VALUES LESS THAN (MAXVALUE)
);
-- create two local indexes (one unique and other normal)
create unique index idx_part_tab_unq on part_tab(id,dates) local;
create index idx_part_tab_names on part_tab(names) local;
-- create one global index
create index idx_part_tab_id on part_tab(id);
2. check the status of the patition table and its indexes
select table_name,partition_name from user_tab_partitions where table_name='PART_TAB';
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_unq');
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_names');
select table_name,index_name,status from user_indexes where index_name=upper('idx_part_tab_id');
3. inset sample data
insert into part_tab values(1,'aa','1-Jun-2011');
insert into part_tab values(2,'bb','1-Jun-2011');
insert into part_tab values(3,'cc','2-Jun-2011');
insert into part_tab values(4,'dd','2-Jun-2011');
insert into part_tab values(5,'ee','3-Jun-2011');
insert into part_tab values(6,'ff','3-Jun-2011');
insert into part_tab values(7,'gg','4-Jun-2011');
insert into part_tab values(8,'hh','4-Jun-2011');
insert into part_tab values(9,'ii','5-Jun-2011');
insert into part_tab values(10,'jj','5-Jun-2011');
commit;
--check data , there should be 2 rows in each partition
select * from part_tab partition(PART_01_JUN_2011);
select * from part_tab partition(PART_02_JUN_2011);
select * from part_tab partition(PART_03_JUN_2011);
select * from part_tab partition(PART_04_JUN_2011);
select * from part_tab partition(PART_05_JUN_2011);
4. Now create the target non partitioned table with same structure and indexes [similar indexes like all local indexes, no global indexes and no extra indexes]
create table TAB_01_JUN_2011 as select * from part_tab partition(PART_01_JUN_2011) where 1=2;
create unique index idx_TAB_01_JUN_2011_unq on TAB_01_JUN_2011(id,dates);
create index idx_TAB_01_JUN_2011_names on TAB_01_JUN_2011(names);
5. check target tables indexes
select table_name,index_name,status from user_indexes where index_name in('IDX_TAB_01_JUN_2011_UNQ','IDX_TAB_01_JUN_2011_NAMES');
6. check target tables data [should be empty]
select * from TAB_01_JUN_2011;
7. now exchange the partition
ALTER TABLE part_tab
EXCHANGE PARTITION PART_01_JUN_2011 WITH TABLE TAB_01_JUN_2011
INCLUDING INDEXES
WITHOUT VALIDATION
update global indexes;
8. Now check the source partition[should be empty now]
select * from part_tab partition(PART_01_JUN_2011);
9. now check the target table [should have all the rows of the source partition]
select * from TAB_01_JUN_2011;
10. check whether indexes are ok on both
insert into TAB_01_JUN_2011 select * from TAB_01_JUN_2011; --ORA-00001, means unique index is ok
insert into part_tab select * from part_tab;--ORA-00001, means unique index is ok
select * from part_tab where names='cc';--no error and plan show that it is using index
select * from TAB_01_JUN_2011 where names='aa';--no error and plan show that it is using index
select * from part_tab where id=8;--no error and plan show that it is using index
11. check the status of partitioned table & indexes and target table and indexes
select table_name,partition_name from user_tab_partitions where table_name='PART_TAB';
select partition_name from user_ind_partitions where index_name=upper('idx_part_tab_unq');
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_names');
select table_name,index_name,status from user_indexes where index_name=upper('idx_part_tab_id');
select table_name,index_name,status from user_indexes where index_name in('IDX_TAB_01_JUN_2011_UNQ','IDX_TAB_01_JUN_2011_NAMES');
12. alter table part_tab drop partition PART_01_JUN_2011 update global indexes;
select test_num_rows from dual;
13. now check whether any proceudre,function or other objects becomes invalid
--if soem object becomes invalid, create necessary scripts to recompile just it after partition exchange;
select object_name,object_type,status from user_objects where status<>'VALID';
14. Tables/partitons older than one year on the ARCHIVE DB may again converted to standalone tables and TTS and then table the whole TTS set to tape.
From above we have found that:
- Global index on the source partitioned table becomes invalid if we donot user update global indexes
- Objects(finctions & procedures) referencing to the target partitioned table becomes invalid
So, at the end we decided to go according to the plan with below conditions:
- There should not be any global index and "update global indexes" is a time consuming operation for a large table
- There should not be any object which directly references to partitions current partitioned tables
- current data (3 months) should be on faster filesystem [RAID 1+0]
- older than 2 months should be on slower filesystem RAID 5
- older than 1 year data should be taken to Tape
So we have planed as below:
1. Current data should be kept on a Partitioned table [daily ranged partition]
So, that - each day partition will not going to contain garbage data[other day data]
- ETL processing and application design becomes simple
- tables can be managed easily.
2. Daily partition should be created on a seperate tablespace [or 10 day data will be kept on single tablespace]
3. Older partitions will be converted to stand alone tables and converted to TTS along with their tablespaces
4. Older partitions will be dropped from the current partitioned table
5. Newly created TTS with converted standalone tables will be removed from the PROD DB and moved to the slower filesystem
6. Finally these standalone tables will be reattached to the ARCHIVE database [as standalone table in partitioned view or as a partition of a table]
We have several indexes on the current partitioned table and we wanted to check the scrnerios specially related to converting a partition to a table.
So, we went through below steps:
1. create a date ranged partition
CREATE TABLE part_tab (id NUMBER, names varchar2(10) ,dates DATE)
PARTITION BY RANGE (dates)
(PARTITION PART_01_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-02', 'YYYY-MM-DD')),
PARTITION PART_02_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-03', 'YYYY-MM-DD')),
PARTITION PART_03_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-04', 'YYYY-MM-DD')),
PARTITION PART_04_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-05', 'YYYY-MM-DD')),
PARTITION PART_05_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-06', 'YYYY-MM-DD')),
PARTITION PART_MAX VALUES LESS THAN (MAXVALUE)
);
-- create two local indexes (one unique and other normal)
create unique index idx_part_tab_unq on part_tab(id,dates) local;
create index idx_part_tab_names on part_tab(names) local;
-- create one global index
create index idx_part_tab_id on part_tab(id);
2. check the status of the patition table and its indexes
select table_name,partition_name from user_tab_partitions where table_name='PART_TAB';
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_unq');
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_names');
select table_name,index_name,status from user_indexes where index_name=upper('idx_part_tab_id');
3. inset sample data
insert into part_tab values(1,'aa','1-Jun-2011');
insert into part_tab values(2,'bb','1-Jun-2011');
insert into part_tab values(3,'cc','2-Jun-2011');
insert into part_tab values(4,'dd','2-Jun-2011');
insert into part_tab values(5,'ee','3-Jun-2011');
insert into part_tab values(6,'ff','3-Jun-2011');
insert into part_tab values(7,'gg','4-Jun-2011');
insert into part_tab values(8,'hh','4-Jun-2011');
insert into part_tab values(9,'ii','5-Jun-2011');
insert into part_tab values(10,'jj','5-Jun-2011');
commit;
--check data , there should be 2 rows in each partition
select * from part_tab partition(PART_01_JUN_2011);
select * from part_tab partition(PART_02_JUN_2011);
select * from part_tab partition(PART_03_JUN_2011);
select * from part_tab partition(PART_04_JUN_2011);
select * from part_tab partition(PART_05_JUN_2011);
4. Now create the target non partitioned table with same structure and indexes [similar indexes like all local indexes, no global indexes and no extra indexes]
create table TAB_01_JUN_2011 as select * from part_tab partition(PART_01_JUN_2011) where 1=2;
create unique index idx_TAB_01_JUN_2011_unq on TAB_01_JUN_2011(id,dates);
create index idx_TAB_01_JUN_2011_names on TAB_01_JUN_2011(names);
5. check target tables indexes
select table_name,index_name,status from user_indexes where index_name in('IDX_TAB_01_JUN_2011_UNQ','IDX_TAB_01_JUN_2011_NAMES');
6. check target tables data [should be empty]
select * from TAB_01_JUN_2011;
7. now exchange the partition
ALTER TABLE part_tab
EXCHANGE PARTITION PART_01_JUN_2011 WITH TABLE TAB_01_JUN_2011
INCLUDING INDEXES
WITHOUT VALIDATION
update global indexes;
8. Now check the source partition[should be empty now]
select * from part_tab partition(PART_01_JUN_2011);
9. now check the target table [should have all the rows of the source partition]
select * from TAB_01_JUN_2011;
10. check whether indexes are ok on both
insert into TAB_01_JUN_2011 select * from TAB_01_JUN_2011; --ORA-00001, means unique index is ok
insert into part_tab select * from part_tab;--ORA-00001, means unique index is ok
select * from part_tab where names='cc';--no error and plan show that it is using index
select * from TAB_01_JUN_2011 where names='aa';--no error and plan show that it is using index
select * from part_tab where id=8;--no error and plan show that it is using index
11. check the status of partitioned table & indexes and target table and indexes
select table_name,partition_name from user_tab_partitions where table_name='PART_TAB';
select partition_name from user_ind_partitions where index_name=upper('idx_part_tab_unq');
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_names');
select table_name,index_name,status from user_indexes where index_name=upper('idx_part_tab_id');
select table_name,index_name,status from user_indexes where index_name in('IDX_TAB_01_JUN_2011_UNQ','IDX_TAB_01_JUN_2011_NAMES');
12. alter table part_tab drop partition PART_01_JUN_2011 update global indexes;
select test_num_rows from dual;
13. now check whether any proceudre,function or other objects becomes invalid
--if soem object becomes invalid, create necessary scripts to recompile just it after partition exchange;
select object_name,object_type,status from user_objects where status<>'VALID';
14. Tables/partitons older than one year on the ARCHIVE DB may again converted to standalone tables and TTS and then table the whole TTS set to tape.
From above we have found that:
- Global index on the source partitioned table becomes invalid if we donot user update global indexes
- Objects(finctions & procedures) referencing to the target partitioned table becomes invalid
So, at the end we decided to go according to the plan with below conditions:
- There should not be any global index and "update global indexes" is a time consuming operation for a large table
- There should not be any object which directly references to partitions current partitioned tables
Subscribe to:
Posts (Atom)