Sunday, July 29, 2012

Working with FLASHBACK in oracle Database




FLASHBACK in Oracle DATABASE:
=============================

  Flashback is nothing but going to previous state,same case with Oracle
Database also,here we go to a previous state of Database.

Advantages and Applications of Flashback:
--------------------------------------------

1)Useful in recovering the lost data.
2)Useful in saving time and system resources.
2)Usefull in performing Data testing.

Disadvantages:
---------------

1)Additional Overhead on Database


prerequisites:
---------------

prerequisite 1: Configure FRA(Flash Recovery Area)

    SQL> alter system set db_recovery_file_dest_size=1g;

    System altered.

    SQL> alter system set db_recovery_file_dest=’/oracle/test_flash’;

    System altered.

Note: If it is a RAC database the FRA should point to the shared storage.
Ensure you estimate appropriately db_recovery_file_dest_size and have enough space.
 STORAGE_SIZE column from V$RESTORE_POINT can help you for that.

prerequisite 2:The Oracle database should be in archivelog

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     70
Next log sequence to archive   73
Current log sequence           73

Note:Below steps can be use to switch Database to archive log mode.

    [oracle@orclbox ~]$ sqlplus /nolog

    SQL*Plus: Release 11.2.0.2.0 Production

    Copyright (c) 1982, 2010, Oracle.  All rights reserved.

    SQL> conn / as sysdba
    Connected.
    SQL> alter system set log_archive_dest_1=’location=/oracle/test_flash’;

    System altered.

    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 1653518336 bytes
    Fixed Size                  2227032 bytes
    Variable Size            1275069608 bytes
    Database Buffers          369098752 bytes
    Redo Buffers                7122944 bytes
    Database mounted.
    SQL> alter database archivelog;

    Database altered.

    SQL> alter database open;

    Database altered.

    SQL> archive log list
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /oracle/test_flash
    Oldest online log sequence     2
    Next log sequence to archive   4
    Current log sequence           4
    SQL>


prerequisite 2:On the Flashback in Database

SQL> alter database flashback on;

Database altered.

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON

------------ ------------------

ARCHIVELOG   YES

Note:To off the flashback use the below statement.

SQL> alter database flashback off;

Database altered.

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON

------------ ------------------

ARCHIVELOG   NO



Example 1: Let us consider one eg.of restoring a table to a particular scn(system change number):

Below i'm creating one 'test' user with some test data,than I will check the scn after committing the changes,than I will delete the data and finally I will restore that table using flashback scn technique.

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded


 SQL> insert into test.flashback_test values(1);

    1 row created.

    SQL> insert into test.flashback_test values(2);

    1 row created.

    SQL> insert into test.flashback_test values(3);

    1 row created.

    SQL> commit;

    SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
----------- ---------------------------------------------------------------------------
     931892 28-JUL-12 09.49.40.000000000 AM


    SQL> delete from test.flashback_test;

    3 rows deleted.

    SQL> commit;

    Commit complete.

    SQL> select * from test.flashback_test;

    no rows selected


Restore of table flashback_test using flashback database:
=====================================================

 step 1:Shut down the instances of Database

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down

Step 2:Bring the Database in mount state
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.

Step 3:Use flashback database command to go to scn when table was dropped.

If  you want to revert the database to SCN=930717 where flashback_test table has 3 rows

SQL> flashback database to scn 930717;

Flashback complete.

    SQL> alter database open resetlogs;
--set log sequence no. and the database will be synchonized.

Verify the Data:

    SQL> select * from flashback_test;

    COL1
    ———-
    1
    2
    3

Example 2:Flashback to timestamp

Lets say you have to drop some table on a particular day and the user know the time when he had dropped that table,than we can use the below steps to recover that table using flashback technique.

flashshback to timestamp:
======================

step 1:Shut down the instances of Database

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down

Step 2:Bring the Database in mount state
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.


step 3: flashback to timestamp 28-JUL-12 09.49.40

SQL>flashback database to timestamp TO_TIMESTAMP('2012-07-28 09:49:40','YYYY-MM-DD HH24:MI:SS');

    Flashback complete.

    SQL> select * from test.flashback_test;

    COL1
    ———-
    1
    2
    3

Example 3: Enable recyclebin and get back the lost data.

Step 1:Enable recycle bin when the Database is in open state.

SQL>alter session enable recyclebin=on;

Step 2:Recover lost table using below command.
 
    SQL> FLASHBACK TABLE test.flashback_test TO BEFORE DROP;

    Flashback complete.

    SQL> select * from test.flashback_test;

    COL1
    ———-
    1
    2
    3

Example 4:Create guaranteed restore point and restore that restore point:
========


How we can restore the entire data which is lost in a particular period,this can be use to test data also.
 This is very vital feature of Oracle flashback.

Create Restore point:
=====================

Step 1:Create restore point 'test_rest1':

    SQL> create restore point test_rest1 guarantee flashback database;

    Restore point created.

    * To view the restore points:

    SQL> select NAME,SCN,TIME,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
         from V$RESTORE_POINT
         where GUARANTEE_FLASHBACK_DATABASE='YES';


SQL> select NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
         from V$RESTORE_POINT
         where GUARANTEE_FLASHBACK_DATABASE='YES';  2    3

NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST_REST1
    930537
28-JUL-12 08.57.51.000000000 AM                                             YES
           0


NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST_REST2
    930660
28-JUL-12 09.02.54.000000000 AM                                             YES
    30203904


NAME
--------------------------------------------------------------------------------
       SCN
----------
TIME                                                                        GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE
------------
TEST1
    932549
28-JUL-12 10.30.01.000000000 AM                                             YES
     8192000

Restore Restore point:
=======================

step 1:shut the database

SQL> shut immediate
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down

step 2:Startup in mount state

SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.


step 3:flashback Database to restore point 'test1':

SQL> FLASHBACK database TO RESTORE POINT test1;

Flashback complete

Note:To restore a table below command can be used.
FLASHBACK TABLE emp TO RESTORE POINT test1;


step 4:Open the Database with resetlogs

SQL> alter database open resetlogs;

Database altered

step 6:Verify the data

SQL> select *from test.flashback_test;

      COL1
----------
         1
         2
         3

Step 7:Monitor flashback  v$flashback_database_log

    SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
--------------------
              930314

Note: Dropping restore point.

SQL> DROP RESTORE POINT TEST_REST1;

Restore point dropped.

Note:
Possible flashback options available are:

SQL>flashback table test.flashback_test to (SYSDATE-1/24);

-- be sure to substitute your SCN
FLASHBACK DATABASE TO SCN 19513917;
or
FLASHBACK DATABASE TO RESTORE POINT bef_damage;

/*
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);

FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00';

FLASHBACK DATABASE
TO TIMESTAMP to_timestamp('2002-11-11 16:00:00','YYYY-MM-DD HH24:MI:SS');
*/


Offcourse,we do have flashback query,flashback transaction query,but above are very useful.


Hope it helps....

Enjoy DBA tasks and practice...



Best regards,

Rafi.













Thursday, July 26, 2012

Understanding Oracle Tuxedo

Hi,

Understanding Oracle Tuxedo is very vital for Oracle PeopleSoft DBA

Where did Tuxedo come from?
      Transactions for UniX Extended for Distributed Operations.Tuxedo is a middleware platform used to manage distributed transaction processing in distributed computing environments.
Tuxedo is a transaction processing system or transaction-oriented middleware, or enterprise application server for a variety of systems and programming languages.
Developed first in the 1980s,it became a software product of Oracle Corporation in 2008.

What is jolt?
Jolt is a Java-based interface to the Tuxedo system that extends the functionality of existing Tuxedo applications to include Intranet- and Internet-wide availability.
Using Jolt,we can now easily transform any Tuxedo application so that its services are available to customers using an ordinary browser on the Internet.
Jolt interfaces with existing and new Tuxedo applications and services to allow secure, scalable, intranet/Internet transactions between client and server.
Jolt enables us to build client applications and applets that can remotely invoke existing Tuxedo services,such as application messaging,component management and distributed transaction processing.

The PeopleSoft application server uses the Oracle Fusion Middleware product,Oracle Tuxedo,to perform transaction management, messaging, and administration.
It is essential that you install Oracle Tuxedo version 10gR3, which is available on Oracle E-Delivery.We need to install Oracle Tuxedo before you go any further in setting up
your application server and your PeopleSoft Pure Internet Architecture. After we perform the installation described below,we have to configure the application server environment to incorporate Oracle Tuxedo with the PeopleSoft components.

Note:Oracle supports Oracle Tuxedo 10gR3 for Linux or UNIX, and Oracle Tuxedo 10gR3_VS2008 for
Microsoft Windows, with PeopleSoft PeopleTools 8.51. If we have a previous version of Oracle Tuxedo
installed, we need to install the new version of Oracle Tuxedo, and re-create your application server domains.(You must create your domains using PSADMIN; you cannot migrate existing domains.) You can also use psadmins's domain import utility.


The minimum patch level certified for running Oracle Tuxedo 10gR3 with PeopleSoft PeopleTools 8.51 is
RP031. These installation instructions include the installation of the base Oracle Tuxedo 10gR3,followed by
the patch installation.

We can install Oracle Tuxedo once for each release on a machine,regardless of the number of PeopleSoft
applications or databases the server supports. For example, if you are a PeopleSoft 9.1 customer and have
Oracle Tuxedo 6.5 installed, you may install Oracle Tuxedo 6.5 and Oracle Tuxedo 10gR3 on the same
machine in separate directories. For example:

Installation of Oracle Tuxedo:
==============================


On Windows,we may install into C:\oracle\tuxedo10gR3_VS2008 and C:\tux65.
On UNIX,we may install into /home/oracle/tuxedo10gR3 and /prod/tuxedo/6.5.

If more than one PeopleSoft application uses the same Oracle Tuxedo version (that is, the same patch level),
then it is recommended that you have a single installation of Oracle Tuxedo to serve all the supported
PeopleSoft applications. A single Oracle Tuxedo installation simplifies future maintenance (such as applying
patches). However, if you choose to have more than one Oracle Tuxedo installation (this scenario is possible
only on UNIX systems, as Oracle Tuxedo does not allow multiple installations of the same version of Oracle
Tuxedo on Microsoft Windows), you must install and maintain the same Oracle Tuxedo version more than
once in different directories.


Hope it helps....

Enjoy Oracle PeopleSoft DBA concepts....

Best regards,


Rafi.

How to change User Password when FRM-40200:Field is protected against update Error comes in Oracle EBS Application

How to change User Password when FRM-40200:Field is protected against update Error comes in Oracle EBS Application:
==================================================================================================================

        In the below example I'm using form based method for changing User's password,as form based method is the fasted  and most recommended method to create the user and change the password for 'n' number of users in Oracle E-Business Suite Application,other method is you can go to 'User Management' responsibility which already explained in my previous posts under Apps DBA tasks Label and from Oracle Database also from backend using PL/SQL Package which I will explain as the other method
step in last to avoid confusion.

To Resolve FRM-40200:


Step 1:Login to Oracle EBS Database as 'Apps' User

update fnd_user set USER_GUID=null where user_name='E123456';
  
    1 row updated.
  
    SQL> commit
         ;
  
    Commit complete.
  
 
Step 2:Login to Oracle EBS Home page as 'sysadmin' user or User having 'System Administrator' Responsibility

Navigate to System Administrator=>Security:User=>Define form page

A Users form page will open which display Username and all the details:

Press F11 key

Type Username:%E123456%

Press ctrL+F11 key

Enter  password:welcome
Verify password:welcome


press F11

Do you want to save the changes

click 'Yes'

FRM-40400 Transaction is complete 1 record applied and saved

save the changes click OK.


Step 3:Request user to change password on First Login


Enter old password:
Enter your own new password:


Note:Password should be atleast 5 character long

Other Method to create user and change password:

The following script can be used to create a user in Oracle EBS.

DECLARE
l_responsibility_id NUMBER;
l_application_id NUMBER;
l_user_id NUMBER := fnd_global.user_id;
x_user_id NUMBER;
l_password VARCHAR2 (2000) := 'welcome123';
BEGIN
apps.hr_user_acct_internal.create_fnd_user (p_user_name => 'operations',
p_password => l_password,
p_employee_id => NULL,
p_user_id => x_user_id,
p_user_start_date => SYSDATE,
p_email_address => NULL,
p_description => NULL,
p_password_date => NULL
);
DBMS_OUTPUT.put_line (x_user_id);

IF x_user_id IS NOT NULL
THEN
UPDATE fnd_user
SET password_lifespan_days = 90
WHERE user_id = x_user_id;

COMMIT;
END IF;
END;
/



Happy Apps DBA learning.....



Best regards,

Rafi.

















Sunday, July 8, 2012

Sequence of events when you login Oracle PeopleSoft Applications

Hi,

Below are the sequence of events which will occur when you access the Oracle PeopleSoft Applications:

1)The URL in the Web browser address window invokes a PS servlet on the Web server.
2)Servlet running in the servlet engine interprets request and comes up with the list of objects required to build the page.
3)Request for all required objects sent to the application server in the form of a Jolt Message.
4)Tuxedo receives the Jolt Message and converts it into a service request which it routes to the appropriate PeopleSoft server process.
5)eopleSoft server process converts service request into SQL statement(s).
6)SQL statements sent to the Database.
7)Data requested is supplied by the Database.
8)PeopleSoft process constructs HTML page out of object data.
9)Tuxedo acknowledges receipt of data and closes connection with the PeopleSoft process.
10)Data forwarded by Tuxedo to requesting Java servlet.
11)Servlet forwards page requested by the browser.
12)When all objects are in place,HTML page forwarded to the Web services
13)Browser views the PeopleSoft Home Page.



Best regards,

Rafi.

Understanding Oracle PeopleSoft Architechture

Hi,

Few months back I started learning PeopleSoft Architecture,I will be always using in my post the term Oracle PeopleSoft Applications,since it is the Oracle product from few years,as it is acquired recently and when we add Oracle prefix to any technology the interest to learn automatically arises,so I given the Label also
Oracle PeopleSoft Admin.

             Oracle PeopleSoft Architecture consists of :




Many of my DBA friends and myself feel if we have clear understanding of Architecture,than we can troubleshoot any kind of issues.
So lets start
with Oracle PeopleSoft Architecture first.

Oracle's PeopleSoft Architecture consists of following components:


1)Web Browser 
2)Web Server
        3)Application Server
   4)Tuxedo and Jolt
             5)Process Scheduler and 
   6)Database Server


1)Web Browser:PeopleSoft is a browser-based program that uses the Internet or Intranet to accessdata stored on servers and presents that data in ways that are helpful to end users in their daily routine. 
On the one hand, because PeopleSoft relies on a tool as ubiquitous as an Internet browser, it presents itself as rather commonplace and
comfortable.

2)The Web Server:PeopleSoft requires a Java-enabled web server to support browser transaction request and application messaging technology. 
The web server component of PIA consists of a collection of PeopleSoft Java serviettes that are designed to handle various PeopleSoft transactions.

3)The Application Server:The Application Server is the core of the PeopleSoft Internet Architecture,
it presents SQL to the relational database management system on behalf of the end user and executes business logic. 
It is made up of several PeopleSoft services and server processes that handle end-user transaction request.



4)Tuxedo and Jolt:PeopleSoft uses BEA Tuxedo to manage database transactions and jolt (Tuxedo’s counterpart) to facilitate request issued from the Internet.

Tuxedo is a middleware platform used to manage distributed transaction processing in distributed computing environments. 
Tuxedo is a transaction processing system or transaction-oriented middleware or enterprise application server for a variety of systems and programming languages.
Developed first in the 1980s,it became a software product of Oracle Corporation in 2008.


5)Process scheduler server: The  Batch/process scheduler server is where the Process Scheduler is installed and
configured; it is also the location where many of your batch programs run, such as Application Engine programs.If you know Apps DBA,this is our Concurrent Processing Server.
In most situations this is also where you have your COBOL and SQR executable installed. In many cases this function shares the
physical hardware with your database server, in other cases where the database may be running on a Unix platform the batch server may be split to also use an Intel based machine to support Crystal reports and nVision.


6)Database server:The database server houses the database engine and the PeopleSoft database,which includes all of your object definitions,system tables,application tables and transaction data. The database server must be running one of the supported
RDBMS/operating system combinations.The relationship between a database server and an application server is a one to-Many model. Meaning,a single database server can have multiple application servers connecting to it.

Enjoy the PeopleSoft DBA learning....



Best regards,
Rafi.




Thanks for following my blog


Dear All,
Thanks for following my blog.My followers list crossed 100,thanks for showing the keen interest.This blog
is  covering technical details on Oracle DBA,Apps DBA  and in future you will be seeing more on PeopleSoft Administration stuffs.

My interest areas are Apps and RAC,but I have lot of interest in all the areas of Oracle technologies like Performance Tuning,SOA,weblogic,Designing,Business Intelligence,Data Integration  and now PeopleSoft Administration.If you are preparing for interviews for Oracle DBA,Apps DBA and PeopleSoft DBA,this blog will act as Catalyst.I like blogging on Oracle stuffs as I always believe knowlege grows when it is shared.Keeping the focus on interview patterns in mind I  always try to share most frequently asked question and the way we can answer them and we should always focus more on learning and practice of Oracle Stuffs,than we can cover the interview questions and answers.


I wish all of you for your successful Oracle Carrier ahead,remember "Dreams are not those you see while you are sleeping,dreams are those that don't let you sleep".


Best regards,

Rafi.