Navigation Bar

Saturday, October 26, 2024

Oracle Database cold backup - ARCHIVELOG mode

Set the environment variable 
set ORACLE_HOME=E:\install\21cXE\dbhomeXE
set ORACLE_SID=XE
set PATH=%ORACLE_HOME%/bin:%PATH%
set LD_LIBRARY_PATH=%ORACLE_HOME%/lib
Mount the database and set it it archivelog mode 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1610608792 bytes
Fixed Size                  9855128 bytes
Variable Size             973078528 bytes
Database Buffers          620756992 bytes
Redo Buffers                6918144 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.
Check the database log mode 
select name, db_unique_name, open_mode, log_mode from v$database;
namedb_unique_nameopen_modelog_mode
XEXEREAD WRITEARCHIVELOG
Do manual switch of log files to create some archive logs 
alter system switch logfile;
system SWITCH altered.

alter system switch logfile;
system SWITCH altered.

alter system switch logfile;
system SWITCH altered.
Select and check the path of the log files in v$log table 
select group#, sequence#, status from v$log;
group#sequence#status
167CURRENT
265INACTIVE
366ACTIVE
Query and check the logfile members and status from v$logfile 
select group#, status, type, member from v$logfile;
group#statustypemember
3 ONLINEE:\INSTALL\21CXE\ORADATA\XE\REDO03.LOG
2 ONLINEE:\INSTALL\21CXE\ORADATA\XE\REDO02.LOG
1 ONLINEE:\INSTALL\21CXE\ORADATA\XE\REDO01.LOG
Select and check archive logs are created 
select name, sequence#, blocks from v$archived_log;
namesequence#blocks
E:\INSTALL\21CXE\HOMES\ORADB21HOME1\RDBMS\ARC0000000063_1170148935.00016313126
E:\INSTALL\21CXE\HOMES\ORADB21HOME1\RDBMS\ARC0000000064_1170148935.000164134
E:\INSTALL\21CXE\HOMES\ORADB21HOME1\RDBMS\ARC0000000065_1170148935.00016522
E:\INSTALL\21CXE\HOMES\ORADB21HOME1\RDBMS\ARC0000000066_1170148935.00016620
In RMAN command, do a checkpoint, shutdown the database and restart in mount mode 
RMAN> sql 'alter system checkpoint';

using target database control file instead of recovery catalog
sql statement: alter system checkpoint

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1610608792 bytes

Fixed Size                     9855128 bytes
Variable Size                973078528 bytes
Database Buffers             620756992 bytes
Redo Buffers                   6918144 bytes

SQL> create pfile='E:\technical\blogs\RMAN\backupplusarchive\initXE.ora' from spfile;
Run the below RMAN script to backup the database and the archivelogs. 
RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT 'E:\technical\blogs\RMAN\backupplusarchive\%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT 'E:\technical\blogs\RMAN\backupplusarchive\%U';
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT 'E:\technical\blogs\RMAN\backupplusarchive\%U';
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
BACKUP CURRENT CONTROLFILE FORMAT 'E:\technical\blogs\RMAN\backupplusarchive\cntrl_%s_%p_%t';
RELEASE CHANNEL disk1;
RELEASE CHANNEL disk2;
RELEASE CHANNEL disk3;
}
Steps to restore the cold backup including archivelogs 
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup mount exclusive restrict;
ORACLE instance started.

Total System Global Area 1610608792 bytes
Fixed Size                  9855128 bytes
Variable Size             973078528 bytes
Database Buffers          620756992 bytes
Redo Buffers                6918144 bytes
Database mounted.

SQL> drop database;

Database dropped.

E:\>rman target sys/oracle123

Recovery Manager: Release 21.0.0.0.0 - Production on Thu Oct 17 17:36:47 2024
Version 21.3.0.0.0

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

connected to target database (not started)

RMAN>
run
{
startup pfile='E:\technical\blogs\RMAN\backupplusarchive\INITXE.ORA' nomount;
restore controlfile from 'E:\technical\blogs\RMAN\backupplusarchive\CNTRL_22_1_1182706753';
alter database mount;
restore database;
alter database open resetlogs;
}

Oracle instance started

Total System Global Area    1610608792 bytes

Fixed Size                     9855128 bytes
Variable Size                973078528 bytes
Database Buffers             620756992 bytes
Redo Buffers                   6918144 bytes
Sample logs of the restore command 
Starting restore at 18-OCT-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=E:\INSTALL\21CXE\ORADATA\XE\CONTROL01.CTL
output file name=E:\INSTALL\21CXE\ORADATA\XE\CONTROL02.CTL
Finished restore at 18-OCT-24

released channel: ORA_DISK_1
Statement processed

Starting restore at 18-OCT-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to E:\INSTALL\21CXE\ORADATA\XE\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00007 to E:\INSTALL\21CXE\ORADATA\XE\USERS01.DBF
channel ORA_DISK_1: reading from backup piece E:\TECHNICAL\BLOGS\RMAN\BACKUPPLUSARCHIVE\0E37TAUG_14_1_1
channel ORA_DISK_1: piece handle=E:\TECHNICAL\BLOGS\RMAN\BACKUPPLUSARCHIVE\0E37TAUG_14_1_1 tag=TAG20241018T173718
channel ORA_DISK_1: restored backup piece 1
...
...
...
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to E:\INSTALL\21CXE\ORADATA\XE\PDBSEED\UNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece E:\TECHNICAL\BLOGS\RMAN\BACKUPPLUSARCHIVE\0L37TB1T_21_1_1
channel ORA_DISK_1: piece handle=E:\TECHNICAL\BLOGS\RMAN\BACKUPPLUSARCHIVE\0L37TB1T_21_1_1 tag=TAG20241018T173718
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 18-OCT-24

Statement processed
Verify the logfiles restored. 
select group#, sequence#,  status from v$log;
group#sequence#status
11CURRENT
20UNUSED
30UNUSED
Verify the status of the logfiles restored 
select group#, status, type, member from v$logfile;
group#statustypemember
3 ONLINEE:\INSTALL\21CXE\ORADATA\XE\REDO03.LOG
2 ONLINEE:\INSTALL\21CXE\ORADATA\XE\REDO02.LOG
1 ONLINEE:\INSTALL\21CXE\ORADATA\XE\REDO01.LOG
Verify the archivelog files restored. 
select name, sequence#, blocks from v$archived_log;
namesequence#blocks
E:\INSTALL\21CXE\HOMES\ORADB21HOME1\RDBMS\ARC0000000063_1170148935.00016313126
E:\INSTALL\21CXE\HOMES\ORADB21HOME1\RDBMS\ARC0000000064_1170148935.000164134
E:\INSTALL\21CXE\HOMES\ORADB21HOME1\RDBMS\ARC0000000065_1170148935.00016522
E:\INSTALL\21CXE\HOMES\ORADB21HOME1\RDBMS\ARC0000000066_1170148935.00016620
See the RMAN configuration parameters 
RMAN> SHOW ALL;

RMAN configuration parameters for database with db_unique_name XE are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
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 NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\INSTALL\21CXE\DATABASE\SNCFXE.ORA'; # default
References 
Restore RMAN archivelog Backup from a different location 
Archivelog mode rman cold backup and restore

Thought for the day
Like the glaze covering an earthen vessel, are smooth words with an evil heart.
An enemy dissembles in speaking while harboring deceit within;
When an enemy speaks graciously, do not believe it, for there are seven abominations concealed within;
Though hatred is covered with guile, the enemies wickedness will be exposed in the assembly.
Proverbs 26:23-26

Lord Jesus, give me the wisdom to discern.
at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)