informatique:base_de_donnees:tips

Différences

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

Lien vers cette vue comparative

Prochaine révision		 Révision précédente
informatique:base_de_donnees:tips [2010/03/09 08:37] – modification externe 127.0.0.1informatique: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 ======
 +
 +Forcer prise en compte rapide des services BDD
 +
 +<code>
 +sqlplus / as sysdba
 +alter system register ;
 +</code>
 +
 +===== 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 =====
 +
 +<code>
 +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 ;
 +</code>  
 +
 +===== ORA-12514 =====
 +
 +<code>
 +SQL> show parameter service ;
 +
 +NAME                                 TYPE
 +------------------------------------ --------------------------------
 +VALUE
 +------------------------------
 +service_names                        string
 +PLNAVAMP_1DC2.fr.orpea.net
 +</code>
 +
 +<code>
 +SQL> show parameter domain ;
 +
 +NAME                                 TYPE
 +------------------------------------ --------------------------------
 +VALUE
 +------------------------------
 +db_domain                            string
 +fr.orpea.net
 +</code>
 +
 +<code>
 +SQL> alter system reset db_domain ;
 +
 +System altered.
 +</code>
 +
 +<code>
 +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
 +</code>
 +
 +
 +===== ASM ======
 +
 +  * Check liste datafiles ;
 +
 +<code>
 +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
 +</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 =====
 +
 +  * Voir logs
 +
 +<code>
 +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 ;
 +</code>
 +
 +  * Check réception archivelogs (à lancer sur standby) :
 +
 +<code>
 +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);
 +</code>
 +
 +
 +  * Checker config 
 +
 +<code>
 +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');
 +/
 +</code>
 +
 +<code>
 +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
 +</code>
 +
 +<code>
 +SQL> select dest_name,status,destination from V$ARCHIVE_DEST;
 +LOG_ARCHIVE_DEST_1                                                                                                                                                                             VALID
 +USE_DB_RECOVERY_FILE_DEST
 +</code>
 +
 +  * Modif config
 +  
 +<code>
 +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 = '';
 +</code>
 +
 +===== 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>
 +ALTER DATABASE FLASHBACK ON;
 +create restore point BEFORE_MIG GUARANTEE FLASHBACK DATABASE;
 +</code>
 +
 +  select * from v$restore_point;
 +
 +<code>
 +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
 +</code>
 +
 +===== Pending sessions =====
 +
 +<code>
 +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;
 +</code>
 +
 +<code>
 +-- To Force Rollback
 +SQL> ROLLBACK FORCE  '96.22.163456'
 +-- To Force Commit
 +SQL> COMMIT FORCE  '96.22.163456'
 +</code>
 +
 +-> 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://oraclefiles.com/2019/03/04/resolving-in-doubt-transactions/\\
 +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>
 +
 +===== Unlock users =====
 +
 +<code>
 +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>
 +
 +===== Drop user connecté =====
 +
 +<code>
 +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 ;
 +</code>
 +
 +
 +===== Taille database =====
 +
 +<code>
 +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
 +);
 +</code>
 +
 +  select sum (bytes)/1024/1024/1024 size_GB  from dba_segments ;
 +  
 +<code>
 +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 ;
 +</code>
 +
 +===== Sessions =====
 +
 +<code>
 +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;
 +</code>
 +
 +<code>
 +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>
 +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
 +
 +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
 +</code>
 +
 +<code>
 +ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=400g SCOPE=BOTH ;
 +</code>
 +
 +<code>
 +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 ;
 +</code>
 +
 +===== Copie password ASM =====
 +
 +<code>
 +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
 +
 +
 +
 +
 +
 +===== Tempfile =====
 +
 +<code>
 +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
 +</code>
 +
 +===== Change ORACLE_HOME =====
 +
 +<code>
 +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
 +</code>
 +
 +===== Datapatch =====
 +
 +<code>
 +oda2db0host# . oraenv
 +oda2db0host# cd $ORACLE_HOME && ./OPatch/datapatch -db PHBIO1 -verbose
 +</code>
 +
 +===== Recompile invalid objects =====
 +
 +  @$ORACLE_HOME/rdbms/admin/utlrp.sql
 +
 +===== PDBs ======
 +
 +  * show
 +<code>
 +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>
 +</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>
 +
 +  * change mode
 +
 +<code>
 +SQL>  alter pluggable database pdb$seed  open read only  ;
 +
 +Warning: PDB altered with errors.
 +</code>
 +
 +  * clean violations
 +
 +<code>
 +exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'PFRBNO01');
 +</code>
 +
 +  * cd to PDB
 +
 +<code>
 +ALTER SESSION SET CONTAINER=PFRNPC01;
 +</code>
 +
 +  * Restricted
 +
 +Check si datapatch OK 
 +
 +<code>
 +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 ;
 +</code>
 +
 +<code>
 +SQL> alter session set container=PHQR_OEM1 ;
 +
 +Session altered.
 +
 +SQL> alter system disable restricted session;
 +</code>
 +===== Dataguard =====
 +
 +  * start Redo Apply
 +
 +  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
 +
 +  * Switch manuel
 +
 +<code>
 +## 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   
 +</code>
 +
 +  * Switch semi-manuel
 +
 +<code>
 +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;
 +</code>
 +
 +  * Logs du broker : drc* file
 +
 +
 +  * Check de la conf du broker
 +
 +<code>
 +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
 +</code>
 +
 +  * Restart du broker
 +
 +<code>
 +SQL> alter system set dg_broker_start=FALSE scope=BOTH SID='*' ;
 +SQL> alter system set dg_broker_start=TRUE scope=BOTH SID='*' ;
 +</code>
 +
 +  * Switchover
 +
 +<code>
 +dgmgrl sys@P72HBIO
 +show configuration
 +validate database <STANDBY>
 +show database <STANDBY>
 +show database verbose <STANDBY>
 +</code>
 +
 +  * Recréer config
 +
 +<code>
 +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;
 +</code>
 +
 +  * Check lag
 +
 +<code>
 +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;
 +</code>
 +
 +<code>
 +select name, value, time_computed, datum_time from v$dataguard_stats where name='%lag';
 +</code>
 +
 +<code>
 +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;
 +     
 +</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>
 +
 +<code>
 +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);
 +</code>
 +
 +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 =====
 +
 +<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 s.status='ACTIVE' 
 +/ 
 +</code>
 +
 ===== Mettre le résultat d'une requête sql dans une variable ===== ===== Mettre le résultat d'une requête sql dans une variable =====
 <code> <code>
  • informatique/base_de_donnees/tips.1268120256.txt.gz
  • Dernière modification : 2010/03/09 08:37
  • de 127.0.0.1