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.

Setting password for listener in 10g


This is a post in which i will tell you that how can you setup the password for your listener in 10g

NOTE:-It is not absolute necessary that you have to set a password for your listener in 10g because 10g is already very much secure as you can only run any oracle executable from only the authorised user

Follow these simple steps to add a password for your listener,screenshots are also provided for a better result

Step 1)_Start lsnrctl from cmd and type change_password <listener_name> for changing password of the listener

Step 2)_Type save_config to save the configuration in the listener.ora file

Step 3)_Now go to computer management and create a new user and dsnt give the ora_dba group to the new user(ora_dba is the group for accessing the oracle executables)

Step 4)_Now logout of the main administrator user and login with the newly created user,this step is necessary because any user who is having privilege to ora_dba group can start and stop the listener without the password,only the remote users i.e who dsnt have ora_dba group assign to them will require password for using lsnrctl utility,and we are pretending a similar remote user situation here

Step 5)_Now run cmd and type lsnrctl and then try stopping the listener for which you have set the password(in this case its listener1) it will show the error

NOTE:-for accessing lsnrctl utility you must have already instaled it

Step 6)_Now type set password this will prompt for the password of the listener which you have already set,provide it the password

Step 7)_Now try to stop the listener again and this time it will stop successfully

Congratulations!!!!

You have successfully controlled a listener using a password

Using database replay with oracle 11g


This is a post which will help you to use one of the newest features of oracle database 11g  which is Database Replay basically,The Database Replay functionality of Oracle 11g allows you to capture workloads on a production system and replay them exactly as they happened on a test system. This provides an accurate method to test the impact of a variety of system changes including:

• Database upgrades.
• Operating system upgrades or migrations.
• Configuration changes, such as changes to initialization parameters or conversion from a single node to a RAC environment.
• Hardware changes or migrations.

Follow these simple steps of how you can use Database Replay in your 11g database,Screenshots are also provided for a better result

Step 1)_create an empty directory in any location for storing captured stats,this directory will be used for the storing of the files generated during the Database Replay

Step 2)_create a directory using the CREATE OR REPLACE DIRECTORY command pointing towards the already created empty directory

Step 3)_start the capture using start_capture procedure.NOTE-null means you have to manually stop the capture process

BEGIN
  DBMS_WORKLOAD_CAPTURE.start_capture (name     => 'test_capture_1', 
                                       dir      => 'DB_REPLAY_CAPTURE_DIR',
                                       duration => NULL);
END;
/

Step 4)_create a user for which the capture process will run

NOTE:-the capture process is already running and all the task which is being performed in the database is being captured

Step 5)_connect with the newly created user

Step 6)_let us do some work on the database so that the work can be captured,you can make a table and populate it with data

Step 7)_connect with sys user and stop the capture process using finish capture procedure,this will stop the capturing of data

Step 8)_check the capture folder for capture files generation,you can see that there were some files being generated in that folder

Step 9)_check for the capture directory number to generate AWR report associated with this number of capture

SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info('DB_REPLAY_CAPTURE_DIR')
FROM   dual;

Step 10)_generate the AWR report associated with the capture

Step 11)_Physically copy the captured data to the test server and create a directory into the test db using the step 2 above for creating a directory

Step 12)_We can now prepare to replay(not start the replay) the existing capture logs using the PROCESS_CAPTURE, INITIALIZE_REPLAY and PREPARE_REPLAY procedures

BEGIN
  DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_CAPTURE_DIR');

  DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'test_capture_1',
                                          replay_dir  => 'DB_REPLAY_CAPTURE_DIR');

  DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);
END;
/

Step 13)_Before we can start the replay, we need to calibrate and start a replay client using the WRC utility.

Step 14)_The calibration step suggest a single client on a single CPU is enough, so we only need to start a single replay client, which is shown below.

Step 15)_start the replay from sys user account

BEGIN
  DBMS_WORKLOAD_REPLAY.start_replay;
END;
/

NOTE:-the replay will take sometime to complete so just wait patiently

Step 16)_after the completion of the replay you can check for the table that you have created on the production server and the data which was captured

Congratulations!!!!!!

You have successfully used database replay with oracle 11g

Using Data Recovery Advisor in Oracle Database 11g

The Data Recovery Advisor automatically diagnoses corruption or loss of persistent data on disk, determines the appropriate repair options, and executes repairs at the user's request

Now here are the simple steps how you can use DRA,the screenshots are also provided for a better result

NOTE:-You must have a full database backup available with you

Step 1)_shutdown the database using immediate option

step 2)_Before we can start identifying and repairing failures, we need to create one and for creating a simple error we are using the following command

echo > users01.dbf

the above command will intentionally corrupt the users01 datafile

Step 3)_connect with RMAN and list the failures by using list failure command

Step 4)_now type advise failure command and it will advise how to recover from the current situation

Step 5)_ Using the REPAIR FAILURE PREVIEW option will lists the contents of the repair script without applying it

Step 6)_now use the repair failure option for the repairing of the failure and type yes

Congratulations you have just repaired a failure using DRA