DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

Oracle Database on ACFS: a perfect marriage?

Posted on November 26, 2015 by Ludovico

Update: I will give this presentation at UKOUG Tech15, Wed 9 December at 14:30.

This presentation has had a very poor score in selections for conferences (no OOW, no DOAG) but people liked it very much at Paris Oracle Meetup. The Database on ACFS is mainstream now, thanks to the new ODA releases. Having some knowledge about why and how you should run (not) Databases on ACFS is definitely worth a read.

Slides

Demo 1 recording

Demo 2 recording

Demo script (DB ACFS clone from Standby Database)

#!/bin/bash
####################
# create the clone #
####################

set -x

NUM=`echo $$ | cut -c 1-4`
export NEWNAME=${1:-SN$NUM}

cat <<EOF 
################################################
################################################
##
## CLONING DATABASE USING NEW SID: $NEWNAME
##
################################################
################################################

EOF

export ORACLE_SID=ACFSDB_1

dgmgrl <<EOF
connect sys/racattack
edit database ACFSDB set state="APPLY-OFF";
exit
EOF

acfsutil snap create -w $NEWNAME /u02

dgmgrl <<EOF
connect sys/racattack
edit database ACFSDB set state="APPLY-ON";
exit
EOF

cd /u02/.ACFS/snaps/$NEWNAME/ACFSDB

sqlplus / as sysdba <<EOF
alter database backup controlfile to trace as '/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc' reuse resetlogs;
create pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora' from spfile;
exit
EOF

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc
sed -i -e "s/CREATE CONTROLFILE.*$/CREATE CONTROLFILE REUSE SET DATABASE \"$NEWNAME\" RESETLOGS FORCE LOGGING NOARCHIVELOG/" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc
rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/controlfile/*
rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/controlfile/*
sed -i '/^ACFS.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.db_unique_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.dispatchers.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.audit_file_dest.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.fal_server.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.log_archive_config.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.log_archive_dest_1.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.memory_target.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.service_names.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.cluster_database.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora


#find /u02/.ACFS/snaps/SNAP1/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/archivelog/ -type f -exec mv {} /u02/.ACFS/snaps/SNAP1/ACFSDB/archivelog/ \;

mkdir -p $ORACLE_BASE/admin/$NEWNAME/adump

cat  >>/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora <<EOF
*.audit_file_dest='$ORACLE_BASE/admin/$NEWNAME/adump'
*.db_name='$NEWNAME'
*.db_unique_name='$NEWNAME'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=${NEWNAME}XDB)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
*.sga_target=1900M
$NEWNAME.instance_number=1
$NEWNAME.undo_tablespace=UNDOTBS1
*.service_names='$NEWNAME'
*.cluster_database=false
EOF

export ORACLE_SID=$NEWNAME

head -n $((`grep -n ^RECOVER /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc | awk -F: '{print $1}'`-2)) /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc > /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

 
sqlplus / as sysdba <<EOF
create spfile from pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora';
@/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc
--recover automatic database using backup controlfile until cancel;
--CANCEL
alter database open resetlogs;
alter tablespace temp add tempfile size 50M ;
EOF

#!/bin/bash

####################

# create the clone #

####################

set -x

NUM=`echo $$ | cut -c 1-4`

export NEWNAME=${1:-SN$NUM}

cat <<EOF

################################################

## CLONING DATABASE USING NEW SID: $NEWNAME

################################################

EOF

export ORACLE_SID=ACFSDB_1

dgmgrl <<EOF

connect sys/racattack

edit database ACFSDB set state="APPLY-OFF";

exit

EOF

acfsutil snap create -w $NEWNAME /u02

dgmgrl <<EOF

connect sys/racattack

edit database ACFSDB set state="APPLY-ON";

exit

EOF

cd /u02/.ACFS/snaps/$NEWNAME/ACFSDB

sqlplus / as sysdba <<EOF

alter database backup controlfile to trace as '/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc' reuse resetlogs;

create pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora' from spfile;

exit

EOF

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc

sed -i -e "s/CREATE CONTROLFILE.*$/CREATE CONTROLFILE REUSE SET DATABASE \"$NEWNAME\" RESETLOGS FORCE LOGGING NOARCHIVELOG/" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc

rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/controlfile/*

rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/controlfile/*

sed -i '/^ACFS.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_unique_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.dispatchers.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.audit_file_dest.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.fal_server.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.log_archive_config.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.log_archive_dest_1.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.memory_target.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.service_names.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.cluster_database.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

#find /u02/.ACFS/snaps/SNAP1/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/archivelog/ -type f -exec mv {} /u02/.ACFS/snaps/SNAP1/ACFSDB/archivelog/ \;

mkdir -p $ORACLE_BASE/admin/$NEWNAME/adump

cat >>/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora <<EOF

*.audit_file_dest='$ORACLE_BASE/admin/$NEWNAME/adump'

*.db_name='$NEWNAME'

*.db_unique_name='$NEWNAME'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=${NEWNAME}XDB)'

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'

*.sga_target=1900M

$NEWNAME.instance_number=1

$NEWNAME.undo_tablespace=UNDOTBS1

*.service_names='$NEWNAME'

*.cluster_database=false

EOF

export ORACLE_SID=$NEWNAME

head -n $((`grep -n ^RECOVER /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc | awk -F: '{print $1}'`-2)) /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc > /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

sqlplus / as sysdba <<EOF

create spfile from pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora';

@/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

--recover automatic database using backup controlfile until cancel;

--CANCEL

alter database open resetlogs;

alter tablespace temp add tempfile size 50M ;

EOF

Comments are, as always, very appreciated 🙂

—

Ludo

Script that duplicates a database using a physical standby RAC as source

Posted on March 18, 2012 by Ludovico

It’s possibile to duplicate a database for testing purposes (it’s an example) using a standby database as source. This allows you to off-load the production environment.

This is a simple script that makes use of ASM and classic duplicate, although I guess it’s possible to use the standby DB for a duplicate from active database.
You can launch it everyday to align your test env at a point in time.

#!/bin/bash
if [ $USER != 'oracle' ] ; then
        echo "need to be oracle"
        exit 0
fi

. $HOME/set11
export ORACLE_SID=test1

srvctl stop database -d test -o immediate

## this is supposed to be a script that erase your ASM  from your old test dbfiles:
## it's as simple as running with the CORRECT ENV:
## asmcmd rm -rf \
##   +DATA/TEST/ONLINELOG
##   +DATA/TEST/DATAFILE
##   +DATA/TEST/CONTROLFILE
##   +DATA/TEST/TEMPFILE
##   +FRA/TEST/ONLINELOG
##   +FRA/TEST/CONTROLFILE

ssh grid@testsrv /shared/refresh_test/remove_test_files.sh

sqlplus / as sysdba < <EOF
        set echo on
        startup nomount
        alter system set cluster_database=false scope=spfile;
        shutdown immediate
        startup nomount
EOF

rman <<EOF
connect target sys/mystrongpassword@stdby
connect auxiliary /

duplicate database to 'test' until time "trunc(sysdate)+6/24";

EOF


sqlplus / as sysdba <<EOF
        set echo on
        alter system set cluster_database=true scope=spfile;
        shutdown immediate
        startup mount
        alter database noarchivelog;
        shutdown immediate
EOF


srvctl start database -d test

#!/bin/bash

if [ $USER != 'oracle' ] ; then

echo "need to be oracle"

exit 0

. $HOME/set11

export ORACLE_SID=test1

srvctl stop database -d test -o immediate

## this is supposed to be a script that erase your ASM from your old test dbfiles:

## it's as simple as running with the CORRECT ENV:

## asmcmd rm -rf \

## +DATA/TEST/ONLINELOG

## +DATA/TEST/DATAFILE

## +DATA/TEST/CONTROLFILE

## +DATA/TEST/TEMPFILE

## +FRA/TEST/ONLINELOG

## +FRA/TEST/CONTROLFILE

ssh grid@testsrv /shared/refresh_test/remove_test_files.sh

sqlplus / as sysdba < <EOF

set echo on

startup nomount

alter system set cluster_database=false scope=spfile;

shutdown immediate

startup nomount

EOF

rman <<EOF

connect target sys/mystrongpassword@stdby

connect auxiliary /

duplicate database to 'test' until time "trunc(sysdate)+6/24";

EOF

sqlplus / as sysdba <<EOF

set echo on

alter system set cluster_database=true scope=spfile;

shutdown immediate

startup mount

alter database noarchivelog;

shutdown immediate

EOF

srvctl start database -d test

Mass datafile resizing

Posted on March 10, 2012 by Ludovico

Recently I needed to extend many datafiles on a database with more than 500 tablespaces because a lot of tablespaces were reaching the critical threshold.
Autoextend was not an option due to a bug I encountered on 10gR2 RAC on ASM and AIX.

The solution was the following script: it generates statements to autoextend datafiles with usage over a defined threshold (the “80” in the where clause) to low down the percentage below another defined threshold (the “75” in the select clause).

SELECT 'alter database datafile '''||f.file_name||
''' resize '||round(ceil(bytes/1024/1024/75*t.pct_used+50),-2)||'M;'
 FROM
   dba_data_files f, (
   SELECT  a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free,
          round((a.total-nvl(b.free,0))*100/a.total) pct_used
  FROM    (SELECT tablespace_name,round(sum(bytes)/1024/1024) free, max(bytes) maxfree
          FROM dba_free_space GROUP BY tablespace_name) b,
          (SELECT tablespace_name,decode(round(sum(bytes)/1024/1024),0,1,round(sum(bytes)/1024/1024)) total
          FROM dba_data_files GROUP BY tablespace_name) a
  WHERE a.tablespace_name = b.tablespace_name (+)) t  WHERE t.tablespace_name=f.tablespace_name
  AND t.pct_used>80;

SELECT 'alter database datafile '''||f.file_name||

''' resize '||round(ceil(bytes/1024/1024/75*t.pct_used+50),-2)||'M;'

FROM

dba_data_files f, (

SELECT a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free,

round((a.total-nvl(b.free,0))*100/a.total) pct_used

FROM (SELECT tablespace_name,round(sum(bytes)/1024/1024) free, max(bytes) maxfree

FROM dba_free_space GROUP BY tablespace_name) b,

(SELECT tablespace_name,decode(round(sum(bytes)/1024/1024),0,1,round(sum(bytes)/1024/1024)) total

FROM dba_data_files GROUP BY tablespace_name) a

WHERE a.tablespace_name = b.tablespace_name (+)) t WHERE t.tablespace_name=f.tablespace_name

AND t.pct_used>80;

Prior to extend it’s possible to show how much space is required to do this mass resizing:

 SELECT sum(mb_new-mb_old) FROM (
  SELECT t.tablespace_name, f.file_name, bytes/1024/1024 mb_old, round(ceil(bytes/1024/1024/75*t.pct_used+50),-2) mb_new FROM
 dba_data_files f, (
 SELECT  a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free,
        round((a.total-nvl(b.free,0))*100/a.total) pct_used
FROM    (SELECT tablespace_name,round(sum(bytes)/1024/1024) free, max(bytes) maxfree
        FROM dba_free_space GROUP BY tablespace_name) b,
        (SELECT tablespace_name,decode(round(sum(bytes)/1024/1024),0,1,round(sum(bytes)/1024/1024)) total
        FROM dba_data_files GROUP BY tablespace_name) a
WHERE a.tablespace_name = b.tablespace_name (+)) t  WHERE t.tablespace_name=f.tablespace_name
AND t.pct_used>80);

SELECT sum(mb_new-mb_old) FROM (

SELECT t.tablespace_name, f.file_name, bytes/1024/1024 mb_old, round(ceil(bytes/1024/1024/75*t.pct_used+50),-2) mb_new FROM

dba_data_files f, (

SELECT a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free,

round((a.total-nvl(b.free,0))*100/a.total) pct_used

FROM (SELECT tablespace_name,round(sum(bytes)/1024/1024) free, max(bytes) maxfree

FROM dba_free_space GROUP BY tablespace_name) b,

(SELECT tablespace_name,decode(round(sum(bytes)/1024/1024),0,1,round(sum(bytes)/1024/1024)) total

FROM dba_data_files GROUP BY tablespace_name) a

WHERE a.tablespace_name = b.tablespace_name (+)) t WHERE t.tablespace_name=f.tablespace_name

AND t.pct_used>80);

10gR2 RAC hangs and “KSV master wait”

Posted on June 3, 2011 by Ludovico

We recently migrated a customer’s 10gR2 RAC on AIX6.1 from GPFS+HACMP to a “basic” Clusterware with datafiles over ASM.
After (many) problems related to various installation bugs (the list of requirements for AIX is very long, incomplete and requires many one-off patches to complete), we had a problem during an import of a new schema: the import hung with no apparent wait events. We found that the event it was waiting for was classified as ‘Idle’:

SQL&gt; select sid, username, status, event, wait_class, program from gv$session;

 SID USERNAME   STATUS   EVENT                WAIT_CLASS PROGRAM
---- ---------- -------- -------------------- ---------- ----------------------------------------
...
 135 SYS        ACTIVE   KSV master wait      Idle       imp@trndcsaixdb1 (TNS V1-V3)
...

SQL> select sid, username, status, event, wait_class, program from gv$session;

SID USERNAME STATUS EVENT WAIT_CLASS PROGRAM

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

...

135 SYS ACTIVE KSV master wait Idle imp@trndcsaixdb1 (TNS V1-V3)

...

The on ASM instance:

SQL&gt; @wait10g

SID USERNAME   MACHINE         PROGRAM              EVENT                  SEQ#           P1  S_IN_WAIT STATE               STATUS
---- ---------- --------------- -------------------- -------------------- ------ ------------ ---------- ------------------- --------
201 SYS        trndcsaixdb1    oracle@trndcsaixdb1  enq: FA - access file      6   1178664965        744 WAITING             ACTIVE

SQL> @wait10g

SID USERNAME MACHINE PROGRAM EVENT SEQ# P1 S_IN_WAIT STATE STATUS

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

201 SYS trndcsaixdb1 oracle@trndcsaixdb1 enq: FA - access file 6 1178664965 744 WAITING ACTIVE

The problem was related to datafile resize (we use autoextend) and according to MOS, we were encountering a bug:

Bug 11712836: RESIZING DATAFILE HUNG WAITING FOR KSV MASTER WAIT IN RAC

Shutting down one instance solved the problem. Now we have to avoid autoextend……. We never encountered this bug in many 10.2.0.4 rac installations.