Création d'une logical standby

CreateLogicalStby.pdf

Oracle RAC -> Installation - configuration

Network, user & group, etc host file configuration

Script pré-chech pour upgrade Oracle de 12.1 à 12.2

Script perso pour lister les checks à faire sur toutes les bases Oracle d'un serveur avant la montée de version en 12.2.

 

Détecter les lignes migrées avec AWR

 

https://easyteam.fr/detecter-les-lignes-migrees-avec-awr/

 

http://www.dba-oracle.com/t_identify_chained_rows.htm

 

https://www.akadia.com/services/ora_chained_rows.html

 

 

Créer sql profile à partir du sql id

create_sql_profile.txt

Récupérer les infos de stats en base

Pour connaître les infos des stats dans la base Oracle.

Problème de compilation de trigger RMAN après montée de version en 12.2

alter trigger RMAN.VPC_CONTEXT_TRG disable; 

 

=> marche pas :(

lancer :

 

@?/rdbms/admin/dbmsrmanvpc.sql -vpd rman

 

puis UPGRADE CATALOG ; 

source

Merci aux dev SQL

Merci aux dev sql qui m'ont bien aidé sur ce forum : 

 

https://www.developpez.net/forums/d2021961/bases-donnees/langage-sql/insert-into-long-s-executer-optimiser/#post11229277

ORA-609: opiodr aborting process unknown ospid

The Solution

To resolve this error, you must increase the values for INBOUND_ CONNECT_ TIMEOUT on both the listener side and the server side. If you are getting ORA-609 because of a connection timeout issue, increasing the values should fix the problem. You should change INBOUND_ CONNECT_ TIMEOUT to read as the following:

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180

Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

Above, the timeout seconds are being set to 120 in place of the default 60. While this simple change should fix most ORA-609 errors, if you continue to see this error then you should try to locate which client is causing the error. Set the sqlnet.ora file parameter to

sqlnet.ora file: DIAG_ADR_ENABLED = OFF


Putain je me suis pris la tête avec mon client pour cette erreur à la con!!!

https://www.tekstream.com/resource-center/ora-609-opiodr-aborting-process-unknown-ospid/

Tuning SQL ID

http://www.runningoracle.com/product_info.php?products_id=435

 

Summary 
You can create an SQL TUNING TASK manually ad hoc with the following simple steps. 

ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; 

0. Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids. 
In case this is a current sql running use the v$session. 

select sql_id from v$session where sid = :x 

1. Login as SYSTEM (or any other user) at sqlplus and create the tuning task:

SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '5tru8vxmktswq');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/
task_id: TASK_69287

2. Run the SQL TUNING TASK

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_69287');
end;
/

3. You can monitor the processing of the tuning task with the statement 

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_69287'; 

4. When the task has a status=COMPLETED, then run:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_69287') AS recommendations FROM dual;

5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile.

begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_69287', task_owner => 'SYSTEM', replace => TRUE);
end;
/

6. You can check the database sql profiles with the statement:

select * from dba_sql_profiles;

In case you want to disable an sql profile use the statement:

begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED');
end;
/

 

 

https://dbaclass.com/article/how-to-run-sql-tuning-advisor-for-a-sql_id/

 

declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
begin_snap => 4883,
end_snap => 4888,
sql_id => '1pfbzr2wbnwsy',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 10800,
task_name => 'tune_sql_1pfbzr2wbnwsy',
description => 'tuning_du_sql_1pfbzr2wbnwsy');
dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'tune_sql_1pfbzr2wbnwsy');

set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('tune_sql_1pfbzr2wbnwsy') from dual;