Export import Oracle Table statistics

 
Export import Oracle Table statistics 
 
--SOURCE DB

SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                          EMPLOYEE                       23.04.19
 
--SOURCE DB

SQL> BEGIN
     DBMS_STATS.CREATE_STAT_TABLE (ownname => 'SCOTT',stattab => 'STATTAB_EMPLOYEE');
     END;
     /  2    3    4
 
PL/SQL procedure successfully completed. 

--SOURCE DB

SQL> BEGIN  dbms_stats.export_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',stattab=>'STATTAB_EMPLOYEE',cascade=>true);
END;
/  2    3    4
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> --SOURCE DB

SQL> select owner,table_name from dba_tables where table_name='STATTAB_EMPLOYEE';
 
OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                         STATTAB_EMPLOYEE

 --SOURCE DB
SQL> select count(1) from SCOTT.STATTAB_EMPLOYEE;
 
  COUNT(1)
----------
        99
 
--SOURCE DB : Export statistics of table EMPLOYEE
[oracle@rac02 ~]$ expdp dumpfile=STATTAB_EMPLOYEE_28SEPT2021.dmp logfile=STATTAB_EMPLOYEE_28SEPT2021.log tables=SCOTT.STATTAB_EMPLOYEE directory=SRC_EMPLOYEE_EXPORT_DIR
 
Export: Release 10.2.0.3.0 - Production on Monday, 27 September, 2021 23:52:28
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Username: / as sysdba
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=STATTAB_EMPLOYEE_28SEPT2021.dmp logfile=STATTAB_EMPLOYEE_28SEPT2021.log tables=SCOTT.STATTAB_EMPLOYEE directory=SRC_SCOTT_EXPORT_DIR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "SCOTT"."STATTAB_EMPLOYEE"               30.07 KB      99 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u03/EXPORT_BACKUP/STATTAB_EMPLOYEE_28SEPT2021.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 23:52:35
  
SQL> set pages 200 lines 192
SQL> col DIRECTORY_NAME format a30
SQL> col DIRECTORY_PATH format a40
select directory_name,directory_path from dba_directories;
 
DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ----------------------------------------
TRINITYDUMP                    /backup/expdp
XML_DIR                        /home/oracle/xml
SRC_SCOTT_EXPORT_DIR           /u03/EXPORT_BACKUP
WORK_DIR                       /opt/oracle/product/10.2.0/db_1/work
ADMIN_DIR                      /opt/oracle/product/10.2.0/db_1/md/admin
DATA_PUMP_DIR                  /opt/oracle/product/10.2.0/db_1/rdbms/log
 
 
7 rows selected.
 
SQL> !ls -lrt /u03/EXPORT_BACKUP
total 112
-rw-r--r-- 1 oracle oinstall   1132 Sep 27 23:52 STATTAB_EMPLOYEE_28SEPT2021.log
-rw-r----- 1 oracle oinstall 106496 Sep 27 23:52 STATTAB_EMPLOYEE_28SEPT2021.dmp
 
--SCP  STATTAB_EMPLOYEE_28SEPT2021.dmp to target DB Server
--TARGET DB 
--import STATTABB_EMPLOYEE_28SEPT2021.dmp in the target DB

--impdp dumpfile=STATTAB_EMPLOYEE_28SEPT2021.dmp logfile=impdp_STATTAB_EMPLOYEE_28SEPT2021.log tables=SCOTT.STATTAB_EMPLOYEE directory=TGT_EMPLOYEE_IMPORT_DIR

--SOURCE DB 

SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                          EMPLOYEE                       23.04.19
 
--TARGET DB 

SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                          EMPLOYEE                       28.09.21
--TARGET DB import the  
SQL> BEGIN  dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',stattab=>'STATTAB_EMPLOYEE',cascade=>true);
END;
/  
 
PL/SQL procedure successfully completed.
--TARGET DB  
SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                           EMPLOYEE                      23.04.19   -- statistics imported successfully
 

Expdp selected table using INCLUDE parameter in Oracle

Expdp selected table when the table list is big

If table list is big  then There may be  two option  which can  help us to  expdp selected tables
   1. we can use select query in the INCLUDE parameter in expdp with filter condition
   2. Manual Approach we can use par file and pass those files which has to take expdp backup

Let suppose if we want to export only those table which has records, then we would have to specify INCLUDE parameter in the EXPDP and specify NUM_ROW>0

include=table:\"in \(select table_name from user_tables where NUM_ROWS >0 \)\"

Below are the demonstration example

C:\Users>expdp Dilip/PasswD123#@PDB1 DIRECTORY=PDB1_DUMP_DIR DUMPFILE=TESTDUMP12JUNE2023_ROWGT0.dmp include=table:\"in \(select table_name from user_tables where NUM_ROWS >0 \)\"

Export: Release 12.2.0.1.0 - Production on Mon Jun 12 17:19:28 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DILIP"."SYS_EXPORT_SCHEMA_01":  Dilip/********@PDB1 DIRECTORY=PDB1_DUMP_DIR DUMPFILE=TESTDUMP12JUNE2023_ROWGT0.dmp include=table:"in \(select table_name from user_tables where NUM_ROWS >0 \)"

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "DILIP"."TESTING_TBL_01" 1.781 GB 34023780 rows
. . exported "DILIP"."TESTING_TBL_02" 21.78 KB       1 rows
. . exported "DILIP"."TESTING_TBL_03" 20.64 KB     165 rows
. . exported "DILIP"."TESTING_TBL_04" 19.60 KB     291 rows
. . exported "DILIP"."TESTING_TBL_05" 17.38 KB     164 rows
Master table "DILIP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DILIP.SYS_EXPORT_SCHEMA_01 is:
  D:\BACKUP1\PDB_DUMP_DIR\TESTDUMP12JUNE2023_ROWGT0.DMP
Job "DILIP"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jun 12 17:20:36 2023 elapsed 0 00:01:06

2 Second option is Manual Approach

I create parfile and save it as expdp_table_12062023.par

with below contents I will use this parfile in expdp parameter

We can get table list which has more than 0 rows using below query

select table_name||',' from user_tables where NUM_ROWS>0;

DIRECTORY=PDB1_DUMP_DIR

DUMPFILE=TESTDUMP12JUNE2023_ROWGT01.DMP

TABLES=

TESTING_TBL_01,

TESTING_TBL_02,

TESTING_TBL_03,

TESTING_TBL_04,

TESTING_TBL_05

LOGFILE=expdp_TESTDUMP12JUNE2023_ROWGT01.log

C:\Users>expdp Dilip/PasswD123#@PDB1 parfile=expdp_table_12062023.par

Export: Release 12.2.0.1.0 - Production on Mon Jun 12 18:13:09 2023

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "DILIP"."SYS_EXPORT_TABLE_01":  Dilip/********@PDB1 parfile=expdp_table_12062023.par

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/TRIGGER

. . exported "DILIP"."TESTING_TBL_01" 1.781 GB 34023780 rows

. . exported "DILIP"."TESTING_TBL_02" 403.8 KB    3493 rows

. . exported "DILIP"."TESTING_TBL_03" 347.5 KB    2695 rows

. . exported "DILIP"."TESTING_TBL_04" 170.7 KB    1677 rows

. . exported "DILIP"."TESTING_TBL_05" 174.4 KB    3103 rows

Master table "DILIP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for DILIP.SYS_EXPORT_TABLE_01 is:

  D:\BACKUP1\PDB_DUMP_DIR\TESTDUMP12JUNE2023_ROWGT01.DMP

Job "DILIP"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jun 12 18:13:44 2023 elapsed 0 00:00:34

Command

#expdp \"sys/Oracle_4U@PDB1 as sysdba \" directory=PDB1_DUMP_DIR schemas=LOCAL_SCH1,LOCAL_SCH2 dumpfile=LOCAL_SCH_21JULY2023.dmp logfile=Expdp_LOCAL_SCH_21JULY2023.log

expdp directory=DATA_PUMP_DIR dumpfile=gokhan.dmp include=table:\"in \(select table_name from user_tables where table_name like \'INVOICE%\' or table_name like \'PRODUCT%\' \)\”

expdp APP_SCHEMA/Oracle_4U@PDB1 directory=EXPORT_BACKUP_DIR dumpfile=APP_SCHEMA_DULICATED_TABLES_12MAY2023.dmp include=table:\"in \(select table_name from user_tables where DUPLICATED = \'Y\' \)\"

expdp APP_SCHEMA/Oracle_4U@PDB directory=EXPORT_BACKUP_DIR dumpfile=APP_SCHEMA_sharded_TABLES_12MAY2023.dmp include=table:\"in \(select table_name from user_tables where sharded = \'Y\' \)\"

--Exclude big table

expdp directory=DATA_EXPORT_DIR schemas=SCOTT dumpfile=TESTSCHEMA_07102024.dmp logfile=TESTSCHEMA_07102024.log exclude=TABLE:"IN('BIG_TABLE_01')"

  

Delete Duplicate Data from Table

create table Student_test(ROLLNO VARCHAR2(10) NOT NULL
                         ,STNAME VARCHAR2(30) NOT NULL
,PROGRAM VARCHAR2(10) NOT NULL
 ,FEES NUMBER NOT NULL);

alter table Student_test modify ROLLNO VARCHAR2(20) NOT NULL;
 
insert into Student_test values('10020220314','DILIP BAKSHI','MCA',42000);
insert into Student_test values('10020220314','DILIP BAKSHI','MCA',42000);
insert into Student_test values('10120220314','SANJAY SINHA','BCA',22000);
insert into Student_test values('10120220314','SANJAY SINHA','BCA',22000);

select ROLLNO,STNAME,PROGRAM,count(1) from Student_test
GROUP BY ROLLNO,STNAME,PROGRAM 
HAVING COUNT(1) >1
ORDER BY ROLLNO,STNAME,PROGRAM;

SQL> alter table student_test add constraint pk_student_test primary key(ROLLNO,STNAME,PROGRAM);
alter table student_test add constraint pk_student_test primary key(ROLLNO,STNAME,PROGRAM)
                                        *
ERROR at line 1:
ORA-02437: cannot validate (SYS.PK_STUDENT_TEST) - primary key violated
SQL>
SQL>  select * from student_test;
ROLLNO               STNAME                         PROGRAM          FEES
-------------------- ------------------------------ ---------- ----------
10020220314          DILIP BAKSHI                   MCA             42000
10020220314          DILIP BAKSHI                   MCA             42000
10120220314          SANJAY SINHA                   BCA             22000
10120220314          SANJAY SINHA                   BCA             22000

SQL> select ROLLNO,STNAME,PROGRAM,count(1) from Student_test
     GROUP BY ROLLNO,STNAME,PROGRAM
     HAVING COUNT(1) >1
     ORDER BY ROLLNO,STNAME,PROGRAM;  

ROLLNO               STNAME                         PROGRAM      COUNT(1)
-------------------- ------------------------------ ---------- ----------
10020220314          DILIP BAKSHI                   MCA                 2
10120220314          SANJAY SINHA                   BCA                 2

SQL> select * from student_test
     where rowid NOT IN (select max(rowid)  from student_test group by ROLLNO,STNAME,PROGRAM); 

ROLLNO               STNAME                         PROGRAM          FEES
-------------------- ------------------------------ ---------- ----------
10120220314          SANJAY SINHA                   BCA             22000
10020220314          DILIP BAKSHI                   MCA             42000

SQL> DELETE FROM Student_test WHERE rowid IN (
     SELECT MAX(rowid) FROM Student_test GROUP BY ROLLNO,STNAME,PROGRAM); 
2 rows deleted.
SQL>
SQL> alter table student_test add constraint pk_student_test primary key(ROLLNO,STNAME,PROGRAM);
Table altered.

SQL>

SQL SERVER

==================
delete T from 
(select Row_Number() Over(Partition By BINARY_CHECKSUM(*) order by %%physloc%% ) As RowNumber, * From Student_test) T
Where T.RowNumber > 1

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

crontab entry

How to schedule cron job

            Any Value                  *

           value list separator       ,

           range of values            -

           step values                /      

uses :       

                           1             2              3          4            5          <command or script with path>

                        <MIN>  <HRS>  <DOM> <MON> <DOW> <command or script with path>

Example

           <MIN>   Minute                         0 to 59 

           <HRS>   Hours                           0 to 24

           <DOM> Day of Month              1 to 31 as per month days

           <MON> Month                          1 to 12

           <DOW> Day of Week                1 to 7

1.       Execute a cron job every 5 Minutes

*/5 * * * * /home/ramesh/backup.sh

Note: In the same way, use */10 for every 10 minutes, */15 for every 15 minutes, */30 for every 30 minutes, etc.

2.        Execute a cron job every 5 Hours

0 */5 * * * /home/ramesh/backup.sh

Note: In the same way, use */2 for every 2 hours, */3 for every 3 hours, */4 for every 4 hours, etc.

3.       Execute a job every 5 Seconds

Cron job cannot be used to schedule a job in seconds interval. i.e You cannot schedule a cron job to run every 5 seconds. The alternative is to write a shell script that uses ‘sleep 5’ command in it.

$ cat every-5-seconds.sh
#!/bin/bash
while true
  do
    /home/ramesh/backup.sh
    sleep 5
 done

            $ nohup ./every-5-seconds.sh &

4.       Execute a job every 5th weekday

This example is not about scheduling “every 5 days”. But this is for scheduling “every 5th weekday”.

The 5th field is DOW (day of the week). If you specify * in this field, it runs every day. To run every Friday, specify either 5 of Fri in this field.

The following example runs the backup.sh every Friday at midnight.

        0 0 * * 5 /home/ramesh/backup.sh

        (or)

        0 0 * * Fri /home/ramesh/backup.sh

5.  Execute a job every 5 months

There is no direct way of saying ‘every 5 months’, instead you have to specify what specific months you want to run the job. Probably you may want to run the job on 5th month (May), and 10th month (Oct).

The fourth field is for Months. If you specify * in this field, it runs every month. To run for the specific month, you have to specify the number that corresponds to the month. For example, to run the job on May and Oct, you should specify 5,10 (or) you can simply use the 3 letter acronym of the month and specify May,Oct.

The third field is for DOM (Day of the Month). If you specify * in this field, it runs every day of the month. If you specify 1 in this month, it runs 1st of the month.

The following example runs the backup.sh twice a year. i.e 1st May at midnight, and 1st Oct at midnight.

0 0 1 5,10 * /home/ramesh/backup.sh

(or)

0 0 1 May,Oct * /home/ramesh/backup.sh

############################    RMAN Database FULL Backup  #######################

00 00 * * * /home/oracle/RMANBACKUP_SCRIPTS/RMAN_FULL.sh > /home/oracle/RMANBACKUP_SCRIPTS/ORACLE_RMAN_FULL_backup_`date +\%d-\%b-\%Y_\%H_\%M_\%S`.log

############################     RMAN Database INCREMENTAL  Backup  #############

#Backup every 2 hours

00 02,04,06,08,10,12,14,16,18,20,22 * * * /home/oracle/RMANBACKUP_SCRIPTS/RMAN_INCREMETAL.sh > /home/oracle/RMANBACKUP_SCRIPTS/ORACLE_RMAN_INC_BACKUP_`date +\%d-\%b-\%Y_\%H_\%M_\%S`.log

############################     RMAN ARCHIVE Backup  #############

#Archive backup in every 30 Minutes

30 * * * * /home/oracle/RMANBACKUP_SCRIPTS/RMAN_ARCHIVE.sh > /home/oracle/RMANBACKUP_SCRIPTS/ORACLE_RMAN_ARCHIVE_backup_`date +\%d-\%b-\%Y_\%H_\%M_\%S`.log

RMAN Backup Scripting

vi /home/oracle/RMANBACKUP_SCRIPTS/RMAN_FULL.sh

#########                      RMAN FULL backup Scripts    ###################################

#!/bin/ksh
ORACLE_UNQNAME=oracle; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=oracle; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
mkdir /home/oracle/TEST_2016/ORACLE_"$(date +"%d-%m-%Y")"
test=/home/oracle/TEST_2016/ORACLE_"$(date +"%d-%m-%Y")"
rman target=/ << EOF
RUN {
  ALLOCATE CHANNEL ch1 TYPE  DISK FORMAT '$test/%d_DB_FULL_%u_%s_%p';
  sql 'ALTER SYSTEM SWITCH LOGFILE ';
  sql 'ALTER SYSTEM SWITCH LOGFILE ';
  sql 'ALTER SYSTEM SWITCH LOGFILE ';
  sql 'ALTER SYSTEM CHECKPOINT';
  BACKUP DATABASE PLUS ARCHIVELOG;
  BACKUP SPFILE;
  BACKUP CURRENT CONTROLFILE;
  delete force noprompt archivelog all backed up 2 times to disk;
  DELETE FORCE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK;
  DELETE FORCE NOPROMPT OBSOLETE;
  RELEASE CHANNEL ch1;
}
EXIT;$HOME/`date +\%Y\%m\%d\%H\%M\%S`-cron.log
EOF

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

chmod u+x /home/oracle/RMANBACKUP_SCRIPTS/RMAN_FULL.sh

vi /home/oracle/RMANBACKUP_SCRIPTS/RMAN_INCREMETAL.sh

########RMAN INCREMENTAL backup Scripts########################################

#!/bin/ksh
ORACLE_UNQNAME=oracle; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=oracle; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
test=/home/oracle/TEST_2016/ORACLE_"$(date +"%d-%m-%Y")"

rman target=sys/Oracle_4U@emrep << EOF
RUN {
  ALLOCATE CHANNEL ch1 TYPE  DISK FORMAT '$test/%d_DB_INC_%u_%s_%p';
  sql 'ALTER SYSTEM SWITCH LOGFILE ';
  sql 'ALTER SYSTEM SWITCH LOGFILE ';
  sql 'ALTER SYSTEM SWITCH LOGFILE ';
  sql 'ALTER SYSTEM CHECKPOINT';
  BACKUP INCREMENTAL LEVEL 1 DATABASE;
  RELEASE CHANNEL ch1;
}
EXIT;

EOF

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

chmod u+x /home/oracle/RMANBACKUP_SCRIPTS/RMAN_INCREMETAL.sh

vi /home/oracle/RMANBACKUP_SCRIPTS/RMAN_ARCHIVE.sh

##################### RMAN ARCHIVE backup Scripts##########################
ORACLE_UNQNAME=oracle; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=oracle; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
test=/home/oracle/TEST_2016/ORACLE_"$(date +"%d-%m-%Y")"
rman target=/ << EOF
RUN {
  ALLOCATE CHANNEL ch1 TYPE
  DISK FORMAT '$test/%d_ARCHIVE_%u_%s_%p';
  BACKUP ARCHIVELOG ALL delete input;
  RELEASE CHANNEL ch1;
}
EXIT;
EOF

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

chmod u+x /home/oracle/RMANBACKUP_SCRIPTS/RMAN_ARCHIVE.sh

1.  Every day RMAN Full Backup

2.  Every 2 hours RMAN Incremental Backup

3.  Every Hours RMAN Archive Backup

4.  Every Day Gather stats of schema

5.  Every week crontab backup

6.  Every Month reducing alert logfile size

7.  Every Day copy RMAN backup of shared storage

8.  Every week purging database recycle bin.

9.  Record every minute active inactive session and Events in log file daily basis.

10. Record Database health in text file daily basis and Zip health report file monthly basis.

Copy all file and folder created today under directory /home/oracle/TEST_2016/

vi /home/oracle/RMANBACKUP_SCRIPTS/CP_RMAN_FILES.sh

find /home/oracle/TEST_2016/ORACLE_`date +"%d-%m-%Y"` -ctime -1 -type d -exec cp -avr "{}" /home/oracle/TEST_2016/SHARED_STORAGE/ORACLE_RMAN_BACKUP/ \;

chmod u+x /home/oracle/RMANBACKUP_SCRIPTS/CP_RMAN_FILES.sh

#################### crontab entry ##############################

00 23 * * * /home/oracle/RMANBACKUP_SCRIPTS/CP_RMAN_FILES.sh > /home/oracle/RMANBACKUP_SCRIPTS/BACKUP_COPY_LOG/RMAN_BACKUP_COPY_LOG_`date +\%d-\%b-\%Y_\%H_\%M_\%S`.log

echo `date +"%d-%m-%Y" --date="-7 day"`

vi /home/oracle/RMANBACKUP_SCRIPTS/RM_RMAN_BACKUP_FOLDER_SHARED_LOCATION.sh

find /home/oracle/TEST_2016/SHARED_STORAGE/ORACLE_RMAN_BACKUP/ORACLE_`date +"%d-%m-%Y" --date=" 0 day"` -ctime 0 -type d -exec rm -rf "{}"  \;

chmod u+x /home/oracle/RMANBACKUP_SCRIPTS/RM_RMAN_BACKUP_FOLDER_SHARED_LOCATION.sh

=============crontab entry==========================================

30 00 * * * /home/oracle/RMANBACKUP_SCRIPTS/RM_RMAN_BACKUP_FOLDER_SHARED_LOCATION.sh > /home/oracle/RMANBACKUP_SCRIPTS/BACKUP_COPY_LOG/RMAN_BACKUP_REMOVE_LOG_`date +\%d-\%b-\%Y_\%H_\%M_\%S`.log

RMAN-08138: WARNING: archived log not deleted - must create more backups archived log file name

RMAN-08138: WARNING: archived log not deleted - must create more backups archived log file name      

 

RMAN-08138 : Reason of  RMAN-08138 is RMAN DELETION policy

while we deleting archivelogs files using RMAN, RMAN checks whether archivelog file has been taken backup N times to Disk or Tape before deleting archivelog files.

 

Default :  ARCHIVELOG DELETION POLICY TO NONE

 

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TESTPRY are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 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'; # default

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;

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   'F:\ORCL_BACKUP\TEST_BACKUP\02_FEB_2016\TEST_%d_%U';

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 ARCHIVELOG DELETION POLICY TO NONE;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\APP\DILIPBAKSHI\PRODUCT\11.2.0\DBHOME_2\DATABASE\SNCFTESTPRY.ORA'; # default

 

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TESTPRY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 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'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
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   'F:\ORCL_BACKUP\TEST_BACKUP\02_FEB_2016\TEST_%d_%U';
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 ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\APP\DILIPBAKSHI\PRODUCT\11.2.0\DBHOME_2\DATABASE\SNCFTESTPRY.ORA'; # default

RMAN> backup archivelog all FORMAT 'F:\temp\Temp3\Backup\RMAN_BACKUP\%n_ARCHIVE_BKP_%p_%s_%D_%M_%Y_%U' delete input;

 Starting backup at 17-NOV-16

using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1686 RECID=2307 STAMP=928106656
channel ORA_DISK_1: starting piece 1 at 17-NOV-16
channel ORA_DISK_1: finished piece 1 at 17-NOV-16
piece handle=F:\TEMP\TEMP3\BACKUP\RMAN_BACKUP\TESTPRYX_ARCHIVE_BKP_1_1693_17_11_2016_KTRL4Q5H_1_1 tag=TAG20161117T104712 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

channel ORA_DISK_1: deleting archived log(s)

RMAN-08138: WARNING: archived log not deleted - must create more backups archived log file name      =F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_16\O1_MF_1_1686_D2S7B6YJ_.ARC

thread=1 sequence=1686

Finished backup at 17-NOV-16

 

Starting Control File and SPFILE Autobackup at 17-NOV-16
piece handle=F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\AUTOBACKUP\2016_11_17\O1_MF_S_928147640_D2THC29D_.BKP comment=NONE

Finished Control File and SPFILE Autobackup at 17-NOV-16

Note : According to above policy RMAN will not delete those Archivelog files which have not been  backed up 2 Times to Disk

RMAN> list archivelog all backed up 1 times to DISK;

List of Archived Log Copies for database with db_unique_name TESTPRY
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
2307    1    1686    A 16-NOV-16
Name:F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_16 \O1_MF_1_1686_D2S7B6YJ_.ARC

 

RMAN> list archivelog all backed up 0 times to DISK;

List of Archived Log Copies for database with db_unique_name TESTPRY
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
2307    1    1686    A 16-NOV-16
Name:F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_16\O1_MF_1_1686_D2S7B6YJ_.ARC

2308    1    1687    A 16-NOV-16
Name:F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_17\O1_MF_1_1687_D2THGPLY_.ARC

 

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

old RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TESTPRY are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 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'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
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   'F:\ORCL_BACKUP\TEST_BACKUP\02_FEB_2016\TEST_%d_%U';
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 ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\APP\DILIPBAKSHI\PRODUCT\11.2.0\DBHOME_2\DATABASE\SNCFTESTPRY.ORA'; # default

 

RMAN>

RMAN> backup archivelog all FORMAT 'F:\temp\Temp3\Backup\RMAN_BACKUP\%n_ARCHIVE_BKP_%p_%s_%D_%M_%Y_%U' delete input;

Starting backup at 17-NOV-16

current log archived
using channel ORA_DISK_1
skipping archived log file F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_16\O1_MF_1_1686_D2S7B6YJ_.ARC; already backed up 1 time(s)
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1687 RECID=2308 STAMP=928147759
input archived log thread=1 sequence=1688 RECID=2309 STAMP=928149066
channel ORA_DISK_1: starting piece 1 at 17-NOV-16
channel ORA_DISK_1: finished piece 1 at 17-NOV-16
piece handle=F:\TEMP\TEMP3\BACKUP\RMAN_BACKUP\TESTPRYX_ARCHIVE_BKP_1_1695_17_11_2016_KVRL4RIB_1_1 tag=TAG20161117T111106 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_17\O1_MF_1_1687_D2THGPLY_.ARC RECID=2308 STAMP=928147759
archived log file name=F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_17\O1_MF_1_1688_D2TJQK8D_.ARC RECID=2309 STAMP=928149066
channel ORA_DISK_1: deleting archived log(s)
archived log file name=F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_16\O1_MF_1_1686_D2S7B6YJ_.ARC RECID=2307 STAMP=928106656
Finished backup at 17-NOV-16

Starting Control File and SPFILE Autobackup at 17-NOV-16
piece handle=F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\AUTOBACKUP\2016_11_17\O1_MF_S_928149073_D2TJQW31_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 17-NOV-16

 

Note : This time No RMAN-08138: warning  raised.