Alert log

The alert log file (also referred to as the ALERT.LOG) is a chronological log of messages and errors written out by an Oracle Database. Typical messages found in this file is: database startup, shutdown, log switches, space errors, etc. This file should constantly be monitored to detect unexpected messages and corruptions.Oracle will automatically create a new alert log file whenever the old one is deleted.

Oracle 11g has introduced many new features for database monitoring and administration. This month, we will examine the new interface for monitoring database events within the Oracle 11g database release. In previous releases of Oracle database, there was the alert.log text based file that contains all database activity of use to the vigilant DBA. The alert.log file contains error messages and major database activities and operations which are monitored by the DBA as part of daily maintenance. However, this alert.log file disappears with Oracle 11g and is now replaced by an XML file called the log.xml file.

Oracle 11g also introduces a new command line utility called the ADR Command Interface or ADRCI to review the contents of the log.xml file. By default, the ADR tool is located under the Oracle 11g directory ORACLE_BASE/diag/rdbms/SID/SID/alert
In order to use the ADR tool, the command adrci is issued with Oracle 11g.

Location of the ALERT.LOG file

Oracle will write the alert.log file to the directory as specified by the BACKGROUND_DUMP_DEST parameter. If this parameter is not set, the alert.log will be created in the ORACLE_HOME/rdbms/trace directory.

SQL> show parameter BACKGROUND_DUMP_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string     /u01/app/oracle/diag/rdbms/o11gr1/
                                                 o11gr1/trace

Am sure it will work in 11g, that parameter has been around for a long time.
Seems like the correct way to find it to me.
If the background_dump_dest parameter isn't set, the alert.log will be put in $ORACLE_HOME/RDBMS/trace
The default location where the log.xml file is located on the test Oracle 11g Red Hat Linux server in this example:
/u01/app/oracle/diag/rdbms/ora11g/ORA11G/alert

Writing to the ALERT.LOG file

Users can write messages to the alert.log file. Example:

 -- Write message to alert.log
 exec dbms_system.ksdwrt(2, 'Look Ma, I can write to the alert.log file!');
 PL/SQL procedure successfully completed.

 -- Flush the buffer
 exec dbms_system.ksdfls;
 PL/SQL procedure successfully completed.

Check Oracle Applications product status

How to check if certain Oracle Applications product/module is implemented?

 

I. Identify product short name.Each Oracle Applications product/module has short name which you should use to check if product is implemented.

To identify product short name you can use Metalink.
Go to https://metalink.oracle.com

1. Click on Patches & Updates.
2. Click on Advanced Search.
3. Under Applications search options enter Product or Product Family

a) Click to search and select
b) Search: E-Business SuiteEnter part of product/module name and put '%' from both sides
ex: %receivables%
c) Click 'Go'
Product Name received is:
Receivables (ar)
where 'ar' is the product short name that you need.

II. Check if product is implemented/installed:

You can use one of the following methods to check if certain Oracle Applications product/module is implemented.

Method 1:

From the output of script adutconf.sql.
a) Source the applications environment file for Admin node
b) cd $AD_TOP/sql/
c) Execute:
sqlplus apps/ @adutconf.sql
File adutconf.ls will be created in directory $AD_TOP/sql.
Check product status by product short name.

Example:

--> Product Installation Status, Version Info and Patch Level

Product Appl Status Version Patchset Level
ABM Inactive 11.5.0 11i.ABM.F
AD Shared 11.5.0 11i.AD.I.5
AP Installed 11.5.0 11i.AP.K
Implemented product has status 'Installed'. 'Inactive' product is not fully implemented and not used.Please note that products with status 'Shared' are not fully implemented and patching these products isn't required.

Method 2:

Use Oracle Applications Manager (OAM).
Connect to http://.:/servlets/weboam/oam/oamLogin
a. Click 'Site Map'.
b. Under 'System Configuration' choose 'License Manager'.
c. Go to 'Products' link.d. Click ' Licensed Products' under 'Reports' section.

You can use filter for certain product.
Filter: Product Abbreviation is AR
The value in status column shows product installation status.

Method 3:

Use sql query.

select fa.APPLICATION_SHORT_NAME, fpi.PATCH_LEVEL, DECODE(fpi.STATUS, 'I','Installed','S','Shared', 'N', 'Inactive', fpi.STATUS) Status , fpi.DB_STATUSfrom fnd_product_installations fpi,FND_APPLICATION fawhere fpi.APPLICATION_ID in (select APPLICATION_ID from FND_APPLICATION where APPLICATION_SHORT_NAME like '')and fa.APPLICATION_ID=fpi.APPLICATION_ID;


where is the product short name

Example How to check if Knowledge Management (CSK), iSupport (IBU), Business Intelligence(BIS) areimplemented?

sqlplus apps/password

sql> select fa.APPLICATION_SHORT_NAME, fpi.PATCH_LEVEL, DECODE(fpi.STATUS, 'I','Installed','S','Shared', 'N', 'Inactive', fpi.STATUS) Status , fpi.DB_STATUSfrom fnd_product_installations fpi,FND_APPLICATION fawhere fpi.APPLICATION_ID in (select APPLICATION_ID from FND_APPLICATION where APPLICATION_SHORT_NAME in ('CSK','IBU','BIS'))and fa.APPLICATION_ID=fpi.APPLICATION_ID;

if any of the products is implemented status should be Installed.

Method 4:

Use script patchsets.sh which is documented in Note 139684.1 Oracle Applications Current Patchset Comparison Utility - patchsets.sh.
If you have any errors when running script patchsets.sh refer to the following note to:
resolve the issue:Note 314442.1 Cannot run Patchsets.Sh Script

crsctl start crs Vs start cluster

CRSCTL utility is used to manage oracle Clusterware.

Starting Clusterware


In 11gR2, you can start the High Availability Service Daemon (OHASD) and Clusterware stack in a single step (A) or start it in a two step process(B).

(A) To start the OHASD & clusterware stack in a single step, use

$ crsctl start crs

Note that you can start components on the local server only with this.

(B) You can do this in two steps; starting OHASD and then starting Clusterware stack. The advantage of using this method is that, if OHASD is running, you can start the clusterware stack on any node from local node.

You can start OHASD by,

$ crsctl start has

then, you can start the clusterware stack by,

$ crsctl start cluster -all

Syntax

crsctl start cluster [-all | -n server_name [...]]

-all Start clusterware on all nodes
-n Start clusterware on particular nodes

If -all or -n is note used, then the clusterware is started only on the local node.

Stopping Clusterware


In 11gR2, you can stop the High Availability Service Daemon (OHASD) and Clusterware stack in a single step (A) or stop it in a two step process(B).

(A) To stop the OHASD & clusterware stack in a single step, use 
$ crsctl stop crs

If any of the process managed by oracle Clusterware is still running after executing it, then the command fails. Use the force option in such cases to terminate them unconditionally.

$ crsctl stop crs –all –f

Note that you can stop components on the local server only with this.

(B) You can stop processes in two steps; stoping OHASD and then stoping Clusterware stack. The advantage of using this method is that, you can stop the clusterware stack on any node from local node.

You can stop the clusterware stack by,

$ crsctl stop cluster -all

Syntax

crsctl stop cluster [-all | -n server_name [...]] [-f]

-all Stop clusterware on all nodes
-n Stop clusterware on particular nodes
-f Use this to stop clusterware resources unconditionally if 'crsctl stop cluster' fails to stop.

If -all or -n is note used, then the clusterware is stopped only on the local node.

Once the clusterware stack is down then you can stop OHASD by,

$ crsctl stop has

The advantage of crsctl stop cluster compared to stop crs command is that it prevent the relocation of 'certain' resources to other servers in the cluster before the Clusterware is stopped on that server.

Status

[root@rac2 ~]# crsctl status server -f
NAME=rac1
STATE=ONLINE
ACTIVE_POOLS=Generic ora.RACDB
STATE_DETAILS=

NAME=rac2
STATE=ONLINE
ACTIVE_POOLS=Generic ora.RACDB
STATE_DETAILS=AUTOSTARTING RESOURCES

[root@rac2 ~]# 

Tablespace Point-In-Time Recovery

RMAN – Tablespace Point-In-Time Recovery

Tablespace Point-In-Time Recovery (tbspitr) is kind of cumbersome RMAN recovery to perform. There are basically three methods by which tbspitr can be achieved. Below expounded is a easiest of them, "fully automated" point in time recovery task, to bring home the basics.


You can play around with the variables for more control of the process in the other two methods of tbspitr with the risk of added complexity

There are various conditions to be met to perform a successful tbspitr. RMAN - Backup and Recovery User Guide is the best source for information on that. 

The database here uses filesystem to store datafiles. Using ASM would not make any difference in the activity.

Step 1: Create tablespace

Create a sample tablespace that can be used for this task. 

CREATE TABLESPACE tbspit DATAFILE
'/home/oracle/rprod/data/rprod/tbspit01.dbf' SIZE 250M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
Step 2: Create user to hold data in this tablespace

SQL> Create user test identified by test123 default tablespace tbspit temporary tablespace temp;

Step 3: Create data to recover

Create a table to store data that we will use during point in time recovery.

SQL> Create table t1 (col1 number, col2 varchar2(20)) tablespace tbspitr;

Insert data into the table t1.

SQL> Conn test/test123

SQL> create sequence seq1; 

Begin
for i in 1..50 loop
insert into t1
select seq1.nextval, to_char(sysdate, 'dd-mon-yy hh24:mi:ss') from dual;
commit;
dbms_lock.sleep(57);
end loop;
end;
/
SQL> select * from t1;
COL1 COL2
---------- --------------------
...
32 23-mar-11 20:23:30
33 23-mar-11 20:24:27 ------ We will recover here.
34 23-mar-11 20:25:24
35 23-mar-11 20:26:21
36 23-mar-11 20:27:18
37 23-mar-11 20:28:15
38 23-mar-11 20:29:12
39 23-mar-11 20:30:09
40 23-mar-11 20:31:06
...
Step 4: Database structure

RMAN> report schema;
Report of database schema for database with db_unique_name RPROD
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM YES /home/oracle/rprod/data/rprod/system01.dbf
2 520 SYSAUX NO /home/oracle/rprod/data/rprod/sysaux01.dbf
3 85 UNDOTBS1 YES /home/oracle/rprod/data/rprod/undotbs01.dbf
4 5 USERS NO /home/oracle/rprod/data/rprod/users01.dbf
5 100 EXAMPLE NO /home/oracle/rprod/data/rprod/example01.dbf
6 250 TBSPIT NO /home/oracle/rprod/data/rprod/tbspit01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/rprod/data/rprod/temp01.dbf

RMAN>

Step 5: Check Tablespace Recoverability

SQL> begin
2 dbms_tts.transport_set_check('TBSPIT',TRUE,TRUE);
3 end;
4 /

PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;

no rows selected

Step 6: Perform Tablespace Point-In-Time Recovery.

Note that you do not allocate maintenance channels and do not connect to auxillary instance.Also database has to be up and accessible when tbspitr is kicked off

The activities that happens upon kicking off the tbspitr rman commands are as listed below:

*Create auxiliary instance – pvqe with db_create_file_dest set to the location we provide on the rman script.
*Reboots the target instance - RPROD
*Run TRANSPORT_SET_CHECK on recovery set tablespaces (here TBSPIT) to check dependency. (We will manually check this before starting tbspitr lest it fails at this point).
*Restore the controlfile and mount the clone database in auxilary location
*Restore SYSTEM, SYSAUX, UNDO datafiles at the auxillary destination.
*Restore TBSPIT datafile at target database (original) destination (RPROD) itself.
*Switch SYSTEM, SYSAUX & UNDO datafiles to clone copy.
*Bring the datafiles of these clone tablespaces online.
*Recover SYSTEM, SYSAUX, UNDO & TBSPIT clone tablespaces.
*Open resetlogs clone database.
*Make TBSPIT tablespace read-only
*Create directory for data pump as the same auxdest location.
*Do a transportable tablespace (Metadata) datapump export
*Shutdown clone database.
*Drop tablespace TBSPIT including contents. Keep datafile (for transport import)
*Do metadata datapump import.
*Make tablespace TBSPIT READ WRITE
*Take tablespace TBSPIT offline.
*Clean up the clone instance – remove restored datafiles, onlinelogs and controlfiles.

Starting RMAN TBSPITR

RMAN> shutdown immediate; -- This is just to show what happens if the db is down.
RMAN> startup mount; 

RMAN> run{
recover tablespace tbspit
until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')"
auxiliary destination '/home/oracle/auxdest';
}

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/23/2011 20:52:54
RMAN-05010: target database must be opened in READ WRITE mode for Tablespace Point-in-Time Recovery

As said above, the database has to be open and tablespace accessible for tbspitr.

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN> run{
recover tablespace tbspit
until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')"
auxiliary destination '/home/oracle/auxdest';
}
2> 3> 4> 5>
Starting recover at 23-MAR-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

Creating automatic instance, with SID='pvqe'

initialization parameters used for automatic instance:
db_name=RPROD
db_unique_name=pvqe_tspitr_RPROD
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/home/oracle/auxdest
log_archive_dest_1='location=/home/oracle/auxdest'
#No auxiliary parameter file used

starting up automatic instance RPROD

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2212736 bytes
Variable Size 100666496 bytes
Database Buffers 184549376 bytes
Redo Buffers 4849664 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog
resync catalog;
}
executing Memory Script
executing command: SET until clause

Starting restore at 23-MAR-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=81 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=5 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=30 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=56 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/rprod/flash/RPROD/autobackup/2011_03_23/o1_mf_s_746568895_6ro37z65_.bkp
channel ORA_AUX_DISK_1: piece handle=/home/oracle/rprod/flash/RPROD/autobackup/2011_03_23/o1_mf_s_746568895_6ro37z65_.bkp tag=TAG20110323T201455
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/auxdest/RPROD/controlfile/o1_mf_6ro5nbcq_.ctl
Finished restore at 23-MAR-11

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# set requested point in time
set until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')";
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'TBSPIT' ||' offline immediate';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 6 to
"/home/oracle/rprod/data/rprod/tbspit01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 6;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TBSPIT offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/auxdest/RPROD/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 23-MAR-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /home/oracle/rprod/data/rprod/tbspit01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/rprodbackup/05m7vf4b_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to /home/oracle/auxdest/RPROD/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: restoring datafile 00003 to /home/oracle/auxdest/RPROD/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /home/oracle/rprodbackup/03m7vf4b_1_1
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00002 to /home/oracle/auxdest/RPROD/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /home/oracle/rprodbackup/04m7vf4b_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/rprodbackup/05m7vf4b_1_1 tag=23MAR2011_BEFORE_BCKP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_3: piece handle=/home/oracle/rprodbackup/04m7vf4b_1_1 tag=23MAR2011_BEFORE_BCKP
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_2: piece handle=/home/oracle/rprodbackup/03m7vf4b_1_1 tag=23MAR2011_BEFORE_BCKP
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:25
Finished restore at 23-MAR-11

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=746571371 file name=/home/oracle/auxdest/RPROD/datafile/o1_mf_system_6ro5nl12_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=746571371 file name=/home/oracle/auxdest/RPROD/datafile/o1_mf_undotbs1_6ro5nl5q_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=746571371 file name=/home/oracle/auxdest/RPROD/datafile/o1_mf_sysaux_6ro5nl1c_.dbf

contents of Memory Script:
{
# set requested point in time
set until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 6 online";
# recover and open resetlogs
recover clone database tablespace "TBSPIT", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 6 online

Starting recover at 23-MAR-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery

archived log for thread 1 with sequence 20 is already on disk as file /home/oracle/rprod/archive/1_20_745790363.dbf
archived log for thread 1 with sequence 21 is already on disk as file /home/oracle/rprod/archive/1_21_745790363.dbf
archived log for thread 1 with sequence 22 is already on disk as file /home/oracle/rprod/archive/1_22_745790363.dbf
archived log file name=/home/oracle/rprod/archive/1_20_745790363.dbf thread=1 sequence=20
archived log file name=/home/oracle/rprod/archive/1_21_745790363.dbf thread=1 sequence=21
archived log file name=/home/oracle/rprod/archive/1_22_745790363.dbf thread=1 sequence=22
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-MAR-11

database opened

contents of Memory Script: 
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace TBSPIT read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/auxdest''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/auxdest''";
}
executing Memory Script

sql statement: alter tablespace TBSPIT read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/auxdest''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/auxdest''

Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_pvqe":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_pvqe" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_pvqe is:
EXPDP> /home/oracle/auxdest/tspitr_pvqe_91264.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TBSPIT:
EXPDP> /home/oracle/rprod/data/rprod/tbspit01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_pvqe" successfully completed at 20:56:57
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace TBSPIT including contents keep datafiles';
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

sql statement: drop tablespace TBSPIT including contents keep datafiles

Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_pvqe" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_pvqe":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_pvqe" successfully completed at 20:57:10
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace TBSPIT read write';
sql 'alter tablespace TBSPIT offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

sql statement: alter tablespace TBSPIT read write

sql statement: alter tablespace TBSPIT offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/auxdest/RPROD/datafile/o1_mf_temp_6ro5oh04_.tmp deleted
auxiliary instance file /home/oracle/auxdest/RPROD/onlinelog/o1_mf_3_6ro5og8j_.log deleted
auxiliary instance file /home/oracle/auxdest/RPROD/onlinelog/o1_mf_2_6ro5ofov_.log deleted
auxiliary instance file /home/oracle/auxdest/RPROD/onlinelog/o1_mf_1_6ro5odhf_.log deleted
auxiliary instance file /home/oracle/auxdest/RPROD/datafile/o1_mf_sysaux_6ro5nl1c_.dbf deleted
auxiliary instance file /home/oracle/auxdest/RPROD/datafile/o1_mf_undotbs1_6ro5nl5q_.dbf deleted
auxiliary instance file /home/oracle/auxdest/RPROD/datafile/o1_mf_system_6ro5nl12_.dbf deleted
auxiliary instance file /home/oracle/auxdest/RPROD/controlfile/o1_mf_6ro5nbcq_.ctl deleted
Finished recover at 23-MAR-11

RMAN>

Meanwhile I was checking in another session on the auxillary instance created by the process

oracle:/home/oracle [rprod]$ pmon
grid 3911 1 0 19:45 ? 00:00:00 asm_pmon_+ASM
oracle 4492 1 0 19:49 ? 00:00:00 ora_pmon_mdiuat
oracle 6510 1 0 20:51 ? 00:00:00 ora_pmon_rprod
oracle 6661 1 0 20:53 ? 00:00:00 ora_pmon_pvqe
oracle 6703 4982 0 20:54 pts/3 00:00:00 grep pmon

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TBSPIT';

TABLESPACE_NAME STATUS
------------------------------ ---------
TBSPIT OFFLINE

Step 7: Take backup before bringing the tablespace online

RUN {
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT = '/home/oracle/rprodbackup/rprod_%U';
ALLOCATE CHANNEL ch2 DEVICE TYPE DISK FORMAT = '/home/oracle/rprodbackup/rprod_%U';
ALLOCATE CHANNEL ch3 DEVICE TYPE DISK FORMAT = '/home/oracle/rprodbackup/rprod_%U';
ALLOCATE CHANNEL ch4 DEVICE TYPE DISK FORMAT = '/home/oracle/rprodbackup/rprod_%U';
backup database tag = '23Mar11_After_tbspitr' include current controlfile;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup archivelog all;
BACKUP CURRENT CONTROLFILE tag = '23Mar11_aft_After_tbspitr_bkup';
}

SQL> alter tablespace tbspit online;

Tablespace altered.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TBSPIT';

TABLESPACE_NAME STATUS
------------------------------ ---------
TBSPIT ONLINE

Step 8: Verify TBSPITR

Check to see if the data in the table is exactly as we expect it to be, which is -> until time "to_date('23-mar-11 20:24:27','dd-mon-rr hh24:mi:ss')"

SQL> select * from t1;

COL1 COL2
---------- --------------------
2 21-mar-11 18:40:06
3 21-mar-11 18:42:03
4 21-mar-11 18:44:00
5 21-mar-11 18:45:57
6 21-mar-11 18:47:54
7 21-mar-11 18:49:51
8 21-mar-11 18:51:48
9 21-mar-11 18:53:45
10 21-mar-11 18:55:42
11 21-mar-11 18:57:39
12 21-mar-11 18:59:36
13 21-mar-11 19:01:33
14 21-mar-11 19:03:30
15 21-mar-11 19:05:27
16 21-mar-11 19:07:25
17 21-mar-11 19:21:17
18 21-mar-11 19:24:34
19 21-mar-11 19:27:51
20 21-mar-11 19:31:09
21 21-mar-11 19:34:26
22 21-mar-11 19:37:43
23 21-mar-11 19:41:00
24 21-mar-11 19:44:17
25 21-mar-11 19:47:34
26 21-mar-11 19:50:51
27 21-mar-11 19:54:08
28 21-mar-11 19:57:25
29 21-mar-11 20:00:42
30 21-mar-11 20:03:59
31 21-mar-11 20:07:16
32 23-mar-11 20:23:30

31 rows selected.

It can be seen that it does not include the time that we used to recover,

Free download E-Book

Oracle database 11g book download link

To view / download the file, simply click on the link below

Link: http://www.ziddu.com/download/22870378/Oracle_Database_11g_DBA_book.pdf.html


 To view / download the file, simply click on the link below

Link: http://www.ziddu.com/download/22870198/11gpdf.zip.html

Oracle Dumps

 
To view / download the file, simply click on the link below

Link: http://www.ziddu.com/download/22869626/oracle_OCA_dump_1Z0_051.pdf.html

To view / download the file, simply click on the link below

Link: http://www.ziddu.com/download/22869734/oracle_OCA_dump_1Z0_052.pdf.html

SQL Book

To view / download the file, simply click on the link below

Link: http://www.ziddu.com/download/22870311/sqlbook.zip.html

PLSQL Book

Use the below Link(s) to download the file.

http://www.ziddu.com/download/22869495/PLSQL_Book.pdf.html  

Linux Book

To view / download the file, simply click on the link below

Link: http://www.ziddu.com/download/22870188/linuxbook.zip.html

 

HOW TO SETUP .ORAENV FILE

HOW TO SETUP .ORAENV:

1. If you are creating a database manually you have to put the entry in ORATAB file. If you are creating database using DBCA then the database entry will automatically filed in ORATAB file.

2. Location of ORATAB FILE :
$ Cd /etc
$ Vi oratab
 
3. Then edit the oratab file. It will look like below:
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
*:/u01/app/oracle/product/9.2.0:N
TSH1:/u01/app/oracle/product/9.2.0:N
juli:/u01/app/oracle/product/9.2.0:N
orcl:/u01/app/oracle/product/10.2.0/db_1:N
+ASM:/u01/app/oracle/product/10.2.0/db_1:N
rmanrep:/u01/app/oracle/product/10.2.0/db_1:N
mom:/u01/app/oracle/product/10.2.0/db_1:N
preetha:/u01/app/oracle/product/10.2.0/db_1:N
4. Then run the .bash_profile ----- > . .bash_profile
5. Then run . oraenv
6. Then enter the name of the database you wanna open

Below is the output for your statements.

$ echo $ORACLE_SID
Sonu
oracle@sujeet{Primary}:Sonu:/home/oracle

$ echo $ORACLE_HOME

oracle@sujeet{Primary}:Sonu:/home/oracle 

$ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin:/usr/bin

 oracle@sujeet{Primary}:Sonu:/home/oracle

Here is the output for the echo $ORACLE_HOME after running . oraenv file. 

$ . oraenv
ORACLE_SID = [Sonu] ? test
oracle@sujeet{Primary}:pavs:/home/oracle

 $ echo $ORACLE_HOME
/home/oracle/oracle/product/10.2.0/db_1

 oracle@sujeet{Primary}:test:/home/oracle

 $ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin:/usr/bin:/home/oracle/oracle/product/10.2.0/db_1/bin 

oracle@sujeet{Primary}:test:/home/oracle

 $ echo $ORACLE_SID
test 

oracle@sujeet{Primary}:test:/home/oracle
$

Oracle oraenv

There are 2 Oracle Homes , One 9i and another 10g home

In 10g , there are 2 databases , db1 and db2

Now..i type
$ . oraenv

Enter ORACLE_SID : db1
(Oracle Home is automatically set)

Now i type
$ . oraenv
Enter Oracle_sid: db2
Enter Oracle_Home: (i have to manually type here the oracle home path)

How can i get Oracle Home to be automatically set like above

Delete Files Older Than x Days on Linux

The find utility on linux allows you to pass in a bunch of interesting arguments, including one to execute another command on each file. We’ll use this in order to figure out what files are older than a certain number of days, and then use the rm command to delete them.

Command Syntax

find /path/to/files* -mtime +5 -exec rm {} \;

Note that there are spaces between rm, {}, and \;

Explanation

• The first argument is the path to the files. This can be a path, a directory, or a wildcard as in the example above. I would recommend using the full path, and make sure that you run the command without the exec rm to make sure you are getting the right results.
• The second argument, -mtime, is used to specify the number of days old that the file is. If you enter +5, it will find files older than 5 days.
• The third argument, -exec, allows you to pass in a command such as rm. The {} \; at the end is required to end the command.

This should work on Ubuntu, Suse, Redhat, or pretty much any version of linux.

Remove files older than x days, filtered by files that i don't want to delete

 I need to perform routine maintenance on a server every 3 weeks, and will need to remove various files and directories. But files in one directory, i need to keep. From what i have found, this is how to remove files older than x days:
find /path/to/files* -mtime +5 -exec rm {} \;

Does this mean that only the files highlighted in red will be deleted? The server is running on SuSe 9.