informatique:base_de_donnees:tips

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentes Révision précédente
Prochaine révision		 Révision précédente
informatique:base_de_donnees:tips [2023/03/21 09:29] – beninformatique:base_de_donnees:tips [2024/05/07 14:32] (Version actuelle) – ben
Ligne 1: Ligne 1:
-===== Switchover (Dataguard) =====+===== Restore en mode RAC ===== 
 + 
 +<code> 
 +alter pluggable database OEM2 close immediate instances=all; 
 + 
 +run { 
 +set until time "to_date('04/12/2023 09:00:00', 'dd/mm/yyyy hh24:mi:ss')"; 
 +restore pluggable database OEM2; 
 +recover pluggable database OEM2; 
 +alter pluggable database OEM2 open resetlogs; <= don't apply archivelogs 
 +} 
 + 
 +alter pluggable database OEM2 open instances=all; 
 + 
 +</code> 
 + 
 +===== Process sessions ===== 
 + 
 +<code> 
 +select 'alter system kill session '''|| sid || ','||serial#||',@'||inst_id||''' ;', s.* 
 +from gv$session s 
 +where sql_id='8xdrs3uww5c0p' 
 +and plsql_entry_object_id = 71000 
 +and sql_exec_start <sysdate - 100/24/60/60 
 +; 
 + 
 +</code> 
 + 
 +===== Dupliquer database ===== 
 +<code> 
 + 
 +cd $ORACLE_HOME/dbs 
 +. oraenv 
 +echo "DB_NAME=PDBIO_R2"> initPDBIO_R2.ora 
 +export ORACLE_SID=PDBIO_R2 
 +sqlplus / as sysdba 
 + 
 +startup nomount pfile='initPDBIO_R2.ora' ; 
 + 
 +export NLS_DATE_FORMAT="DD-MON-YYYY.HH24:MI:SS" 
 +export NLS_LANG=AMERICAN.WE8ISO8859P1 
 +rman auxiliary / log=/home/oracle/test-restore_PDBIO.log 
 + 
 +run 
 +{ 
 +SET  UNTIL TIME "to_date('30/10/2023 07:00:00','dd/mm/yyyy hh24:mi:ss')"; 
 +SET ARCHIVELOG DESTINATION TO '/u01/app/oradata/restore'; 
 +DUPLICATE DATABASE TO PDBIO_R2 BACKUP LOCATION '/BACKUP-HXL01/PDBIO/' SPFILE SET CLUSTER_DATABASE='FALSE' NOFILENAMECHECK  ; 
 +switch datafile all; 
 +switch tempfile all; 
 +} 
 + 
 +</code> 
 + 
 +<code> 
 +select NAME,OPEN_MODE  from v$database; 
 +show pdbs ; 
 +shutdown abort ; 
 +startup mount exclusive restrict ; 
 +drop database ; 
 + 
 +</code> 
 + 
 +===== Listener ===== 
 + 
 +Forcer prise en compte rapide des services BDD 
 + 
 +<code> 
 +sqlplus / as sysdba 
 +alter system register ; 
 + 
 +</code> 
 + 
 +===== ORA-xxx ===== 
 + 
 +  * ORA-01033 : check password primary <⇒ standby 
 +  * ORA-16136 : checker la FRA 
 +  * ORA-16136 : ORA-46372: audit file '/u01/app/odaorabase/oracle/audit/PABIO2 : mv répertoire en .old ou rm 
 +  * Checks sémaphores : sysresv 
 + 
 +===== Divers checks ===== 
 + 
 +<code> 
 +select * from v$restore_point; 
 + 
 +select database_role from v$database ; 
 + 
 +SELECT LOG_MODE FROM SYS.V$DATABASE; 
 + 
 +set line 200 
 +col dest_name format a45 
 +select NAME,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL, CURRENT_SCN,FLASHBACK_ON,FORCE_LOGGING from v$database; 
 + 
 +select 
 +"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)", 
 +"Free_Space(GB)" 
 +from( 
 +select 
 +(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)", 
 +(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)" 
 +from dual 
 +); 
 + 
 +select name 
 +,      round(space_limit / 1024 / 1024) size_mb 
 +,      round(space_used  / 1024 / 1024) used_mb 
 +,      decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used 
 +from v$recovery_file_dest 
 +order by name ; 
 + 
 +</code> 
 + 
 +===== ORA-12514 ===== 
 + 
 +<code> 
 +SQL> show parameter service ; 
 + 
 +NAME                                 TYPE 
 +------------------------------------ -------------------------------- 
 +VALUE 
 +------------------------------ 
 +service_names                        string 
 +PLNAVAMP_1DC2.fr.orpea.net 
 + 
 +</code> 
 + 
 +<code> 
 +SQL> show parameter domain ; 
 + 
 +NAME                                 TYPE 
 +------------------------------------ -------------------------------- 
 +VALUE 
 +------------------------------ 
 +db_domain                            string 
 +fr.orpea.net 
 + 
 +</code> 
 + 
 +<code> 
 +SQL> alter system reset db_domain ; 
 + 
 +System altered. 
 + 
 +</code> 
 + 
 +<code> 
 +SQL> alter system reset db_domain scope = spfile SID='*' ; 
 +alter system reset db_domain scope = spfile SID='*' 
 +* 
 +ERROR at line 1: 
 +ORA-32010: cannot find entry to delete in SPFILE 
 + 
 +</code> 
 + 
 +===== ASM ===== 
 + 
 +  * Check liste datafiles ; 
 + 
 +<code> 
 +SQL> select NAME from v$datafile ; 
 + 
 +NAME 
 +-------------------------------------------------------------------------------- 
 ++ADATA/antarp/system-antarp-01.dbf 
 ++ADATA/antarp/sysaux-antarp-01.dbf 
 ++ADATA/antarp/undotbs1-antarp-01.dbf 
 ++ADATA/antarp/users-antarp-01.dbf 
 ++ADATA/antarp/antares_data-antarp-01.dbf 
 ++ADATA/antarp/antares_indx-antarp-01.dbf 
 ++ADATA/antarp/undotbs2-antarp-01.dbf 
 ++ADATA/antarp/cpsure-antarp-01.dbf 
 ++ADATA/antarp/antares_data-antarp-02.dbf 
 + 
 +</code> 
 + 
 +  * Resize 
 + 
 +<code> 
 +set lin 1000 
 +col header_status form a12 
 +col mode_status form a12 
 +col path form a15 
 +col name form a15 
 +select 
 +    header_status 
 +    ,mode_status 
 +    ,os_mb 
 +    ,total_mb 
 +    ,free_mb 
 +    ,name 
 +    ,path 
 +from v$asm_disk ; 
 + 
 +alter diskgroup reco resize all ; 
 + 
 +</code> 
 + 
 +===== Archivelogs ===== 
 + 
 +  * Voir logs 
 + 
 +<code> 
 +select to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp",MESSAGE,SEVERITY from v$dataguard_status 
 +where SEVERITY <>'Control'; 
 + 
 +select to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp",MESSAGE,SEVERITY from v$dataguard_status ; 
 + 
 +</code> 
 + 
 +  * Check réception archivelogs (à lancer sur standby) : 
 + 
 +<code> 
 +select 
 + to_char(max(next_time), 'DD-MON-YY:HH24:MI:SS') v_Last_Received 
 + from v$archived_log 
 + where sequence# = (select max(sequence#) from v$archived_log); 
 + 
 +</code> 
 + 
 +  * Checker config 
 + 
 +<code> 
 +set linesize 500 pages 0 
 +col value for a120 
 +col name for a25 
 +select name, value 
 +from v$parameter 
 +where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_3','log_archive_dest_4', 
 +'log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3', 'log_archive_dest_state_4','remote_login_passwordfile', 
 + 'log_archive_format','log_archive_max_processes','fal_server','fal_client', 
 + 'standby_file_management'); 
 +/ 
 + 
 +</code> 
 + 
 +<code> 
 +SQL> ARCHIVE LOG LIST ; 
 +Database log mode              Archive Mode 
 +Automatic archival             Enabled 
 +Archive destination            USE_DB_RECOVERY_FILE_DEST 
 +Oldest online log sequence     41817 
 +Next log sequence to archive   41819 
 +Current log sequence           41819 
 + 
 +SQL>  SHOW PARAMETER DB_RECOVERY_FILE_DEST ; 
 +db_recovery_file_dest                string                           +FPFRA 
 +db_recovery_file_dest_size           big integer                      152G 
 + 
 +</code> 
 + 
 +<code> 
 +SQL> select dest_name,status,destination from V$ARCHIVE_DEST; 
 +LOG_ARCHIVE_DEST_1                                                                                                                                                                             VALID 
 +USE_DB_RECOVERY_FILE_DEST 
 + 
 +</code> 
 + 
 +  * Modif config 
 + 
 +<code> 
 +alter system set fal_server='frantarp_1dc2' scope=both; 
 +ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3 = DEFER SCOPE=BOTH; 
 +alter system reset log_archive_dest_2 sid='*'; 
 +alter system set log_archive_config='DG_CONFIG=(FRANTARP_1DC1,FRANTARP_1DC2)' scope=both; 
 +alter system set LOG_ARCHIVE_DEST_2='SERVICE=frantarp_1dc2 SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frantarp_1dc2' scope=both sid='*'; 
 +ALTER SYSTEM SET log_archive_dest_3 = ''; 
 + 
 +</code> 
 + 
 +===== Recovery point ===== 
 + 
 +<code> 
 +-- 2 days (3 * 24 * 60) 
 + 
 +alter system set db_flashback_retention_target=2880 scope=both sid='*'; 
 + 
 +-- 3 days (3 * 24 * 60) 
 + 
 +alter system set db_flashback_retention_target=4320 scope=both sid='*'; 
 + 
 +declare 
 + 
 +  v_date varchar2(20); 
 + 
 +begin 
 + 
 +  select to_char(sysdate, 'yyyymmdd_hh24miss') into v_date from dual; 
 + 
 +  execute immediate 'create restore point restore_point_' || v_date; 
 + 
 +end; 
 + 
 +/ 
 + 
 +select * from V$RESTORE_POINT ; 
 + 
 +</code> 
 +<code> 
 + 
 +ALTER DATABASE FLASHBACK ON; 
 +create restore point BEFORE_MIG GUARANTEE FLASHBACK DATABASE; 
 + 
 +</code> 
 + 
 +<code> 
 +select * from v$restore_point; 
 + 
 +</code> 
 + 
 +<code> 
 +RMAN>  LIST RESTORE POINT ALL; 
 + 
 +using target database control file instead of recovery catalog 
 +SCN              RSP Time  Type       Time      Name 
 +---------------- --------- ---------- --------- ---- 
 +1099414692                 GUARANTEED 06-APR-23 BEFORE_SWITCH 
 + 
 +</code> 
 + 
 +===== Pending sessions ===== 
 + 
 +<code> 
 +set pagesize 999; 
 +set feedback off; 
 +set wrap on; 
 + 
 +column local_tran_id format a22 heading 'Local Txn Id' 
 +column global_tran_id format a50 heading 'Global Txn Id' 
 +column state format a16 heading 'State' 
 +column mixed format a5 heading 'Mixed' 
 +column advice format a5 heading 'Advice' 
 + 
 +select local_tran_id, 
 + global_tran_id, 
 + state,mixed,advice 
 +from 
 + dba_2pc_pending 
 +order 
 + by local_tran_id; 
 + 
 +</code> 
 + 
 +<code> 
 +-- To Force Rollback 
 +SQL> ROLLBACK FORCE  '96.22.163456' 
 +-- To Force Commit 
 +SQL> COMMIT FORCE  '96.22.163456' 
 + 
 +</code> 
 + 
 +→ forced rollback status ; 
 + 
 +<code> 
 +Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (‘LOCAL TRANSACTION ID’); 
 + 
 +</code> 
 + 
 +[[https://revanth935.wordpress.com/2013/04/19/roll-back-pending-transactions-ora-02075/|https://revanth935.wordpress.com/2013/04/19/roll-back-pending-transactions-ora-02075/]] \\ 
 +[[https://oraclefiles.com/2019/03/04/resolving-in-doubt-transactions/|https://oraclefiles.com/2019/03/04/resolving-in-doubt-transactions/]] \\ 
 +[[http://www.dba-oracle.com/t_two_phase_commit_2pc.htm|http://www.dba-oracle.com/t_two_phase_commit_2pc.htm]] 
 + 
 +===== Check last connections ===== 
 +<code> 
 + 
 +select to_timestamp(to_char(logon_Time, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS'), machine from gv$session  where type = 'USER' order by logon_time desc; 
 + 
 +</code> 
 + 
 +===== Unlock users ===== 
 + 
 +<code> 
 +column column_name format a30 
 +set linesize 300 
 +SELECT username, 
 +       account_status 
 +  FROM dba_users WHERE ACCOUNT_STATUS like '%LOCKED%' ; 
 + 
 +</code> 
 + 
 +<code> 
 +ALTER USER HEXALIS_DECISION identified by <password> account unlock ; 
 + 
 +</code> 
 + 
 +<code> 
 +ALTER USER HEXALIS_DECISION account unlock ; 
 + 
 +</code> 
 + 
 +===== Drop user connecté ===== 
 + 
 +<code> 
 +SYS@TBBIO1> startup ; 
 +ORACLE instance started. 
 + 
 +Total System Global Area 8589931880 bytes 
 +Fixed Size                 13874536 bytes 
 +Variable Size            5905580032 bytes 
 +Database Buffers         2583691264 bytes 
 +Redo Buffers               86786048 bytes 
 +Database mounted. 
 +Database opened. 
 +SYS@TBBIO1>  SHOW PDBS; 
 + 
 +    CON_ID CON_NAME                       OPEN MODE  RESTRICTED 
 +---------- ------------------------------ ---------- ---------- 
 +         2 PDB$SEED                       READ ONLY  NO 
 +         3 TFRHNO01                       READ WRITE NO 
 +         4 TFRHNO01Q                      READ WRITE NO 
 +         5 TFRMPY01                       READ WRITE NO 
 +SYS@TBBIO1> ALTER SESSION set container=TFRMPY01; 
 + 
 +SYS@TBBIO1> show pdbs ; 
 + 
 +    CON_ID CON_NAME                       OPEN MODE  RESTRICTED 
 +---------- ------------------------------ ---------- ---------- 
 +         5 TFRMPY01                       MOUNTED 
 +SYS@TBBIO1> alter pluggable database TFRMPY01 open restricted; 
 + 
 +Pluggable database altered. 
 + 
 +SYS@TBBIO1> show pdbs ; 
 + 
 +    CON_ID CON_NAME                       OPEN MODE  RESTRICTED 
 +---------- ------------------------------ ---------- ---------- 
 +         5 TFRMPY01                       READ WRITE YES 
 +SYS@TBBIO1> ALTER SESSION set container=TFRMPY01; 
 + 
 +Session altered. 
 + 
 +SYS@TBBIO1> DROP USER FRMPY01 CASCADE ; 
 + 
 +</code> 
 + 
 +===== Taille database ===== 
 + 
 +<code> 
 +select 
 +"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)", 
 +"Free_Space(GB)" 
 +from( 
 +select 
 +(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)", 
 +(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)" 
 +from dual 
 +); 
 + 
 +</code> 
 + 
 +<code> 
 +select sum (bytes)/1024/1024/1024 size_GB  from dba_segments ; 
 + 
 +</code> 
 + 
 +<code> 
 +WITH 
 + TS_ALLOC as 
 + ( 
 +select TABLESPACE_NAME TS, round(sum(bytes/1024/1024/1024)) ALLOC 
 + from cdb_data_files 
 + group by TABLESPACE_NAME 
 + union 
 + select TABLESPACE_NAME TS, round(sum(bytes/1024/1024/1024)) ALLOC 
 + from cdb_temp_files 
 +  group by TABLESPACE_NAME 
 +  ), 
 +  TS_USED as 
 + ( 
 +  select TABLESPACE_NAME TS, round(sum(bytes/1024/1024/1024)) FREE 
 +  from cdb_free_space 
 + group by TABLESPACE_NAME 
 +  ) 
 +  select sum(ALLOC) "ALLOC (G)", sum(FREE) "FREE (G)", sum(ALLOC) - sum(FREE) "USED (G)" 
 + from TS_ALLOC a LEFT OUTER JOIN TS_USED u on a.TS=u.TS ; 
 + 
 +</code> 
 + 
 +===== Sessions ===== 
 + 
 +<code> 
 +set echo on time on timing on lines 200 pages 1000 
 +col username format a30 
 +col machine format a30 
 +col service_name format a30 
 +col logon_time format a20 
 +select 
 +        username 
 +--      , machine 
 +        , inst_id 
 +        , service_name 
 +        , status 
 +      , to_char(logon_time,'YYYY/MM/DD HH24:MI') logon_time 
 +        , count(1) sessions_cnt 
 +from 
 +        gv$session 
 +where 
 +        1=1 
 +        and type='USER' 
 +        and service_name not like 'SYS%' 
 +        and username not like 'SYS%' 
 +        and username not like 'PUBLIC' 
 +group by 
 +        username 
 +--        , machine 
 +        , inst_id 
 +        , service_name 
 +        , status 
 +      , to_char(logon_time,'YYYY/MM/DD HH24:MI') 
 +order by 
 +        sessions_cnt desc; 
 + 
 +</code> 
 + 
 +<code> 
 +select sid,serial# from v$session where username = 'FRMPY01' ; 
 +select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from gv$session where username ='FRMPY01'; 
 + 
 +</code> 
 + 
 +  * RAC 
 +<code> 
 + 
 +select sid,serial#,inst_id from gv$session where username = 'FRMPY01' ; 
 +select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate;' as script from gv$session where username='FRMPY01' ; 
 + 
 +</code> 
 + 
 +===== FRA ===== 
 +<code> 
 + 
 +SYS@TEBIO1> show parameter recovery 
 + 
 +NAME                                 TYPE 
 +------------------------------------ -------------------------------- 
 +VALUE 
 +------------------------------ 
 +db_recovery_file_dest                string 
 ++RECO 
 +db_recovery_file_dest_size           big integer 
 +200G 
 +recovery_parallelism                 integer 
 +0 
 +remote_recovery_file_dest            string 
 + 
 +</code> 
 + 
 +<code> 
 +ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=400g SCOPE=BOTH ; 
 + 
 +</code> 
 + 
 +<code> 
 +select name 
 +,      round(space_limit / 1024 / 1024) size_mb 
 +,      round(space_used  / 1024 / 1024) used_mb 
 +,      decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used 
 +from v$recovery_file_dest 
 +order by name ; 
 + 
 +</code> 
 + 
 +===== Copie password ASM ===== 
 + 
 +<code> 
 +orapwd file=orapwt21cbio password=password ignorecase=n format=12 force=Y 
 +asmcmd pwcopy --dbuniquename T21CBIO /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_5/dbs/orapwt21cbio +DATA/T21CBIO/orapwt21cbio 
 + 
 +</code> 
 + 
 +<code> 
 +srvctl modify database –d ORCL –pwfile +DATA/DRORCL/PASSWORD/pwdorcl 
 + 
 +</code> 
 + 
 +===== Tempfile ===== 
 + 
 +<code> 
 +SYS@PDBIO1> select name from v$tempfile; 
 + 
 +NAME 
 +-------------------------------------------------------------------------------- 
 ++DATA/P21DBIO/TEMPFILE/temp.1823.1125772463 
 ++DATA/P21DBIO/CB949F6CD1D60A81E0533E60180A9ACE/TEMPFILE/temp.1824.1125772463 
 ++DATA/P21DBIO/CB94C1145D745969E0533E60180A9064/TEMPFILE/temp.1826.1125772465 
 ++DATA/P21DBIO/CCE2ECA49E5F05B5E0533E60180A5B40/TEMPFILE/temp.1825.1125772465 
 ++DATA/P21DBIO/D58B172F55E0EB56E0533E60180A7510/TEMPFILE/temp.1852.1131484295 
 + 
 +</code> 
 + 
 +===== Change ORACLE_HOME ===== 
 + 
 +<code> 
 +oda1db0host# srvctl stop database -db P21CPAT 
 +oda1db0host# srvctl modify database -d P21CPAT -oraclehome "/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_5" 
 +oda1db0host# srvctl start database -db P21CPAT 
 + 
 +</code> 
 + 
 +===== Datapatch ===== 
 + 
 +<code> 
 +oda2db0host# . oraenv 
 +oda2db0host# cd $ORACLE_HOME && ./OPatch/datapatch -db PHBIO1 -verbose 
 + 
 +</code> 
 + 
 +===== Recompile invalid objects ===== 
 + 
 +<code> 
 +@$ORACLE_HOME/rdbms/admin/utlrp.sql 
 + 
 +</code> 
 + 
 +===== PDBs ===== 
 + 
 +  * show 
 + 
 +<code> 
 +SQL> show pdbs 
 + 
 +    CON_ID CON_NAME                       OPEN MODE  RESTRICTED 
 +---------- ------------------------------ ---------- ---------- 
 +         2 PDB$SEED                       READ ONLY  YES 
 +         3 PFRCHA02                       READ WRITE NO 
 +         4 PFRCHA02Q                      READ WRITE NO 
 +         5 PFRNPC01                       READ WRITE NO 
 +SQL> 
 + 
 +</code> 
 + 
 +<code> 
 +alter pluggable database PHQR_OAS1 close instances=all; 
 +alter pluggable database PHQR_OEM1 close instances=all; 
 +alter pluggable database PHQR_OEM1 close immediate instances=all; 
 +alter pluggable database all save state instances=all; 
 +SELECT * FROM cdb_pdb_saved_states; 
 + 
 +</code> 
 + 
 +  * change mode 
 + 
 +<code> 
 +SQL>  alter pluggable database pdb$seed  open read only  ; 
 + 
 +Warning: PDB altered with errors. 
 + 
 +</code> 
 + 
 +  * clean violations 
 + 
 +<code> 
 +exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'PFRBNO01'); 
 + 
 +</code> 
 + 
 +  * cd to PDB 
 + 
 +<code> 
 +ALTER SESSION SET CONTAINER=PFRNPC01; 
 + 
 +</code> 
 + 
 +  * Restricted 
 + 
 +Check si datapatch OK 
 + 
 +<code> 
 +SQL> show pdbs 
 + 
 +    CON_ID CON_NAME                       OPEN MODE  RESTRICTED 
 +---------- ------------------------------ ---------- ---------- 
 +         2 PDB$SEED                       READ ONLY  NO 
 +         3 PHRQ_OEM                       READ WRITE NO 
 +         4 PHQR_OEM1                      READ WRITE YES 
 +         5 PHQR_OAS1                      READ WRITE YES 
 +SQL> alter pluggable database PHQR_OEM1 open force ; 
 + 
 +Pluggable database altered. 
 + 
 +SQL> show pdbs , 
 + 
 +    CON_ID CON_NAME                       OPEN MODE  RESTRICTED 
 +---------- ------------------------------ ---------- ---------- 
 +         2 PDB$SEED                       READ ONLY  NO 
 +         3 PHRQ_OEM                       READ WRITE NO 
 +         4 PHQR_OEM1                      READ WRITE NO 
 +         5 PHQR_OAS1                      READ WRITE YES 
 + 
 +SQL> alter pluggable database PHQR_OAS1 open force ; 
 + 
 +</code> 
 + 
 +<code> 
 +SQL> alter session set container=PHQR_OEM1 ; 
 + 
 +Session altered. 
 + 
 +SQL> alter system disable restricted session; 
 + 
 +</code> 
 + 
 +===== Dataguard ===== 
 + 
 +  * start Redo Apply 
 + 
 +<code> 
 +ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 
 + 
 +</code> 
 + 
 +  * Switch manuel 
 + 
 +<code> 
 +## Check if possible and Convert Primary to Physical Standby 
 + 
 +srvctl stop instance -d FRTLPM_PRM -i TLMP2 
 +sqlplus / as sysdba 
 +SELECT SWITCHOVER_STATUS FROM V$DATABASE;    (must be TO_STANDBY) 
 +ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN; 
 + 
 +## Check if possible and Convert the Standby to Primary 
 + 
 +sqlplus / as sysdba 
 +SELECT SWITCHOVER_STATUS FROM V$DATABASE;    (must be TO_PRIMARY) 
 +ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; 
 + 
 +## Deactivate all STANDBY Database and Open Primary 
 + 
 +alter system set log_archive_dest_state_2=defer scope=both ; 
 +alter system set log_archive_dest_state_3=defer scope=both ; 
 +alter system set log_archive_dest_state_4=defer scope=both ; 
 +alter database open ; 
 + 
 +# Restart old PRIMARY and Start Standby Mode 
 + 
 +startup mount 
 +ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 
 + 
 +# Modify CLUSTER Configuration 
 + 
 +srvctl modify database -d FRTLMP_1DC1 -r PRIMARY -s OPEN 
 + 
 +</code> 
 + 
 +  * Switch semi-manuel 
 + 
 +<code> 
 +kill du switchover ; 
 +stop de la base ; 
 +srvctl stop database -db FRINTEGP_PRM 
 +restart des bases de l'autre côté 
 + 
 +srvctl modify database -db frintegp_1dc1 -role PRIMARY -startoption OPEN 
 +stop /start 
 + 
 +startup mount de l'ancienne primary 
 +check tnsping 
 +remove configuration ; 
 + 
 +CREATE CONFIGURATION drintegp AS PRIMARY DATABASE IS frintegp_1dc1 CONNECT IDENTIFIER IS frintegp_1dc1; 
 +ADD DATABASE frintegp_1dc2 AS CONNECT IDENTIFIER IS frintegp_1dc2; 
 +ENABLE CONFIGURATION; 
 + 
 +</code> 
 + 
 +  * Logs du broker : drc* file 
 + 
 +  * Check de la conf du broker 
 + 
 +<code> 
 +SQL> show parameter broker ; 
 + 
 +NAME                                 TYPE 
 +------------------------------------ -------------------------------- 
 +VALUE 
 +------------------------------ 
 +connection_brokers                   string 
 +((TYPE=DEDICATED)(BROKERS=1)), 
 + ((TYPE=EMON)(BROKERS=1)) 
 +dg_broker_config_file1               string 
 ++FPDAT/FRSIRHAP_1DC2/dg_config 
 +-sirhap-01.dbf 
 +dg_broker_config_file2               string 
 ++FPDAT/FRSIRHAP_1DC2/dg_config 
 +-sirhap-02.dbf 
 + 
 +NAME                                 TYPE 
 +------------------------------------ -------------------------------- 
 +VALUE 
 +------------------------------ 
 +dg_broker_start                      boolean 
 +TRUE 
 +use_dedicated_broker                 boolean 
 +FALSE 
 + 
 +</code> 
 + 
 +  * Restart du broker 
 + 
 +<code> 
 +SQL> alter system set dg_broker_start=FALSE scope=BOTH SID='*' ; 
 +SQL> alter system set dg_broker_start=TRUE scope=BOTH SID='*' ; 
 + 
 +</code> 
 + 
 +  * Switchover
  
 <code> <code>
Ligne 6: Ligne 811:
 validate database <STANDBY> validate database <STANDBY>
 show database <STANDBY> show database <STANDBY>
 +show database verbose <STANDBY>
 +
 +</code>
 +
 +  * Recréer config
 +
 +<code>
 +remove configuration ;
 +CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS db11g CONNECT IDENTIFIER IS db11g;
 +ADD DATABASE db11g_stby AS CONNECT IDENTIFIER IS db11g_stby;
 +ENABLE CONFIGURATION;
 +
 +</code>
 +
 +  * Check lag
 +
 +<code>
 +col NAME format a10
 +select NAME,TIME,UNIT,COUNT,LAST_TIME_UPDATED from V$STANDBY_EVENT_HISTOGRAM
 +where name like '%lag' and count>0 order by LAST_TIME_UPDATED;
 +
 +</code>
 +
 +<code>
 +select name, value, time_computed, datum_time from v$dataguard_stats where name='%lag';
 +
 +</code>
 +
 +<code>
 +Primary
 +
 +SQL> select scn_to_timestamp(current_scn) from v$database;
 +
 +select sysdate,database_mode,recovery_mode, gap_status
 + from v$archive_dest_status
 + where type='PHYSICAL'
 + and gap_status !='NO GAP';
 +
 + select *
 + from v$dataguard_status
 + where severity in ('Error','Fatal')
 + and timestamp> (sysdate -1);
 +
 + select sysdate,status,error
 + from gv$archive_dest_status
 + where type='PHYSICAL'
 + and status!='VALID'
 + or error is not null;
 +
 + Standby
 +
 + col name format a45
 + select name,value,time_computed,datum_time
 + from v$dataguard_stats
 + where name='transport lag'
 + and value> '+00 00:01:00';
 +
 + col name format a45
 + select name,value,time_computed,datum_time
 + from v$dataguard_stats
 + where name='apply lag'
 + and value> '+00 00:01:00';
 +
 + select max(timestamp)
 +     from gv$recovery_progress group by inst_id;
 +
 +set line 500 pages 9999
 +col severity form a40
 +col message form a131
 +select SEVERITY,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp",MESSAGE from v$dataguard_status;
 +select  distinct error from v$archive_dest;
 +
 +</code>
 +
 +  * Check réception archives logs
 +
 +<code>
 +select to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp",MESSAGE,SEVERITY from v$dataguard_status
 +where SEVERITY <>'Control';
 +
 +</code>
 +
 +<code>
 +select
 + to_char(max(next_time), 'DD-MON-YY:HH24:MI:SS') v_Last_Received
 + from v$archived_log
 + where sequence# = (select max(sequence#) from v$archived_log);
 +
 +</code>
 +
 +[[https://docs.oracle.com/en/database/oracle/oracle-database/19/haovw/monitor-oracle-data-guard-configuration.html#GUID-51E71BB5-EE63-434A-976B-AE89C807A405|https://docs.oracle.com/en/database/oracle/oracle-database/19/haovw/monitor-oracle-data-guard-configuration.html#GUID-51E71BB5-EE63-434A-976B-AE89C807A405]] \\
 +[[http://www.datadisk.co.uk/html_docs/oracle_dg/cheatsheet.htm|http://www.datadisk.co.uk/html_docs/oracle_dg/cheatsheet.htm]]
 +
 +  * Créer standby + config dataguard
 +
 +{{:informatique:base_de_donnees:sample_initfile_dg.txt|sample_initfile_dg.txt}}
 +<code>
 +
 +****** SOURCE
 +
 +# Activer FORCE LOGGING
 +alter database force logging;
 +
 +# CREER les STANDBY Logs
 +alter database add standby logfile thread 1 group 11 ('+DATA','+RECO') size 400M reuse;
 +alter database add standby logfile thread 1 group 12 ('+DATA','+RECO') size 400M reuse;
 +alter database add standby logfile thread 1 group 13 ('+DATA','+RECO') size 400M reuse;
 +alter database add standby logfile thread 2 group 14 ('+DATA','+RECO') size 400M reuse;
 +alter database add standby logfile thread 2 group 15 ('+DATA','+RECO') size 400M reuse;
 +alter database add standby logfile thread 2 group 16 ('+DATA','+RECO') size 400M reuse;
 +
 +# STANDBY Management AUTO
 +alter system set standby_file_management = 'AUTO' scope=both sid='*' ;
 +
 +# Récupérer le Fichier PASSWORD
 +asmcmd
 +cp +DATA/P21EPAT/PASSWORD/pwdp21epat.1856.1131111009 /home/oracle/orapwTEPAT
 +
 +# Récupérer le SPFILE et le Modifier
 +sqlplus / as sysdba
 +create pfile='/home/oracle/initPEPAT1.ora' from spfile ;
 +
 +Copier les fichiers vers la cible et le modifier.
 +
 +dbs
 +cp initPEPAT1.ora $ORACLE_HOME/dbs/
 +cp orapwPEPAT1 $ORACLE_HOME/dbs/
 +
 +virer unedrscore
 +*.cluster_database=false
 +virer controlfile, log archive config, broker
 +
 +*****   CIBLE
 +
 +# Add Connexion in tnsnames.ora sur $OH Database et $OH Grid. DUP => pour ne pas passer par le scan
 +
 +CDB_RAST_DUP =
 +  (DESCRIPTION =
 +    (ADDRESS = (PROTOCOL = TCP)(HOST = ex1dc2db2cw2cl.nibclt.prod)(PORT = 1521))
 +    (CONNECT_DATA =
 +      (SERVER = DEDICATED)
 +      (SERVICE_NAME = CDB_RAST)
 +      (UR = A)
 +    )
 +  )
 +
 +# Add Connexion in listener.ora
 +SID_LIST_LISTENER =
 +  (SID_LIST = (SID_DESC =
 +    (GLOBAL_DBNAME = CDB_RA)
 +    (ORACLE_HOME = /u01/app/oracle/dbHome)
 +    (SID_NAME = CDB_RA)))
 +
 +# Create DUMP file on both servers
 +mkdir -p /u01/app/odaorabase/oracle/admin/P72EPAT/adump
 +
 +# Start Database NOMOUT with pfile
 +startup nomount pfile='initCDB_RAST1.ora' ;
 +
 +# Create the STANDBY via RMAN Duplicate
 +rman
 +connect target sys/password@CDB_RAST
 +connect auxiliary sys/password@CDB_RAST_DUP
 +RUN
 +{
 +ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ;
 +ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ;
 +ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ;
 +ALLOCATE CHANNEL disk4 DEVICE TYPE DISK ;
 +ALLOCATE CHANNEL disk5 DEVICE TYPE DISK ;
 +ALLOCATE CHANNEL disk6 DEVICE TYPE DISK ;
 +ALLOCATE CHANNEL disk7 DEVICE TYPE DISK ;
 +ALLOCATE CHANNEL disk8 DEVICE TYPE DISK ;
 +ALLOCATE auxiliary CHANNEL disk9 device type disk;
 +ALLOCATE auxiliary CHANNEL disk10 device type disk;
 +ALLOCATE auxiliary CHANNEL disk11 device type disk;
 +ALLOCATE auxiliary CHANNEL disk12 device type disk;
 +ALLOCATE auxiliary CHANNEL disk13 device type disk;
 +ALLOCATE auxiliary CHANNEL disk14 device type disk;
 +ALLOCATE auxiliary CHANNEL disk15 device type disk;
 +ALLOCATE auxiliary CHANNEL disk16 device type disk;
 +duplicate target database for standby from active database;
 +}
 +
 +# Changer les parametres dans $ORACLE_HOME/dbs/initPEPAT1.ora
 +*.cluster_database=true
 +*.control_files='+DATA/P72EPAT/CONTROLFILE/current.1594.1137853621','+RECO/P72EPAT/CONTROLFILE/current.62887.1137853621'
 +
 +output file name=+DATA/P72EPAT/CONTROLFILE/current.1594.1137853621
 +output file name=+RECO/P72EPAT/CONTROLFILE/current.62887.1137853621
 +
 +# Remove spfile
 +remove spfilePEPAT1.ora
 +
 +# Shutdown the STANDBY and RESTART In Mount.
 +shutdown immediate;
 +startup mount
 +
 +# Create spfile in ASM
 +create spfile='+DATA/P72EPAT/spfileP72EPAT.ora' from pfile;
 +shutdown immediate;
 +
 +# Modifier init.ora
 +SPFILE='+DATA/P72EPAT/spfileP72EPAT.ora'
 +
 +# Cluster Creation
 +srvctl add database -d P72EPAT -o $ORACLE_HOME -c RAC -r PHYSICAL_STANDBY -s MOUNT -n P72EPAT -a "DATA,RECO"
 +srvctl add instance -d P72EPAT -i PEPAT1 -n oda2db0host
 +srvctl add instance -d P72EPAT -i PEPAT2 -n oda2db1host
 +srvctl modify database -d P72EPAT -p '+DATA/P72EPAT/spfileP72EPAT.ora'
 +
 +srvctl start database -d P72EPAT
 +
 +# Transférer le fichier de mot de passe sur le diskgroup ASM avec le user grid
 +asmcmd -p
 +pwcopy --dbuniquename P72EPAT ''/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_2/dbs/orapwPEPAT1'' '+DATA/P72EPAT/orapwP72EPAT'
 +
 +# Supprimer le Password FIle
 +rm $ORACLE_HOME/dbs/orapwTEPAT1
 +
 +# Modify RMAN Configuration to remove ARCHIVE LOG on PRIMARY
 +rman target /
 +CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
 +
 +# Modify RMAN Configuration to remove ARCHIVE LOG on STANDBY
 +rman target /
 +CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
 +CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/P72EPAT/PEPAT-snapshot-01.ctl';
 +
 +***** BROKER
 +
 +# Vérifier les TNS sur les 2 Noeuds
 +P21EPAT =
 +  (DESCRIPTION =
 +    (ADDRESS = (PROTOCOL = TCP)(HOST = oda1scan)(PORT = 1521))
 +    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = P21EPAT)))
 +
 +P72EPAT =
 +  (DESCRIPTION =
 +    (ADDRESS = (PROTOCOL = TCP)(HOST = oda2scan)(PORT = 1521))
 +    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = P72EPAT)))
 +
 +# Activer le BROKER sur les 2 Noeuds
 +alter system set dg_broker_config_file1='+DATAC2/CDB_RA/dr1CDB_RA.dat' scope=both sid='*';
 +alter system set dg_broker_config_file2='+RECOC2/CDB_RA/dr2CDB_RA.dat' scope=both sid='*';
 +alter system set dg_broker_start=TRUE scope=both;
 +
 +alter system set dg_broker_config_file1='+DATAC2/CDB_RAST/dr1CDB_RAST.dat' scope=both sid='*';
 +alter system set dg_broker_config_file2='+RECOC2/CDB_RAST/dr2CDB_RAST.dat' scope=both sid='*';
 +alter system set dg_broker_start=TRUE scope=both;
 +
 +# Configuration
 +dgmgrl /
 +create configuration DG_TESTRA as primary database is CDB_RA connect identifier is "CDB_RA";
 +add database CDB_RAST as connect identifier is "CDB_RAST" maintained as physical;
 +enable configuration;
 +
 +# Check Dataguard
 +On PRIMARY
 +set linesize 1000
 +select * from V$DATAGUARD_STATUS ;
 +select thread#,max(sequence#) from v$archived_log group by thread#;
 +
 +On STANDBY
 +select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
 +
 </code> </code>
  
Ligne 11: Ligne 1082:
  
 <code> <code>
-set head off pages 0 lines 120  +set head off pages 0 lines 120 
-select  p.spid,s.sid, s.serial#, substr(s.username,1,10)||','||process,  +select  p.spid,s.sid, s.serial#, substr(s.username,1,10)||','||process, 
-s.program,s.module,s.status, osuser ,   +s.program,s.module,s.status, osuser , 
-buffer_gets, disk_reads, executions,users_executing, first_load_time,'**',s.wait_class  +buffer_gets, disk_reads, executions,users_executing, first_load_time,'**',s.wait_class 
-,a.rows_processed,  a.sql_id,  sql_text -- sql_fulltext +,a.rows_processed,  a.sql_id,  sql_text -- sql_fulltext
 from v$process p, v$session s,  v$sqlarea a from v$process p, v$session s,  v$sqlarea a
 where a.address=s.sql_address where a.address=s.sql_address
 and p.addr=s.paddr and p.addr=s.paddr
-and users_executing > 0 +and users_executing> 0 
-and s.status='ACTIVE'  +and s.status='ACTIVE' 
-/ +/ 
 </code> </code>
  
 ===== Mettre le résultat d'une requête sql dans une variable ===== ===== Mettre le résultat d'une requête sql dans une variable =====
 <code> <code>
 +
 vMaxSeqApply=`sqlplus -s "/ as sysdba" <<EOF vMaxSeqApply=`sqlplus -s "/ as sysdba" <<EOF
 set head off set head off
Ligne 34: Ligne 1107:
 EOF EOF
 ` `
 +
 </code> </code>
 +
 +
  • informatique/base_de_donnees/tips.txt
  • Dernière modification : 2024/05/07 14:32
  • de ben