Showing posts with label Backup and Recovery. Show all posts
Showing posts with label Backup and Recovery. Show all posts
Thursday, March 1, 2018
How to delete archive log files using PL/SQL
Although RMAN is always the first choice for most DBAs to delete archive log files, PL/SQL could be easier for this scenario,
User wants to switch an existing database from NOARCHIVELOG mode to ARCHIVELOG mode in order to increase the database availability because the database backup job has to shut down database when running in NOARCHIVELOG mode.
Monday, January 15, 2018
ORA-65500 When using DUPLICATE to Create Standby Database
Oracle Database - Enterprise Edition - Version 12.2.0.1 + RU 12.2.0.1.171017
Operation System - Oracle Solaris 11.3 SPARC
When running RMAN command DUPLICATE to create physical standby database as following:
Got error ORA-65500:
Oracle reported this issue as Bug 20977794 which causes the ORA-65500 to be returned with any attempt to change DB_UNIQUE_NAME for database when a CRS resource for it exists. As RMAN attempts to make a change to db_unique_name, this error will occur.
Current system is configured with Oracle Restart (Grid Infrastructure - standalone) 12.2.0.1. Although it is supposed to be fixed in 12.2.0.1 base release, the error still happened. Anyway, we can safely try to remove database resource from crs registry as workaround,
The database DBNEW does exist in the crs registry, try to remove it
Then DUPLICATE can run successfully.
Operation System - Oracle Solaris 11.3 SPARC
When running RMAN command DUPLICATE to create physical standby database as following:
RMAN> duplicate target database
2> for standby
3> from active database
4> dorecover
5> spfile
6> reset control_files
7> set db_unique_name='DBNEW'
8> set fal_server='DB01'
9> nofilenamecheck;
using target database control file instead of recovery catalog
Starting Duplicate Db at 15-JAN-18
current log archived
... ...
2> for standby
3> from active database
4> dorecover
5> spfile
6> reset control_files
7> set db_unique_name='DBNEW'
8> set fal_server='DB01'
9> nofilenamecheck;
using target database control file instead of recovery catalog
Starting Duplicate Db at 15-JAN-18
current log archived
... ...
Got error ORA-65500:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/15/2018 11:46:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 01/15/2018 11:46:19
RMAN-11003: failure during parse/execution of SQL statement: alter system set db_unique_name='DBNEW' comment='' scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/15/2018 11:46:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 01/15/2018 11:46:19
RMAN-11003: failure during parse/execution of SQL statement: alter system set db_unique_name='DBNEW' comment='' scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists
Oracle reported this issue as Bug 20977794 which causes the ORA-65500 to be returned with any attempt to change DB_UNIQUE_NAME for database when a CRS resource for it exists. As RMAN attempts to make a change to db_unique_name, this error will occur.
Current system is configured with Oracle Restart (Grid Infrastructure - standalone) 12.2.0.1. Although it is supposed to be fixed in 12.2.0.1 base release, the error still happened. Anyway, we can safely try to remove database resource from crs registry as workaround,
[oracle@dbaplus]$ srvctl config database
DBNEW
DBNEW
The database DBNEW does exist in the crs registry, try to remove it
[oracle@dbaplus]$ srlvctl remove database -db DBNEW
Remove the database DBNEW? (y/[n]) y
[oracle@dbaplus]$
Remove the database DBNEW? (y/[n]) y
[oracle@dbaplus]$
Then DUPLICATE can run successfully.
Monday, March 21, 2016
RMAN got ORA-00245 while backing up RAC control file
When backing up control file for RAC databases, you could got ORA-00245 as following,
In order to have a consistent version of the control file, RMAN creates a snapshot when asked to back up the control file,
then copy the snapshot control file to backup media. The default file name of the snapshot is system-dependent, each instance
of same RAC database has default file name,
Depending on whether the RMAN session, which creates snapshot file, is connected to instance racdb_1 running on node host01 or instance racdb_2
running on node host02, the snapshot file name will be snapcf_racdb_1.f or snapcf_racdb_2.f respectively.
When RMAN backs up the control file and the snapshot file is create on other node, ORA-00245 will be raised up. To avoid the error, the
RMAN Snapshot Control File Location has to be configured on shared storage which is accessible by all nodes in the cluster (all instances of
the RAC databases),
RMAN> backup device type sbt current controlfile ;
Starting backup at 2016-03-20 19:08:49
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=1941 instance=racdb_1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 03/20/2016 19:08:50
ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage
Starting backup at 2016-03-20 19:08:49
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=1941 instance=racdb_1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 03/20/2016 19:08:50
ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage
In order to have a consistent version of the control file, RMAN creates a snapshot when asked to back up the control file,
then copy the snapshot control file to backup media. The default file name of the snapshot is system-dependent, each instance
of same RAC database has default file name,
[oracle@host01 ~]$ export ORACLE_SID=racdb_1
[oracle@host01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 21 16:54:07 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: racdb (DBID=435209035)
RMAN> SHOW SNAPSHOT CONTROLFILE NAME;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name racdb are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_racdb_1.f'; # default
[oracle@host02]$ export ORACLE_SID=racdb_2
[oracle@host02]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 21 16:54:07 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: racdb (DBID=435209035)
RMAN> SHOW SNAPSHOT CONTROLFILE NAME;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name racdb are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_racdb_2.f'; # default
[oracle@host01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 21 16:54:07 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: racdb (DBID=435209035)
RMAN> SHOW SNAPSHOT CONTROLFILE NAME;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name racdb are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_racdb_1.f'; # default
[oracle@host02]$ export ORACLE_SID=racdb_2
[oracle@host02]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 21 16:54:07 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: racdb (DBID=435209035)
RMAN> SHOW SNAPSHOT CONTROLFILE NAME;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name racdb are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_racdb_2.f'; # default
Depending on whether the RMAN session, which creates snapshot file, is connected to instance racdb_1 running on node host01 or instance racdb_2
running on node host02, the snapshot file name will be snapcf_racdb_1.f or snapcf_racdb_2.f respectively.
When RMAN backs up the control file and the snapshot file is create on other node, ORA-00245 will be raised up. To avoid the error, the
RMAN Snapshot Control File Location has to be configured on shared storage which is accessible by all nodes in the cluster (all instances of
the RAC databases),
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/racdb/snapcf_racdb.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/racdb/snapcf_racdb.f';
new RMAN configuration parameters are successfully stored
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/racdb/snapcf_racdb.f';
new RMAN configuration parameters are successfully stored
Wednesday, April 29, 2015
Insufficient archive log space caused ORA-03113
Oracle 11g database was shut down for hardware maintenance. Unfortunately, the database can not be started after maintenance because of error ORA-03113.
Apparently, something happended while instance status is changed to OPEN from MOUNT. Check alert log file:
Instance is terminated due to error 16038: log 3 sequence# 32464 cannot be archived. Redo log cannot be archived because the recovery area is 100% used.
Backup archive log and release space by running:
Try to open database:
Database is opened successfully.
$ srvctl sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 27 14:10:05 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 33947688
Session ID: 749 Serial number: 3
Apparently, something happended while instance status is changed to OPEN from MOUNT. Check alert log file:
ARC3 started with pid=30, OS id=51183782
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '+FRA2'
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-16038: log 3 sequence# 32464 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+REDO1/db01/onlinelog/group_3.310.857090421'
ORA-00312: online log 3 thread 1: '+REDO2/db01/onlinelog/group_3.310.857090421'
USER (ospid: 45220056): terminating the instance due to error 16038
Instance is terminated due to error 16038: log 3 sequence# 32464 cannot be archived. Redo log cannot be archived because the recovery area is 100% used.
Backup archive log and release space by running:
$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 27 14:17:52 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 835104768 bytes
Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes
RMAN> backup archivelog all delete all input;
... ...
Try to open database:
RMAN> alter database open;
database opened
Database is opened successfully.
Friday, December 19, 2014
Got 'database incarnation not set' while using RMAN with catalog
RMAN> run { 2> allocate channel oem_backup_sbt1 type 'SBT_TAPE' format '%d_%U' parms "ENV=(NB_ORA_POLICY=oracle_rman, NB_ORA_SCHED=Default)"; 3> backup as BACKUPSET tag 'BACKUP_REVQAS1_121814161014' recovery files; 4> release channel oem_backup_sbt1; 5> } new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete allocated channel: oem_backup_sbt1 channel oem_backup_sbt1: SID=162 device type=SBT_TAPE channel oem_backup_sbt1: Veritas NetBackup for Oracle - Release 6.5 (2009050106) Starting backup at 2014-12-18 16:11:33 released channel: oem_backup_sbt1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 12/18/2014 16:11:33 RMAN-03014: implicit resync of recovery catalog failed RMAN-06004: ORACLE error from recovery catalog database: RMAN-20020: database incarnation not set RMAN> exit; Recovery Manager complete.
select * from dual;
Subscribe to:
Posts (Atom)