Pages

Thursday, September 23, 2010

Manual Switchover

For manual switchover

NOW ON PRIMARY DATABASE
connect sys/oracle@to_primary as sysdba

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;


NOW ON STABD BY DATABASE
connect /@to_standby as sysdba

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;



ON previous primary

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;
 
SQL> alter database recover managed standby database disconnect


New Primary (previous stand by )
 
SQL> ALTER DATABASE OPEN;

Oracle 10g Data Guard (windows)

Data Guard Implementation      (For Oracle 10g R2 Windows)


STEP TO PERFORM ON PRIMARY DATABASE
Step # 1

On PRIMARY site create a service in TNSnames.ora file through which the PRIMARY site will be connected to the Standby machine.

Path E:\oracle\product\10.2.0\db_1\netwok\admin\tnsnames.ora
Copy Orcl Service and paste now change the underline word below
 
TO_STANDBY =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = Name of Standby Machine)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = standby)
   ) )


Step # 2

Also check the connectivity from the SQL Prompt
SQL> connect sys/oracle@to_standby as sysdba
Connected.
 
Step # 3

Reconnect to Primary Database 
SQL> connect sys/oracle as sysdba
Connected.
 
Create Pfile form spfile on sql prompt 
SQL> Create pfile=‘d:\pfile_primary.ora’ from spfile
File Created. 
 
Step # 4
Now open the pfile we create above on wordpad and add the following parameter. 
 
*.db_unique_name='PRIMARY'
*.FAL_Client='to_primary'
*.FAL_Server='to_standby'
*.Log_archive_config='DG_CONFIG=(primary,standby)'
*.Log_archive_dest_1='Location=c:\oracle\backup VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary'
*.Log_archive_dest_2='Service=to_standby lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=standby'
*.Log_archive_dest_state_1=ENABLE
*.Log_archive_dest_state_2=ENABLE
*.Service_names='orcl'
*.Standby_File_Management='AUTO'
 


Step # 5

Create Pfysical Folder on C:\oracle\backeup à for archive_log_dest_1 parameter 

Step # 6

Now shutdown the primary database 
Shutdown immediate

Step # 7

Now startup the primary database with the pfile we create above
Startup mount pfile=d:\pfile_primary.ora
 
Check weather database is archive or not 

SQL> Archive Log list  
If not Convert to archive mode  --- using alter database archivelog

Step # 8

Add  standby redo logfile on primary site (this step is optional, used for Real time Apply)

Alter database add standby logfile 
(e:\oracle\product\10.2.0\oradata\standbyredo.log’) size 100m;


 
Step # 9

Now shutdown the primary database 
SQL>Shutdown immediate
 
Step # 10

Copy all datafiles and standby redolog file from primary database to standby database 
Check standby database is shutdown 

Step # 11

Now again start primary database at mount stage from the pfile wo create above 
Startup mount pfile=d:\pfile_primary.ora
 
Step # 12
Now create standby control file to primary site 
Alter database create standby controlfile as ‘c:\oracle\backup\standbycontrol.ctl’;
 
Step # 13 

Now copy this created standby control file to the standby site where other database file like databases, logfiles and control files are located 
First delete the previous control files 
Than paste this controlfile  Rename this file to control01.ctl, control02.ctl, control03.ctl 
 

Step # 14 
 
Now Create spfile from pfile 
Create spfile from pfile=‘D:\pfile_primary.ora’;
This spfile created on this path e:\oracle\product\10.2.0\db_1\database\spfileorcl.ora
 
 
Step # 15 

Now restart the primary database 
Shutdown immediate 
Startup 

Step Completed on Primary database 
 
 
STEP TO PERFORM ON SECONDARY DATABASE
 
Step # 1 

Create physical foleder on c: drice   
C:\oracle\backup 
 
Step # 2 
Create pfile from spfile on sql prompt  
SQL> Create pfile=‘d:\pfile_standby.ora’ from spfile
File Created 

or get it  from D:\oracle\product\10.2.0\Admin\pfile
 
Step # 3 

Shutdown the database 
 

add these parameters in Pfile you just created

*.db_unique_name=‘STANDBY’
*.FAL_Client=‘to_standby’
*.FAL_Server=‘to_primary’
*.Log_archive_config=‘DG_CONFIG=(primary,standby)’
*.Log_archive_dest_1=‘Location=c:\oracle\backup VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby’
*.Log_archive_dest_2=‘Service=to_primary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=primary’
*.Log_archive_dest_state_1=ENABLE
*.Log_archive_dest_state_2=ENABLE
*.Service_names=‘ORCL’
*.Standby_File_Management=‘AUTO’



 
Step # 4 

Now startup the secondary database with the pfile we create above
STARTUP MOUNT PFILE=‘D:\pfile_standby.ora’;
 
 
 
Step # 5 

Now Create spfile from pfile 
SQL> create spfile from pfile=‘D:\pfile_standby.ora’;
File created.
 
 
Step # 6 

Now restart the Secondary database 
Shutdown immediate 
Startup mount
Step Completed on Primary database 
 
 


NOW ON PRIMARY DATABASE


For checking log sequence number

SQL>Archive log list 

SQL>Alter system switch logfile ;
 

SQL>Select status, error from v$archive_dest where dest_id=2
The status column should return the Valid value

NOW ON STAND BY DATABASE

To apply logs start the MPR background process by executing the following statement

SQL>alter database recover managed standby database disconnect;


Database altered.

verify result by querying ....

SQL> select name, applied, archived from v$archived_log;


to check database role (Primary or Standby) perform Following Query.

SQL> select database_role from v$database;

this will return 

Primary                   for Primary database
Physical Standby     for Standby database

Flashback Query

Flashback query:

Query all data as it existed at a specific point in time.
Flashback query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system commit number (SCN).


Example  ---          Delete Case


SQL> --- At 11 : 34
SQL> DELETE FROM EMP
            WHERE JOB='SALESMAN';
4 rows deleted.

SQL> COMMIT;
Commit complete.

Next, use the SELECT . . . AS OF query to retrieve Flashback data from the past.
SQL> --- At 12 : 19
SQL>  SELECT * FROM EMP AS OF TIMESTAMP SYSDATE-1/24;
Or
SQL>  SELECT * FROM emp AS OF TIMESTAMP
             TO_TIMESTAMP ( '2010-03-05 11:33:30' , 'YYYY-MM-DD HH24:MI:SS');

Once we confirm the validity of the accidentally deleted data, it’s easy to reinsert the data by using the previous query as part of an INSERT statement, as shown here:

SQL>INSERT INTO EMP SELECT * FROM EMP
           AS OF TIMESTAMP SYSDATE-1/24
           WHERE JOB='SALESMAN'
4 rows created.

SQL> COMMIT ;
Commit complete.

SQL>  SELECT * FROM EMP


UPDATE CASE

SQL> --- AT 12 : 22
SQL> SELECT * EMP  WHERE EMPNO=7900;
7900   JAMES       CLERK           7698   03-DEC-81         950                     30

SQL> UPDATE EMP SET SAL=3000
            WHERE EMPNO=7900;
1 rows updated.

SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM EMP
           WHERE EMPNO=7900;
7900   JAMES       CLERK           7698   03-DEC-81       3000                     30

SQL> --- AT 12 : 40
SQL> SELECT * FROM EMP AS OF TIMESTAMP
            TO_TIMESTAMP ('2010-03-08 12:20:00' , 'YYYY-MM-DD HH24:MI:SS')
             WHERE EMPNO=7900
7900   JAMES       CLERK           7698   03-DEC-81         950                     30

SQL>  UPDATE EMP SET SAL=(SELECT SAL FROM EMP AS OF TIMESTAMP
           TO_TIMESTAMP ('2010-03-08 12:20:00' , 'YYYY-MM-DD HH24:MI:SS')
           WHERE EMPNO=7900)
           WHERE EMPNO=7900;
1 row updated.

SQL> SELECT * FROM EMP WHERE EMPNO=7900;
7900   JAMES       CLERK           7698   03-DEC-81         950                     30



UPDATE CASE 2


SQL> --- AT 12 : 22

SQL> SELECT * EMP  WHERE JOB=’SALESMAN’;
7499   ALLEN        SALESMAN        7698   20-FEB-81       1600         300          30
7521   WARD         SALESMAN        7698   22-FEB-81       1250          500         30
7654   MARTIN      SALESMAN        7698   28-SEP-81       1250       1400         30
7844   TURNER     SALESMAN        7698   08-SEP-81       1500             0          30

SQL> UPDATE EMP SET SAL=10000
            WHERE JOB=’SALESMAN’;
4 rows updated.

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM EMP
           WHERE JOB=’SALESMAN’;
7499   ALLEN        SALESMAN        7698   20-FEB-81       10000          300          30
7521   WARD         SALESMAN        7698   22-FEB-81       10000          500          30
7654   MARTIN      SALESMAN        7698   28-SEP-81       10000       1400          30
7844   TURNER     SALESMAN        7698   08-SEP-81       10000              0          30

SQL> --- AT 12 : 40

SQL> SELECT * FROM EMP AS OF TIMESTAMP
            TO_TIMESTAMP ('2010-03-08 12:20:00' , 'YYYY-MM-DD HH24:MI:SS')
             WHERE JOB=’SALESMAN’
7499   ALLEN        SALESMAN        7698   20-FEB-81       1600         300          30
7521   WARD         SALESMAN        7698   22-FEB-81       1250          500         30
7654   MARTIN      SALESMAN        7698   28-SEP-81       1250       1400         30
7844   TURNER     SALESMAN        7698   08-SEP-81       1500             0          30

SQL> UPDATE EMP E
            SET SAL=( SELECT SAL FROM EMP AS OF TIMESTAMP
            TO_TIMESTAMP('2010-03-08 15:25:04','RRRR-MM-DD HH24:MI:SS')
            WHERE E.EMPNO=EMPNO AND JOB='SALESMAN')
            WHERE JOB='SALESMAN';
4 row updated.
SQL> SELECT * FROM EMP WHERE JOB=’SALESMAN’;
7499   ALLEN        SALESMAN        7698   20-FEB-81       1600         300          30
7521   WARD         SALESMAN        7698   22-FEB-81       1250          500         30
7654   MARTIN      SALESMAN        7698   28-SEP-81       1250       1400         30
7844   TURNER     SALESMAN        7698   08-SEP-81       1500             0          30

The previous examples use a time stamp to pinpoint the exact time the data was accidentally dropped. We could use the SCN for the transaction instead of time stamps. If you need to be very specific regarding the time point, use the time-stamp method to specify the time.

Query Parsing

What is Query ParsingThis is a featured page ?

 

In oracle, statement,  DDL/DML, or anything else, gets parsed. Parsing means what Oracle understands about the statement and based on that, how to execute it. This process is also known as Optimization of the query.The idea is how best Oracle can process the query or in other words, optimize its execution.
Parsing is of two types, Hard parse and Soft parse. If the said query is found in Oracle's cache, the query optimization is not needed.Oracle can pick up the optimized query and can execute it. If the query is run for the first time or the query's cached version is obsolete or flushed out from oracle's cache, query needs to be optimized and the process is c called Hard parse of the query. Hard parse , in general , is unavoidable as for the very first time, each query needs to be parsed , atleast for once. But in the subsequent executions, query should be simply soft parsed and executed.
The mechanism which works in the backend for doing all this is called Optimizer. There are two versions of it, Rule Based and Cost Based. The Rule Based optimizer(RBO) is made deprecated from Oracle version release 10g. This was not very much efficient as it was "statement driven". The Cost Based is now the only and supported mode of optimizer which is, as the name suggests, cost based and takes into the account the resource consumption of the query.