informatique:base_de_donnees:tips [Where there is a shell, there is a way]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
===== 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>

<code>
select * from v$restore_point;

</code>

<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://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 =====
<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>

<code>
select sum (bytes)/1024/1024/1024 size_GB  from dba_segments ;

</code>

<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>

<code>
srvctl modify database –d ORCL –pwfile +DATA/DRORCL/PASSWORD/pwdorcl

</code>

===== 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 =====

<code>
@$ORACLE_HOME/rdbms/admin/utlrp.sql

</code>

===== 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

<code>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

</code>

  * 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|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}}
<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 =====
<code>

vMaxSeqApply=`sqlplus -s "/ as sysdba" <<EOF
set head off
set PAGES 0
set FEED off
select substr(max(sequence#),0) from v\\$archived_log where applied='YES' and DEST_ID=2 group by THREAD#;
exit
EOF
`

</code>