Script to take Daily Full Backups using RMAN In Oracle

Script to take Daily Full Backups using RMAN In Oracle

Need to create required directories:

mkdir -p /u03/scripts

mkdir -p /u03/rman_backup


Actual Script:

[oratest@mqmhrd1 rman_backup]$ cd scripts/

vi rman_backup.sh

. /home/oratest/MQMGEMTEST.env
today="$(date --date '0 days ago' '+%d_%b_%Y')";
BKUP_DIR=/u03/rman_backup/
BKUP_LOG=$BKUP_DIR/logs/"$ORACLE_SID"_Full_hot_backup_"$today".log
mkdir -p $BKUP_DIR/$today/hot
mkdir -p $BKUP_DIR/$today/cfile
mkdir -p $BKUP_DIR/$today/alogbkp
rman target / log=$BKUP_LOG<<EOF
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired backup of archivelog all;
sql 'alter system archive log current';
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 format '$BKUP_DIR/$today/hot/%d-fullhot-%T-%t-%s-%p.bkp' filesperset 8 DATABASE plus archivelog format '$BKUP_DIR/$today/alogbkp/%d-alogs-fha-%T-%t-%s-%p.archbkp' filesperset 8;
delete noprompt obsolete;
backup current controlfile FORMAT='$BKUP_DIR/$today/cfile/%d-CTRL-%U.ctrl';
delete noprompt archivelog all completed before 'sysdate-4';
release channel d1;
release channel d2;
}
EOF

## Logfile deletion
find /u03/rman_backup/logs/*.log -mtime +2 -print -exec rm {} \;

## Empty Folder deletion
find /u03/rman_backup/ -type d -empty -delete
exit

Crontab:

[oratest@mqmhrd1 rman_backup]$ crontab -l
40 17 * * * /bin/sh /u03/rman_backup/scripts/rman_backup.sh
[oratest@mqmhrd1 rman_backup]$


RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name MQMTEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
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;
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 '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_MQMTEST.f'; # default
RMAN>

Long Running Queries Troubleshoot

 Long Running Queries Troubleshoot

To check the OSPD details:

Long_running

To check the SID details:

SID_Info

Oracle Dataguard Reference

Oracle Dataguard Reference 

 Oracle Dataguard Reference

Oracle Dataguard Troubleshooting Steps

Oracle Dataguard Troubleshooting Steps

Dataguard Troubleshooting Commands

How to find out the locations of CRD files in Oracle

How to find out the locations of CRD files in Oracle

SQL> select distinct regexp_substr(name,'^.*/')from v$datafile;         

SQL> select distinct regexp_substr(member,'^.*/') from v$logfile;          

SQL> select distinct regexp_substr(name,'^.*/') from v$controlfile;                                                 

select name from v$datafile;

select name from v$controlfile;

select member from v$logfile;

How to check the connectivity between primary and standby database

How to check the connectivity between primary and standby database

From DR/STANDBY:

C:\Users\Administrator>sqlplus sys@PRODORA1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 16 09:22:14 2022

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role from v$database;

DATABASE_ROLE

-----------------

PRIMARY

SQL>

From MAIN/PRIMARY:

C:\Users\Administrator>sqlplus sys@PRODDR1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 16 09:22:14 2022

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role from v$database;

DATABASE_ROLE

-----------------------------

PHYSICAL STANDBY

SQL>

How to save sql queries in sqlplus using save command

How to save sql queries in sqlplus using save command

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

--------------

        294025

SQL> save max

Created file max.sql

SQL>

SQL>

SQL> @max

MAX(SEQUENCE#)

--------------

        294025

How to remove junk characters in sqlplus command

How to remove junk characters in sqlplus command

!stty erase ^H

How to modify the file without opening it using vi?

How to modify the file without opening it using vi?

which sed

/bin/sed

TRICK ------> EXAMPLE

cat test.txt

this is a simple trick to modify a file without opening it................

sed -i 's/trick/example/g' test.txt

cat test.txt

this is a simple example to modify a file without opening it................

How to check database startup and shutdown details in Alertlog

How to check database startup and shutdown details in Alertlog

Db startup time:

cat alert*log |awk 'BEGIN{buf=""} /[0-9]:[0-9][0-9]:[0-9]/{buf=$0} /Starting ORACLE/{print buf,$0}'

Db shutdown time:

cat alert*log |awk 'BEGIN{buf=""} /[0-9]:[0-9][0-9]:[0-9]/{buf=$0} /Shutting down instance/{print buf,$0}'

Active Session History Details For a Particular Timeframe

Active Session History Details For a Particular Timeframe

Active session History Details

Troubleshooting java.sql.SQLException: ORA-28000: The account is locked.

Troubleshooting java.sql.SQLException: ORA-28000: The account is locked.

User Locked Issue-ORA-28000

RMAN Backup Based Duplication

RMAN Backup Based Duplication

RMAN Backup Based

How to start expdp without password

How to start expdp without password

expdp_passwordless

RMAN Active Duplicate

RMAN Active Duplicate 

Active_Duplicate

How to check Standby Status

How to check Standby Status

STANDBY_STATUS

How to set EBS Version in Profile

How to set EBS Version in Profile

EBS_PROFILE

Cloning A Schema

Cloning A Schema

Example:

SQL> @cln_user.sql

Enter user to model new user to: SCOTT -----> Existing User

Enter new user name: MQMTEST -----> New User

Enter new user's password: TEST123! -----> New User Password

create user MQMTEST identified by TEST123! default tablespace USERS temporary tablespace TEMP profile DEFAULT;

grant RESOURCE to MQMTEST;

grant CONNECT to MQMTEST;

grant UNLIMITED TABLESPACE to MQMTEST;

alter user MQMTEST default role RESOURCE;

alter user MQMTEST default role CONNECT;

Cloning_User 

RMAN Complete Backup Status Script

RMAN Complete Backup Status Script

Rman_Monitoring

Startup & Shutdown Script

Startup & Shutdown Script

vi start_db.sh

vi stop_db.sh

and save it!

startup&shutdown