Différences

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

--- informatique:base_de_donnees:tips [2024/01/31 20:53] – [ASM] ben
+++ informatique:base_de_donnees:tips [2024/05/07 14:32] (Version actuelle) – ben
@@ Ligne 3: / Ligne 3: @@
 <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')";
@@ Ligne 11: / Ligne 10: @@
 alter pluggable database OEM2 open resetlogs; <= don't apply archivelogs
 }
 alter pluggable database OEM2 open instances=all;
 </code>
@@ Ligne 18: / Ligne 18: @@
 <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
+and sql_exec_start <sysdate - 100/24/60/60
 ;
 </code>
-===== Dupliquer database ======
+===== Dupliquer database =====
 <code>
 cd $ORACLE_HOME/dbs
 . oraenv
-echo "DB_NAME=PDBIO_R2" > initPDBIO_R2.ora
+echo "DB_NAME=PDBIO_R2"> initPDBIO_R2.ora
 export ORACLE_SID=PDBIO_R2
 sqlplus / as sysdba
@@ Ligne 41: / Ligne 42: @@
 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>
@@ Ligne 57: / Ligne 59: @@
 startup mount exclusive restrict ;
 drop database ;
 </code>
-===== Listener ======
+===== Listener =====
 Forcer prise en compte rapide des services BDD
@@ Ligne 66: / Ligne 69: @@
 sqlplus / as sysdba
 alter system register ;
 </code>
 ===== ORA-xxx =====
-  * ORA-01033 : check password primary <=> standby
+  * 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
@@ Ligne 82: / Ligne 86: @@
 select database_role from v$database ;
 SELECT LOG_MODE FROM SYS.V$DATABASE;
 set line 200
@@ Ligne 92: / Ligne 96: @@
 "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)"
@@ Ligne 104: / Ligne 108: @@
 from v$recovery_file_dest
 order by name ;
-</code>
+</code>
 ===== ORA-12514 =====
@@ Ligne 117: / Ligne 122: @@
 service_names                        string
 PLNAVAMP_1DC2.fr.orpea.net
 </code>
@@ Ligne 128: / Ligne 134: @@
 db_domain                            string
 fr.orpea.net
 </code>
@@ Ligne 134: / Ligne 141: @@
 System altered.
 </code>
@@ Ligne 142: / Ligne 150: @@
 ERROR at line 1:
 ORA-32010: cannot find entry to delete in SPFILE
-</code>
+</code>
-===== ASM ======
+===== ASM =====
   * Check liste datafiles ;
@@ Ligne 163: / Ligne 171: @@
 +ADATA/antarp/cpsure-antarp-01.dbf
 +ADATA/antarp/antares_data-antarp-02.dbf
 </code>
   * Resize
 <code>
@@ Ligne 175: / Ligne 183: @@
 col name form a15
 select
-	header_status
+    header_status
-	,mode_status
+    ,mode_status
-	,os_mb
+    ,os_mb
-	,total_mb
+    ,total_mb
-	,free_mb
+    ,free_mb
-	,name
+    ,name
-	,path
+    ,path
 from v$asm_disk ;
 alter diskgroup reco resize all ;
 </code>
 ===== Archivelogs =====
@@ Ligne 195: / Ligne 205: @@
 select to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp",MESSAGE,SEVERITY from v$dataguard_status ;
 </code>
@@ Ligne 204: / Ligne 215: @@
  from v$archived_log
  where sequence# = (select max(sequence#) from v$archived_log);
-</code>
+</code>
   * Checker config
 <code>
@@ Ligne 220: / Ligne 231: @@
  'standby_file_management');
 /
 </code>
@@ Ligne 234: / Ligne 246: @@
 db_recovery_file_dest                string                           +FPFRA
 db_recovery_file_dest_size           big integer                      152G
 </code>
@@ Ligne 240: / Ligne 253: @@
 LOG_ARCHIVE_DEST_1                                                                                                                                                                             VALID
 USE_DB_RECOVERY_FILE_DEST
 </code>
   * Modif config
 <code>
 alter system set fal_server='frantarp_1dc2' scope=both;
@@ Ligne 251: / Ligne 265: @@
 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>
@@ Ligne 259: / Ligne 274: @@
 alter system set db_flashback_retention_target=2880 scope=both sid='*';
 -- 3 days (3 * 24 * 60)
@@ Ligne 279: / Ligne 294: @@
 select * from V$RESTORE_POINT ;
-</code>
+</code>
 <code>
 ALTER DATABASE FLASHBACK ON;
 create restore point BEFORE_MIG GUARANTEE FLASHBACK DATABASE;
 </code>
-  select * from v$restore_point;
+<code>
+select * from v$restore_point;
+</code>
 <code>
@@ Ligne 295: / Ligne 315: @@
 ---------------- --------- ---------- --------- ----
 1099414692                 GUARANTEED 06-APR-23 BEFORE_SWITCH
 </code>
@@ Ligne 309: / Ligne 330: @@
 column mixed format a5 heading 'Mixed'
 column advice format a5 heading 'Advice'
 select local_tran_id,
@@ Ligne 318: / Ligne 338: @@
 order
  by local_tran_id;
 </code>
@@ Ligne 325: / Ligne 346: @@
 -- To Force Commit
 SQL> COMMIT FORCE  '96.22.163456'
 </code>
--> forced rollback status ;
+→ 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://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/|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|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>
@@ Ligne 349: / Ligne 372: @@
 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>
@@ Ligne 404: / Ligne 430: @@
 SYS@TBBIO1> DROP USER FRMPY01 CASCADE ;
 </code>
 ===== Taille database =====
@@ Ligne 414: / Ligne 440: @@
 "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>
-  select sum (bytes)/1024/1024/1024 size_GB  from dba_segments ;
 <code>
 WITH
@@ Ligne 443: / Ligne 473: @@
   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>
@@ Ligne 478: / Ligne 509: @@
 order by
         sessions_cnt desc;
 </code>
@@ Ligne 483: / Ligne 515: @@
 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>
+  * 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
@@ Ligne 509: / Ligne 542: @@
 remote_recovery_file_dest            string
 </code>
 <code>
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=400g SCOPE=BOTH ;
 </code>
@@ Ligne 522: / Ligne 557: @@
 from v$recovery_file_dest
 order by name ;
 </code>
@@ Ligne 529: / Ligne 565: @@
 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>
-  srvctl modify database –d ORCL –pwfile +DATA/DRORCL/PASSWORD/pwdorcl
+<code>
+srvctl modify database –d ORCL –pwfile +DATA/DRORCL/PASSWORD/pwdorcl
+</code>
 ===== Tempfile =====
@@ Ligne 541: / Ligne 577: @@
 <code>
 SYS@PDBIO1> select name from v$tempfile;
 NAME
 --------------------------------------------------------------------------------
@@ Ligne 549: / Ligne 585: @@
 +DATA/P21DBIO/CCE2ECA49E5F05B5E0533E60180A5B40/TEMPFILE/temp.1825.1125772465
 +DATA/P21DBIO/D58B172F55E0EB56E0533E60180A7510/TEMPFILE/temp.1852.1131484295
 </code>
@@ Ligne 557: / Ligne 594: @@
 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>
@@ Ligne 564: / Ligne 602: @@
 oda2db0host# . oraenv
 oda2db0host# cd $ORACLE_HOME && ./OPatch/datapatch -db PHBIO1 -verbose
 </code>
 ===== Recompile invalid objects =====
-  @$ORACLE_HOME/rdbms/admin/utlrp.sql
+<code>
+@$ORACLE_HOME/rdbms/admin/utlrp.sql
-===== PDBs ======
+</code>
+===== PDBs =====
   * show
 <code>
 SQL> show pdbs
@@ Ligne 583: / Ligne 626: @@
 PFRNPC01                       READ WRITE NO
 SQL>
 </code>
@@ Ligne 591: / Ligne 635: @@
 alter pluggable database all save state instances=all;
 SELECT * FROM cdb_pdb_saved_states;
 </code>
@@ Ligne 599: / Ligne 644: @@
 Warning: PDB altered with errors.
+</code>
+  * clean violations
+<code>
+exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'PFRBNO01');
 </code>
@@ Ligne 605: / Ligne 658: @@
 <code>
 ALTER SESSION SET CONTAINER=PFRNPC01;
 </code>
   * Restricted
 Check si datapatch OK
 <code>
@@ Ligne 634: / Ligne 688: @@
 SQL> alter pluggable database PHQR_OAS1 open force ;
 </code>
@@ Ligne 642: / Ligne 697: @@
 SQL> alter system disable restricted session;
 </code>
 ===== Dataguard =====
   * start Redo Apply
-  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
+<code>
+ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
+</code>
   * Switch manuel
@@ Ligne 679: / Ligne 739: @@
 # Modify CLUSTER Configuration
 srvctl modify database -d FRTLMP_1DC1 -r PRIMARY -s OPEN
 </code>
@@ Ligne 700: / Ligne 761: @@
 ADD DATABASE frintegp_1dc2 AS CONNECT IDENTIFIER IS frintegp_1dc2;
 ENABLE CONFIGURATION;
 </code>
   * Logs du broker : drc* file
   * Check de la conf du broker
@@ Ligne 732: / Ligne 793: @@
 use_dedicated_broker                 boolean
 FALSE
 </code>
@@ Ligne 739: / Ligne 801: @@
 SQL> alter system set dg_broker_start=FALSE scope=BOTH SID='*' ;
 SQL> alter system set dg_broker_start=TRUE scope=BOTH SID='*' ;
 </code>
@@ Ligne 749: / Ligne 812: @@
 show database <STANDBY>
 show database verbose <STANDBY>
 </code>
@@ Ligne 758: / Ligne 822: @@
 ADD DATABASE db11g_stby AS CONNECT IDENTIFIER IS db11g_stby;
 ENABLE CONFIGURATION;
 </code>
@@ Ligne 765: / Ligne 830: @@
 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;
+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>
@@ Ligne 780: / Ligne 847: @@
  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);
+ and timestamp> (sysdate -1);
  select sysdate,status,error
  from gv$archive_dest_status
@@ Ligne 792: / Ligne 859: @@
  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';
+ 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';
+ 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
@@ Ligne 817: / Ligne 882: @@
 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>
@@ Ligne 832: / Ligne 898: @@
  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|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|http://www.datadisk.co.uk/html_docs/oracle_dg/cheatsheet.htm]]
   * Créer standby + config dataguard
-{{ :informatique:base_de_donnees:sample_initfile_dg.txt |}}
+{{:informatique:base_de_donnees:sample_initfile_dg.txt|sample_initfile_dg.txt}}
 <code>
 ****** SOURCE
@@ Ligne 858: / Ligne 925: @@
 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
@@ Ligne 875: / Ligne 942: @@
 *.cluster_database=false
 virer controlfile, log archive config, broker
 *****   CIBLE
@@ Ligne 891: / Ligne 957: @@
   )
+# Add Connexion in listener.ora
-# Add Connexion in listener.ora
 SID_LIST_LISTENER =
   (SID_LIST = (SID_DESC =
@@ Ligne 931: / Ligne 995: @@
 }
 # 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 spfile
+remove spfilePEPAT1.ora
-remove spfilePEPAT1.ora
 # Shutdown the STANDBY and RESTART In Mount.
@@ Ligne 956: / Ligne 1018: @@
 # 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'
@@ Ligne 966: / Ligne 1028: @@
 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
@@ Ligne 978: / Ligne 1040: @@
 CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/P72EPAT/PEPAT-snapshot-01.ctl';
 ***** BROKER
 # Vérifier les TNS sur les 2 Noeuds
@@ Ligne 991: / Ligne 1053: @@
     (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = P72EPAT)))
+# Activer le BROKER sur les 2 Noeuds
-# 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='*';
@@ Ligne 1007: / Ligne 1068: @@
 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>
 ===== Voir ce qu'il se passe =====
 <code>
 set head off pages 0 lines 120
 select  p.spid,s.sid, s.serial#, substr(s.username,1,10)||','||process,
 s.program,s.module,s.status, osuser ,
 buffer_gets, disk_reads, executions,users_executing, first_load_time,'**',s.wait_class
 ,a.rows_processed,  a.sql_id,  sql_text -- sql_fulltext
 from v$process p, v$session s,  v$sqlarea a
 where a.address=s.sql_address
 and p.addr=s.paddr
-and users_executing > 0
+and users_executing> 0
 and s.status='ACTIVE'
 /
 </code>
 ===== Mettre le résultat d'une requête sql dans une variable =====
 <code>
 vMaxSeqApply=`sqlplus -s "/ as sysdba" <<EOF
 set head off
@@ Ligne 1045: / Ligne 1107: @@
 EOF
 `
 </code>