Oracle-Delete Old Archives Using RMAN

Deletion of archive logs in oracle 11g database by comparing the sysdate

Prepare the shell script.

cat rman_arch_del.sh

#!/bin/bash
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2.0
export ORACLE_SID=FRANCE
export PATH=$ORACLE_HOME/bin:$PATH

delBackup () {
rman log=/home/oracle/arch_del.log << EOF
connect target /
DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE ‘sysdate-1’;
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
exit
EOF
}
# Main

delBackup

Now configure in crontab:

00 30 * * * /u01/app/oracle/rman_arch_del.sh > /tmp/rmanarch.log

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

 

 

Oracle Schema DDL

Vi Schema.sql (create sqlfile and execute)

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, ‘SQLTERMINATOR’, true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, ‘PRETTY’, true);
end;
/

variable v_username VARCHAR2(30);

exec:v_username := upper(‘&1’);

select dbms_metadata.get_ddl(‘USER’, u.username) AS ddl
from dba_users u
where u.username = :v_username
union all
select dbms_metadata.get_granted_ddl(‘TABLESPACE_QUOTA’, tq.username) AS ddl
from dba_ts_quotas tq
where tq.username = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘ROLE_GRANT’, rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘SYSTEM_GRANT’, sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘OBJECT_GRANT’, tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl(‘DEFAULT_ROLE’, rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rp.default_role = ‘YES’
and rownum = 1
union all
select to_clob(‘/* Start profile creation script in case they are missing’) AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> ‘DEFAULT’
and rownum = 1
union all
select dbms_metadata.get_ddl(‘PROFILE’, u.profile) AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> ‘DEFAULT’
union all
select to_clob(‘End profile creation script */’) AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> ‘DEFAULT’
and rownum = 1
/

set linesize 80 pagesize 14 feedback on trimspool on verify on