Oracle DBA Place

Hi, Recently I realized that this very small and useful feature of RMAN is not known to many of us. I have just learned about this option just two years ago. I am talking about the ability to query from database (in-memory view) 32768 lines of RMAN output, even if it was used by crontab or any 3rd-party vendor. So it is a sample sql command from v$rman_output, in the following example I filter just the last 7 days. SQL>  col OUTPUT for a135 trunc SQL> set pages 0 SQL> select output from gv$rman_output where session_recid in (select session_recid from v$rman_status where start_time > sysdate-7) order by recid ; connected to target database: PDB11 (DBID=3890652951, not open) crosscheck archivelog all; delete noprompt archivelog like '+DATA%'; delete noprompt archivelog like '+RECO%'; delete noprompt archivelog like '/DB%'; delete noprompt obsolete; crosscheck copy; delete noprompt expired copy; exit; using target database contro...

SQL script for calculating the total size of RMAN backups col TYPE for a20 Heading "Backup Type" col completion_time Heading "Completion Time" col MB for 99999999999  Heading "Size (MB)" col BCPTIME for 99999999999 Heading "Backup Time (minutes)" SELECT TO_CHAR (completion_time, 'YYYY-MON-DD') completion_time        , TYPE        , ROUND (SUM (bytes) / 1048576)          MB        , ROUND (SUM (elapsed_seconds) / 60)     BCPTIME     FROM (SELECT CASE                     WHEN s.backup_type = 'L' THEN 'Archive Log' --                    WHEN s.controlfile_included = 'YES' THEN 'Control File'                  WHEN s.backup_type = 'D' THEN 'Full (Level ' || NVL (s.incremental_level, 0) || ')'       ...

Hi Since Oracle 12c we can recover standby only by accessing the standby database/site, no need to copy nor transfer any file Short and easy - just copy and paste enjoy :) Standby Database Name: STBY Primary Database Name: PRIM On Standby sqlplus / as sysdba ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SHUTDOWN IMMEDIATE; STARTUP NOMOUNT; ALTER DATABASE MOUNT STANDBY DATABASE; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; rman target / RECOVER DATABASE FROM SERVICE PRIM USING COMPRESSED BACKUPSET NOREDO SECTION SIZE 120M; sqlplus / as sysdba SHUTDOWN IMMEDIATE; STARTUP NOMOUNT; rman target / RESTORE STANDBY CONTROLFILE FROM SERVICE PRIM; sqlplus / as sysdba ALTER DATABASE MOUNT STANDBY DATABASE; rman target / CATALOG START WITH '+DATA/ STBY /DATAFILE/'; SWITCH DATABASE TO COPY; sqlplus / as sysdba set pages 0 verify off feed off term off echo off spool /tmp/clear.sql select distinct 'ALTER DATABASE CLEAR LOGFILE...

The purpose of this article is to simulate a block level corruption using BBED utility (block browser and editor) and recover using RMAN. In this situation the data file remains online throughout the recovery operation and hence other segments within the tablespace remain accessible. Since BBED exists from Oracle7 to Oracle10g, we will have to copy some files from earlier version and compile it Cp $ORA10g_HOME/rdbms/lib/ssbbded.o $ORA11g_HOME/rdbms/lib Cp $ORA10g_HOME/rdbms/lib/sbbdpt.o $ORA11g_HOME/rdbms/lib Message files (list may differ): Cp $ORA10g_HOME/rdbms/mesg/bbedus.msb $ORA11g_HOME/rdbms/mesg Cp $ORA10g_HOME/rdbms/mesg/bbedus.msg $ORA11g_HOME/rdbms/mesg Cp $ORA10g_HOME/rdbms/mesg/bbedar.msb $ORA11g_HOME/rdbms/mesg Issue the following command: make -f $ORA11g_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed $ORA11g_HOME/bin/bbed password: blockedit SQL> Set pages 0 SQL> set feedback off SQL> spool fileunix.log SQL> select fi...

while running archivelog backup and the file is missing: RMAN-06059: expected archived log not found, lost of archived log compromises recoverability trying to fix it by crosscheck: run { allocate channel c1 type disk ; crosscheck archivelog all ; release channel c1 ; } validation succeeded for archived log archive log filename=D:REDOARCHARCH_1038.DBF recid=1017 stamp=611103638 still need to run: allocate channel for maintenance type disk; crosscheck archivelog all; release channel;

stop all cluster resources Linux> sudo crsctl stop crs create a temporary init.ora file init.ora ====== db_name=nplprd1  <== in RAC use instance name!!!! Linux> export ORACLE_SID=nplprd1 RMAN> startup nomount pfile=/tmp/init.ora; RMAN> run { allocate channel a DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=nplprd,OB2BARLIST=nplprd_weekly)' FORMAT 'nplprd_weekly<nplprd_%s:%t:%p>.dbf'; # we are using Data Protector restore spfile; shutdown immediate; startup nomount; # starting up using the restored spfile restore controlfile; alter database mount; # mounting with the restored controlfile configure channel 1 device type disk clear; # Clear service information configure channel 2 device type disk clear restore database; recover database; alter database open RESETLOGS; configure channel 1 device type sbt_tape connect 'sys/<password>@nplprd1'; # returning the definitions were cleared configure...

While I checked the file location of the archives, I discovered unneeded aliases   ASMCMD [+] > cd +DATA/nplprd ASMCMD [+DATA/nplprd] > ls -l Type           Redund  Striped  Time             Sys  Name                                                       1_12_621591104.dbf => +DATA/nplprd/archivelog/2007_05_10/thread_1_seq_12.295.622220233                                         ...

Configuring parameters in RMAN ========================== RMAN> CONFIGURE BACKUP OPTIMIZATION ON; RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE SBT_TAPE CONNECT 'SYS/ <password> @nplprd1'; # for RAC RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE SBT_TAPE CONNECT 'SYS/ <password> @nplprd2'; # for RAC RMAN> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2; RMAN> CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2; RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 84 DAYS;   T he configuration are written into the control file and then synced to the catalog. Y ou can view the configured parameters via the command SHOW ALL   RMAN> show all;   RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; CONFIGURE C...

Background: Migrating HP-UX oracle 9i to 10g On Linux RH4 using transportable tablespace to ASM The last step in this kind of procedure is to transfer the database file from filesystem to ASM using RMAN: RMAN> backup as copy tablespace TRANS_TEST format '+DATA/psdwh/datafile'; Starting backup at 22-FEB-07 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=136 devtype=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 02/22/2007 14:06:40 RMAN-20202: tablespace not found in the recovery catalog RMAN-06019: could not translate tablespace name "TRANS_TEST" It looks like we are hit bug#2656503 (not published on Metalink) which states that rman does not recognizes plugged in tablespaces until they a...