Applying RMAN Incremental Backup To Standby Database

We can use RMAN incremental backup to roll forward the physical standby database in the following situations:
1. when an archive sequence is missing
2. when lots of archives needs to be applied

Steps:

1. Take the current SCN of the standby database

SQL> select to_char(current_scn) scn from v$database;
SCN
---------------
23339995

2. Stop the redo apply on the standby database

SQL> alter database recover managed standby database cancel;

3. Take the incremental backup of the production database from the current SCN of the standby database

RMAN> run {
allocate channel c1 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c2 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c3 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c4 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
backup as compressed backupset incremental from scn 23339995 database;
}

4. Transfer the incremental backup to the standby system

$ scp /backup/* usename@standbyhost:/tmp/

5. Catalog all the incremental backup pieces to the standby database

RMAN> catalog start with '/tmp/' ;

6. Apply incremental backup to the standby database

RMAN> recover database noredo;

7. Create new standby controlfile at the production database, copy this to standby system and replace current standby controlfile with this new one.

SQL> alter database create standby controlfile as '/backup/standby_incr.ctl';

8. Start redo apply on the physical standby database

SQL> alter database recover managed standby database disconnect;


Note:
If you have added new datafile to the production and if it is not at created at standby you have to restore the datafile.
RAMN> restore datafile 56,57,58;

Password Expiration Notification

This is a simple script to notify about password expiration to users.

#!/bin/bash

# Set the environment variables
export ORACLE_HOME=/opt/oracle/product/102/DB
export ORALCE_SID=oradb
export PATH=$PATH:$ORACLE_HOME/bin

# Declar the vaiable
NOTIFY_LIST=user_name@abc.com
DIFF_FILE=/tmp/pass_exp.log

# Get expiry date
EXP_DATE=`chage -l oracle grep "Password Expires" awk '{print $4"-"$3"-"$5}'sed 's/,//'`

# Calculte in how many days password will expire
sqlplus usr/pass@$ORACLE_SID<<>
--CREATE TABLE pwd_expire (expire_date date);
INSERT INTO pwd_expire values (TO_DATE('$EXP_DATE','DD-MON-YYYY'));
COMMIT;
SET ECHO OFF FEEDBACK OFF
SPOOL $DIFF_FILE
SELECT ROUND(expire_date-sysdate) diff FROM pwd_expire;
SPOOL OFF
--DROP TABLE pwd_expire;
TRUNCATE TABLE pwd_expire;
EXIT

EOF

DAYS=`tail -2 $DIFF_FILEhead -1awk '{print $1}'`


if [ $DAYS -le 5 ]
then
mailx -s "Oracle Password will expires in $DAYS" $NOTIFY_LIST < /dev/null
fi

AXEL: Download accelerator for Linux

AXEL is a download accelerator for Linux. This utility also can be used to transfer files from one machine to another.

Download:
axel -n 16 -a http://download.com/download_file.gz

File Transfer:
axel -n 16 -a ftp://username/password@remote_host//temp/expdp_naikh_01.dmp

axel -n 16 -a ftp://username/password@remote_host//temp/expdp_naikh*

Applying Oracle Patchset

Aplying patchset p5337014_10203_LINUX.zip to upgrade from 10201 to 10203.

1. Shut down the database
SQL> shut immediate

2. Stop the listener
$ lsnrctl stop

3. Unzip the patchset and run OUI
$ ./runinstaller

4. Start the listener
$ lsnrctl start

5. Run the following commnads
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF

6. Restart the database
SQL> SHUT IMMEDIATE
SQL> STARTUP

7. Run the utlrp.sql script to recompile all invalid PL/SQL packages
SQL> @?/rdbms/admin/utlrp.sql

Bash Shell Scripting Guide

1. Basics
a. The first line of the script must be
#!/bin/bash
b. Make the script exicutable
chmod +x test.sh

2. Conditional Statement
2.1. if statement
syntax:
if [ condition ]
then
commands
fi

2.2. if else statement
syntax:
if [ condition ]
then
commands
else
commands
fi

2.3. if elif statement
syntax:
if [ condition1 ]
then
commands
elif [ condition2]
then
commands
else
commands
fi

3.1 Relational operators
-eg Equal to
-lt Less than
-gt Greater than
-ge Greater than or equal to
-le Less than or equel to

3.2 File Related Tests
-f file True if file exists and is a regular file
-r file True if file exists and is readable
-w file True if file exists and is writable
-x file True if file exists and is executable
-d file True if file exists and is a directory
-s file True if file exists and its size is greater than zero

3.3 String tests
-n str True if string str is not a null string
-z str True if string str is a null string
str1 == str2 True if both the strings are equel
str1 != str2 True if both the strings are not equel
str True if str is assigned a value and is not null

3.4 Multiple conditions
-a Performs the AND function
-o Performs the OR function

4. Case Statement
Syntax:
case expression in
pattert1) execute commands;;
pattert2) execute commands;;
pattert3) execute commands;;
....
esac

Example:
#!/bin/bash
case `datacut -d" " -f1` in
Mon) commands;;
Tue) commands;;
Wen) commands;;
....
esac

5. Looping Statements
5.1 while loop
syntax:
while [ condtion_is_true ]
do
execute commands
....
done

Example:
while [ $NUM -gt 100 ]
do
sleep
done

5.2 until loop
Syntax:
until [ condition_is_false ]
do
execute commands
done

Example:
until [ -f $FILE ]
do
sleep 5
done

5.3 for loop
Syntax:
for variable in list
do
execute commands
done

Example:
for I in 1 2 3 4 5
do
echo "The value of I is $I";
done

Example 2:
#!/bin/bash
LIMIT=10
for ((a=1;a<=$LIMIT;a++)) do echo "$a" done
6. Special symbols
$0 Name of the shell script being executed
$1 First parameter passed to the script
$* All the paramerts passes to the script
$# Number of parameters passed to the script
$? Exit status of the last command

7. Read statement
#!/bin/bash
echo "Enter your name:"
read NAME
echo "Hello $NAME, Have a nice day."

8. Functions
Syntax:
Function_name ()
{
statements
}

Example:
#!/bin/bash
sumcalc ()
{
SUM= $[ $1 + $2 ]
echo "Result = $SUM"
}

echo -e "Enter the first number:\c"
read NUM1
echo -e "Enter the second number:\c"
read NUM2
read NUM2

# Call the function
sumcal $NUM1 $NUM2

Standby Database Creation

Steps to create the physical standby database:
1. Take the backup of production database
2. Create standby controlfile
3. Copy the backup, standy controfile and init.ora files to standby box
4. Create necessary directories
5. Edit the init.ora file
6. Mount the database
7. Restore the database
8. Start the recovery

1. Take the backup of production database
rman> run {
allocate channel c1 type disk format '/backup/%d_rman_bkp_%T_p%p_s%s' maxpiecesize 1800M;
allocate channel c2 type disk format '/backup/%d_rman_bkp_%T_p%p_s%s' maxpiecesize 1800M;
allocate channel c3 type disk format '/backup/%d_rman_bkp_%T_p%p_s%s' maxpiecesize 1800M;
allocate channel c4 type disk format '/backup/%d_rman_bkp_%T_p%p_s%s' maxpiecesize 1800M;
backup as compressed backupset databse plus archivelog;
}

2. Create standby controlfile
sql> alter database create controlfile as '/backup/standby_controlfile.ctl';

3. Copy the backup, controfile and init.ora files to standby box
use ftp or scp to copy the files

4. Create necessary directories
$ mkdir adump bdump cdump udump datafiles controlfiles redofiles archive

5. Edit the init.ora file
a. Change controfile path
b. Change the paths of all dump_dest parametes
c. Change the LOG_ARCHIVE_DEST path
d. Add below 3 parametes
DB_FILE_NAME_CONVERT='/old_path1/','/new_path1/' ,'/old_path2/','/new_path2/'
LOG_FILE_NAME_CONVERT='/old_path/','/new_path/'
STANDBY_FILE_MANAGEMENT='AUTO'

6. Mount the database
sql> startup mount pfile='init.ora'

7. Restore the database
rman> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
restore database;
}

8. Start the recovery
sql> alter database recover manged standby database disconnect;

Restore Spfile and Controlfile

Different ways of restoring spfile and controlfile

1. Restore SPFILE

restore spfile to '/bkp/spfile.ora';

restore spfile to '/bkp/spfile.ora' from autobackup;

restore spfile to '/bkp/spfile.ora' from tag=man_bkp;

restore spfile to '/bkp/spfile.ora' from /home/oracle/bkp/c-543432-24342-01 ;

restore spfile to '/bkp/spfile.ora' until time 'sysdate-31;

2. Restore Controlfile

restore controlfile to '/bkp/controlfile.ctl';

restore controlfile to '/bkp/controlfile.ctl' from autobackup;

restore controlfile to '/bkp/controlfile.ctl' from tag=man_bkp;

restore controlfile to '/bkp/controlfile.ctl' from /bkp/c-543432-24342-01 ;

restore controlfile to '/bkp/controlfile.ctl' until time 'sysdate-31;

Transportable TBS - Part 3

Transportable Tablespace from RMAN backup.

Assumptions
* Tablespace name=TTBS
* Same platform (source and target)
* We have RMAN backup of the database

RMAN> transport tablespace TTBS
tablespace destination '/opt/oracle/ttbs_dir'
auxiliary destination '/opt/oracle/aux_ttbs'
datapump directory DATA_PUMP_DIR
dump file 'expdp_ttbs_01.dmp'
import script 'impdp_ttbs_01.sql'
export log 'expdp_ttbs.log';


Explanation:
tablespace destination - in this location RMAN stores datafiles of the TTBS
auxiliary destination - in this location RMAN creates temporary database and later deletes it.
datapump directory - in this export dump of metadata of TTBS is stored
dump file - Name of the datapump dump file
import script - RMAN creates import file which can be used to import the metadata

Possible Error:
While importing i got the below error. i just created the user_name in the database.

ORA-29342: user user_name does not exist in the database

awk: Find & Replace

1. Single file - Replaces Oracle with Naikh
awk '{gsub("Oracle","Naikh",$0); print > FILENAME }' *.txt

2. Multiple Files - Replaces Oracle with Naikh
awk '{gsub("Oracle","Naikh",$0); print > FILENAME }' *.txt

3. All the file which contains a pattern
awk '{gsub("Oracle","Naikh",$0); print > FILENAME }' `find . exec grep -l "Oracle" {} \;

Note: Below command will not work
find . exec grep -l "Oracle" {} \; awk '{gsub("Oracle","Naikh",$0); print > FILENAME }'

Transportable TBS - Part 2

Assumptions
* Tablespace name: TTBS
* Source platform: Linux (32 bit)
* Target platform: Solaris (64 bit)

To Identify the endian format
select d.platform_name, endian_format
from v$transfortable_platform tp, v$database d
where tp.platform_name=d.platform_name;

List of all platform and respective endian format
select * from v$transfortable_platform
order by platform _id;


Source Machine

1. check tablespace is transprtable
a. execute this procedure
exec dbms_tts.transport_set_check=('ttbs',true);

b. check for any violations
select * from transport_set_violations;

2. Make tablespace read only
alter tablspace ttbs read only;

3. Identify the datafiles
select file_name
from dba_data_files
where tablespace_name='TTBS';

4. Export tablespace's metadata
expdp directory=dump1
dumpfile=expdp_ttbs.dmp
transportable_tablespace=ttbs
logfile=expdp_ttbs.log
job_name=j1

5. Copy datafile and export dump file to target machine
cp /home/oracle/dba01/ttbs1.dbf /temp/ttbs1.dbf cp /home/oracle/dpump1/expdp_ttbs.dmp /temp/expdp_ttbs.dmp

6. Make the tablespace read write
alter tablespace ttbs read write;

Target Machine

1. Convert the datafile
rman> convert datafile '/temp/ttbs1.dbf'
to platform 'Solaris [tm] (64-bit)'
from platform 'Linux (32-bit)'
db_file_name_convert="/temp/","/home/oracle/testdb/ttbs1.dbf";

2. Import metadata
impdp directory=dump2
dumpfile=expdp_ttbs.dmp
transport_datafiles=/home/oracle/testdb/ttbs1.dbf
logfile=impdp_ttbs.log
job_name=j2

3. Make tablespace read write
alter tablespace ttbs read write;

Transportable TBS - Part 1

Assumptions
* Tablespace name=TTBS
* Same platform (source and target)

Source Machine
1. check tablespace is transprtable
a. execute this procedure
exec dbms_tts.transport_set_check=('ttbs',true);

b. check for any violations
select * from transport_set_violations;

2. Make tablespace read only
alter tablspace ttbs read only;

3. Identify the datafiles
select file_name
from dba_data_files
where tablespace_name='TTBS';

4. Export tablespace's metadata
expdp directory=dump1
dumpfile=expdp_ttbs.dmp
transportable_tablespace=ttbs
logfile=expdp_ttbs.log
job_name=j1

5. Copy datafile and export dump file to new location cp /home/oracle/dba01/ttbs1.dbf /temp/ttbs1.dbf cp /home/oracle/dpump1/expdp_ttbs.dmp /temp/expdp_ttbs.dmp

6. Make the tablespace read write
alter tablespace ttbs read write;

Target Machine

1. Import metadata
impdp directory=dump2
dumpfile=expdp_ttbs.dmp
transport_datafiles=/temp/ttbs1.dbf
logfile=impdp_ttbs.log
job_name=j2

2. Make tablespace read write
alter tablespace ttbs read write;

Useful SQL statements

1. To create a script to copy all the datafiles to new location:

select 'cp 'name' /newpath/'substr(name,instr(name,'/',-1,1)+1))' &'

from v$database;

2. To create a script to copy all the datafiles, controlfiles and redolog files to new location:

select 'cp 'name'/new_path/'
substr(name,instr(name,'/',-1,1)+1))' &'
from (
select name from v$datafile
union all
select name from v$controlfile
union all
select member from v$logfile
)

3. To get the sql statements to Rename all datafiles and redolog files:

select 'alter database rename file '''name ''' to
''/new_path/'substr(name,instr(name,'/',-1,1)+1)''';'
from (
select name from v$datafile
union all
select member from v$logfile
)

4. Copy datafile

select 'cp ' file_name

decode(substr(file_name,1,instr(file_name,'/',-1,1)),

'/Old_path1/','/New_path1/',

'/Old_path2/','/New_path2/'

)substr(file_name,instr(file_name,'/',-1,1)+1)

' &' file_name

from dba_data_files;

5. Copy redolog file

select 'cp ' member ' '

decode(substr(member,1,instr(member,'/',-1,1)),
'/Old_path1/','/New_path1/',
'/Old_path2/','/New_path2/'
)substr(member,instr(member,'/',-1,1)+1)' &' member

from v$logfile;

6. Rename data file

select ' Alter database rename file '''file_name ''' to '''

decode(substr(file_name,1,instr(file_name,'/',-1,1)),
'/Old_path1/','/New_path1/',
'/Old_path2/','/New_path2/'
)substr(file_name,instr(file_name,'/',-1,1)+1) ' ;' file_name
from dba_data_files;

7. Rename redolog file

select 'alter database rename file ''' member ''' to '''
decode(substr(member,1,instr(member,'/',-1,1)),

'/Old_path1/','/New_path1/'

,'/Old_path2/','/New_path2/')

substr(member,instr(member,'/',-1,1)+1) ''' ;' member
from v$logfile;

8. Datafile Offline drop

select 'alter database datafile 'file_id' offline drop;'

from dba_data_files;

Managing Standby Database

Start the standby database

startup nomount;

alter database mount standby database;

alter database recover managed standby database disconnect;


Shutdown the standby database

alter database recover managed standby database cancel;

shutdown immediate;


Registering the archive logs manualy

alter database register or replace logfile '/opt/oracle/archive/oradb_1_1234_48540.arc';


Opening standby database in READ ONLY mode

alter database recover managed standby database cancel;

alter database open read only;


From READ ONLY mode to Managed recovery
a. terminate all the active user sessions on the standby database

b.
alter database recover managed standby database disconnect;