List of Topics (Linux, Database, RAC, EBS)
The following script is used for compressed RMAN backup
+++----- the following in Dark Orange color is RMAN backup Script -----+++
sql 'alter system archive log current';
sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";
RUN
{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
configure retention policy to recovery window of 30 days;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;
set command id to 'SCGOnlineBackupFull';
backup AS COMPRESSED BACKUPSET full database tag SC_FULL format '/u02/BACKUP/RMAN/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
backup tag SC_ARCHIVE format '/u02/BACKUP/RMAN/%d_%T_%s_%p_ARCHIVE' archivelog all not backed up ;
backup tag SC_CONTROL current controlfile format '/u02/BACKUP/RMAN/%d_%T_%s_%p_CONTROL';
crosscheck archivelog all;
delete noprompt archivelog until time 'SYSDATE-8';
delete noprompt obsolete device type disk;
}
+++----------------- END OF SCRIPT ----------------------+++
+++----- the following in Light Orange color is RMAN Restore Script -----+++
SQL>
[oracle@ rac1 dbs]$ cat initDELL1.ora
db_name=DELL
control_files=+DATA
undo_management=AUTO
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2166536 bytes
Variable Size 427819256 bytes
Database Buffers 624951296 bytes
Redo Buffers 14000128 bytes
SQL> exit
[oraclone@clone RMAN]$ rman target /
RMAN> LIST BACKUP OF SPFILE;
RMAN> LIST BACKUP OF CONTROLFILE;
RMAN> resotre spfile;
RMAN> restore controlfile from '/u01/rman/RMAN/c-298384717-20170810-01';
RMAN> alter database mount;
RMAN>
RMAN> select NAME,OPEN_MODE,CREATED,LOG_MODE,CONTROLFILE_TYPE,FLASHBACK_ON,DATABASE_ROLE,GUARD_STATUS,PROTECTION_MODE from gv$database;
RMAN> list backup summary;
RMAN> list backup;
RMAN> list backup of archivelog all;
RMAN> RESTORE DATABASE VALIDATE;
RMAN>
run
{
set UNTIL TIME "to_date('12/20/2017 11:34:50 am','mm/dd/yyyy hh:mi:ss am')";
restore database;
recover database;
}
channel ORA_DISK_3: restore complete, elapsed time: 01:08:43
Finished restore at 15-AUG-17
. . . . .
. . . . .
. . . . .
archived log file name=/u01/CLONE/db/tech_st/11.1.0/dbs/arch1_723_886546382.dbf thread=1 sequence=723
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-AUG-17
RMAN>
RMAN> alter database open resetlogs;
RMAN>
SQL>
+++----- End of Restore Script -----+++
==============================================================================================================
=============================
RUN Command in RMAN For Channels:
no need of providing Release channel if it allocated in Run{ once the duplicate command completed it automatically releases the channel and you wont get below error.
Lets make a try with out issuing any duplicate in RUN{
same will happen for normal channel or auxiliary channel.
RMAN> RUN{
allocate AUXILIARY channel t1 type disk; # channels in Run{ will be released by it self when run command complete given tasks by closing "}"
allocate AUXILIARY channel t2 type disk;
allocate AUXILIARY channel t3 type disk;}
allocated channel: t1
channel t1: SID=4 device type=DISK
allocated channel: t2
channel t2: SID=2006 device type=DISK
allocated channel: t3
channel t3: SID=4013 device type=DISK
released channel: t1
released channel: t2
released channel: t3
RMAN>
=============================
RMAN> show all;
=============================
To check previous backups performed by RMAN
To Check RMAN Configuration for Target Database:
RMAN>RMAN> show all;
=============================
SQL > SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS;
RMAN> crosscheck backup;
RMAN> list backup;
RMAN> list backup summary;
RMAN> list backup summary completed before 'sysdate-1';
RMAN> list backup summary device type disk completed before 'sysdate-8';
RMAN> list backup of archivelog all;
RMAN> delete backup;
RMAN> report obsolete;
RMAN> report obsolete device type disk;
=============================
To Delete expired Backup & Archive log
RMAN> list expired backup;
RMAN> delete expired backup;
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> LIST EXPIRED ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;
RMAN> DELETE OBSOLETE;
=============================
To Crosscheck/Delete RMAN Archivelog/Backup older than 1 week:
RMAN> crosscheck archivelog until time 'sysdate -8'; ----- (Days: 1,2,3,4,5,6,7,8,9,10)
RMAN> delete archivelog until time 'sysdate -8';
RMAN> crosscheck backup completed before 'sysdate -8'; ----- (Days: 1,2,3,4,5,6,7,8,9,10)
RMAN> crosscheck backup device type disk completed before 'sysdate-8';
RMAN> list backup summary completed before 'sysdate-8';
RMAN> delete backup completed before 'sysdate -8';
=============================
No prompt delete from RMAN:
RMAN> delete noprompt obsolete;
RMAN> delete noprompt archivelog until time 'SYSDATE-13';
RMAN> delete noprompt backup completed before 'SYSDATE -10';
RMAN> delete noprompt backup device type disk completed before 'sysdate-9';
RMAN> delete noprompt force archivelog all; #RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
RMAN> delete noprompt force archivelog until time 'SYSDATE-20';
==============================================================================================================
Crontab job Schedule for RMAN backups in Solaris:
oraahc@rac1:~$
oraahc@rac1:~$ systemctl status crond
● crond.service - Command Scheduler
Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2023-03-08 10:14:13 +03; 6 days ago
Main PID: 2293 (crond)
oraahc@rac1:~$ crontab -l
# Perform full backup on database Saturday at 12am & 12pm
0 0,12 * * 6 /$HOME/scripts/backup_db SCRYD111 full > /$HOME/scripts/logs/full_backup.log
# Perform incremental backups on database Sun-Fri at 0,6,12,20
0 0,6,12,20 * * 0-5 /$HOME/scripts/backup_db SCRYD111 inc > /$HOME/scripts/logs/inc_backup.log
oraahc@rac1:~$
oraahc@rac1:~$
oraahc@rac1:~$ cat $HOME/scripts/backup_db
#!/bin/bash
# Perform RMAN backup
# Expected usage: f_perform_backup DB LEVEL
# DB: Database SID to pass to RMAN
# LEVEL: full/inc - full is full incremental, inc is differential incremental
f_perform_backup() {
export LEVEL="$1"
echo "Performing backup on $ORACLE_SID."
echo "Performing $LEVEL backup."
# Run RMAN
$ORACLE_HOME/bin/rman <<RMAN
connect target /
run {
backup
incremental level = $LEVEL
database include current controlfile;
backup archivelog all;
}
exit;
RMAN
echo "backup completed on $ORACLE_SID, at: $(date)"
}
# Main
# Expected usage: ./backup_db DBNAME BACKUP_TYPE
# DBNAME: SID of Oracle database
# BACKUP_TYPE: Incremental backup, or full backup
f_main() {
echo "ORACLE_HOME set to: $ORACLE_HOME"
if [ -z "$ORACLE_HOME" ]; then
echo "\$ORACLE_HOME isn't set. Exiting."
exit 133
fi
# which database?
if [ -z "$1" ]; then
echo "\$ORACLE_SID not provided. Exiting."
exit 133
else
export ORACLE_SID=$1
fi
# incremental or full?
if [ -z "$2" ]; then
echo "No backup method specified. Exiting."
exit 133
else
if [ "$2" == "inc" ]; then
f_perform_backup "1"
elif [ "$2" == "full" ]; then
f_perform_backup "0"
else
echo "Invalid backup method specified. Exiting."
fi
fi
}
# Source environment
source $HOME/.profile # <---- (.bashrc for linux & .profile for solaris)
# Colors
RESTORE='\033[0m'
PURPLE='\033[00;35m'
# Call main, pass parameters
echo "Running script on: $(date)."
f_main "$1" "$2"
oraahc@rac1:~$
oraahc@rac1:~$
RMAN Configuration Parameters:
alter system set db_recovery_file_dest='+RECO' scope=spfile sid='*';
alter system set db_recovery_file_dest_size=1500G scope=spfile sid='*';
select * from gv$rman_configuration;
show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 1500G
oraahc@rac1:~$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Mar 9 08:38:06 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: SCRYD11 (DBID=1306257231)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name SCRYD11 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 10 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '+RECOC1/rmanbkp/%U';
CONFIGURE MAXSETSIZE TO 16200 M;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+RECOC1/rmanbkp/cntrlbkp/snapcf_SCRYD111.f';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+RECOC1/rmanbkp/cntrlbkp/snapcf_SCRYD111.f';
RMAN>
RMAN Backup to ASM disk +RECO as per init para reco (or) to any local mount point /u01 by passing format
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '+RECO/rmanbkp/%d_%T_%s_%p_%U.bkp';
ASMCMD [+reco] > mkdir rmanbkp
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK CLEAR;
RMAN> show device type;
RMAN> show channel;
RMAN> backup database plus archivelog;
run {
backup as compressed backupset database format '+RECO' tag DB_FULL;
sql 'alter system archive log current';
backup as compressed backupset archivelog all format '+RECO' tag DB_ARCH;
}
==============================================================================================================
[oracle@RUH-ERPDB01~]$ cat ~/scripts/backup_db
export Backup_Location=/home/oracle/expdp/rman/`date +%y-%m-%d`
export ORACLE_SID=AREEFDB1
export ORACLE_UNQNAME=AREEFDB
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=/home/oracle/dbhome_1
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
echo "backup started on $ORACLE_SID, at: $(date) Bkp_Loc=/home/oracle/expdp/rman"
mkdir -p $Backup_Location
rman target / log=$Backup_Location/LogRMAN.log << EOF
#rman target / log /backup/rman/logs/log_`date +\%a_\%d\%b\%y_\%H\%M`.log <<EOF #---->log_Wed_31Aug22_1500.log
#rman target / log /backup/rman/logs/Delete_`date +\%a_\%d\%b\%y_\%H\%M`.log <<EOF #---->Delete_Wed_31Aug22_1430.log
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
show all;
crosscheck backup;
crosscheck archivelog all;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
delete noprompt expired backup;
delete noprompt obsolete;
#backup AS COMPRESSED BACKUPSET database format '$Backup_Location/%d_DATA_%T_%s_%p.bkp' tag DB_FULL;
sql 'alter system archive log current';
#backup AS COMPRESSED BACKUPSET archivelog all format '$Backup_Location/%d_ARCH_%T_%s_%p.bkp' tag DB_ARCH;
#backup current controlfile format '$Backup_Location/%d_CTRL_%T_%s_%p.bkp' tag DB_CTRL;
BACKUP SPFILE format '$Backup_Location/SPFILE_%d_%T_%s_%p.bak' tag DB_SPFILE;
delete noprompt archivelog until time 'SYSDATE-8';
delete noprompt backup completed before 'SYSDATE -3';
#restore database validate;
#list backup summary;
}
EOF
echo "RMAN Auto-backup started on $ORACLE_SID at 00:01 AM as per cronjob and completed at: $(date)" >> $Backup_Location/LogRMAN.log
rman target / log=$Backup_Location/ListSUMMARY.log << EOF
list backup summary;
exit
EOF
cat $Backup_Location/ListSUMMARY.log >> $Backup_Location/LogRMAN.log
echo "RMAN BKP/list_summary started on $ORACLE_SID at 00:01 AM as per cronjob and completed at: $(date)" >> $Backup_Location/LogRMAN.log
find /home/oracle/expdp/rman \( -name "22-*" -o -name "23-*" \) -mtime +4 -exec rm {} \;
du -sh /home/oracle/fra/AREEFDB/archivelog/* >> $Backup_Location/LogRMAN.log
echo "RMAN Auto-backup Folder Check for daily deleting backups as pe customized RMAN bkp Script" >> $Backup_Location/LogRMAN.log
du -sh /home/oracle/expdp/rman/* >> $Backup_Location/LogRMAN.log
echo "EXPD Storage check for Mount Point folder ~/expdp as per Schema backups as pe customized RMAN bkp Script " >> $Backup_Location/LogRMAN.log
du -sh ~/expdp/* >> $Backup_Location/LogRMAN.log
echo "Server Storage check for Mount Point status for daily deleting backups as pe customized RMAN bkp Script " >> $Backup_Location/LogRMAN.log
df -h >> $Backup_Location/LogRMAN.log
[oracle@RUH-ERPDB01~]$
[oracle@RUH-ERPDB01~]$ crontab -l
# Perform full backup on AREEFDB database Sunday at 12am
1 0 * * * $HOME/scripts/backup_db
[oracle@RUH-ERPDB01~]$
==============================================================================================================
RMAN Backup has failed with the following error
RMAN Backup has failed with the following error
"ORA-27072: File I/O error"
[oraprod@RUH-ERPDB01 ~]$
[oraprod@RUH-ERPDB01 ~]$ rman target /
Recovery Manager: Release 11.1.0.7.0 - Production on Mon Apr 9 11:41:01 2018
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: PROD (DBID=298384717)
RMAN>
channel ORA_DISK_3 disabled, job failed on it will be run on another channel
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_4 channel at 04/09/2018 09:14:38
ORA-19502: write error on file "/u02/BACKUP/RMAN/PROD_20180409_612_1_ARCHIVE", block number 4665345 (block size=512)
ORA-27072: File I/O error
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4
Additional information: 4665345
Additional information: 3584
RMAN>
RMAN>
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2355 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=2358 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=2288 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=2311 device type=DISK
validation succeeded for archived log
archived log file name=/u02/oracle/PROD/db/apps_st/archive/1_851_886546382.dbf RECID=569 STAMP=968665382
. . . .
. . . .
. . . .
. . . .
archived log file name=/u02/oracle/PROD/db/apps_st/archive/1_931_886546382.dbf RECID=649 STAMP=972983342
Crosschecked 81 objects
RMAN>
RMAN>
RMAN> delete noprompt archivelog until time 'SYSDATE-8';
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2355 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=2358 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=2288 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=2311 device type=DISK
List of Archived Log Copies for database with db_unique_name PROD
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
569 1 851 A 20-FEB-18
Name: /u02/oracle/PROD/db/apps_st/archive/1_851_886546382.dbf
. . . .
. . . .
. . . .
. . . .
644 1 926 A 29-MAR-18
Name: /u02/oracle/PROD/db/apps_st/archive/1_926_886546382.dbf
deleted archived log
archived log file name=/u02/oracle/PROD/db/apps_st/archive/1_851_886546382.dbf RECID=569
. . . .
. . . .
. . . .
. . . .
archived log file name=/u02/oracle/PROD/db/apps_st/archive/1_926_886546382.dbf RECID=644 STAMP=972382678
Deleted 76 objects
RMAN>
RMAN>
RMAN> CROSSCHECK ARCHIVELOG ALL;
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2355 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=2358 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=2288 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=2311 device type=DISK
validation succeeded for archived log
archived log file name=/u02/oracle/PROD/db/apps_st/archive/1_927_886546382.dbf RECID=645 STAMP=972659484
validation succeeded for archived log
archived log file name=/u02/oracle/PROD/db/apps_st/archive/1_928_886546382.dbf RECID=646 STAMP=972926082
validation succeeded for archived log
archived log file name=/u02/oracle/PROD/db/apps_st/archive/1_929_886546382.dbf RECID=647 STAMP=972982491
validation succeeded for archived log
archived log file name=/u02/oracle/PROD/db/apps_st/archive/1_930_886546382.dbf RECID=648 STAMP=972983337
validation succeeded for archived log
archived log file name=/u02/oracle/PROD/db/apps_st/archive/1_931_886546382.dbf RECID=649 STAMP=972983342
Crosschecked 5 objects
RMAN>
RMAN>
RMAN>
Solution:
Just reclaim the storage space by setting appropriate retention to RMAN backup policy and
Deleted some old files that not needed for any kind of recovery.
By doing above steps, RMAN backup completed successfully.
==============================================================================================================
--------------------------------------
[oracle@rac2 dbs]$
[oracle@rac2 dbs]$ rman auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 13 22:00:38 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: DCLONE (not mounted)
RMAN>
RMAN> duplicate database to DCLONE backup location '/u01/RMAN';
==============================================================================================================
[oracle@rac3 ~]$
[oracle@rac3 ~]$ rman target sys/sys123@DELL nocatalog auxiliary sys/sys123@CDELL
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 21 16:15:24 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DELL (DBID=3936476647)
using target database control file instead of recovery catalog
connected to auxiliary database: CDELL (not mounted)
RMAN> duplicate database to CDELL from active database nofilenamecheck;
==============================================================================================================
[oracle@rac2 dbs]$
[oracle@rac2 dbs]$ rman target sys/sys@dell_live auxiliary sys/sys@dell_stby
[oracle@rac2 dbs]$ rman target sys/sys@dell_live auxiliary sys/sys@dell_stby
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 7 13:25:14 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DELL (DBID=3908745866)
connected to auxiliary database: DELL (not mounted)
RMAN>
RMAN>
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
--------------------------------------
Create Standby Database “using compressed network backup set from service” to reduce network bandwidth in far STBY Destination by enabling Compression Parameter for dest_2 on Primary:
SQL> show parameter dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service="ahcstby1", ASYNC NOAF
FIRM delay=0 optional compress
ion=enable max_failure=0 max_c
onnections=1 reopen=30 db_uniq
ue_name="ahcstby1" net_timeout
=300, valid_for=(online_logfil
es,all_roles)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service="ahcstby1", ASYNC NOAF
FIRM delay=0 optional compress
ion=enable max_failure=0 max_c
onnections=1 reopen=30 db_uniq
ue_name="ahcstby1" net_timeout
=300, valid_for=(online_logfil
es,all_roles)
RMAN> duplicate target database for standby from active database using compressed backupset nofilenamecheck;
==============================================================================================================
[oracle@rac1 ~]$
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 17 09:43:20 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DELL (not mounted)
RMAN>
RMAN> restore controlfile from '/u01/app/oracle/DELL/control01.ctl';
Starting restore at 17-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/dell/control01.ctl
output file name=+DATA/dell/control02.ctl
Finished restore at 17-DEC-17
RMAN>
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
RMAN> backup as copy database format '+DATA';
EBS-R12.1.3 DB version 11.1.0.7 RMAN CLONE
==============================================================================================================
==============================================================================================================
Point in Time RECOVERY (PITR)(Restore/Recover/Recreate_bkp)
Recover a Table from an RMAN Backup in an Oracle 12c
In Oracle 12c, a single table or a single partition of a partitioned table can be restored from an RMAN backup via the RECOVER TABLE command. Prior to 12c restoring a table was a long and time-consuming process.
So what Activity "RMAN RECOVER TABLE" command does in background?
It creates an auxiliary database or instance at auxiliary destination with same db_name but, different db_unique_name which is used to recover the tables to a specific point in time. This database will contain a few system related data files like SYSTEM, SYSAUX, UNDO and data files belonging to the tablespace containing the tables we are looking to restore. Then it creates a Data Pump export dump file which will contain the recovered table or partitions of tables. It will then import the data into the target database using Data Pump Import. Finally, it will remove the temporary auxiliary instance.
MOS DOCUMENTS:
RMAN RECOVER TABLE Feature New to Oracle Database 12c (Doc ID 1521524.1)
Rman Restore Table Fails with ORA-29283 RMAN-06962 RMAN-06960 ORA-31626 in 12c (Doc ID 2231855.1)
Because of less processes RMAN will fail to restore the table. So create auxiliary parameter file as below and set parameters accordingly.
oraahc@dbnode2:/u02/aux$ cat initaux1.ora
compatible=12.1.0.0.0 #for EBS DB and AJDB 12.1.0.2.0
db_block_size=8192
db_files=200
sga_target=2G
processes=400
db_files=1000
oraahc@dbnode2:~$
. Before starting take backup of current table:
. Restore can be done by timestamp or SCN number
. Create a aux. parameter file to increase processes, SGA
. If auxiliary destination is from ASM to ASM, then performance improves by 75%
Check CURRENT SCN of the Oracle Database
select CURRENT_SCN from v$database;
Example of TIMESTAMP_TO_SCN & SCN_TO_TIMESTAMP function
select timestamp_to_scn(to_timestamp('22-11-2020 19:40:00','DD-MM-YYYY HH24:MI:SS')) "SCN" from dual;
SQL> select scn_to_timestamp(39999823174) from dual;
SQL> SET NUMWIDTH 20;
Register the catalog backup peices to RMAN Repository:
oraahc@dbnode2:~$ cd expdp/rman/22-03-16_old/
oraahc@dbnode2:~$ ls -ltrh
-rw-r-----. 1 oracle oinstall 134M Mar 16 00:01 AJ_2022-03-16_ms0og7u4_1_1_DB.bkp
-rw-r-----. 1 oracle oinstall 284M Mar 16 00:02 AJ_2022-03-16_mr0og7u4_1_1_DB.bkp
-rw-r-----. 1 oracle oinstall 330M Mar 16 00:02 AJ_2022-03-16_mt0og7u5_1_1_DB.bkp
-rw-r-----. 1 oracle oinstall 3.7G Mar 16 00:11 AJ_2022-03-16_mq0og7u4_1_1_DB.bkp
-rw-r-----. 1 oracle oinstall 11M Mar 16 00:12 c-1547436721-20220316-00
-rw-r-----. 1 oracle oinstall 770M Mar 16 00:14 AJ_2022-03-16_mv0og8iv_1_1_ArchiveLog.bkp
-rw-r-----. 1 oracle oinstall 702M Mar 16 00:14 AJ_2022-03-16_n10og8iv_1_1_ArchiveLog.bkp
-rw-r-----. 1 oracle oinstall 735M Mar 16 00:14 AJ_2022-03-16_n00og8iv_1_1_ArchiveLog.bkp
-rw-r-----. 1 oracle oinstall 127M Mar 16 00:14 AJ_2022-03-16_n30og8na_1_1_ArchiveLog.bkp
-rw-r-----. 1 oracle oinstall 424M Mar 16 00:14 AJ_2022-03-16_n20og8j0_1_1_ArchiveLog.bkp
-rw-r-----. 1 oracle oinstall 1.2M Mar 16 00:15 AJ_2022-03-16_ControlFile.bkp
-rw-r-----. 1 oracle oinstall 96K Mar 16 00:15 SPFILE_AJDB_20220316_741_1.bak
-rw-r-----. 1 oracle oinstall 11M Mar 16 00:15 c-1547436721-20220316-01
-rw-r--r--. 1 oracle oinstall 3.7K Mar 16 00:15 ListSUMMARY.log
-rw-r--r--. 1 oracle oinstall 33K Mar 16 00:15 LogRMAN.log
oraahc@dbnode2:~$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Aug 24 20:29:06 2020
RMAN>
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/AJ_2022-03-16_ms0og7u4_1_1_DB.bkp';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/AJ_2022-03-16_mr0og7u4_1_1_DB.bkp';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/AJ_2022-03-16_mt0og7u5_1_1_DB.bkp';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/AJ_2022-03-16_mq0og7u4_1_1_DB.bkp';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/c-1547436721-20220316-00';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/AJ_2022-03-16_mv0og8iv_1_1_ArchiveLog.bkp';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/AJ_2022-03-16_n10og8iv_1_1_ArchiveLog.bkp';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/AJ_2022-03-16_n00og8iv_1_1_ArchiveLog.bkp';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/AJ_2022-03-16_n30og8na_1_1_ArchiveLog.bkp';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/AJ_2022-03-16_n20og8j0_1_1_ArchiveLog.bkp';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/AJ_2022-03-16_ControlFile.bkp';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/SPFILE_AJDB_20220316_741_1.bak';
catalog backuppiece '/home/oracle/expdp/rman/22-03-16_old/c-1547436721-20220316-01';
RMAN> list backup summary;
768 B F A DISK 16-MAR-22 1 1 YES AJ_FULL
769 B F A DISK 16-MAR-22 1 1 YES AJ_FULL
770 B F A DISK 16-MAR-22 1 1 YES AJ_FULL
771 B F A DISK 16-MAR-22 1 1 YES AJ_FULL
772 B F A DISK 16-MAR-22 1 1 NO TAG20220316T001205
773 B A A DISK 16-MAR-22 1 1 YES AJ_ARCH
774 B A A DISK 16-MAR-22 1 1 YES AJ_ARCH
775 B A A DISK 16-MAR-22 1 1 YES AJ_ARCH
776 B A A DISK 16-MAR-22 1 1 YES AJ_ARCH
777 B A A DISK 16-MAR-22 1 1 YES AJ_ARCH
778 B F A DISK 16-MAR-22 1 1 YES AJ_CTRL
779 B F A DISK 16-MAR-22 1 1 YES AJ_SPFILE
780 B F A DISK 16-MAR-22 1 1 NO TAG20220316T001503
RMAN> set auxiliary instance parameter file to '/u02/aux/initaux1.ora';
executing command: SET auxiliary parameter file
using target database control file instead of recovery catalog
Scenario 1: Recover Table by creating a new table/remapping table to timestamp
It creates a new table with contents as of specified Timestamp
RMAN> recover table AJ_LIVE.CAR_TECHNICAL_TWO until time "TO_DATE('22-NOV-2020 19:40', 'DD-MON-YYYY HH24:MI')" AUXILIARY DESTINATION '+DATAC1' remap table "AJ_LIVE"."CAR_TECHNICAL_TWO":"CAR_TECHNICAL_TWO_2";
Scenario 2: Recover Table to dumpfile by exporting:
In cases where you do not want the table to be import but just need the export dump you can use the notableimport option;
It creates table dumpfile with contents as of specified Timestamp using expdp and later can be imported any Instance.
oraahc@dbnode2:~$ df -h
RMAN> recover table AJ_LIVE.CAR_TECHNICAL until time "TO_DATE('16-MAR-2022 19:40', 'DD-MON-YYYY HH24:MI')" AUXILIARY DESTINATION '/u02/aux' DUMP FILE 'aj_live_CT1.dmp' NOTABLEIMPORT;
SQL> CREATE TABLE CAR_TECHNICAL_19032022 AS (SELECT * FROM CAR_TECHNICAL);
SQL> drop table CAR_TECHNICAL;
impdp system/manager tables=aj_live.CAR_TECHNICAL directory=expdp dumpfile=aj_live_CT1.dmp logfile=aj_live_CT1.log
Scenario 3: Restore Table to timestamp
The below command directly restores & apply the contents on table to specified Timestamp. (used when records deleted)
As the entries were deleted Current table Records shows only:
SQL> select count(*) from AJ_LIVE.JOB_CHARGE_DETAIL;
COUNT(*)
----------
23
RMAN> recover table AJ_LIVE.job_charge_detail until time "TO_DATE('24-AUG-2020 13:00', 'DD-MON-YYYY HH24:MI')" AUXILIARY DESTINATION '/u02/aux';
Starting recover at 24-AUG-20
. . . . .
. . . . .
. . . . .
Creating automatic instance, with SID='aaxq'
using contents of file /u02/aux/initaux1.ora
initialization parameters used for automatic instance:
db_name=SCR11
db_unique_name=aaxq_pitr_SCRD11
compatible=12.1.0
db_block_size=8192
db_files=1000
diagnostic_dest=/u01/app/oraahc
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/u02/aux
log_archive_dest_1='location=/u02/aux'
ifile=/u02/aux/initaux1.or
starting up automatic instance SCRD11
. . . . .
. . . . .
. . . . . Restoring Auxiliary DB Depends on Resources & DB Size:
. . . . .
. . . . .
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_aaxq_ahwp":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 3 MB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
EXPDP> Processing object type TABLE_EXPORT/TABLE/TRIGGER
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_PO_OTM_OUT_S','PO',1,1,'12.01.00.00.00',newblock)
. . . . .
. . . . .
. . . . .
EXPDP> . . exported "AJ_LIVE"."JOB_CHARGE_DETAIL" 2.224 MB 17358 rows
EXPDP> Master table "SYS"."TSPITR_EXP_aaxq_ahwp" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_aaxq_ahwp is:
EXPDP> /u02/aux/tspitr_aaxq_72831.dmp
EXPDP> Job "SYS"."TSPITR_EXP_aaxq_ahwp" completed with 12 error(s) at Mon Aug 24 20:53:19 2020 elapsed 0 00:01:34
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_aaxq_aibE" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_aaxq_aibE":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
IMPDP> Processing object type TABLE_EXPORT/TABLE/TRIGGER
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_aaxq_aibE" successfully completed at Mon Aug 24 20:53:52 2020 elapsed 0 00:00:23
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /u02/aux/SCRD11/datafile/o1_mf_crpr_t_hn7z7o3o_.tmp deleted
. . . . .
. . . . .
. . . . .
auxiliary instance file tspitr_aaxq_72831.dmp deleted
Finished recover at 24-AUG-20
RMAN>
SQL> select count(*) from AJ_LIVE.JOB_CHARGE_DETAIL;
COUNT(*)
----------
17358
==============================================================================================================
If RMAN duplicate Command Fails with following errors:
(RMAN-05501, RMAN-03015, ORA-19660, ORA-19661, ORA-19849, ORA-19504, ORA-17502, ORA-15001, ORA-01017)
RMAN> duplicate database to ECCTEST from active database nofilenamecheck;
Starting Duplicate Db at 12-NOV-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2362 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=2598 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=2834 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''SCRY11'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''ECCTEST'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone from service 'SCRY11' primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''SCRY11'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''ECCTEST'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 4294967296 bytes
Fixed Size 2922264 bytes
Variable Size 889194728 bytes
Database Buffers 3388997632 bytes
Redo Buffers 13852672 bytes
Starting restore at 12-NOV-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2598 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=1654 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=2834 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service SCRY11
channel ORA_AUX_DISK_1: restoring control file
dbms_backup_restore.restoreCancel() failed
Oracle instance started
Total System Global Area 4294967296 bytes
Fixed Size 2922264 bytes
Variable Size 889194728 bytes
Database Buffers 3388997632 bytes
Redo Buffers 13852672 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''ECCTEST'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
}
executing Memory Script
sql statement: alter system set db_name = ''ECCTEST'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/12/2020 16:17:21
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 0 could not be verified
ORA-19849: error while reading backup piece from service SCRY11
ORA-19504: failed to create file "+DATA/cntrl01.dbf"
ORA-17502: ksfdcre:3 Failed to create file +DATA/cntrl01.dbf
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-01017: invalid username/password; logon denied
ORA-15040: diskgroup is incomplete
RMAN>
Solution: Add Database User to ASM Groups and retry.
useradd -u 1100 -g oinstall -G dba,asmdba,asmadmin oracle
Solution for : ORA-15040: diskgroup is incomplete
CAUSE: DBCA throwing error while accessing the ASM diskgroup.
There was no patches applied to 12.1.0.2 RDBMS_HOME
Applied the patch 31305174 to DB_HOME using opatchauto.
And DBCA completed successfully.
DBCA failing with RMAN-03002, ORA-01119, ORA-17502, ORA-15040: diskgroup is incomplete (Doc ID 2714528.1)
==============================================================================================================
RMAN percentage calculation made as per allotted channels for backup/restore
SQL>
col OPNAME for a30
select OPNAME,SOFAR/TOTALWORK*100 PCT, trunc(TIME_REMAINING/60) MIN_RESTANTES,
trunc(ELAPSED_SECONDS/60) MIN_ATEAGORA
from v$session_longops where TOTALWORK>0 and OPNAME like '%RMAN%';
RMAN Duplicate Transfer Rate for duplicate
SELECT 'DUPLICATE/RESTORE THROUGHPUT',
round(SUM(v.value/1024/1024),1) mbytes_sofar,
round(SUM(v.value /1024/1024)/nvl((SELECT MIN(elapsed_seconds)
FROM v$session_longops
WHERE OPNAME LIKE 'RMAN: aggregate input'
AND SOFAR != TOTALWORK AND elapsed_seconds IS NOT NULL ),SUM(v.value /1024/1024)),2) mbytes_per_sec,n.name
FROM gv$sesstat v, v$statname n, gv$session s
WHERE v.statistic#=n.statistic#
AND n.name = 'physical write total bytes' AND v.sid = s.sid AND v.inst_id=s.inst_id AND s.program like 'rman@%'
GROUP BY 'DUPLICATE/RESTORE THROUGHPUT',n.name;
==============================================================================================================
RMAN Backup and Restore Procedure still need to investigate;
Basic Parameters to start database in Nomount stage;
[oracle@rac01 dbs]$ cat initDELL1.ora
db_name=DELL
#control_files=+DATA
undo_management=AUTO
[oracle@rac01 dbs]
RMAN> delete noprompt backup;
/$HOME/scripts/backup_db DELL1 full > /$HOME/scripts/logs/full_backup.log
set linesize 200;
select dbid,NAME,INSTANCE_NAME,OPEN_MODE,CREATED,LOG_MODE,CONTROLFILE_TYPE,FLASHBACK_ON,DATABASE_ROLE,GUARD_STATUS,PROTECTION_MODE from gv$database,gv$instance;
select name from v$controlfile;
show parameter spfile;
show parameter recovery
show parameter sga
archive log list;
############################################################
############################################################
SQL> startup force mount restrict
select dbid,NAME,OPEN_MODE,CREATED,LOG_MODE,CONTROLFILE_TYPE,FLASHBACK_ON,DATABASE_ROLE,GUARD_STATUS,PROTECTION_MODE from gv$database;
drop database
ASMCMD [+data/DELL/PARAMETERFILE] > ls -ltrh
WARNING: option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE MAR 31 22:00:00 Y spfile.297.1132957805
PARAMETERFILE UNPROT COARSE MAR 31 22:00:00 Y spfile.300.1132957977
PARAMETERFILE UNPROT COARSE MAR 31 23:00:00 Y spfile.293.1132960109
PARAMETERFILE UNPROT COARSE MAR 31 23:00:00 Y spfile.294.1132960939
PARAMETERFILE UNPROT COARSE APR 01 14:00:00 Y spfile.333.1133015823
ASMCMD [+data/DELL/PARAMETERFILE] > rm -rf *
ASMCMD [+data/DELL/PARAMETERFILE] > cd ..
ASMCMD [+data/DELL] > ls
CONTROLFILE/
PASSWORD/
ASMCMD [+data/DELL] > cd CON*
ASMCMD [+data/DELL/CONTROLFILE] > ls
current.257.1132949195
current.267.1132955147
current.276.1132959087
current.301.1133015939
current.322.1132956779
current.331.1132960237
current.336.1132958209
ASMCMD [+data/DELL/CONTROLFILE] > rm -rf *
ASMCMD [+data/DELL/CONTROLFILE] > cd ..
ASMCMD [+data/DELL] > ls
PASSWORD/
ASMCMD [+data/DELL] >
############################################################
############################################################
[oracle@rac01 dbs]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Mar 31 17:11:53 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 759165264 bytes
Fixed Size 8929616 bytes
Variable Size 541065216 bytes
Database Buffers 201326592 bytes
Redo Buffers 7843840 bytes
RMAN> select INSTANCE_NAME,STATUS,DATABASE_TYPE,DATABASE_STATUS from gv$instance;
INSTANCE_NAME STATUS DATABASE_TYPE DATABASE_STATUS
---------------- ------------ --------------- -----------------
DELL1 STARTED RAC ACTIVE
RMAN>
RMAN>
-----> Use same DBID of DATABASE TO REstore else will get error as belwo whle mounting controlfile
RMAN> set dbid 4092997409;
RMAN> a-lter database mount;------->
released channel: ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 04/01/2023 15:27:25
RMAN-06189: current DBID 4092933333 does not match target mounted database (4092997409)
RMAN>
RMAN> restore spfile to '+DATA' FROM AUTOBACKUP;
RMAN> restore spfile to '+DATA' from '+reco/daily_rman/spfile_DELL_20230331_49_1.bak';
Starting restore at 31-MAR-23
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +reco/daily_rman/spfile_DELL_20230331_49_1.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 31-MAR-23
RMAN>
ASMCMD [+DATA/DELL] > ls
PARAMETERFILE/
PASSWORD/
ASMCMD [+DATA/DELL] > cd PARA*
ASMCMD [+DATA/DELL/PARAMETERFILE] > ls
spfile.283.1132948825
####################################### INVALID RESTORE COMMAND FOR CONTROLFILE ####################
RMAN> restore controlfile;
Starting restore at 01-APR-23
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/01/2023 14:55:01
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP
RMAN>
RMAN> restore controlfile to '+DATA' from '/u01/app/oracle/product/19.0.0.0/db_1/dbs/c-4092997409-20230331-07';
Starting restore at 31-MAR-23
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 31-MAR-23
RMAN>
ASMCMD [+data/DELL] > ls
CONTROLFILE/
PARAMETERFILE/
PASSWORD/
ASMCMD [+data/DELL] >
RMAN> restore controlfile to '+DATA' from AUTOBACKUP;
Starting restore at 31-MAR-23
using channel ORA_DISK_1
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20230331
channel ORA_DISK_1: AUTOBACKUP found: c-4092997409-20230331-0b
channel ORA_DISK_1: restoring control file from AUTOBACKUP c-4092997409-20230331-0b
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
Finished restore at 31-MAR-23
RMAN>
RMAN> alter database mount;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/31/2023 22:14:22
ORA-00205: error in identifying control file, check alert log for more info
RMAN>
RMAN> restore controlfile from '/u01/app/oracle/product/19.0.0.0/db_1/dbs/c-4092997409-20230331-07';
Starting restore at 31-MAR-23
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/product/19.0.0.0/db_1/dbs/cntrlDELL1.dbf
Finished restore at 31-MAR-23
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>
##############################################################################################################
##############################################################################################################
##############################################################################################################
RMAN> restore controlfile from AUTOBACKUP;
Starting restore at 01-APR-23
using channel ORA_DISK_1
recovery area destination: +RECO
database name (or database unique name) used for search: DELL
channel ORA_DISK_1: AUTOBACKUP +RECO/DELL/AUTOBACKUP/2023_04_01/s_1133014977.383.1133014977 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20230401
channel ORA_DISK_1: restoring control file from AUTOBACKUP +RECO/DELL/AUTOBACKUP/2023_04_01/s_1133014977.383.1133014977
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATA/DELL/CONTROLFILE/current.335.1133015991
Finished restore at 01-APR-23
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>
RMAN> restore controlfile from '+RECO/DAILY_RMAN/DELL_ctrl_20230331_107_1.bkp';
Starting restore at 31-MAR-23
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/DELL/CONTROLFILE/current.301.1132960437
Finished restore at 31-MAR-23
RMAN>
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>
##############################################################################################################
##############################################################################################################
##############################################################################################################
set linesize 200 pagesize 400;
col HOST_NAME for a27;
select dbid,NAME,INSTANCE_NAME,DATABASE_TYPE,OPEN_MODE,CREATED,LOG_MODE,FLASHBACK_ON,CONTROLFILE_TYPE,DATABASE_ROLE,GUARD_STATUS,PROTECTION_MODE from gv$database,gv$instance;
select INSTANCE_NUMBER,INSTANCE_NAME,DATABASE_TYPE,HOST_NAME,STATUS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE,INSTANCE_MODE from gv$instance;
show parameter spfile;
show parameter recovery
archive log list;
RMAN> list backup;
RMAN> list backup of archivelog all;
RMAN> RESTORE DATABASE PREVIEW;
RMAN> restore database preview summary;
RMAN> RESTORE DATABASE VALIDATE;
RMAN> restore database;
Finished restore at 01-APR-23
RMAN> recover database;
Starting recover at 01-APR-23
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file +RECO/DELL/ARCHIVELOG/2023_04_01/thread_1_seq_1.373.1133016577
archived log for thread 1 with sequence 2 is already on disk as file +RECO/DELL/ARCHIVELOG/2023_04_01/thread_1_seq_2.324.1133016581
archived log for thread 1 with sequence 3 is already on disk as file +RECO/DELL/ARCHIVELOG/2023_04_01/thread_1_seq_3.400.1133016643
archived log file name=+RECO/DELL/ARCHIVELOG/2023_04_01/thread_1_seq_1.373.1133016577 thread=1 sequence=1
archived log file name=+RECO/DELL/ARCHIVELOG/2023_04_01/thread_1_seq_2.324.1133016581 thread=1 sequence=2
archived log file name=+RECO/DELL/ARCHIVELOG/2023_04_01/thread_1_seq_3.400.1133016643 thread=1 sequence=3
unable to find archived log
archived log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/01/2023 15:03:05
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 2402620
RMAN>
RMAN> alter database open resetlogs;
SQL>
set linesize 200 pagesize 400;
col HOST_NAME for a27;
select dbid,NAME,INSTANCE_NAME,DATABASE_TYPE,OPEN_MODE,CREATED,LOG_MODE,FLASHBACK_ON,CONTROLFILE_TYPE,DATABASE_ROLE,GUARD_STATUS,PROTECTION_MODE from gv$database,gv$instance;
select INSTANCE_NUMBER,INSTANCE_NAME,DATABASE_TYPE,HOST_NAME,STATUS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE,INSTANCE_MODE from gv$instance;
show parameter spfile;
show parameter recovery
archive log list;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL>
SQL> create spfile from pfile;
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/DELL/PARAMETERFILE/spfi
le.283.1132948825
SQL>
alter system set db_recovery_file_dest='+RECO' scope=spfile sid='*';
alter system set db_recovery_file_dest_size=999G scope=spfile;
alter system set sga_max_size=16G scope=spfile sid='*';
alter system set sga_target=16G scope=spfile sid='*';
alter system set pga_aggregate_target=16G scope =spfile sid='*';
[oracle@rac01 ~]$ srvctl config database -d $ORACLE_UNQNAME
Database unique name: DELL
Database name: DELL
Oracle home: /u01/app/oracle/product/19.0.0.0/db_1
Oracle user: oracle
Spfile: +DATA/DELL/PARAMETERFILE/spfile.283.1132948825
--- not required for controlfile if restored in ASM
RMAN> backup current controlfile;
RMAN> restore controlfile to '+DATA';
SQL> alter system set control_files='+data/DELL/CONTROLFILE/current.331.1132957723' scope=spfile;
[oracle@rac01 dbs]$ srvctl config database -d $ORACLE_UNQNAME
Database unique name: DELL
Database name: DELL
Oracle home: /u01/app/oracle/product/19.0.0.0/db_1
Oracle user: oracle
Spfile: +DATA/DELL/PARAMETERFILE/spfile.297.1132957805
RMAN> backup spfile;
RMAN> restore spfile to '+DATA';
[oracle@rac01 dbs]$ srvctl config database -d $ORACLE_UNQNAME
Database unique name: DELL
Database name: DELL
Oracle home: /u01/app/oracle/product/19.0.0.0/db_1
Oracle user: oracle
Spfile: +DATA/DELL/PARAMETERFILE/spfile.300.1132957977
ASMCMD [+data/DELL/PARAMETERFILE] > ls
spfile.293.1132959463
spfile.297.1132957805
spfile.300.1132957977
ASMCMD [+data/DELL/PARAMETERFILE] >
shut immediate
startup
set linesize 200;
select dbid,NAME,INSTANCE_NAME,OPEN_MODE,CREATED,LOG_MODE,CONTROLFILE_TYPE,FLASHBACK_ON,DATABASE_ROLE,GUARD_STATUS,PROTECTION_MODE from gv$database,gv$instance;
select name from v$controlfile;
show parameter spfile;
show parameter recovery
show parameter sga
archive log list;
select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE,INSTANCE_MODE from gv$instance;
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/DELL/PARAMETERFILE/spfi
le.333.1132960993
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 999G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>
RMAN> delete noprompt backup;
/$HOME/scripts/backup_db DELL1 full > /$HOME/scripts/logs/full_backup.log
Thanks for Reading
Regards,
Mohammed Areefuddin.
Suggested Topics :
Linux | DATABASE | RMAN | RAC | EBS |
| R1229 M7 Clone | ||||
| RAC DataGuard | Pluggable DB Clone | |||
| appsutil for DB | ||||
| JDK JRE upgrade | ||||
| Add EBS Node | ||||
No comments:
Post a Comment