GoldenGate – RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process.

Posted on February 9, 2018 by Alex Lima

1- One of the obsolete integrated extract was causing Rman not to delete expired archive logs.


orcl:/u02/DBA/scripts ->./delete_arch.sh
The Oracle base remains unchanged with value /u01/app/oracle

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 9 15:25:15 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1450870477)

RMAN> 2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_153_f7w7xolv_.arc RECID=139 STAMP=967649093
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_154_f7w7xtxh_.arc RECID=140 STAMP=967649098
Crosschecked 2 objects


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_153_f7w7xolv_.arc thread=1 sequence=153
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_154_f7w7xtxh_.arc thread=1 sequence=154

RMAN>

2- Deleted the extracts but didn’t unregister from the database


orcl:/u01/ogg ->./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jun 30 2017 16:12:28
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (oradb1.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     ABENDED     E1TEST      00:00:09      601:55:43
REPLICAT    ABENDED     R1TEST      00:00:00      601:56:10


GGSCI (oradb1.localdomain) 2> dblogin USERID GGUSER, PASSWORD gguser
Successfully logged into database.

GGSCI (oradb1.localdomain as GGUSER@orcl) 3> delete *test
Are you sure you want to delete all groups? y
Deleted EXTRACT E1TEST.

Deleted REPLICAT R1TEST.


GGSCI (oradb1.localdomain as GGUSER@orcl) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

3- Check the database to see what is the extract holding the arch logs


SQL> SELECT CAPTURE_NAME, 
            CAPTURE_TYPE, STATUS, 
            to_char(REQUIRED_CHECKPOINT_SCN,'999999999999999')as REQ_SCN ,
            to_char(OLDEST_SCN,'999999999999999')as OLDEST_SCN 
     FROM DBA_CAPTURE;

CAPTURE_NAME             CAPTURE_TY STATUS   REQ_SCN          OLDEST_SCN
------------------------ ---------- -------- ---------------- ----------------
OGG$CAP_E1TEST           LOCAL      ENABLED  2255523          2255523

1 row selected.

4- Because database still have the GoldenGate integrated capture registered, the database won’t delete the arch logs required


orcl:/u02/DBA/scripts ->./delete_arch.sh
The Oracle base remains unchanged with value /u01/app/oracle

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 9 15:23:00 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1450870477)

RMAN> 2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=284 device type=DISK
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_150_f7w7q3tq_.arc RECID=136 STAMP=967648886
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_151_f7w7q3st_.arc RECID=137 STAMP=967648886
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_152_f7w7q7t7_.arc RECID=138 STAMP=967648893
Crosschecked 3 objects


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=284 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_150_f7w7q3tq_.arc thread=1 sequence=150
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_151_f7w7q3st_.arc thread=1 sequence=151
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_152_f7w7q7t7_.arc thread=1 sequence=152

RMAN>

5- Unregister the GoldenGate Integrated capture from the database


orcl:/u01/ogg ->./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jun 30 2017 16:12:28
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (oradb1.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (oradb1.localdomain) 2> dblogin userid gguser, password gguser
Successfully logged into database.

GGSCI (oradb1.localdomain as gguser@orcl) 3> UNREGISTER EXTRACT e1test database

2018-02-09 15:28:41  INFO    OGG-01750  Successfully unregistered EXTRACT E1TEST from database.

6- Delete the arch logs successfully


orcl:/u02/DBA/scripts ->./delete_arch.sh
The Oracle base remains unchanged with value /u01/app/oracle

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 9 15:29:04 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1450870477)

RMAN> 2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_153_f7w7xolv_.arc RECID=139 STAMP=967649093
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_154_f7w7xtxh_.arc RECID=140 STAMP=967649098
Crosschecked 2 objects


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
139     1    153     A 09-FEB-18
        Name: /u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_153_f7w7xolv_.arc

140     1    154     A 09-FEB-18
        Name: /u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_154_f7w7xtxh_.arc

deleted archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_153_f7w7xolv_.arc RECID=139 STAMP=967649093
deleted archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_154_f7w7xtxh_.arc RECID=140 STAMP=967649098
Deleted 2 objects


RMAN>

Recovery Manager complete.

ORA-00257: archiver error – FRA Clean Up emergency

Posted on July 13, 2017 by Alex Lima

A quick emergency FRA clean up when archived logs are full.

RMAN – incremental backup

Posted on November 6, 2011 by Alex Lima

RMAN command to create level 0 backup which is needed before running of incremental backup
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

RMAN command to run level 1 backup. Level 1 backup will backup all blocks changed since most recent cumulative or differential backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

RMAN command to run level 1 cumulative backup. Level 1 backup will backup all blocks changed since most recent Level 0 backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

RMAN command to backup database level 1 and skip datafiles and archived redo logs that cannot be read due to I/O errors to be excluded from backup
RMAN> BACKUP INCREMENTAL LEVEL 1 INACCESSIBLE DATABASE;

How to check if RMAN catalog is installed for a database?

Posted on July 17, 2011 by Alex Lima

There are couple of ways to determine if RMAN database is registered with a catalog.

Using RMAN when you connect to RMAN catalog and try to run a RMAN command like “list backup” it will generate an error as shown below
calora4db01q:/home/oracle $ rman target / catalog rmancataloguser/rmancatalogpassword@catalogdb

Recovery Manager: Release 10.2.0.4.0 – Production on Sun Jul 17 08:33:42 2011

connected to target database: TESTDB (DBID=1023910334)
connected to recovery catalog database

RMAN> list backup;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/17/2011 08:33:48
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

Another way would be to connect to the catalog schema through SQL*Plus and check view RC_DATABASE
$ sqlplus rmancataloguser/rmancatalogpassword@catalogdb
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Jul 17 08:45:58 2011

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from rc_database where name = ‘TESTDB’;

no rows selected

Using RMAN to validate backup

Posted on January 30, 2011 by Alex Lima

When using VALIDATE option, Oracle will validate backup and not create backups and will not restore when this option is used.

# Validate if datafile exists and have no physical or logical corruptions that would prevent RMAN from running. If “archivelog” is specified it will also check if archive logs are valid too
RMAN> backup validate database archivelog all;

# Validate if datafile exists and have no physical or logical corruptions that would prevent RMAN from running.
RMAN> backup validate database;

# Validate if a specific tablespace can be restored, Oracle will check the backup piece to validate if the tablespace can be restored
RMAN> RESTORE VALIDATE TABLESPACE system;

Starting restore at 31-JAN-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u01/oradata/TEST/rman/60m3efef
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oradata/TEST/rman/60m3efef tag=TAG20110130T203006
channel ORA_DISK_1: validation complete, elapsed time: 00:00:47
Finished restore at 31-JAN-11

# Validate backup for database full restore, RMAN will not perform restore it will validate backup piece
RMAN> RESTORE VALIDATE DATABASE;

Starting restore at 31-JAN-11
using channel ORA_DISK_1

How to rename block change tracking file?

Posted on January 30, 2011 by Alex Lima

To move/rename current block change tracking file, one would need to restart the database.

# Get current block change tracking file
SQL> SELECT filename FROM v$block_change_tracking;

FILENAME
——————————————————————————–
/tmp/blocktracking.f

# Shutdown database and start database in mount state
SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

# Rename block tracking file and
SQL> ALTER DATABASE RENAME FILE ‘/tmp/blocktracking.f’ TO ‘/u01/oradata/TEST/blocktracking.f’;

# Move current block tracking file through OS
SQL> !mv /tmp/blocktracking.f /u01/oradata/TEST/blocktracking.f

# Open database
SQL> ALTER DATABASE OPEN;

Note: Recreating the file by using disable and creating it will not use until level 0 backup is run.

RMAN – to enable compression of backupset

Posted on January 22, 2011 by Alex Lima

Backup in RMAN can be compressed when they are being created. Using the following command one can compress backup to disk or tape. The commands used to backup database remain the same.

# Enable compression when creating backup to disk
RMAN> configure device type disk backup type to compressed backupset;

# Enable compression when creating backup to tape
RMAN> configure device type sbt backup type to compressed backupset;

# Backup the database shows the command remains the same
RMAN> run {
backup database;
}

Shows backup size when non-compressed backup versus compressed backup, the percentage of compression may vary.
-rw-r—– 1 oracle oinstall 1109958656 Jan 22 20:09 4rm2pb5i # Shows non-compression backup size
-rw-r—– 1 oracle oinstall 139124736 Jan 22 20:12 4tm2pbb5 # Shows compressed backup size

By looking at v$sesstat one can see the compression rate by comparing physical write total bytes for RMAN sessions
RMAN session where compression was enabled
SID NAME VALUE
———- —————————————————————- ———-
23 physical write total bytes 152788992
23 physical read total bytes 1368006656

RMAN session where compression was not enabled
SID NAME VALUE
———- —————————————————————- ———-
24 physical write total bytes 1124999168
24 physical read total bytes 1367990272

To disable backup for disk
RMAN> configure device type disk backup type to backupset;

To disable backup for tape
RMAN> configure device type sbt backup type to backupset;

An Oracle Spin by Alex Lima

Sharing My Database Experience

Category Archives: RMAN