informatique:base_de

Restore en mode RAC

alter pluggable database OEM2 close immediate instances=all;

run {
set until time "to_date('04/12/2023 09:00:00', 'dd/mm/yyyy hh24:mi:ss')";
restore pluggable database OEM2;
recover pluggable database OEM2;
alter pluggable database OEM2 open resetlogs; <= don't apply archivelogs
}

alter pluggable database OEM2 open instances=all;

Process sessions

select 'alter system kill session '''|| sid || ','||serial#||',@'||inst_id||''' ;', s.*
from gv$session s
where sql_id='8xdrs3uww5c0p'
and plsql_entry_object_id = 71000
and sql_exec_start <sysdate - 100/24/60/60
;

Dupliquer database

cd $ORACLE_HOME/dbs
. oraenv
echo "DB_NAME=PDBIO_R2"> initPDBIO_R2.ora
export ORACLE_SID=PDBIO_R2
sqlplus / as sysdba

startup nomount pfile='initPDBIO_R2.ora' ;

export NLS_DATE_FORMAT="DD-MON-YYYY.HH24:MI:SS"
export NLS_LANG=AMERICAN.WE8ISO8859P1
rman auxiliary / log=/home/oracle/test-restore_PDBIO.log

run
{
SET  UNTIL TIME "to_date('30/10/2023 07:00:00','dd/mm/yyyy hh24:mi:ss')";
SET ARCHIVELOG DESTINATION TO '/u01/app/oradata/restore';
DUPLICATE DATABASE TO PDBIO_R2 BACKUP LOCATION '/BACKUP-HXL01/PDBIO/' SPFILE SET CLUSTER_DATABASE='FALSE' NOFILENAMECHECK  ;
switch datafile all;
switch tempfile all;
}

select NAME,OPEN_MODE  from v$database;
show pdbs ;
shutdown abort ;
startup mount exclusive restrict ;
drop database ;

Listener

Forcer prise en compte rapide des services BDD

sqlplus / as sysdba
alter system register ;

ORA-xxx

ORA-01033 : check password primary <⇒ standby
ORA-16136 : checker la FRA
ORA-16136 : ORA-46372: audit file '/u01/app/odaorabase/oracle/audit/PABIO2 : mv répertoire en .old ou rm
Checks sémaphores : sysresv

Divers checks

select * from v$restore_point;

select database_role from v$database ;

SELECT LOG_MODE FROM SYS.V$DATABASE;

set line 200
col dest_name format a45
select NAME,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL, CURRENT_SCN,FLASHBACK_ON,FORCE_LOGGING from v$database;

select
"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)",
"Free_Space(GB)"
from(
select
(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)",
(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)"
from dual
);

select name
,      round(space_limit / 1024 / 1024) size_mb
,      round(space_used  / 1024 / 1024) used_mb
,      decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used
from v$recovery_file_dest
order by name ;

ORA-12514

SQL> show parameter service ;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
service_names                        string
PLNAVAMP_1DC2.fr.orpea.net

SQL> show parameter domain ;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_domain                            string
fr.orpea.net

SQL> alter system reset db_domain ;

System altered.

SQL> alter system reset db_domain scope = spfile SID='*' ;
alter system reset db_domain scope = spfile SID='*'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

ASM

Check liste datafiles ;

SQL> select NAME from v$datafile ;

NAME
--------------------------------------------------------------------------------
+ADATA/antarp/system-antarp-01.dbf
+ADATA/antarp/sysaux-antarp-01.dbf
+ADATA/antarp/undotbs1-antarp-01.dbf
+ADATA/antarp/users-antarp-01.dbf
+ADATA/antarp/antares_data-antarp-01.dbf
+ADATA/antarp/antares_indx-antarp-01.dbf
+ADATA/antarp/undotbs2-antarp-01.dbf
+ADATA/antarp/cpsure-antarp-01.dbf
+ADATA/antarp/antares_data-antarp-02.dbf

Resize

set lin 1000
col header_status form a12
col mode_status form a12
col path form a15
col name form a15
select
    header_status
    ,mode_status
    ,os_mb
    ,total_mb
    ,free_mb
    ,name
    ,path
from v$asm_disk ;

alter diskgroup reco resize all ;

Archivelogs

Voir logs

select to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp",MESSAGE,SEVERITY from v$dataguard_status
where SEVERITY <>'Control';

select to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp",MESSAGE,SEVERITY from v$dataguard_status ;

Check réception archivelogs (à lancer sur standby) :

select
 to_char(max(next_time), 'DD-MON-YY:HH24:MI:SS') v_Last_Received
 from v$archived_log
 where sequence# = (select max(sequence#) from v$archived_log);

Checker config

set linesize 500 pages 0
col value for a120
col name for a25
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_3','log_archive_dest_4',
'log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3', 'log_archive_dest_state_4','remote_login_passwordfile',
 'log_archive_format','log_archive_max_processes','fal_server','fal_client',
 'standby_file_management');
/

SQL> ARCHIVE LOG LIST ;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     41817
Next log sequence to archive   41819
Current log sequence           41819

SQL>  SHOW PARAMETER DB_RECOVERY_FILE_DEST ;
db_recovery_file_dest                string                           +FPFRA
db_recovery_file_dest_size           big integer                      152G

SQL> select dest_name,status,destination from V$ARCHIVE_DEST;
LOG_ARCHIVE_DEST_1                                                                                                                                                                             VALID
USE_DB_RECOVERY_FILE_DEST

Modif config

alter system set fal_server='frantarp_1dc2' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3 = DEFER SCOPE=BOTH;
alter system reset log_archive_dest_2 sid='*';
alter system set log_archive_config='DG_CONFIG=(FRANTARP_1DC1,FRANTARP_1DC2)' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=frantarp_1dc2 SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frantarp_1dc2' scope=both sid='*';
ALTER SYSTEM SET log_archive_dest_3 = '';

Recovery point

-- 2 days (3 * 24 * 60)

alter system set db_flashback_retention_target=2880 scope=both sid='*';

-- 3 days (3 * 24 * 60)

alter system set db_flashback_retention_target=4320 scope=both sid='*';

declare

  v_date varchar2(20);

begin

  select to_char(sysdate, 'yyyymmdd_hh24miss') into v_date from dual;

  execute immediate 'create restore point restore_point_' || v_date;

end;

/

select * from V$RESTORE_POINT ;

ALTER DATABASE FLASHBACK ON;
create restore point BEFORE_MIG GUARANTEE FLASHBACK DATABASE;

select * from v$restore_point;

RMAN>  LIST RESTORE POINT ALL;

using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
1099414692                 GUARANTEED 06-APR-23 BEFORE_SWITCH

Pending sessions

set pagesize 999;
set feedback off;
set wrap on;

column local_tran_id format a22 heading 'Local Txn Id'
column global_tran_id format a50 heading 'Global Txn Id'
column state format a16 heading 'State'
column mixed format a5 heading 'Mixed'
column advice format a5 heading 'Advice'

select local_tran_id,
 global_tran_id,
 state,mixed,advice
from
 dba_2pc_pending
order
 by local_tran_id;

-- To Force Rollback
SQL> ROLLBACK FORCE  '96.22.163456'
-- To Force Commit
SQL> COMMIT FORCE  '96.22.163456'

→ forced rollback status ;

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (‘LOCAL TRANSACTION ID’);

https://revanth935.wordpress.com/2013/04/19/roll-back-pending-transactions-ora-02075/
https://oraclefiles.com/2019/03/04/resolving-in-doubt-transactions/
http://www.dba-oracle.com/t_two_phase_commit_2pc.htm

Check last connections

select to_timestamp(to_char(logon_Time, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS'), machine from gv$session  where type = 'USER' order by logon_time desc;

Unlock users

column column_name format a30
set linesize 300
SELECT username,
       account_status
  FROM dba_users WHERE ACCOUNT_STATUS like '%LOCKED%' ;

ALTER USER HEXALIS_DECISION identified by <password> account unlock ;

ALTER USER HEXALIS_DECISION account unlock ;

Drop user connecté

SYS@TBBIO1> startup ;
ORACLE instance started.

Total System Global Area 8589931880 bytes
Fixed Size                 13874536 bytes
Variable Size            5905580032 bytes
Database Buffers         2583691264 bytes
Redo Buffers               86786048 bytes
Database mounted.
Database opened.
SYS@TBBIO1>  SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TFRHNO01                       READ WRITE NO
         4 TFRHNO01Q                      READ WRITE NO
         5 TFRMPY01                       READ WRITE NO
SYS@TBBIO1> ALTER SESSION set container=TFRMPY01;

SYS@TBBIO1> show pdbs ;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 TFRMPY01                       MOUNTED
SYS@TBBIO1> alter pluggable database TFRMPY01 open restricted;

Pluggable database altered.

SYS@TBBIO1> show pdbs ;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 TFRMPY01                       READ WRITE YES
SYS@TBBIO1> ALTER SESSION set container=TFRMPY01;

Session altered.

SYS@TBBIO1> DROP USER FRMPY01 CASCADE ;

Taille database

select
"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)",
"Free_Space(GB)"
from(
select
(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)",
(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)"
from dual
);

select sum (bytes)/1024/1024/1024 size_GB  from dba_segments ;

WITH
 TS_ALLOC as
 (
select TABLESPACE_NAME TS, round(sum(bytes/1024/1024/1024)) ALLOC
 from cdb_data_files
 group by TABLESPACE_NAME
 union
 select TABLESPACE_NAME TS, round(sum(bytes/1024/1024/1024)) ALLOC
 from cdb_temp_files
  group by TABLESPACE_NAME
  ),
  TS_USED as
 (
  select TABLESPACE_NAME TS, round(sum(bytes/1024/1024/1024)) FREE
  from cdb_free_space
 group by TABLESPACE_NAME
  )
  select sum(ALLOC) "ALLOC (G)", sum(FREE) "FREE (G)", sum(ALLOC) - sum(FREE) "USED (G)"
 from TS_ALLOC a LEFT OUTER JOIN TS_USED u on a.TS=u.TS ;

Sessions

set echo on time on timing on lines 200 pages 1000
col username format a30
col machine format a30
col service_name format a30
col logon_time format a20
select
        username
--      , machine
        , inst_id
        , service_name
        , status
      , to_char(logon_time,'YYYY/MM/DD HH24:MI') logon_time
        , count(1) sessions_cnt
from
        gv$session
where
        1=1
        and type='USER'
        and service_name not like 'SYS%'
        and username not like 'SYS%'
        and username not like 'PUBLIC'
group by
        username
--        , machine
        , inst_id
        , service_name
        , status
      , to_char(logon_time,'YYYY/MM/DD HH24:MI')
order by
        sessions_cnt desc;

select sid,serial# from v$session where username = 'FRMPY01' ;
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from gv$session where username ='FRMPY01';

select sid,serial#,inst_id from gv$session where username = 'FRMPY01' ;
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate;' as script from gv$session where username='FRMPY01' ;

FRA

SYS@TEBIO1> show parameter recovery

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
+RECO
db_recovery_file_dest_size           big integer
200G
recovery_parallelism                 integer
0
remote_recovery_file_dest            string

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=400g SCOPE=BOTH ;

select name
,      round(space_limit / 1024 / 1024) size_mb
,      round(space_used  / 1024 / 1024) used_mb
,      decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used
from v$recovery_file_dest
order by name ;

Copie password ASM

orapwd file=orapwt21cbio password=password ignorecase=n format=12 force=Y
asmcmd pwcopy --dbuniquename T21CBIO /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_5/dbs/orapwt21cbio +DATA/T21CBIO/orapwt21cbio

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

Tempfile

SYS@PDBIO1> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/P21DBIO/TEMPFILE/temp.1823.1125772463
+DATA/P21DBIO/CB949F6CD1D60A81E0533E60180A9ACE/TEMPFILE/temp.1824.1125772463
+DATA/P21DBIO/CB94C1145D745969E0533E60180A9064/TEMPFILE/temp.1826.1125772465
+DATA/P21DBIO/CCE2ECA49E5F05B5E0533E60180A5B40/TEMPFILE/temp.1825.1125772465
+DATA/P21DBIO/D58B172F55E0EB56E0533E60180A7510/TEMPFILE/temp.1852.1131484295

Change ORACLE_HOME

oda1db0host# srvctl stop database -db P21CPAT
oda1db0host# srvctl modify database -d P21CPAT -oraclehome "/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_5"
oda1db0host# srvctl start database -db P21CPAT

Datapatch

oda2db0host# . oraenv
oda2db0host# cd $ORACLE_HOME && ./OPatch/datapatch -db PHBIO1 -verbose

Recompile invalid objects

@$ORACLE_HOME/rdbms/admin/utlrp.sql

PDBs

show

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  YES
         3 PFRCHA02                       READ WRITE NO
         4 PFRCHA02Q                      READ WRITE NO
         5 PFRNPC01                       READ WRITE NO
SQL>

alter pluggable database PHQR_OAS1 close instances=all;
alter pluggable database PHQR_OEM1 close instances=all;
alter pluggable database PHQR_OEM1 close immediate instances=all;
alter pluggable database all save state instances=all;
SELECT * FROM cdb_pdb_saved_states;

change mode

SQL>  alter pluggable database pdb$seed  open read only  ;

Warning: PDB altered with errors.

clean violations

exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'PFRBNO01');

cd to PDB

ALTER SESSION SET CONTAINER=PFRNPC01;

Restricted

Check si datapatch OK

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PHRQ_OEM                       READ WRITE NO
         4 PHQR_OEM1                      READ WRITE YES
         5 PHQR_OAS1                      READ WRITE YES
SQL> alter pluggable database PHQR_OEM1 open force ;

Pluggable database altered.

SQL> show pdbs ,

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PHRQ_OEM                       READ WRITE NO
         4 PHQR_OEM1                      READ WRITE NO
         5 PHQR_OAS1                      READ WRITE YES

SQL> alter pluggable database PHQR_OAS1 open force ;

SQL> alter session set container=PHQR_OEM1 ;

Session altered.

SQL> alter system disable restricted session;

Dataguard

start Redo Apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Switch manuel

## Check if possible and Convert Primary to Physical Standby

srvctl stop instance -d FRTLPM_PRM -i TLMP2
sqlplus / as sysdba
SELECT SWITCHOVER_STATUS FROM V$DATABASE;    (must be TO_STANDBY)
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

## Check if possible and Convert the Standby to Primary

sqlplus / as sysdba
SELECT SWITCHOVER_STATUS FROM V$DATABASE;    (must be TO_PRIMARY)
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

## Deactivate all STANDBY Database and Open Primary

alter system set log_archive_dest_state_2=defer scope=both ;
alter system set log_archive_dest_state_3=defer scope=both ;
alter system set log_archive_dest_state_4=defer scope=both ;
alter database open ;

# Restart old PRIMARY and Start Standby Mode

startup mount
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

# Modify CLUSTER Configuration

srvctl modify database -d FRTLMP_1DC1 -r PRIMARY -s OPEN

Switch semi-manuel

kill du switchover ;
stop de la base ;
srvctl stop database -db FRINTEGP_PRM
restart des bases de l'autre côté

srvctl modify database -db frintegp_1dc1 -role PRIMARY -startoption OPEN
stop /start

startup mount de l'ancienne primary
check tnsping
remove configuration ;

CREATE CONFIGURATION drintegp AS PRIMARY DATABASE IS frintegp_1dc1 CONNECT IDENTIFIER IS frintegp_1dc1;
ADD DATABASE frintegp_1dc2 AS CONNECT IDENTIFIER IS frintegp_1dc2;
ENABLE CONFIGURATION;

Logs du broker : drc* file

Check de la conf du broker

SQL> show parameter broker ;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
connection_brokers                   string
((TYPE=DEDICATED)(BROKERS=1)),
 ((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1               string
+FPDAT/FRSIRHAP_1DC2/dg_config
-sirhap-01.dbf
dg_broker_config_file2               string
+FPDAT/FRSIRHAP_1DC2/dg_config
-sirhap-02.dbf

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
dg_broker_start                      boolean
TRUE
use_dedicated_broker                 boolean
FALSE

Restart du broker

SQL> alter system set dg_broker_start=FALSE scope=BOTH SID='*' ;
SQL> alter system set dg_broker_start=TRUE scope=BOTH SID='*' ;

Switchover

dgmgrl sys@P72HBIO
show configuration
validate database <STANDBY>
show database <STANDBY>
show database verbose <STANDBY>

Recréer config

remove configuration ;
CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS db11g CONNECT IDENTIFIER IS db11g;
ADD DATABASE db11g_stby AS CONNECT IDENTIFIER IS db11g_stby;
ENABLE CONFIGURATION;

Check lag

col NAME format a10
select NAME,TIME,UNIT,COUNT,LAST_TIME_UPDATED from V$STANDBY_EVENT_HISTOGRAM
where name like '%lag' and count>0 order by LAST_TIME_UPDATED;

select name, value, time_computed, datum_time from v$dataguard_stats where name='%lag';

Primary

SQL> select scn_to_timestamp(current_scn) from v$database;

select sysdate,database_mode,recovery_mode, gap_status
 from v$archive_dest_status
 where type='PHYSICAL'
 and gap_status !='NO GAP';

 select *
 from v$dataguard_status
 where severity in ('Error','Fatal')
 and timestamp> (sysdate -1);

 select sysdate,status,error
 from gv$archive_dest_status
 where type='PHYSICAL'
 and status!='VALID'
 or error is not null;

 Standby

 col name format a45
 select name,value,time_computed,datum_time
 from v$dataguard_stats
 where name='transport lag'
 and value> '+00 00:01:00';

 col name format a45
 select name,value,time_computed,datum_time
 from v$dataguard_stats
 where name='apply lag'
 and value> '+00 00:01:00';

 select max(timestamp)
     from gv$recovery_progress group by inst_id;

set line 500 pages 9999
col severity form a40
col message form a131
select SEVERITY,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp",MESSAGE from v$dataguard_status;
select  distinct error from v$archive_dest;

Check réception archives logs

select to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp",MESSAGE,SEVERITY from v$dataguard_status
where SEVERITY <>'Control';

select
 to_char(max(next_time), 'DD-MON-YY:HH24:MI:SS') v_Last_Received
 from v$archived_log
 where sequence# = (select max(sequence#) from v$archived_log);

https://docs.oracle.com/en/database/oracle/oracle-database/19/haovw/monitor-oracle-data-guard-configuration.html#GUID-51E71BB5-EE63-434A-976B-AE89C807A405
http://www.datadisk.co.uk/html_docs/oracle_dg/cheatsheet.htm

Créer standby + config dataguard

sample_initfile_dg.txt

****** SOURCE

# Activer FORCE LOGGING
alter database force logging;

# CREER les STANDBY Logs
alter database add standby logfile thread 1 group 11 ('+DATA','+RECO') size 400M reuse;
alter database add standby logfile thread 1 group 12 ('+DATA','+RECO') size 400M reuse;
alter database add standby logfile thread 1 group 13 ('+DATA','+RECO') size 400M reuse;
alter database add standby logfile thread 2 group 14 ('+DATA','+RECO') size 400M reuse;
alter database add standby logfile thread 2 group 15 ('+DATA','+RECO') size 400M reuse;
alter database add standby logfile thread 2 group 16 ('+DATA','+RECO') size 400M reuse;

# STANDBY Management AUTO
alter system set standby_file_management = 'AUTO' scope=both sid='*' ;

# Récupérer le Fichier PASSWORD
asmcmd
cp +DATA/P21EPAT/PASSWORD/pwdp21epat.1856.1131111009 /home/oracle/orapwTEPAT

# Récupérer le SPFILE et le Modifier
sqlplus / as sysdba
create pfile='/home/oracle/initPEPAT1.ora' from spfile ;

Copier les fichiers vers la cible et le modifier.

dbs
cp initPEPAT1.ora $ORACLE_HOME/dbs/
cp orapwPEPAT1 $ORACLE_HOME/dbs/

virer unedrscore
*.cluster_database=false
virer controlfile, log archive config, broker

*****   CIBLE

# Add Connexion in tnsnames.ora sur $OH Database et $OH Grid. DUP => pour ne pas passer par le scan

CDB_RAST_DUP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ex1dc2db2cw2cl.nibclt.prod)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB_RAST)
      (UR = A)
    )
  )

# Add Connexion in listener.ora
SID_LIST_LISTENER =
  (SID_LIST = (SID_DESC =
    (GLOBAL_DBNAME = CDB_RA)
    (ORACLE_HOME = /u01/app/oracle/dbHome)
    (SID_NAME = CDB_RA)))

# Create DUMP file on both servers
mkdir -p /u01/app/odaorabase/oracle/admin/P72EPAT/adump

# Start Database NOMOUT with pfile
startup nomount pfile='initCDB_RAST1.ora' ;

# Create the STANDBY via RMAN Duplicate
rman
connect target sys/password@CDB_RAST
connect auxiliary sys/password@CDB_RAST_DUP
RUN
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk4 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk5 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk6 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk7 DEVICE TYPE DISK ;
ALLOCATE CHANNEL disk8 DEVICE TYPE DISK ;
ALLOCATE auxiliary CHANNEL disk9 device type disk;
ALLOCATE auxiliary CHANNEL disk10 device type disk;
ALLOCATE auxiliary CHANNEL disk11 device type disk;
ALLOCATE auxiliary CHANNEL disk12 device type disk;
ALLOCATE auxiliary CHANNEL disk13 device type disk;
ALLOCATE auxiliary CHANNEL disk14 device type disk;
ALLOCATE auxiliary CHANNEL disk15 device type disk;
ALLOCATE auxiliary CHANNEL disk16 device type disk;
duplicate target database for standby from active database;
}

# Changer les parametres dans $ORACLE_HOME/dbs/initPEPAT1.ora
*.cluster_database=true
*.control_files='+DATA/P72EPAT/CONTROLFILE/current.1594.1137853621','+RECO/P72EPAT/CONTROLFILE/current.62887.1137853621'

output file name=+DATA/P72EPAT/CONTROLFILE/current.1594.1137853621
output file name=+RECO/P72EPAT/CONTROLFILE/current.62887.1137853621

# Remove spfile
remove spfilePEPAT1.ora

# Shutdown the STANDBY and RESTART In Mount.
shutdown immediate;
startup mount

# Create spfile in ASM
create spfile='+DATA/P72EPAT/spfileP72EPAT.ora' from pfile;
shutdown immediate;

# Modifier init.ora
SPFILE='+DATA/P72EPAT/spfileP72EPAT.ora'

# Cluster Creation
srvctl add database -d P72EPAT -o $ORACLE_HOME -c RAC -r PHYSICAL_STANDBY -s MOUNT -n P72EPAT -a "DATA,RECO"
srvctl add instance -d P72EPAT -i PEPAT1 -n oda2db0host
srvctl add instance -d P72EPAT -i PEPAT2 -n oda2db1host
srvctl modify database -d P72EPAT -p '+DATA/P72EPAT/spfileP72EPAT.ora'

srvctl start database -d P72EPAT

# Transférer le fichier de mot de passe sur le diskgroup ASM avec le user grid
asmcmd -p
pwcopy --dbuniquename P72EPAT ''/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_2/dbs/orapwPEPAT1'' '+DATA/P72EPAT/orapwP72EPAT'

# Supprimer le Password FIle
rm $ORACLE_HOME/dbs/orapwTEPAT1

# Modify RMAN Configuration to remove ARCHIVE LOG on PRIMARY
rman target /
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

# Modify RMAN Configuration to remove ARCHIVE LOG on STANDBY
rman target /
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/P72EPAT/PEPAT-snapshot-01.ctl';

***** BROKER

# Vérifier les TNS sur les 2 Noeuds
P21EPAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oda1scan)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = P21EPAT)))

P72EPAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oda2scan)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = P72EPAT)))

# Activer le BROKER sur les 2 Noeuds
alter system set dg_broker_config_file1='+DATAC2/CDB_RA/dr1CDB_RA.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+RECOC2/CDB_RA/dr2CDB_RA.dat' scope=both sid='*';
alter system set dg_broker_start=TRUE scope=both;

alter system set dg_broker_config_file1='+DATAC2/CDB_RAST/dr1CDB_RAST.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+RECOC2/CDB_RAST/dr2CDB_RAST.dat' scope=both sid='*';
alter system set dg_broker_start=TRUE scope=both;

# Configuration
dgmgrl /
create configuration DG_TESTRA as primary database is CDB_RA connect identifier is "CDB_RA";
add database CDB_RAST as connect identifier is "CDB_RAST" maintained as physical;
enable configuration;

# Check Dataguard
On PRIMARY
set linesize 1000
select * from V$DATAGUARD_STATUS ;
select thread#,max(sequence#) from v$archived_log group by thread#;

On STANDBY
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

Voir ce qu'il se passe

set head off pages 0 lines 120
select  p.spid,s.sid, s.serial#, substr(s.username,1,10)||','||process,
s.program,s.module,s.status, osuser ,
buffer_gets, disk_reads, executions,users_executing, first_load_time,'**',s.wait_class
,a.rows_processed,  a.sql_id,  sql_text -- sql_fulltext
from v$process p, v$session s,  v$sqlarea a
where a.address=s.sql_address
and p.addr=s.paddr
and users_executing> 0
and s.status='ACTIVE'
/

Mettre le résultat d'une requête sql dans une variable

vMaxSeqApply=`sqlplus -s "/ as sysdba" <<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
`

Table des matières

Restore en mode RAC

Process sessions

Dupliquer database

Listener

ORA-xxx

Divers checks

ORA-12514

ASM

Archivelogs

Recovery point

Pending sessions

Check last connections

Unlock users

Drop user connecté

Taille database

Sessions

FRA

Copie password ASM

Tempfile

Change ORACLE_HOME

Datapatch

Recompile invalid objects

PDBs

Dataguard

Voir ce qu'il se passe

Mettre le résultat d'une requête sql dans une variable