linux下oracle的逻辑备份和物理备份的部署

好记忆不如烂笔头abc

已于 2024-11-05 10:31:12 修改

阅读量1.1k

点赞数 29

CC 4.0 BY-SA版权

文章标签： exp rman

于 2024-11-02 14:00:55 首次发布

本文链接：https://blog.csdn.net/jycjyc/article/details/143450070

[oracle@jyc db]$ mkdir arch
[oracle@jyc db]$ cd arch
[oracle@jyc arch]$ pwd
/home/db/arch
[oracle@jyc arch]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 1 12:48:32 2024

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

SQL> archive log list;
Database log mode No Archive Mode
Automatic archivalDisabled
Archive destination /home/db/oracle/product/12.2.0/dbhome_1/dbs/arch
Oldest online log sequence 884
Current log sequence 886
SQL> alter system set log_archive_dest_1='location=/home/db/arch' scope=spfile;

System altered.

SQL> shutdown immedaite
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archivalEnabled
Archive destination /home/db/arch
Oldest online log sequence 884
Next log sequence to archive 886
Current log sequence 886
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3.5031E+10 bytes
Fixed Size26323896 bytes
Variable Size 5771363400 bytes
Database Buffers 2.9125E+10 bytes
Redo Buffers 107892736 bytes
Database mounted.
Database opened.
SQL> !df -hT /home
Filesystem Type Size Used Avail Use% Mounted on
/dev/mapper/ol-home xfs 2.9T 1.4T 1.6T 46% /home

SQL> !ls -l /home/db/arch
total 0

SQL> alter system switch logfile;

System altered.

SQL> !ls -l /home/db/arch
total 80916
-rw-r----- 1 oracle oinstall 82854912 Nov 1 12:56 1_886_1179589860.dbf

SQL>

[root@jyc ~]# crontab -l
0 5 * * * sh /home/oracle/scripts/autoexp_del.sh>/dev/null 2>&1
1 0 * * * sh /home/oracle/scripts/level0.sh>/dev/null 2>&1
[root@jyc ~]# cat /home/oracle/scripts/autoexp_del.sh
#!/bin/bash
ny=`date +"%Y.%m"`
rq=`date +"%Y.%m.%d_%H.%M.%S"`

mkdir -p /home/oracle/scripts/logs/$ny
su - oracle -c /home/oracle/scripts/autoexp.sh >> /home/oracle/scripts/logs/$ny/$rq.autobak.log 2>&1
[root@jyc ~]#

[oracle@jyc ~]$ more /home/oracle/scripts/autoexp.sh
#!/bin/bash
export ORACLE_BASE=/home/db/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/dbhome_1
export ORACLE_SID=jycaps
export LANG=en_US.UTF-8
export NLS_LANG=american_america.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin:/home/oracle/run
export ORACLE_TERM=xterm
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib

today=`date +"%Y%m%d"`
expdp \'/ as sysdba\' DIRECTORY=DMP FULL=Y PARALLEL=4 CLUSTER=N DUMPFILE=jycaps-`date "+%Y%m%d_%H%M%S"`_%U.dmp logfile=jycaps-`date "+%Y%m%d_%H%M%S"`.log

#create status file
sleep 1
echo "atlas exp ok">/home/dmp/exp_ok_"$today"
sleep 1
#auto delete old backupfiles
echo "Delete 1 days before data files..."
cd /home/dmp
date
echo "below file to delete"
#find ./ -mtime +1 -name 'jycaps*' -type f -exec ls -l {} \;
#find ./ -mtime +1 -name 'exp_ok*' -type f -exec ls -l {} \;
find ./ -mtime +1 -name 'jycaps*' -type f -exec rm -rf {} \;
find ./ -mtime +1 -name 'exp_ok*' -type f -exec rm -rf {} \;
echo "delete before 1days success"
[oracle@jyc ~]$

[root@jyc ~]# cat /home/oracle/scripts/level0.sh
#!/bin/bash

ny=`date +"%Y.%m"`
rq=`date +"%Y.%m.%d_%H.%M.%S"`

RMAN_BKUPDIR="/home/rman"
RMAN_SCRIPT="/home/oracle/scripts/rman.level0.sh"
RMAN_LOGDIR="/home/oracle/scripts/logs"

mkdir -p $RMAN_LOGDIR/$ny

if [ -d $RMAN_BKUPDIR ]
then
echo "The RMAN Backup Destination Dir exists!"
cd $RMAN_BKUPDIR
rm -rf $RMAN_BKUPDIR/*
su - oracle -c "sh $RMAN_SCRIPT" >> $RMAN_LOGDIR/$ny/$rq.level0.log 2>&1
else
echo "The RMAN Backup Destination Dir doesn't exists, Please prepare and create is first!"
fi
[root@jyc ~]# cat /home/oracle/scripts/rman.level0.sh
rman target / << EOF_RMAN
run{
configure controlfile autobackup on;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/rman/CTLFILE_%d_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate';
backup full database format '/home/rman/FULL_%d_%I_%U_%T' include current controlfile;
sql 'alter system archive log current';
backup archivelog all format '/home/rman/ARCH_%d_%I_%U_%T';
backup spfile format '/home/rman/SPFILE_%d_%I_%U_%T';
}
EOF_RMAN
[root@jyc ~]# su - oracle
[oracle@jyc ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Nov 1 13:22:02 2024

connected to target database: RSBAPS (DBID=2374181147)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name RSBAPS 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 '/home/db/oracle/product/12.2.0/dbhome_1/dbs/snapcf_jycaps.f'; # default

RMAN> exit

Recovery Manager complete.

加个简单的逻辑备份：

[oracle@TEST01 trace]$ crontab -l
0 4 * * * /orabak/bak.sh
[oracle@TEST01 trace]$ more /orabak/bak.sh
#!/bin/sh
source /home/oracle/.bash_profile
cd /orabak/dmp
t=`date "+%Y%m%d_%H%M%S"`
find /orabak/dmp -ctime +2 -name "*.*" -exec rm -f {} \;
expdp \'/ as sysdba\' DIRECTORY=bak schemas=JYCSCHE,JYCSCHE_MONTH,KETTLE,MDM,SPAS,TJP PARALLEL=1 CLUSTER=N DUMPFILE=JYC-$t.dmp logfile=JYC-$t.log
/bin/gzip JYC-$t.dmp
echo `date` >> JYC-$t.log
[oracle@TEST01 trace]$ ll /orabak/dmp
total 165722932
-rw-r----- 1 oracle oinstall 42317453620 Nov 2 04:57 JYC-20241102_040001.dmp.gz
-rw-r--r-- 1 oracle oinstall 152883 Nov 2 06:43 JYC-20241102_040001.log
-rw-r----- 1 oracle oinstall 42392706712 Nov 3 04:58 JYC-20241103_040001.dmp.gz
-rw-r--r-- 1 oracle oinstall 152883 Nov 3 06:45 JYC-20241103_040001.log
-rw-r----- 1 oracle oinstall 42455389183 Nov 4 04:55 JYC-20241104_040001.dmp.gz
-rw-r--r-- 1 oracle oinstall 152883 Nov 4 06:42 JYC-20241104_040001.log
-rw-r----- 1 oracle oinstall 42534086057 Nov 5 04:58 JYC-20241105_040001.dmp.gz
-rw-r--r-- 1 oracle oinstall 152883 Nov 5 06:45 JYC-20241105_040001.log