Différences
Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentes Révision précédente Prochaine révision | Révision précédente Prochaine révisionLes deux révisions suivantes | ||
informatique:base_de_donnees:tips [2019/10/21 14:53] – ben | informatique:base_de_donnees:tips [2024/02/05 16:19] – ben | ||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
+ | ===== Restore en mode RAC ===== | ||
+ | |||
+ | < | ||
+ | alter pluggable database OEM2 close immediate instances=all; | ||
+ | |||
+ | |||
+ | run { | ||
+ | set until time " | ||
+ | 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 ''' | ||
+ | from gv$session s | ||
+ | where sql_id=' | ||
+ | and plsql_entry_object_id = 71000 | ||
+ | and sql_exec_start < sysdate - 100/ | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | ===== Dupliquer database ====== | ||
+ | |||
+ | < | ||
+ | cd $ORACLE_HOME/ | ||
+ | . oraenv | ||
+ | echo " | ||
+ | export ORACLE_SID=PDBIO_R2 | ||
+ | sqlplus / as sysdba | ||
+ | |||
+ | startup nomount pfile=' | ||
+ | |||
+ | export NLS_DATE_FORMAT=" | ||
+ | export NLS_LANG=AMERICAN.WE8ISO8859P1 | ||
+ | rman auxiliary / log=/ | ||
+ | |||
+ | run | ||
+ | { | ||
+ | SET UNTIL TIME " | ||
+ | SET ARCHIVELOG DESTINATION TO '/ | ||
+ | DUPLICATE DATABASE TO PDBIO_R2 BACKUP LOCATION '/ | ||
+ | switch datafile all; | ||
+ | switch tempfile all; | ||
+ | } | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | select NAME, | ||
+ | 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 '/ | ||
+ | * 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, | ||
+ | |||
+ | select | ||
+ | " | ||
+ | " | ||
+ | from( | ||
+ | select | ||
+ | (select sum(bytes/ | ||
+ | (select sum(bytes/ | ||
+ | from dual | ||
+ | ); | ||
+ | |||
+ | select name | ||
+ | , round(space_limit / 1024 / 1024) size_mb | ||
+ | , round(space_used | ||
+ | , decode(nvl(space_used, | ||
+ | from v$recovery_file_dest | ||
+ | order by name ; | ||
+ | </ | ||
+ | |||
+ | ===== ORA-12514 ===== | ||
+ | |||
+ | < | ||
+ | SQL> show parameter service ; | ||
+ | |||
+ | NAME TYPE | ||
+ | ------------------------------------ -------------------------------- | ||
+ | VALUE | ||
+ | ------------------------------ | ||
+ | service_names | ||
+ | PLNAVAMP_1DC2.fr.orpea.net | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | SQL> show parameter domain ; | ||
+ | |||
+ | NAME TYPE | ||
+ | ------------------------------------ -------------------------------- | ||
+ | VALUE | ||
+ | ------------------------------ | ||
+ | db_domain | ||
+ | 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/ | ||
+ | +ADATA/ | ||
+ | +ADATA/ | ||
+ | +ADATA/ | ||
+ | +ADATA/ | ||
+ | +ADATA/ | ||
+ | +ADATA/ | ||
+ | +ADATA/ | ||
+ | +ADATA/ | ||
+ | </ | ||
+ | |||
+ | * 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 | ||
+ | , | ||
+ | ,os_mb | ||
+ | ,total_mb | ||
+ | ,free_mb | ||
+ | ,name | ||
+ | ,path | ||
+ | from v$asm_disk ; | ||
+ | |||
+ | alter diskgroup reco resize all ; | ||
+ | </ | ||
+ | ===== Archivelogs ===== | ||
+ | |||
+ | * Voir logs | ||
+ | |||
+ | < | ||
+ | select to_char(timestamp,' | ||
+ | where SEVERITY <>' | ||
+ | |||
+ | select to_char(timestamp,' | ||
+ | </ | ||
+ | |||
+ | * Check réception archivelogs (à lancer sur standby) : | ||
+ | |||
+ | < | ||
+ | select | ||
+ | | ||
+ | from v$archived_log | ||
+ | where sequence# = (select max(sequence# | ||
+ | </ | ||
+ | |||
+ | |||
+ | * Checker config | ||
+ | |||
+ | < | ||
+ | set linesize 500 pages 0 | ||
+ | col value for a120 | ||
+ | col name for a25 | ||
+ | select name, value | ||
+ | from v$parameter | ||
+ | where name in (' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | SQL> ARCHIVE LOG LIST ; | ||
+ | Database log mode Archive Mode | ||
+ | Automatic archival | ||
+ | Archive destination | ||
+ | Oldest online log sequence | ||
+ | Next log sequence to archive | ||
+ | Current log sequence | ||
+ | |||
+ | SQL> | ||
+ | db_recovery_file_dest | ||
+ | db_recovery_file_dest_size | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | SQL> select dest_name, | ||
+ | LOG_ARCHIVE_DEST_1 | ||
+ | USE_DB_RECOVERY_FILE_DEST | ||
+ | </ | ||
+ | |||
+ | * Modif config | ||
+ | | ||
+ | < | ||
+ | alter system set fal_server=' | ||
+ | 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=' | ||
+ | alter system set LOG_ARCHIVE_DEST_2=' | ||
+ | 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, | ||
+ | |||
+ | execute immediate ' | ||
+ | |||
+ | end; | ||
+ | |||
+ | / | ||
+ | |||
+ | select * from V$RESTORE_POINT ; | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | ALTER DATABASE FLASHBACK ON; | ||
+ | create restore point BEFORE_MIG GUARANTEE FLASHBACK DATABASE; | ||
+ | </ | ||
+ | |||
+ | select * from v$restore_point; | ||
+ | |||
+ | < | ||
+ | RMAN> | ||
+ | |||
+ | using target database control file instead of recovery catalog | ||
+ | SCN RSP Time Type | ||
+ | ---------------- --------- ---------- --------- ---- | ||
+ | 1099414692 | ||
+ | </ | ||
+ | |||
+ | ===== 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 ' | ||
+ | column state format a16 heading ' | ||
+ | column mixed format a5 heading ' | ||
+ | column advice format a5 heading ' | ||
+ | |||
+ | |||
+ | select local_tran_id, | ||
+ | | ||
+ | | ||
+ | from | ||
+ | | ||
+ | order | ||
+ | by local_tran_id; | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | -- To Force Rollback | ||
+ | SQL> ROLLBACK FORCE ' | ||
+ | -- To Force Commit | ||
+ | SQL> COMMIT FORCE ' | ||
+ | </ | ||
+ | |||
+ | -> forced rollback status ; | ||
+ | |||
+ | < | ||
+ | Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (‘LOCAL TRANSACTION ID’); | ||
+ | </ | ||
+ | |||
+ | https:// | ||
+ | https:// | ||
+ | http:// | ||
+ | |||
+ | |||
+ | ===== Check last connections ===== | ||
+ | |||
+ | < | ||
+ | select to_timestamp(to_char(logon_Time, | ||
+ | </ | ||
+ | |||
+ | ===== Unlock users ===== | ||
+ | |||
+ | < | ||
+ | column column_name format a30 | ||
+ | set linesize 300 | ||
+ | SELECT username, | ||
+ | | ||
+ | FROM dba_users WHERE ACCOUNT_STATUS like ' | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | ALTER USER HEXALIS_DECISION identified by < | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | ALTER USER HEXALIS_DECISION account unlock ; | ||
+ | </ | ||
+ | |||
+ | ===== Drop user connecté ===== | ||
+ | |||
+ | < | ||
+ | SYS@TBBIO1> | ||
+ | ORACLE instance started. | ||
+ | |||
+ | Total System Global Area 8589931880 bytes | ||
+ | Fixed Size | ||
+ | Variable Size 5905580032 bytes | ||
+ | Database Buffers | ||
+ | Redo Buffers | ||
+ | Database mounted. | ||
+ | Database opened. | ||
+ | SYS@TBBIO1> | ||
+ | |||
+ | CON_ID CON_NAME | ||
+ | ---------- ------------------------------ ---------- ---------- | ||
+ | 2 PDB$SEED | ||
+ | 3 TFRHNO01 | ||
+ | 4 TFRHNO01Q | ||
+ | 5 TFRMPY01 | ||
+ | SYS@TBBIO1> | ||
+ | |||
+ | SYS@TBBIO1> | ||
+ | |||
+ | CON_ID CON_NAME | ||
+ | ---------- ------------------------------ ---------- ---------- | ||
+ | 5 TFRMPY01 | ||
+ | SYS@TBBIO1> | ||
+ | |||
+ | Pluggable database altered. | ||
+ | |||
+ | SYS@TBBIO1> | ||
+ | |||
+ | CON_ID CON_NAME | ||
+ | ---------- ------------------------------ ---------- ---------- | ||
+ | 5 TFRMPY01 | ||
+ | SYS@TBBIO1> | ||
+ | |||
+ | Session altered. | ||
+ | |||
+ | SYS@TBBIO1> | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== Taille database ===== | ||
+ | |||
+ | < | ||
+ | select | ||
+ | " | ||
+ | " | ||
+ | from( | ||
+ | select | ||
+ | (select sum(bytes/ | ||
+ | (select sum(bytes/ | ||
+ | from dual | ||
+ | ); | ||
+ | </ | ||
+ | |||
+ | select sum (bytes)/ | ||
+ | | ||
+ | < | ||
+ | WITH | ||
+ | | ||
+ | ( | ||
+ | select TABLESPACE_NAME TS, round(sum(bytes/ | ||
+ | from cdb_data_files | ||
+ | group by TABLESPACE_NAME | ||
+ | union | ||
+ | | ||
+ | from cdb_temp_files | ||
+ | group by TABLESPACE_NAME | ||
+ | ), | ||
+ | TS_USED as | ||
+ | ( | ||
+ | select TABLESPACE_NAME TS, round(sum(bytes/ | ||
+ | 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,' | ||
+ | , count(1) sessions_cnt | ||
+ | from | ||
+ | gv$session | ||
+ | where | ||
+ | 1=1 | ||
+ | and type=' | ||
+ | and service_name not like ' | ||
+ | and username not like ' | ||
+ | and username not like ' | ||
+ | group by | ||
+ | username | ||
+ | -- , machine | ||
+ | , inst_id | ||
+ | , service_name | ||
+ | , status | ||
+ | , to_char(logon_time,' | ||
+ | order by | ||
+ | sessions_cnt desc; | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | select sid,serial# from v$session where username = ' | ||
+ | select 'alter system kill session ''' | ||
+ | </ | ||
+ | |||
+ | * RAC | ||
+ | |||
+ | < | ||
+ | select sid, | ||
+ | select 'alter system kill session ''' | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== FRA ===== | ||
+ | |||
+ | < | ||
+ | SYS@TEBIO1> | ||
+ | |||
+ | NAME TYPE | ||
+ | ------------------------------------ -------------------------------- | ||
+ | VALUE | ||
+ | ------------------------------ | ||
+ | db_recovery_file_dest | ||
+ | +RECO | ||
+ | db_recovery_file_dest_size | ||
+ | 200G | ||
+ | recovery_parallelism | ||
+ | 0 | ||
+ | remote_recovery_file_dest | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=400g SCOPE=BOTH ; | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | select name | ||
+ | , round(space_limit / 1024 / 1024) size_mb | ||
+ | , round(space_used | ||
+ | , decode(nvl(space_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 / | ||
+ | </ | ||
+ | |||
+ | srvctl modify database –d ORCL –pwfile +DATA/ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== Tempfile ===== | ||
+ | |||
+ | < | ||
+ | SYS@PDBIO1> | ||
+ | |||
+ | NAME | ||
+ | -------------------------------------------------------------------------------- | ||
+ | +DATA/ | ||
+ | +DATA/ | ||
+ | +DATA/ | ||
+ | +DATA/ | ||
+ | +DATA/ | ||
+ | </ | ||
+ | |||
+ | ===== Change ORACLE_HOME ===== | ||
+ | |||
+ | < | ||
+ | oda1db0host# | ||
+ | oda1db0host# | ||
+ | oda1db0host# | ||
+ | </ | ||
+ | |||
+ | ===== Datapatch ===== | ||
+ | |||
+ | < | ||
+ | oda2db0host# | ||
+ | oda2db0host# | ||
+ | </ | ||
+ | |||
+ | ===== Recompile invalid objects ===== | ||
+ | |||
+ | @$ORACLE_HOME/ | ||
+ | |||
+ | ===== PDBs ====== | ||
+ | |||
+ | * show | ||
+ | < | ||
+ | SQL> show pdbs | ||
+ | |||
+ | CON_ID CON_NAME | ||
+ | ---------- ------------------------------ ---------- ---------- | ||
+ | 2 PDB$SEED | ||
+ | 3 PFRCHA02 | ||
+ | 4 PFRCHA02Q | ||
+ | 5 PFRNPC01 | ||
+ | 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> | ||
+ | |||
+ | Warning: PDB altered with errors. | ||
+ | |||
+ | * clean violations | ||
+ | |||
+ | < | ||
+ | exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => ' | ||
+ | </ | ||
+ | |||
+ | </ | ||
+ | |||
+ | * cd to PDB | ||
+ | |||
+ | < | ||
+ | ALTER SESSION SET CONTAINER=PFRNPC01; | ||
+ | </ | ||
+ | |||
+ | * Restricted | ||
+ | |||
+ | Check si datapatch OK | ||
+ | |||
+ | < | ||
+ | SQL> show pdbs | ||
+ | |||
+ | CON_ID CON_NAME | ||
+ | ---------- ------------------------------ ---------- ---------- | ||
+ | 2 PDB$SEED | ||
+ | 3 PHRQ_OEM | ||
+ | 4 PHQR_OEM1 | ||
+ | 5 PHQR_OAS1 | ||
+ | SQL> alter pluggable database PHQR_OEM1 open force ; | ||
+ | |||
+ | Pluggable database altered. | ||
+ | |||
+ | SQL> show pdbs , | ||
+ | |||
+ | CON_ID CON_NAME | ||
+ | ---------- ------------------------------ ---------- ---------- | ||
+ | 2 PDB$SEED | ||
+ | 3 PHRQ_OEM | ||
+ | 4 PHQR_OEM1 | ||
+ | 5 PHQR_OAS1 | ||
+ | |||
+ | 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; | ||
+ | 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; | ||
+ | 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' | ||
+ | |||
+ | srvctl modify database -db frintegp_1dc1 -role PRIMARY -startoption OPEN | ||
+ | stop /start | ||
+ | |||
+ | startup mount de l' | ||
+ | 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 | ||
+ | ((TYPE=DEDICATED)(BROKERS=1)), | ||
+ | | ||
+ | dg_broker_config_file1 | ||
+ | +FPDAT/ | ||
+ | -sirhap-01.dbf | ||
+ | dg_broker_config_file2 | ||
+ | +FPDAT/ | ||
+ | -sirhap-02.dbf | ||
+ | |||
+ | NAME TYPE | ||
+ | ------------------------------------ -------------------------------- | ||
+ | VALUE | ||
+ | ------------------------------ | ||
+ | dg_broker_start | ||
+ | TRUE | ||
+ | use_dedicated_broker | ||
+ | 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, | ||
+ | where name like ' | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | select name, value, time_computed, | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | Primary | ||
+ | |||
+ | SQL> select scn_to_timestamp(current_scn) from v$database; | ||
+ | |||
+ | select sysdate, | ||
+ | from v$archive_dest_status | ||
+ | where type=' | ||
+ | and gap_status !='NO GAP'; | ||
+ | |||
+ | | ||
+ | from v$dataguard_status | ||
+ | where severity in (' | ||
+ | and timestamp > (sysdate -1); | ||
+ | |||
+ | | ||
+ | from gv$archive_dest_status | ||
+ | where type=' | ||
+ | and status!=' | ||
+ | or error is not null; | ||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | col name format a45 | ||
+ | | ||
+ | from v$dataguard_stats | ||
+ | where name=' | ||
+ | and value > '+00 00: | ||
+ | |||
+ | col name format a45 | ||
+ | | ||
+ | from v$dataguard_stats | ||
+ | where name=' | ||
+ | and value > '+00 00: | ||
+ | |||
+ | | ||
+ | from gv$recovery_progress group by inst_id; | ||
+ | |||
+ | set line 500 pages 9999 | ||
+ | col severity form a40 | ||
+ | col message form a131 | ||
+ | select SEVERITY, | ||
+ | select | ||
+ | |||
+ | </ | ||
+ | |||
+ | * Check réception archives logs | ||
+ | | ||
+ | < | ||
+ | select to_char(timestamp,' | ||
+ | where SEVERITY <>' | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | select | ||
+ | | ||
+ | from v$archived_log | ||
+ | where sequence# = (select max(sequence# | ||
+ | </ | ||
+ | |||
+ | https:// | ||
+ | http:// | ||
+ | |||
+ | * Créer standby + config dataguard | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | < | ||
+ | ****** SOURCE | ||
+ | |||
+ | # Activer FORCE LOGGING | ||
+ | alter database force logging; | ||
+ | |||
+ | # CREER les STANDBY Logs | ||
+ | alter database add standby logfile thread 1 group 11 (' | ||
+ | alter database add standby logfile thread 1 group 12 (' | ||
+ | alter database add standby logfile thread 1 group 13 (' | ||
+ | alter database add standby logfile thread 2 group 14 (' | ||
+ | alter database add standby logfile thread 2 group 15 (' | ||
+ | alter database add standby logfile thread 2 group 16 (' | ||
+ | |||
+ | # STANDBY Management AUTO | ||
+ | alter system set standby_file_management = ' | ||
+ | |||
+ | # Récupérer le Fichier PASSWORD | ||
+ | asmcmd | ||
+ | cp +DATA/ | ||
+ | |||
+ | # Récupérer le SPFILE et le Modifier | ||
+ | sqlplus / as sysdba | ||
+ | create pfile='/ | ||
+ | |||
+ | Copier les fichiers vers la cible et le modifier. | ||
+ | |||
+ | dbs | ||
+ | cp initPEPAT1.ora $ORACLE_HOME/ | ||
+ | cp orapwPEPAT1 $ORACLE_HOME/ | ||
+ | |||
+ | virer unedrscore | ||
+ | *.cluster_database=false | ||
+ | virer controlfile, | ||
+ | |||
+ | |||
+ | ***** 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 = / | ||
+ | (SID_NAME = CDB_RA))) | ||
+ | |||
+ | # Create DUMP file on both servers | ||
+ | mkdir -p / | ||
+ | |||
+ | # Start Database NOMOUT with pfile | ||
+ | startup nomount pfile=' | ||
+ | |||
+ | # Create the STANDBY via RMAN Duplicate | ||
+ | rman | ||
+ | connect target sys/ | ||
+ | connect auxiliary sys/ | ||
+ | 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/ | ||
+ | *.cluster_database=true | ||
+ | *.control_files=' | ||
+ | |||
+ | |||
+ | output file name=+DATA/ | ||
+ | output file name=+RECO/ | ||
+ | |||
+ | |||
+ | # Remove spfile | ||
+ | remove spfilePEPAT1.ora | ||
+ | |||
+ | # Shutdown the STANDBY and RESTART In Mount. | ||
+ | shutdown immediate; | ||
+ | startup mount | ||
+ | |||
+ | # Create spfile in ASM | ||
+ | create spfile=' | ||
+ | shutdown immediate; | ||
+ | |||
+ | # Modifier init.ora | ||
+ | SPFILE=' | ||
+ | |||
+ | # Cluster Creation | ||
+ | srvctl add database -d P72EPAT -o $ORACLE_HOME -c RAC -r PHYSICAL_STANDBY -s MOUNT -n P72EPAT -a " | ||
+ | srvctl add instance -d P72EPAT -i PEPAT1 -n oda2db0host | ||
+ | srvctl add instance -d P72EPAT -i PEPAT2 -n oda2db1host | ||
+ | srvctl modify database -d P72EPAT -p ' | ||
+ | |||
+ | 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 ''/ | ||
+ | |||
+ | # Supprimer le Password FIle | ||
+ | rm $ORACLE_HOME/ | ||
+ | |||
+ | # 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 ' | ||
+ | |||
+ | ***** 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=' | ||
+ | alter system set dg_broker_config_file2=' | ||
+ | alter system set dg_broker_start=TRUE scope=both; | ||
+ | |||
+ | alter system set dg_broker_config_file1=' | ||
+ | alter system set dg_broker_config_file2=' | ||
+ | alter system set dg_broker_start=TRUE scope=both; | ||
+ | |||
+ | # Configuration | ||
+ | dgmgrl / | ||
+ | create configuration DG_TESTRA as primary database is CDB_RA connect identifier is " | ||
+ | add database CDB_RAST as connect identifier is " | ||
+ | enable configuration; | ||
+ | |||
+ | # Check Dataguard | ||
+ | On PRIMARY | ||
+ | set linesize 1000 | ||
+ | select * from V$DATAGUARD_STATUS ; | ||
+ | select thread#, | ||
+ | |||
+ | On STANDBY | ||
+ | select thread#, | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
===== Voir ce qu'il se passe ===== | ===== Voir ce qu'il se passe ===== | ||