Creating Active Datagaurd in 11g using RMAN backup restore(Manual restore standby)


This post is about the creation of Active Datagaurd in 11g using RMAN backup restore,there arevarious differenet methods of creating a standby database in oracle 11g for eg

1)-Using RMAN duplicate command
2)-Manual standby using the database files
3)-Using Backup Restore

i am using RMAN backup restore(Manual restore standby) in the post

Follow these simple steps for the creation of Active Datagaurd in 11g using RMAN backup restore(Manual restore standby) Screenshots are also provided for a better result


Step 1)_ON PRIMARY SERVER-check for archivelog mode,check that the primary server is in archivelog mode,if it is not in archivelog mode then convert it into archivelog mode

Step 2)_ON PRIMARY SERVER-Enabled forced logging so that everything gets logged

Step 3)_ON PRIMARY SERVER-Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters,this is helpful as you  have to set the same values in the standby server

Step 4)_ON PRIMARY SERVER-add the name of standby database and the service name of the standby server in log_archive_config parameter,this can be used for sending redo logs to standby server

Step 5)_ON STANDBY SERVER-install only binaries in exact location as in PRIMARY

Step 6)_ON PRIMARY SERVER-Set suitable remote archive log destinations and enable remote archiving,this can be done by using the service name for the standby database

Step 7)_ON PRIMARY SERVER-set archive log format,max process and password file exclusivity

Step 8)_ON PRIMARY SERVER-it is recommended to make sure the primary is ready to switch roles to become a standby. For that we need to set these parameters

1)-FAL_SERVER
2)-DB_FILE_NAME_CONVERT
3)-LOG_FILE_NAME_CONVERT

Step 9)_ON PRIMARY AND STANDBY SERVER-Entries for the primary and standby databases are needed in the tnsnames.ora files on both servers

Step 10)_ON PRIMARY SERVER-take a backup of the primary database,this step distinguishes this type of method creating the standby database,if you are using RMAN DUPLICATE command then this step is not necessary

Step 11)_ON PRIMARY SERVER-Create a controlfile for the standby database

Step 12)_ON PRIMARY SERVER-Create a parameter file for the standby database

Step 13)_ON STANDBY SERVER-make exact folders in the standby server as there in the primary server

Step 14)_ON PRIMARY SERVER-copy the newly created standby control file

Step 15)_ON STANDBY SERVER-paste the controlfile to the standby server and rename it to control01,02,03 respectively

Step 16)_ON STANDBY SERVER-copy and paste controlfile,archive log files,datafiles,password file,backupset into the standby server to the exact location as of the primary server

Step 17)_ON STANDBY SERVER-make changes in the tnsnames file for the standby server

Step 18)_ON STANDBY SERVER-start the listener

Step 19)_ON STANDBY SERVER-make the following changes in the standby server pfile

1)-FAL_SERVER
2)-LOG_ARCHIVE_DEST_2
3)-DB_UNIQUE_NAME

Step 20)_ON STANDBY SERVER-create a new service for the database using oradim

Step 21)_ON STANDBY SERVER-startup the server in nomount mode using the newly created pfile

Step 22)_ON STANDBY SERVER-create spfile from the standby server pfile

Step 23)_ON STANDBY SERVER-shutdown n startup in nomount using newly created spfile

Step 24)_ON STANDBY SERVER RMAN-mount the database and restore the database

Step 25)_ON STANDBY SERVER-create the online redo logs

Step 26)_ON STANDBY SERVER-In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database (in case of switchovers)

Step 27)_ON PRIMARY SERVER-switch the logfile to see the archivelog generation and also check the copying it to standby

Step 28)_ON STANDBY SERVER-recover the standby to make it upto-date with primary

Congratulations!!!

You have successfully created an active datagaurd using RMAN restore command

Creating a clone database in oracle 11g called as active database cloning

normally in Oracle9i/10g cloning is done using duplicate cloning command, we do the RMAN cloning.and

We need a RMAN full backup for cloning the database in oracle9i/10g.

In oracle 11g provides a new feature, without RMAN database backup we can clone the database.

follow these simple steps to perform cloning database in oracle 11g,screenshota are also provided for a better result

Step 1)_Configure the network files like tnsnames.ora and listener.ora,as the configuration for the clone server/database is totally dependent on the network files this step is very important

Step 2)_Make entries for source and target/clone database into the listener file(preferably through net manager) and restart the listener afterwards

Step 3)_Make entry for the target/clone database in the tnsnames.ora file

Step 4)_Place the UR=A parameter in the tnsnames file to avoid connection blocking error generated from listener

Step 5)_Create a new instance on the clone server for the new target/clone database

Step 6)_Create a password file with a password for the target database format for the same would be PWDinstancename.ora

Step 7)_Connect with the source database and create a pfile from spfile

Step 8)_Make the following changes in the newly created pfile(left one is of source db and right one is created for clone db)

Step 9)_Create appropriate folder in the same location as in the source database for adump

Step 10)_Create appropriate folder in the same location as in the source database for clonedb files in oradata folder

Step 11)_Set oracle sid for clone db and connect and start in nomount mode with newly created pfile

Step 12)_Now create the spfile from the newly created pfile and then shutdown and start the db in nomount mode again

Step 13)_Connect with the rman of source database with user sys and the password for sys user(not like rman target /,because this will create problems)

Step 14)_Connect with the auxiliarry clone database with the password for the sys user given in the password file

Step 15)_Perform the cloning of the database using the following command

RMAN> duplicate target database to clonedb from active database nofilenamecheck;

Step 16)_After the successful execution of the cloning process connect with the clone db and check for the working status as well as total objects

CONGRATULATIONS!!!!

You have successfully created a clone database using active database cloning method in oracle 11g

Simple SQL tuning

In this post i will tell you how to perform a simple SQL tuning,SQL tuning is a very vast topic and there are various ways for doing it,but here is the simplest of the ways

follow these simple steps for tuning a timeconsuming SQL statement,screenshots are also provided for a better result

Step 1) Connect to the database and connect to particular user in database whose query is to be tuned

Step 2) Now fire the query which is taking time or sql query which you want to tune.

Step 3) Use autotrace utility by making traceonly option and again fire the query which you want to tune,this time the query will not display the output,rather it will display the execution plan of the query

Step 4) Now from the execution plan you can check full table access,consistent gets, physical reads, sorts to disk, recursive calls and sorts to memory and fine tune your query based on this

NOTE:-from the execution plan you can only get some statistics related to the SQL query,you will then have to use those statistics to tune the SQL

Using SQL tuning advisor in 11g


this is a post in which i will tell you about the usage of  SQL tuning advisor in 11g,Use the SQL Tuning Advisor for tuning SQL statements.you can run the SQL Tuning Advisor on the most resource-intensive SQL statements, referred to as top SQL, from the cursor cache or the AWR, as well as on a user-defined SQL workload.


I will provide you with the steps of using SQL tuning advisor in 11g on a user-defined SQL workload.screenshots are also attached for a better result


Step 1)_Connect as sysdba user and grant advisor privilege to scott and connect with it

Step 2)_Run the procedure with case sensetive username i.e scott is not same as SCOTT,change the sql statement according to your own statement

-- Tuning task created for a manually specified statement.
DECLARE
  l_sql               VARCHAR2(500);
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql := 'SELECT e.*, d.* ' ||
           'FROM   emp e JOIN dept d ON e.deptno = d.deptno ' ||
           'WHERE  NVL(empno, ''0'') = :empno';

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => l_sql,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => 'SCOTT',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'emp_dept_tuning_task',
                          description => 'Tuning task for an EMP to DEPT join query.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Step 3)_Execute the newly created tuning task using the EXECUTE_TUNING_TASK procedure.

Step 4)_Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function.spool this onto ypur desired location so that you can read it later also.

Step 5)_You can also check the status of the tuning task with dba_advisor_log view

Step 6)_Once the tuning session is over the tuning task can be dropped using the DROP_TUNING_TASK procedure.

Step 7)_There are many useful views regarding sql tuning advisor

• DBA_ADVISOR_TASKS
• DBA_ADVISOR_FINDINGS
• DBA_ADVISOR_RECOMMENDATIONS
• DBA_ADVISOR_RATIONALE
• DBA_SQLTUNE_STATISTICS
• DBA_SQLTUNE_BINDS
• DBA_SQLTUNE_PLANS
• DBA_SQLSET
• DBA_SQLSET_BINDS
• DBA_SQLSET_STATEMENTS
• DBA_SQLSET_REFERENCES
• DBA_SQL_PROFILES
• V$SQL
• V$SQLAREA
• V$ACTIVE_SESSION_HISTORY

Congratulations!!!

You have successfully used SQL tuning advisor in 11g,now you can be able to tune your queries by resolving the issues which you noticed in the spool file generated from the REPORT_TUNING_TASK function.A sample file from the REPORT_TUNING_TASK function looks like this.

NOTE:-This is just a sample file and  your file structure may vary.

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : emp_dept_tuning_task
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 05/06/2004 09:29:13
Completed at       : 05/06/2004 09:29:15

-------------------------------------------------------------------------------
SQL ID  : 0wrmfv2yvswx1
SQL Text: SELECT e.*, d.* FROM   emp e JOIN dept d ON e.deptno = d.deptno
          WHERE  NVL(empno, '0') = :empno

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "SCOTT"."EMP" and its indices were not analyzed.

  Recommendation
  --------------
    Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
            'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE)

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  The predicate NVL("E"."EMPNO",0)=:B1 used at line ID 2 of the execution plan
  contains an expression on indexed column "EMPNO". This expression prevents
  the optimizer from selecting indices on table "SCOTT"."EMP".

  Recommendation
  --------------
    Rewrite the predicate into an equivalent form to take advantage of
    indices. Alternatively, create a function-based index on the expression.

  Rationale
  ---------
    The optimizer is unable to use an index if the predicate is an inequality
    condition or if there is an expression or an implicit data type conversion
    on the indexed column.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1863486531

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |   107 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |   107 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |     1 |    87 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|   4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

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


1 row selected.