REPORT RMAN querying catalog RMAN not each db:

Report day to day:
[/appl/oracle/BIN]# cat report_rman.sql
set lines 200
set pages 250
prompt '-------------------------------------------------------------------------'
prompt 'backup bilan'
prompt '-------------------------------------------------------------------------'
select status, count(status) from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS where trunc(START_TIME) >trunc(sysdate -2) group by status;

prompt '-------------------------------------------------------------------------'
prompt 'backup ok'
prompt '-------------------------------------------------------------------------'
select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a where trunc(START_TIME) >trunc(sysdate -2)
and status='COMPLETED'
and b.db_key=a.db_key order by 4;

prompt '-------------------------------------------------------------------------'
prompt 'backup a controler'
prompt '-------------------------------------------------------------------------'
select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a where trunc(START_TIME) >trunc(sysdate -2)
and status!='COMPLETED' and status !='FAILED'
and b.db_key=a.db_key order by STATUS;
prompt '-------------------------------------------------------------------------'
prompt 'backup FAILED'
prompt '-------------------------------------------------------------------------'
select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a where trunc(START_TIME) >trunc(sysdate -2)
and status='FAILED'
and b.db_key=a.db_key order by STATUS;

Duree pour faire un diagramme de gant :

select input_type||'_'||b.DB_NAME||'_'||a.dbid database,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,

--to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,

--input_type,STATUS,

ELAPSED_SECONDS,

--OUTPUT_DEVICE_TYPE,

TIME_TAKEN_DISPLAY --,SESSION_KEY,SESSION_RECID,SESSION_STAMP

from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a where trunc(START_TIME) >trunc(sysdate -3)

and ELAPSED_SECONDS >300

and b.db_key=a.db_key order by start_time;

============
Keep
============
select c.DB_NAME,b.bs_key,KEEP,KEEP_UNTIL,KEEP_OPTIONS ,b.db_key,b.backup_type,b.START_TIME
,MEDIA
from RC_BACKUP_SET_details b ,RC_BACKUP_PIECE_DETAILS c
where
c.db_key=b.db_key
and trunc(b.START_TIME) <trunc(sysdate -30)
and KEEP_UNTIL is not null
and b.db_name=c.db_name
and b.BS_KEY=c.bs_key
and c.db_name ='PRD'
and trunc (c.START_TIME) =trunc(b.START_TIME)
--order by b.start_time, KEEP_UNTIL;
and KEEP_UNTIL is not null
order by b.start_time;

Copie de RAI PROD sur un autre serveur:

L’idée est d’utiliser le DR pour le backup

2 backup sur dr

2.1 prerequis

Pour info le backup des archives sur srv104

39 3,5,8,11,14,17,20,22,23 * * * /appl/oracle/BIN/backup_archivelog.ksh

Crontab sur le DRP : disable la purge des archives sur le DR le temps du backup.

2.2 Dans RMAN

Script de backup

run {

allocate channel ch1 type disk format '/bdtrans/bk/rman.s%s.d%d.u.%U.bck';

allocate channel ch2 type disk format '/bdtrans/bk/rman.s%s.d%d.u.%U.bck';

allocate channel ch3 type disk format '/bdtrans/bk/rman.s%s.d%d.u.%U.bck';

allocate channel ch4 type disk format '/bdtrans/bk/rman.s%s.d%d.u.%U.bck';

backup archivelog all ;

backup database filesperset 1;

backup archivelog all ;

}

Backup sur disque Sans la compression :25 minutes pour 287Go

backup sur disque (14h22 à16h54) =2h30 pour 56Go avec la compression.

backup bd disk	compression	duree	size
RAIP	yes	156	56
RAIP	no	25	287

Dans notre cas on favorise la vitesse.

2.3 Backup du controlfile :

Sur le DRP on a un controlfile for standby :

Alter database backup controlfile to ‘/tmp/ctl.bk’

On ne peut pas prendre un backup du controlfile sur la prod car il ne sera pas a jour.

2.4 Remarques importantes:

Compte tenu du délais de restore d une archive via le robot et du archive_lag sur la production (15 minutes) il est préférable de mettre de cote quelques archives au cas ou !

3 Restore

3.1 Fichier init

Les 2 parametres clefs :

db_name=RAIP

db_unique_name=RAIPV

Voir en annexe l init

3.2 restore du controlfile :

Transfert du controlfile for standby (cf 2.3)

En nomount:

Rman :

Restore controlfile from ‘/dd/rct/ctl.bk’;

Alter database mount;

3.3 Restore (32 minutes) :

Script de restore :

3.4 Recover database avec le controlfile d une standby

3.4.1 Recover

Recover standby database until cancel;

En fait il faut avancer jusqu a ce que cette requete ne ramène au maximum que 2 lignes :

select fhsta, count(*) from X$KCVFH group by fhsta;

FHSTA COUNT(*)

---------- ----------

0 27 èles 27 autres datafiles

8192 1 èsysteme.

3.4.2 Recreation du controlfile :

Attention au redologs car on change d arborescence :

ALTER DATABASE RENAME FILE '+REDO_RAI/drRAIprd/onlinelog/redo_01.dbf' TO '+REDO_RAI/RAIV/onlinelog/redo_01.dbf' ;

ALTER DATABASE RENAME FILE '+REDO_RAI/drRAIprd/onlinelog/redo_02.dbf' TO '+REDO_RAI/RAIV/onlinelog/redo_02.dbf' ;

ALTER DATABASE RENAME FILE '+REDO_RAI/drRAIprd/onlinelog/redo_03.dbf' TO '+REDO_RAI/RAIV/onlinelog/redo_03.dbf' ;

ALTER DATABASE RENAME FILE '+REDO_RAI/drRAIprd/onlinelog/redo_04.dbf' TO '+REDO_RAI/RAIV/onlinelog/redo_04.dbf' ;

ALTER DATABASE RENAME FILE '+REDO_RAI/drRAIprd/onlinelog/redo_05.dbf' TO '+REDO_RAI/RAIV/onlinelog/redo_05.dbf' ;

ALTER DATABASE RENAME FILE '+REDO_RAI/drRAIprd/onlinelog/redo_06.dbf' TO '+REDO_RAI/RAIV/onlinelog/redo_06.dbf' ;

ALTER DATABASE RENAME FILE '+REDO_RAI/drRAIprd/onlinelog/redo_07.dbf' TO '+REDO_RAI/RAIV/onlinelog/redo_07.dbf' ;

ALTER DATABASE RENAME FILE '+REDO_RAI/drRAIprd/onlinelog/group_8.260.821741485' TO '+REDO_RAI/RAIV/onlinelog/group_8.dbf' ;

ALTER DATABASE RENAME FILE '+REDO_RAI/drRAIprd/onlinelog/redo_09.dbf' TO '+REDO_RAI/RAIV/onlinelog/redo_09.dbf' ;

ALTER DATABASE RENAME FILE '+REDO_RAI/drRAIprd/onlinelog/redo_10.dbf' TO '+REDO_RAI/RAIV/onlinelog/redo_10.dbf' ;

Après faut recréer le controlfile pour passer du controlfile for standby a un controlfile pour la primaire en faisant :

alter database backup controlfile to trace;

Création du controlfile a partir de la trace et open resetlogs .

Ajout des tempfiles

alter tablespace temp add tempfile ;

alter tablespace app_temp add tempfile ;

3.5 nid

Nid : c est indspensable pour les backups RMAN afin que le dbid de la prod soit pas sur val.

nid target=sys/xxxxxx dbname=RAIPV

….

Database name changed to RAIPV.

Modify parameter file and generate a new password file before restarting.

Database ID for database RAIPV changed to 2004949404.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

3.6 Ajout de la bd dans le cluster

Avec le user unix oracle

Synopsis de la commande en annexe

srvctl add database -d RAIPV -o ‘/appl/oracle/11.2.0.2’ -p '+DATARAIPVAL/RAIPV/PARAMETERFILE/spfile.375.844349819' -a DATARAIPVAL

3.6.1 verification

srvctl config database -d RAIPV

Database unique name: RAIPV

Database name:

Oracle home: /appl/oracle/11.2.0.2

Oracle user: grid

Spfile: +DATARAIPVAL/RAIPV/PARAMETERFILE/spfile.375.844349819

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups: DATARAIPVAL

Services:

La premiere fois cf la note

Oracle Restart: srvctl fails to start database with error CRS-5010 if RDBMS and Grid under different users (Doc ID 1335607.1)

3.6.2 Ajout d un service

Avec le user unix oracle

srvctl add service -d RAIPV -s RAIdbavalsvc

srvctl add service -d RAIPV -s RAIappvalsvc

4 Annexes:

4.1 Synopsis srvctl add database

srvctl add database -h

Adds a database configuration to be managed by Oracle Restart.

Usage: srvctl add database -d <db_unique_name> -o <oracle_home> [-m <domain_name>] [-p <spfile>] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s <start_options>] [-t <stop_options>] [-n <db_name>] [-y {AUTOMATIC | MANUAL}] [-a "<diskgroup_list>"]

-d <db_unique_name> Unique name for the database

-o <oracle_home> ORACLE_HOME path

-m <domain> Domain for database. Must be set if database has DB_DOMAIN set.

-p <spfile> Server parameter file path

-r <role> Role of the database (primary, physical_standby, logical_standby, snapshot_standby)

-s <start_options> Startup options for the database. Examples of startup options are OPEN, MOUNT, or 'READ ONLY'.

-t <stop_options> Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.

-n <db_name> Database name (DB_NAME), if different from the unique name given by the -d option

-y <dbpolicy> Management policy for the database (AUTOMATIC or MANUAL)

-a "<diskgroup_list>" Comma separated list of disk groups

-h Print usage

4.2 Init RAIPV pour la restore :

*._ash_size=25165824

*._optim_peek_user_binds=TRUE

*.audit_file_dest='/appl/oracle/admin/DRRAIPRD/adump'

*.audit_trail='NONE'

*.cluster_database=TRUE

*.compatible='11.2.0.0.0'

*.control_file_record_keep_time=8

*.control_files='+DATARAIPVAL'

*.control_management_pack_access='NONE'

*.cursor_sharing='EXACT'

*.db_block_size=8192

*.db_create_file_dest='+DATARAIPVAL'

*.db_create_online_log_dest_1='+DATARAIPVAL'

*.db_domain=''

*.db_file_multiblock_read_count=128

*.db_files=1024

*.db_name='RAIP'

*.db_recovery_file_dest='+DATARAIPVAL'

*.db_recovery_file_dest_size=268435456000

*.db_ultra_safe='DATA_AND_INDEX'

*.db_unique_name='RAIPV'

*.db_writer_processes=5

*.dbwr_io_slaves=0

*.deferred_segment_creation=FALSE

*.diagnostic_dest='/appl/oracle'

*.disk_asynch_io=FALSE

*.fast_start_mttr_target=300

*.job_queue_processes=100

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DRRAIPRD'

*.log_archive_format='%t_%s_%r.dbf'

*.log_archive_max_processes=4

*.log_archive_min_succeed_dest=1

*.log_buffer=104857600

*.memory_max_target=4500M

*.memory_target=3500M

*.open_cursors=10000

*.optimizer_dynamic_sampling=4

*.optimizer_index_caching=90

*.optimizer_index_cost_adj=25

*.optimizer_mode='FIRST_ROWS_1000'

*.parallel_degree_policy='AUTO'

*.parallel_max_servers=640

*.parallel_servers_target=256

*.parallel_threads_per_cpu=4

*.processes=2000

*.remote_login_passwordfile='exclusive'

*.resumable_timeout=1800

*.sec_case_sensitive_logon=FALSE

*.session_cached_cursors=100

*.sessions=2205

*.standby_file_management='AUTO'

*.undo_retention=21600

*.undo_tablespace='UNDOTBS2'

#DRRAIPRD1.undo_tablespace='UNDOTBS11'

TANOS dba

Rechercher dans ce blog

jeudi 12 mars 2015

UPGRADE RMAN CATALOG FROM 11G TO 12C

REPORT RMAN

samedi 10 mai 2014

DR backup restore on VAL