Query Historical messages of RMAN


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 control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13056.843.927192449 RECID=26052 STAMP=927192449
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13057.826.927192453 RECID=26055 STAMP=927192453
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13058.841.927193943 RECID=26056 STAMP=927193943
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_17/thread_1_seq_13059.783.928188033 RECID=26057 STAMP=928188041
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_20/thread_1_seq_13060.846.928423541 RECID=26058 STAMP=928423543
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_20/thread_1_seq_13061.825.928423543 RECID=26059 STAMP=928423544
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13062.689.928577549 RECID=26060 STAMP=928577549
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13063.1457.928579241 RECID=26061 STAMP=928579241
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13064.543.928579251 RECID=26067 STAMP=928579250
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13065.1502.928579257 RECID=26071 STAMP=928579256
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13066.1269.928579751 RECID=26073 STAMP=928579751
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13067.634.928579887 RECID=26074 STAMP=928579887
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13068.627.928579891 RECID=26078 STAMP=928579890
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13069.659.928580081 RECID=26080 STAMP=928580080
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13070.610.928581933 RECID=26081 STAMP=928581933
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13071.308.928581937 RECID=26083 STAMP=928581936
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13072.649.928581943 RECID=26086 STAMP=928581943
Crosschecked 17 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13070.610.928581933 thread=1 sequence=13070
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13071.308.928581937 thread=1 sequence=13071
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13072.649.928581943 thread=1 sequence=13072
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13056.843.927192449 RECID=26052 STAMP=927192449
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13057.826.927192453 RECID=26055 STAMP=927192453
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_06/thread_1_seq_13058.841.927193943 RECID=26056 STAMP=927193943
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_17/thread_1_seq_13059.783.928188033 RECID=26057 STAMP=928188041
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_20/thread_1_seq_13060.846.928423541 RECID=26058 STAMP=928423543
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_20/thread_1_seq_13061.825.928423543 RECID=26059 STAMP=928423544
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13062.689.928577549 RECID=26060 STAMP=928577549
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13063.1457.928579241 RECID=26061 STAMP=928579241
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13064.543.928579251 RECID=26067 STAMP=928579250
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13065.1502.928579257 RECID=26071 STAMP=928579256
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13066.1269.928579751 RECID=26073 STAMP=928579751
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13067.634.928579887 RECID=26074 STAMP=928579887
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13068.627.928579891 RECID=26078 STAMP=928579890
deleted archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13069.659.928580081 RECID=26080 STAMP=928580080
Deleted 14 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
specification does not match any archived log in the repository
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
specification does not match any archived log in the repository
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
no obsolete backups found
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
specification does not match any datafile copy in the repository
validation failed for control file copy
control file copy file name=/tmp/farsync_control.ctl RECID=15 STAMP=928579660
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13070.610.928581933 RECID=26081 STAMP=928581933
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13071.308.928581937 RECID=26083 STAMP=928581936
validation succeeded for archived log
archived log file name=+DATA/PDB11/ARCHIVELOG/2018_10_22/thread_1_seq_13072.649.928581943 RECID=26086 STAMP=928581943
Crosschecked 4 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=695 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any archived log in the repository
deleted control file copy
control file copy file name=/tmp/farsync_control.ctl RECID=15 STAMP=928579660
Deleted 1 EXPIRED objects
Remember, that if your database was just bounced, this view will be empty.
In the above example you can see the cron job that we run on our lab (crosscheck and deletes)

Comments

Post a Comment

Popular posts from this blog

Data Guard - Changing IP Addresses

Image
When changing IP of a host we should update/recheck the following places: /etc/hosts or DNS listener.ora tnsnames.ora Database parameters (local_listener, remote_listener) Data Guard configuration This document is also relevant when changing the IP address of the connection between the hosts, other than the original IP addresses we used during the installation. When installation is done using the hostname and not the IP address, most of the changes are not relevant except for /etc/hosts. In this document, I will describe how to change the Data Guard Broker configuration. Dataguard configuration Show the environment DGMGRL> show configuration Configuration - dr   Protection Mode: MaxAvailability   Members:   pdb7 - Primary database     fdb7 - Far sync instance       sdb7 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS   (status updated 351 seconds ago) Show detailed information of the Primary
Read more

Install Oracle Internet Directory (OID) in Standalone mode

Image
Oracle Internet Directory (OID) is an LDAP server which uses an Oracle database as a datastore. Client machines can use the OID for all TNS lookups. With OID. Once all client machines are configured correctly any modifications to the TNS lookups can be done from a central location reducing the amount of client machine administration. This guide is about installing and using Oracle Internet Directory in Standalone mode, no need for Oracle Fusion Middleware Infrastructure. Step-by-step guide Download Oracle Internet Directory (Part of identity management) from http://www.oracle.com/technetwork/middleware/id-mgmt/downloads/index.html Install OID, follow instructions: https://docs.oracle.com/en/middleware/lifecycle/12.2.1.3/inoim/installing-product-software.html#GUID-5D47117E-3983-4BED-BADB-CB7DDD63DB34 Starting the Installation Program: ./fmw_12.2.1.3.0_oid_linux64.bin Configure the Domain: $ORACLE_HOME/oracle_common/common/bin/config.sh Start the Servers for Standal
Read more

Fixing & Registering ORACLE_HOMES in Central Inventory

Central Inventory location is determined by oraInst.loc located in: HP/Solaris /var/opt/oracle/oraInst.loc Linux /etc/oraInst.loc Windows \\HKEY_LOCAL_MACHINE\Software\Oracle\inst_loc To check if all oracle homes are registered within the Central Inventory run the following command: ${ORACLE_HOME}/oui/bin/opatch lsinventory -all Invoking OPatch 10.2.0.3.0 Oracle interim Patch Installer version 10.2.0.3.0 Copyright (c) 2005, Oracle Corporation. All rights reserved.. Oracle Home : /software/oracle/OEM10gR2/agent10g Central Inventory : /software/oracle/oraInventory from : /var/opt/oracle/oraInst.loc OPatch version : 10.2.0.3.0 OUI version : 10.2.0.3.0 OUI location : /software/oracle/OEM10gR2/agent10g/oui Log file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/opatch/opatch....log Lsinventory Output file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/....lsinventory.....txt ----------------------------------... List of Oracle Homes:
Read more