Transparent application failover

Is a client-side feature that allows for clients to reconnect to surviving databases in the event of a failure of a database instance. Notifications are used by the server to trigger TAF callbacks on the client-side. Your options could be: Basic, in which the application connects to a backup node only after the primary connection fails. This approach has low overhead, but the end user experiences a delay while the new connection is created, or Pre-Connect, where the application simultaneously connects to both a primary and a backup node. This offers faster failover, because a pre-spawned connection is ready to use. But the extra connection adds everyday overhead by duplicating connections.

Log switching history

Log switching history

select to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from v$log_history group by to_char(first_time,'YYYY-MON-DD');

Track redo generation by day

select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024)) “REDO PER DAY (MB)”
from v$archived_log
group by trunc(completion_time)
order by 1

Recover from loss of single current online redo log file

Recover from loss of single current online redo log file

RMAN> list backupset summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
7 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
8 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
9 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
10 B A A DISK 11-AUG-10 1 1 NO TAG20100811T141822
11 B A A DISK 11-AUG-10 1 1 NO TAG20100811T141822
RMAN> exit

Recovery Manager complete.
[oracle@crisreplication ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 11 14:19:01 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select member from v$Logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/trialrep/redo02.log
/u01/app/oracle/oradata/trialrep/redo01.log
/u01/app/oracle/oradata/trialrep/redo03.log
SQL> SQL>
SQL>
SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> select group#, member from v$logfile order by 1;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/trialrep/redo01.log
2
/u01/app/oracle/oradata/trialrep/redo02.log
3
/u01/app/oracle/oradata/trialrep/redo03.log

SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@crisreplication ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 11 14:21:18 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 1267164 bytes
Variable Size 318769700 bytes
Database Buffers 872415232 bytes
Redo Buffers 15507456 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/trialrep/redo03.log'

SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 INACTIVE
3 16 50 1 CURRENT
2 14 50 1 INACTIVE
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@crisreplication ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 11 14:24:14 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TRIALREP (DBID=1985177645, not open)

RMAN> run {
2> set until sequence 16;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }

executing command: SET until clause
using target database control file instead of recovery catalog
Starting restore at 11-AUG-10

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/trialrep/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/trialrep/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/trialrep/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/rman0bll30b1_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rman/rman0bll30b1_1_1 tag=EB64LEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/trialrep/system01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/trialrep/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/trialrep/bv1to1.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/rman0all30b1_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rman/rman0all30b1_1_1 tag=EB64LEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46

Finished restore at 11-AUG-10

Starting recover at 11-AUG-10
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 15 is already on disk as file /arch/1_15_726755127.dbf
archive log filename=/arch/1_15_726755127.dbf thread=1 sequence=15
media recovery complete, elapsed time: 00:00:04

Finished recover at 11-AUG-10

database opened

Backup script for Linux using tar and find

script.sh
#!/bin/bash
#
# creates backups of essential files
#
DATA="/home /root /usr/local/httpd"
CONFIG="/etc /var/lib /var/named"
LIST="/tmp/backlist_$$.txt"
#
#mount /mnt/backup
set $(date)
#
if test "$1" = "Tue" ; then
# weekly a full backup of all data and config. settings:
#
tar cfz "/mnt/backup/data/data_full_$6-$2-$3.tgz" $DATA
rm -f /mnt/backup/data/data_diff*
#
tar cfz "/mnt/backup/config/config_full_$6-$2-$3.tgz" $CONFIG
rm -f /mnt/backup/config/config_diff*
else
# incremental backup:
#
find $DATA -depth -type f \( -ctime -1 -o -mtime -1 \) -print > $LIST
tar cfzT "/mnt/backup/data/data_diff_$6-$2-$3.tgz" "$LIST"
rm -f "$LIST"
#
find $CONFIG -depth -type f \( -ctime -1 -o -mtime -1 \) -print > $LIST
tar cfzT "/mnt/backup/config/config_diff_$6-$2-$3.tgz" "$LIST"
rm -f "$LIST"
fi