===== Restore en mode RAC ===== 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; ===== Process sessions ===== 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 ===== Dupliquer database ===== 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; } select NAME,OPEN_MODE from v$database; show pdbs ; shutdown abort ; startup mount exclusive restrict ; drop database ; ===== Listener ===== Forcer prise en compte rapide des services BDD sqlplus / as sysdba alter system register ; ===== 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 ===== 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 ; ===== ORA-12514 ===== SQL> show parameter service ; NAME TYPE ------------------------------------ -------------------------------- VALUE ------------------------------ service_names string PLNAVAMP_1DC2.fr.orpea.net SQL> show parameter domain ; NAME TYPE ------------------------------------ -------------------------------- VALUE ------------------------------ db_domain string fr.orpea.net SQL> alter system reset db_domain ; System altered. 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 ===== ASM ===== * Check liste datafiles ; 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 * Resize 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 ; ===== Archivelogs ===== * Voir logs 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 ; * Check réception archivelogs (à lancer sur standby) : 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); * Checker config 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'); / 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 SQL> select dest_name,status,destination from V$ARCHIVE_DEST; LOG_ARCHIVE_DEST_1 VALID USE_DB_RECOVERY_FILE_DEST * Modif config 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 = ''; ===== Recovery point ===== -- 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 ; ALTER DATABASE FLASHBACK ON; create restore point BEFORE_MIG GUARANTEE FLASHBACK DATABASE; select * from v$restore_point; 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 ===== Pending sessions ===== 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; -- To Force Rollback SQL> ROLLBACK FORCE '96.22.163456' -- To Force Commit SQL> COMMIT FORCE '96.22.163456' → forced rollback status ; Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (‘LOCAL TRANSACTION ID’); [[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 ===== 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; ===== Unlock users ===== column column_name format a30 set linesize 300 SELECT username, account_status FROM dba_users WHERE ACCOUNT_STATUS like '%LOCKED%' ; ALTER USER HEXALIS_DECISION identified by account unlock ; ALTER USER HEXALIS_DECISION account unlock ; ===== Drop user connecté ===== 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 ; ===== Taille 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 sum (bytes)/1024/1024/1024 size_GB from dba_segments ; 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 ; ===== Sessions ===== 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; select sid,serial# from v$session where username = 'FRMPY01' ; select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from gv$session where username ='FRMPY01'; * RAC 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' ; ===== FRA ===== 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 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=400g SCOPE=BOTH ; 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 ; ===== Copie password ASM ===== 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 srvctl modify database –d ORCL –pwfile +DATA/DRORCL/PASSWORD/pwdorcl ===== Tempfile ===== 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 ===== Change ORACLE_HOME ===== 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 ===== Datapatch ===== oda2db0host# . oraenv oda2db0host# cd $ORACLE_HOME && ./OPatch/datapatch -db PHBIO1 -verbose ===== Recompile invalid objects ===== @$ORACLE_HOME/rdbms/admin/utlrp.sql ===== PDBs ===== * show 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> 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; * change mode SQL> alter pluggable database pdb$seed open read only ; Warning: PDB altered with errors. * clean violations exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'PFRBNO01'); * cd to PDB ALTER SESSION SET CONTAINER=PFRNPC01; * Restricted Check si datapatch OK 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 ; SQL> alter session set container=PHQR_OEM1 ; Session altered. SQL> alter system disable restricted session; ===== Dataguard ===== * start Redo Apply ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; * Switch manuel ## 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 * Switch semi-manuel 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; * Logs du broker : drc* file * Check de la conf du broker 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 * Restart du broker SQL> alter system set dg_broker_start=FALSE scope=BOTH SID='*' ; SQL> alter system set dg_broker_start=TRUE scope=BOTH SID='*' ; * Switchover dgmgrl sys@P72HBIO show configuration validate database show database show database verbose * Recréer config 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; * Check lag 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; select name, value, time_computed, datum_time from v$dataguard_stats where name='%lag'; 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; * Check réception archives logs select to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp",MESSAGE,SEVERITY from v$dataguard_status where SEVERITY <>'Control'; 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); [[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}} ****** 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#; ===== Voir ce qu'il se passe ===== 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 s.status='ACTIVE' / ===== Mettre le résultat d'une requête sql dans une variable ===== vMaxSeqApply=`sqlplus -s "/ as sysdba" <