Wednesday, November 19, 2008

Enabling Flashback Database

To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. Follow the process outlined here.

1. Start SQL*Plus and ensure that the database is mounted, but not open. For example:

SQL> SELECT STATUS FROM V$INSTANCE;

2. By default the flashback retention target is set to one day (1440 minutes). If you wish, you can change the retention target. For example, if you want to retain enough flashback logs to be able to perform a 72 hour flashback, set the retention target to 4320 minutes (3 days x 24 hours/day x 60 minutes/hour):

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

3. Enable the Flashback Database feature for the whole database:

SQL> ALTER DATABASE FLASHBACK ON;

By default, flashback logs are generated for all permanent tablespaces. If you wish, you can reduce overhead by disabling flashback logging specific tablespaces:

SQL> ALTER TABLESPACE test1 FLASHBACK OFF;


You can re-enable flashback logging for a tablespace later with this command:

SQL> ALTER TABLESPACE test1 FLASHBACK ON;


Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.

You can disable flashback logging for the entire database with this command:

SQL> ALTER DATABASE FLASHBACK OFF;

You can enable Flashback Database not only on a primary database, but also on a standby database. Enabling Flashback Database on a standby database allows one to perform Flashback Database on the standby database. Flashback Database of standby databases has a number of applications in the Data Guard environment.

Requirements for Flashback Database

The requirements for enabling Flashback Database are:

* Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
* You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
* For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.

Oracle Flashback Table: Returning Individual Tables to Past States

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring the DBA to find and restore application-specific properties. Using Flashback Table causes the contents of one or more individual tables to revert to their state at some past SCN or time.

Flashback Table uses information in the undo tablespace to restore the table. This provides significant benefits over media recovery in terms of ease of use, availability and faster restoration of data.

Prerequisites for Using Flashback Table

The prerequisites for performing a FLASHBACK TABLE operation are as follows:

* You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
* You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
* Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation.
* Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:

ALTER TABLE table ENABLE ROW MOVEMENT;


Performing Flashback Table

The following SQL*Plus statement performs a FLASHBACK TABLE operation on the table employee:

FLASHBACK TABLE employee TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');


The employee table is restored to its state when the database was at the time specified by the timestamp.

You can also specify the target point in time for the FLASHBACK TABLE operation using an SCN:

FLASHBACK TABLE employee TO SCN 123456;

The default for a FLASHBACK TABLE operation is for triggers on a table to be disabled. The database disables triggers for the duration of the operation, and then returns them to the state that they were in before the operation was started. If you wish for the triggers to stay enabled, then use the ENABLE TRIGGERS clause of the FLASHBACK TABLE statement, as shown in this example:

FLASHBACK TABLE t1 TO TIMESTAMP '2003-03-03 12:05:00' ENABLE TRIGGERS;

The following scenario is typical of the kind of logical corruption where Flashback Table could be used:

At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present at 14:00, the last time she ran a report. Someone accidentally deleted the record for "JOHN" between 14:00 and the present time. She uses Flashback Table to return the table to its state at 14:00, as shown in this example:

FLASHBACK TABLE EMPLOYEES TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 14:00:00','YYYY-MM-DD HH:MI:SS')
ENABLE TRIGGERS;


Source: http://www.stanford.edu

Oracle Flashback Query: Recovering at the Row Level

In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMPLOYEE table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

Querying the past state of the table is achieved using the AS OF clause of the SELECT statement. For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2003:

SELECT * FROM EMPLOYEE AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';


Restoring John's information to the table EMPLOYEE requires the following update:

INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');

The missing row is re-created with its previous contents, with minimal impact to the running database.

Oracle Flashback Technology: Overview

Oracle Flashback Technology provides a set of features that support viewing and rewinding data back and forth in time. The flashback features offer the capability to query past versions of schema objects, query historical data, analyze database changes, or perform self-service repair to recover from logical corruptions while the database is online.

* Oracle Flashback Query feature lets you specify a target time and then run queries against your database, viewing results as they would have appeared at that time. To recover from an unwanted change like an erroneous update to a table, a user could choose a target time before the error and run a query to retrieve the contents of the lost rows.
* Oracle Flashback Version Query lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start time, end time, operation, and transaction ID of the transaction that created the version. This feature can be used both to recover lost data values and to audit changes to the tables queried.
* Oracle Flashback Transaction Query lets you view changes made by a single transaction, or by all the transactions during a period of time.
* Oracle Flashback Table returns a table to its state at a previous point in time. You can restore table data while the database is online, undoing changes only to the specified table.

* Oracle Flashback Drop reverses the effects of a DROP TABLE statement.
* Oracle Flashback Database provides a more efficient alternative to database point-in-time recovery. When you use flashback database, your current datafiles revert to their contents at a past time. The result is much like the result of a point-in-time recovery using datafile backups and redo logs, but you do not have to restore datafiles from backup and you do not have to re-apply as many individual changes in the redo logs as you would have to do in conventional media recovery.

Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query all rely on undo data, records of the effects of each update to an Oracle database and values overwritten in the update. Used primarily for such purposes as providing read consistency for SQL queries and rolling back transactions, these undo records contain the information required to reconstruct data as it stood at a past time and examine the record of changes since that past time.

Saturday, November 15, 2008

Flash Back Feature..

1. How to delete a table permanently?
Usage: drop table purge
eg: drop table emp purge;

2.How to delete a table from recycle bin?
Usage: purge < table name >
eg: purge emp;

3. How to recover a table from recycle bin?
Usage: flashback table "HBKLOEKIMO$#^^&777" to before drop;
Usage1:flashback table "HBKLOEKIMO$#^^&777" to before drop rename to emp1;

4.How to remove all objects in recyclebin?
Usage: purge recyclebin

5. How to show all objects in recyclebin?
Usage: show recyclebin

Monday, November 10, 2008

Find files greater than 3 GB in HP unix

How do you find files that are greater than 3GB in HP unix?

find / -size +3000000000c -exec ls -l {} \;

Friday, November 07, 2008

Using srvctl to Manage your 10g RAC Database

Oracle recommends that RAC databases be managed with srvctl, an Oracle-supplied tool that was first introduced with 9i RAC. The 10g version of srvctl is slightly different from the 9i implementation. In this article, we will look at how -- and why -- to manage your 10g databases with srvctl.

Interacting with CRS and the OCR: srvctl

srvctl is the tool Oracle recommends that DBAs use to interact with CRS and the cluster registry. Oracle does provide several tools to interface with the cluster registry and CRS more directly, at a lower level, but these tools are deliberately undocumented and intended only for use by Oracle Support. srvctl, in contrast, is well documented and easy to use. Using other tools to modify the OCR or manage CRS without the assistance of Oracle Support runs the risk of damaging the OCR.

Using srvctl

Even if you are experienced with 9i srvctl, it's worth taking a look at this section; 9i and 10g srvctl commands are slightly different.

srvctl must be run from the $ORACLE_HOME of the RAC you are administering. The basic format of a srvctl command is

srvctl [options]

where command is one of

enable|disable|start|stop|relocate|status|add|remove|modify|getenv|setenv|unsetenv|config

and the target, or object, can be a database, instance, service, ASM instance, or the nodeapps.

The srvctl commands are summarized in this table:



As you can see, srvctl is a powerful utility with a lot of syntax to remember. Fortunately, there are only really two commands to memorize: srvctl -help displays a basic usage message, and srvctl -h displays full usage information for every possible srvctl command.

Examples

Example 1. Bring up the MYSID1 instance of the MYSID database.

[oracle@myserver oracle]$ srvctl start instance -d MYSID -i MYSID1

Example 2. Stop the MYSID database: all its instances and all its services, on all nodes.

[oracle@myserver oracle]$ srvctl stop database -d MYSID

Example 3. Stop the nodeapps on the myserver node. NB: Instances and services also stop.

[oracle@myserver oracle]$ srvctl stop nodeapps -n myserver

Example 4. Add the MYSID3 instance, which runs on the myserver node, to the MYSID
clustered database.

[oracle@myserver oracle]$ srvctl add instance -d MYSID -i MYSID3 -n myserver

Example 4. Add a new node, the mynewserver node, to a cluster.

[oracle@myserver oracle]$ srvctl add nodeapps -n mynewserver -o $ORACLE_HOME -A
149.181.201.1/255.255.255.0/eth1

(The -A flag precedes an address specification.)

Example 5. To change the VIP (virtual IP) on a RAC node, use the command

[oracle@myserver oracle]$ srvctl modify nodeapps -A new_address

Example 6. Find out whether the nodeapps on mynewserver are up.

[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserver
VIP is running on node: mynewserver
GSD is running on node: mynewserver
Listener is not running on node: mynewserver
ONS daemon is running on node: mynewserver

Example 7. Disable the ASM instance on myserver for maintenance.

[oracle@myserver oracle]$ srvctl disable asm -n myserver

Debugging srvctl

Debugging srvctl in 10g couldn't be easier. Simply set the SRVM_TRACE environment variable.

[oracle@myserver bin]$ export SRVM_TRACE=true

Let's repeat Example 6 with SRVM_TRACE set to true:

[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserver
/u01/app/oracle/product/10.1.0/jdk/jre//bin/java -classpath
/u01/app/oracle/product/10.1.0/jlib/netcfg.jar:/u01/app/oracle/product/10.1.0/jdk/jre//lib/rt.jar:
/u01/app/oracle/product/10.1.0/jdk/jre//lib/i18n.jar:/u01/app/oracle/product/10.1.0/jlib/srvm.jar:
/u01/app/oracle/product/10.1.0/jlib/srvmhas.jar:/u01/app/oracle/product/10.1.0/jlib/srvmasm.jar:
/u01/app/oracle/product/10.1.0/srvm/jlib/srvctl.jar
-DTRACING.ENABLED=true -DTRACING.LEVEL=2 oracle.ops.opsctl.OPSCTLDriver status nodeapps -n
mynewserver
[main] [19:53:31:778] [OPSCTLDriver.setInternalDebugLevel:165] tracing is true at level 2 to
file null
[main] [19:53:31:825] [OPSCTLDriver.:94] Security manager is set
[main] [19:53:31:843] [CommandLineParser.parse:157] parsing cmdline args
[main] [19:53:31:844] [CommandLineParser.parse2WordCommandOptions:900] parsing 2-word
cmdline
[main] [19:53:31:866] [GetActiveNodes.create:212] Going into GetActiveNodes constructor...
[main] [19:53:31:875] [HASContext.getInstance:191] Module init : 16
[main] [19:53:31:875] [HASContext.getInstance:216] Local Module init : 19
...
[main] [19:53:32:285] [ONS.isRunning:186] Status of ora.ganges.ons on mynewserver is true
ONS daemon is running on node: mynewserver
[oracle@myserver oracle]$

Pitfalls

A little impatience when dealing with srvctl can corrupt your OCR, ie, put it into a state where the information for a given object is inconsistent or partially missing. Specifically, the srvctl remove command provides the -f option, to allow you to force removal of an object from the OCR. Use this option judiciously, as it can easily put the OCR into an inconsistent state.

Restoring the OCR from an inconsistent state is best done with the assistance of Oracle Support, who will guide you in using the undocumented $CRS_HOME/bin/crs_* tools to repair it. The OCR can also be restored from backup.

Error messages

srvctl errors are PRK% errors, which are not documented in the 10gR1 error messages manual. However, for those with a Metalink account, they are documented on Metalink here.

Conclusion

srvctl is a powerful tool that will allow you to administer your RAC easily and effectively. In addition, it provides a valuable buffer between the DBA and the OCR, making it more difficult to corrupt the OCR.

Note: Info from Natalka Roshak's blog

Cluster Ready Services and the OCR

Cluster Ready Services, or CRS, is a new feature for 10g RAC. Essentially, it is Oracle's own clusterware. On most platforms, Oracle supports vendor clusterware; in these cases, CRS interoperates with the vendor clusterware, providing high availability support and service and workload management. On Linux and Windows clusters, CRS serves as the sole clusterware. In all cases, CRS provides a standard cluster interface that is consistent across all
platforms.

CRS consists of four processes (crsd, occsd, evmd, and evmlogger) and two disks: the Oracle Cluster Registry (OCR), and the voting disk.

CRS manages the following resources:

* The ASM instances on each node
* Databases
* The instances on each node
* Oracle Services on each node
* The cluster nodes themselves, including the following processes, or "nodeapps":
o VIP
o GSD
o The listener
o The ONS daemon

CRS stores information about these resources in the OCR. If the information in the OCR for one of these resources becomes damaged or inconsistent, then CRS is no longer able to manage that resource. Fortunately, the OCR automatically backs itself up regularly and frequently.

RAC Architecture in Brief

RAC Architecture Overview

1.A cluster is a set of 2 or more machines (nodes) that share or coordinate resources to perform the same task.

2. A RAC database is 2 or more instances running on a set of clustered nodes, with all instances accessing a shared set of database files.

3.Depending on the O/S platform, a RAC database may be deployed on a cluster that uses vendor clusterware plus Oracle's own clusterware (Cluster Ready Services), or on a cluster that solely uses Oracle's own clusterware.

Thus, every RAC sits on a cluster that is running Cluster Ready Services. srvctl is the primary tool DBAs use to configure CRS for their RAC database and processes.

Thursday, November 06, 2008

Concurrent Program Phases and status List

Concurrent Program Phases and status List

Phase_Code :

'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',

Status_Code :

'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',

Table: apps.fnd_concurrent_requests

Oracle EBS integration with Oracle IDCS for SSO

Oracle EBS integration with Oracle IDCS for SSO Oracle EBS SSO? Why is it so important? Oracle E-Business Suite is a widely used application...