工作项目
- atuo delete archive logfile ,
- auto start oracle
- atuo expdp and sent to SFTP
- 主备切换
[oracle@VM_Main-os ~]$ mkdir /data/expbak
SQL> select * from dba_directories;
SQL> create directory expbak as '/data/expbak/'; --在SQLPlus 环境下指定目录
定时任务: 删除/备份/上传FTP
[oracle@VM_Main-os ~]$ crontab -l
5 0 * * * sh /home/oracle/run/rman_delete_archivelog.sh
10 0 * * 6 sh /home/oracle/run/fullbak.sql
删除脚本
[oracle@VM_Main-os ~]$ cat /home/oracle/run/rman_delete_archivelog.sh
export ORACLE_BASE=/home/oracle
export ORACLE_SID=db
export ORACLE_HOME=/home/oracle/product/11.2.3/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
rman target / log=/home/oracle/run/del_archivelog.log <<EOF
crosscheck archivelog all;
DELETE NOPROMPT ARCHIVELOG all completed before 'sysdate-5';
exit
EOF
expdb 备份脚本 上传 ftp 脚本
[oracle@VM_Main-os ~]$ cat /home/oracle/run/fullbak.sql
export ORACLE_BASE=/home/oracle
export ORACLE_SID=db
export ORACLE_HOME=/home/oracle/product/11.2.3/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
cd /data/expbak
rm -f db_fullbak*
NOW=`date +%F`
expdp system/syspwd directory=expbak dumpfile=db_fullbak_%U_$NOW.dmp logfile=db_fullbak_$NOW.log compression=ALL full =y reuse_dumpfiles=y parallel=4
gzip db_fullbak_*.dmp
echo "ftp start === `date`"
HOST=ftpipxxx
USER=sftpuser
PASSWD=test123
sshpass -p $PASSWD sftp -o StrictHostKeyChecking=no $USER@$HOST << !
lcd /data/expbak
cd /test_198.196.1.2
put db*
bye
!
#OR yum -y install lftp
#lftp -u ${USER},${PASSWD} sftp://${HOST} << !
#lcd /data/expbak/
#cd /agile9_10.66.12.176
#mput all_database_agile9*
#bye
#!
echo "ftp finished === `date`"
rm -fr /data/expbak/*.gz
OS 重启后自动化启动Oracle
[root@VM_Main-os ~]# cat /etc/rc.local
#!/bin/bash
# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES
#
# It is highly advisable to create own systemd services or udev rules
# to run scripts during boot instead of using this file.
#
# In contrast to previous versions due to parallel execution during boot
# this script will NOT be run after all other services.
#
# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure
# that this script will be executed during boot.
touch /var/lock/subsys/local
su - oracle -c 'sh /home/oracle/run/startDG.sh' 1>/home/oracle/run/startDG.log 2>/home/oracle/run/startDG.err
启动 Oracle脚本
[root@VM_Main-os ~]# cat /home/oracle/run/startDG.sh
#!/bin/sh
export ORACLE_HOME=/home/oracle/product/11.2.3/dbhome_1
/home/oracle/product/11.2.3/dbhome_1/bin/lsnrctl start
export ORACLE_SID=db
/home/oracle/product/11.2.3/dbhome_1/bin/sqlplus '/as sysdba' <<EOF
startup;
archive log list;
exit;
EOF
[root@VM_Slave-os ~]# cat /home/oracle/run/startDG.sh
#!/bin/sh
export ORACLE_HOME=/home/oracle/product/11.2.3/dbhome_1
/home/oracle/product/11.2.3/dbhome_1/bin/lsnrctl start
export ORACLE_SID=db
/home/oracle/product/11.2.3/dbhome_1/bin/sqlplus '/as sysdba' <<EOF
#startup;
#archive log list;
startup mount;
alter database open read only;
alter database recover managed standby database using current logfile disconnect;
exit;
EOF
主备切换:
- 查看有几个实例:
[oracle@VM_Main-os ~]$ cat $ORACLE_HOME/network/admin/
- 进入主备 sqlplus 环境
[oracle@VM_Main-os ~]$ sqlplus / as sysdba
set linesize 1000;
set pagesize 1000;
- 查看主备最后的 Current log sequence 号是否一致
SQL> archive log list;
- 查看數據庫当前我角色, 切换目标为互换
a如果switchover_status为TO_PRIMARY 说明标记恢复可以直接转换为primary库,即当前行待转成主库
b 如果switchover_status为SESSION ACTIVE 就应该断开活动会话
C 如果switchover_status为NOT ALLOWED 说明切换标记还没收到,此时不能执行转换
OPEN_MODE : 主库为 Read Write,备库为 Read only
M_SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- ---------------- --------------------
db READ WRITE MAXIMUM PERFORMANCE PRIMARY TO STANDBY
S_SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- ---------------- --------------------
db READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
- 查看是否有未同步的 sequence ,如果备库有值为No,则提示有未应用的Log,此时无法切换
S_SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP 如果有NO 则提示有未应用的Log,此时无法切换
--------- ---
8 YES
9 YES
10 YES
11 IN-MEMORY
- 查看并比对主/备库的DB Link, 需要在主/备库同样
M/S_SQL> select * from dba_db_links;
- 查看备库与主库是否都有 logfile ,主意文件目录,当主库
如果备库没有 logfile 则需要先创建
S_SQL>alter database add logfile thread 1 group 4 size 52428800
S_SQL>ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5 '/data/pcasolap/standby_redo05.log' size 209715200
S_SQL>alter database drop logfile group 5
M_SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string auto
M_SQL> alter system set standby_file_management='manual';
S_SQL> select group#,type, member from v$logfile;
ONLINE /data/oradata/db_FIXO/onlinelog/o1_mf_1_jp2j1hhj_.log ## 从o1_mf_1_jp2j1hhj_.log 来看是使用OMM自动管理的,扩充文件创建时不需要指定目录
M_SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5 size 209715200
M_SQL> alter system set standby_file_management='manual';
- 主库:切换当前Primary DB 为Standby DB
MSQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; -- 等,此处完成已完成切换;
重啟一庫到mount狀態:
MSQL>shutdown immediate;
MSQL>startup mount;
MSQL> select database_role,switchover_status from v$database;
MSQL> alter database open read only;
- S 退出自动recover模式
SSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE Cancel;
- S切换当前Standby DB为Primary DB
SSQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; -- WAIT;
SSQL> alter database open;
SSQL> alter system switch logfile;
MSQL> alter database recover managed standby database using current logfile disconnect from session;
以上完成主备切换
验证:
SQL> create table testc(id number(10),name varchar2(20));
Table created.
SQL> insert into testc values(1,'testc');
1 row created.
SQL> insert into testc values(2,'testc');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
SQL> archive log list;