Automatic SQL Tuning Advisor

oracle
Automatic SQL Tuning Advisor
============================
1.create a new tuning task
2.execute_tuning_task
3.Report of the Tunning advisory


first step when using the SQL tuning advisor is to create a new tuning task
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
SET SERVEROUTPUT ON

-- Tuning task created for specific a statement from the AWR.
-----------------------------------------------------------------------------
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 764,
                          end_snap    => 938,
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_AWR_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for specific a statement from the cursor cache.
-----------------------------------------------------------------------------
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sql_tuning_task',
                          description => 'Tuning task for statement sql.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created from a SQL tuning set.
-----------------------------------------------------------------------------
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sqlset_name => 'test_sql_tuning_set',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sqlset_tuning_task',
                          description => 'Tuning task for a SQL tuning set.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- 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;
/

--execute_tuning_task:
-----------------------------------------------------------------------------
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '3t70pmjyf8c16_AWR_tuning_task');

--Completed Report of the Tunning advisory
-------------------------------------------
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('3t70pmjyf8c16_AWR_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24


-- Reset a tuning task allowing it to be re-executed.
-----------------------------------------------------------------------------
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'sqlset_tuning_task');

---tuning session is over the tuning task can be dropped using the DROP_TUNING_TASK procedure.
-----------------------------------------------------------------------------
BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqlset_tuning_task');
END;
/

ORACLE 
DataGuard Gap Recovery

Primary Host
==============
primary


SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         Hyderabad         PRIMARY


SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          10899

 SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data01/oradata/Hyderabad1/system01.dbf
/data02/oradata/Hyderabad1/sysaux01.dbf
/data05/oradata/Hyderabad1/undotbs01.dbf
/data04/oradata/Hyderabad1/users01.dbf
/data11/oradata/Hyderabad1/Hyderabad_TGTP_BLOB_DATA_ATTACHMENTS_01.dbf
/data11/oradata/Hyderabad1/Hyderabad_TGTP_CLOB_DATA_TEXT_01.dbf
/data11/oradata/Hyderabad1/Hyderabad_TGTP_DATA_AUDIT_01.dbf
/data11/oradata/Hyderabad1/Hyderabad_TGTP_DATA_CRM_01.dbf
/data11/oradata/Hyderabad1/Hyderabad_TGTP_DATA_MASTER_PRODUCTS_01.dbf
/data11/oradata/Hyderabad1/Hyderabad_TGTP_DATA_OUTPUT_01.dbf
/data11/oradata/Hyderabad1/Hyderabad_TGTP_DATA_STATIC_01.dbf

NAME
--------------------------------------------------------------------------------
/data11/oradata/Hyderabad1/Hyderabad_TGTP_DATA_TNX_PRODUCTS_01.dbf
/data11/oradata/Hyderabad1/Hyderabad_TGTP_DATA_USER_01.dbf
/data12/oradata/Hyderabad1/Hyderabad_TGTP_INDX_AUDIT_01.dbf
/data12/oradata/Hyderabad1/Hyderabad_TGTP_INDX_CRM_01.dbf
/data12/oradata/Hyderabad1/Hyderabad_TGTP_INDX_MASTER_PRODUCTS_01.dbf
/data12/oradata/Hyderabad1/Hyderabad_TGTP_INDX_OUTPUT_01.dbf
/data12/oradata/Hyderabad1/Hyderabad_TGTP_INDX_STATIC_01.dbf
/data12/oradata/Hyderabad1/Hyderabad_TGTP_INDX_TNX_PRODUCTS_01.dbf
/data12/oradata/Hyderabad1/Hyderabad_TGTP_INDX_USER_01.dbf
/data01/oradata/Hyderabad1/patrol_tbs_01.dbf


standby ORACSI
============

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED      Hyderabad         PHYSICAL STANDBY

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1           2954

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   59742180
  


 SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data01/oradata/Hyderabad2/system01.dbf
/data02/oradata/Hyderabad2/sysaux01.dbf
/data05/oradata/Hyderabad2/undotbs01.dbf
/data04/oradata/Hyderabad2/users01.dbf
/data11/oradata/Hyderabad2/Hyderabad_TGTP_BLOB_DATA_ATTACHMENTS_01.dbf
/data11/oradata/Hyderabad2/Hyderabad_TGTP_CLOB_DATA_TEXT_01.dbf
/data11/oradata/Hyderabad2/Hyderabad_TGTP_DATA_AUDIT_01.dbf
/data11/oradata/Hyderabad2/Hyderabad_TGTP_DATA_CRM_01.dbf
/data11/oradata/Hyderabad2/Hyderabad_TGTP_DATA_MASTER_PRODUCTS_01.dbf
/data11/oradata/Hyderabad2/Hyderabad_TGTP_DATA_OUTPUT_01.dbf
/data11/oradata/Hyderabad2/Hyderabad_TGTP_DATA_STATIC_01.dbf

NAME
--------------------------------------------------------------------------------
/data11/oradata/Hyderabad2/Hyderabad_TGTP_DATA_TNX_PRODUCTS_01.dbf
/data11/oradata/Hyderabad2/Hyderabad_TGTP_DATA_USER_01.dbf
/data12/oradata/Hyderabad2/Hyderabad_TGTP_INDX_AUDIT_01.dbf
/data12/oradata/Hyderabad2/Hyderabad_TGTP_INDX_CRM_01.dbf
/data12/oradata/Hyderabad2/Hyderabad_TGTP_INDX_MASTER_PRODUCTS_01.dbf
/data12/oradata/Hyderabad2/Hyderabad_TGTP_INDX_OUTPUT_01.dbf
/data12/oradata/Hyderabad2/Hyderabad_TGTP_INDX_STATIC_01.dbf
/data12/oradata/Hyderabad2/Hyderabad_TGTP_INDX_TNX_PRODUCTS_01.dbf
/data12/oradata/Hyderabad2/Hyderabad_TGTP_INDX_USER_01.dbf

21 ligne(s) sélectionnée(s).
  
SQL>alter database recover managed standby database cancel;
  
PRIMARY
=========

rman target / rcvcat RMAN/RMANRCVCAT@RMAN
   
RMAN> backup incremental from scn 59742180 database format '/produits/oracle/rman/Incremental_%U.bak';

(or) 

run { 
allocate channel c1 type disk format '/produits/oracle/rman/%U.rmb'; 
backup incremental from scn 59742180 database;
}

RMAN> backup current controlfile for standby format '/produits/oracle/rman/Incremental%U.ctl';

(or)

SQL>alter database create standby controlfile as '/produits/oracle/rman/Incremental.ctl';

scp * x130654@standby:/produits/oracle/rman

STANDBY:
============
 rman target / rcvcat RMAN/RMANRCVCAT@RMAN
   
RMAN> catalog start with '/produits/oracle/rman/*';
RMAN> recover database noredo;
RMAN> shutdown immediate
RMAN> startup nomount
RMAN> restore standby controlfile from '/produits/oracle/rman/Incremental%U.ctl'; 

RMAN> shutdown immediate

RMAN> startup mount


Standby Database:
======================
SQL> alter database recover managed standby database disconnectfrom session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------



Primary Database:
======================
On the Primary database, check the Maximum Archivelog Sequence generated.

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)
------- ----------------


Standby Database:
======================

Check the maximum archivelog sequence that is applied on the Physical standby database.

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
 ------- ----------------

oracle

Renaming and Moving Data Files
=====================================

SELECT tablespace_name FROM dba_data_files WHERE file_name = '&datafile_name';

SELECT file_name, status FROM dba_data_files WHERE tablespace_name = '&tablespace_name';

SELECT status FROM dba_tablespaces WHERE tablespace_name = '&tablespace_name';

ALTER TABLESPACE &tablespace_name READ ONLY;

SELECT status FROM dba_tablespaces WHERE tablespace_name = '&tablespace_name';

cp datafile to target location 

ALTER TABLESPACE &tablespace_name OFFLINE;

ALTER DATABASE RENAME FILE '<old location/old file name>' TO '<new location/new file name>';

ALTER TABLESPACE &tablespace_name ONLINE;

ALTER TABLESPACE &tablespace_name READ WRITE;

SELECT status FROM dba_tablespaces WHERE tablespace_name = '&tablespace_name';

SELECT file_name, status FROM dba_data_files WHERE tablespace_name = '&tablespace_name';