Différences

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

--- informatique:base_de_donnees:tips [2023/10/26 17:10] – ben
+++ informatique:base_de_donnees:tips [2024/02/05 16:21] (Version actuelle) – [PDBs] ben
@@ Ligne 1: / Ligne 1: @@
+===== 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 ======
@@ Ligne 105: / Ligne 165: @@
 </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 =====
@@ Ligne 173: / Ligne 254: @@
 ===== 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>
@@ Ligne 420: / Ligne 527: @@
 <code>
-orapwd file=orapwt21cbio password=Dedalus1 ignorecase=n format=12 force=Y
+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>
@@ Ligne 476: / Ligne 583: @@
 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>
@@ Ligne 484: / Ligne 599: @@
 Warning: PDB altered with errors.
+</code>
+  * clean violations
+<code>
+exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'PFRBNO01');
 </code>
@@ Ligne 530: / Ligne 651: @@
 ===== Dataguard =====
+  * start Redo Apply
+  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
   * Switch manuel
@@ Ligne 718: / Ligne 842: @@
 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
+  * Créer standby + config dataguard
+{{ :informatique:base_de_donnees: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>
 ===== Voir ce qu'il se passe =====