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édenteDernière révisionLes deux révisions suivantes | ||
informatique:base_de_donnees:tips [2023/07/07 15:02] – [Dataguard] ben | informatique:base_de_donnees:tips [2024/02/05 16:21] – [PDBs] 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 ====== | ===== Listener ====== | ||
Ligne 105: | Ligne 165: | ||
</ | </ | ||
+ | * 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 ===== | ===== Archivelogs ===== | ||
Ligne 173: | Ligne 254: | ||
===== Recovery point ===== | ===== 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 ; | ||
+ | </ | ||
< | < | ||
Ligne 315: | Ligne 422: | ||
select sum (bytes)/ | 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 ===== | ===== Sessions ===== | ||
Ligne 397: | Ligne 527: | ||
< | < | ||
- | orapwd file=orapwt21cbio password=Dedalus1 | + | orapwd file=orapwt21cbio password=password |
asmcmd pwcopy --dbuniquename T21CBIO / | asmcmd pwcopy --dbuniquename T21CBIO / | ||
</ | </ | ||
Ligne 453: | Ligne 583: | ||
5 PFRNPC01 | 5 PFRNPC01 | ||
SQL> | 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; | ||
</ | </ | ||
Ligne 461: | Ligne 599: | ||
Warning: PDB altered with errors. | Warning: PDB altered with errors. | ||
+ | </ | ||
+ | |||
+ | * clean violations | ||
+ | |||
+ | < | ||
+ | exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => ' | ||
</ | </ | ||
Ligne 506: | Ligne 650: | ||
</ | </ | ||
===== Dataguard ===== | ===== 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 | * Switch semi-manuel | ||
Ligne 661: | Ligne 842: | ||
https:// | https:// | ||
http:// | 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 ===== | ||