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. Some bash shell scripting rules
1) The first line in your script must be

#!/bin/bash

That is a # (Hash) followed by a ! (ban) followed by the path of the shell. This line lets the environment know the file is a shell script and the location of the shell.

2) Before executing your script, you should make the script executable.

$ chmod ugo+x test.sh

3) The name of your shell script must end with a .sh .

Example 1.1: First shell script

a.

$ vi my_shell_script.sh

b.

#!/bin/bash
echo “Hello World “

c.

$ chmod +x my_shell_script.sh

d.

$ ./ my_shell_script.sh

2. Conditional statements

The ‘if’ Statement – evaluates a condition which accompanies its command line.

Syntax 2.1:

if condition_is_true
then
execute commands
fi

Syntax 2.2:

if condition_is_true
then
execute commands
else
execute commands
fi

if condition also permits multiway branching. That is you can evaluate more conditions if the previous condition fails.

Syntax 2.3:

if condition_is_true
then
execute commands
elif another_condition_is_true
then
execute commands
else
execute commands
fi

3. if’s companion – test

test is an internal feature of the shell. test evaluates the condition placed on its right, and returns either a true or false exit status. For this purpose, test uses certain operators to evaluate the condition. They are as follows:

3.1 Relational operators

-eq Equal to
-lt Less than
-gt Greater than
-ge Greater than or equal to
-lt Less than
-le Less than or equal 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 has a size 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 strings are equal
str1 != str2 True if both strings are unequal
str True if string str is assigned a value and is not null.

3.4 Multiple conditions

-a Performs the AND function
-o Performs the OR function

Example 3.1:

if [ $D -eq 25 ]
then
echo $D
fi

Example 3.2:

if [ $STR1 == $STR2 ]
then
do something
fi

Example 3.3:

if [ -n "$STR1" -a -n "$STR2" ]
then
echo ‘Both $STR1 and $STR2 are not null’
fi

… above, I have checked if both strings are not null then execute the echo command.

Things to remember while using test

If you are using square brackets [] instead of test, then care should be taken to insert a space after the [ and before the ].

Note: test is confined to integer values only. Decimal values are simply truncated.

4. Case statement
Case statement is the second conditional offered by the shell.
Syntax:

case expression in
pattern1) execute commands ;;
pattern2) execute commands ;;
…
esac

The keywords here are in, case and esac. The ‘;;’ is used as option terminators. The construct also uses ‘)’ to delimit the pattern from the action.

Example 4.1:

…
echo “Enter your option : “
read I;

case $I in
1) ls -l ;;
2) ps -aux ;;
3) date ;;
4) who ;;
5) exit
esac

Note: The last case option need not have ;;

but you can provide them if you want.

Example 4.2:

case `date +%a` in
Mon) commands ;;
Tue) commands ;;
Wed) commands ;;
…
esac

5. Looping Statements

5.1 while loop

Syntax:

while condition_is_true
do
execute commands
done

Example 5.1.1:

while [ $NUM -gt 100 ]
do
sleep 5
done

Example 5.1.2:

while :
do
execute some commands
done

The above code implements a infinite loop. You could also write ‘while true’ instead of ‘while :’ .

5.2 until loop

Until complements while construct in the sense that the loop body here is executed repeatedly as long as the condition remains false.

Syntax:

until false
do
execute commands
done

Example 5.2:

…
until [ -f myfile ]
do
sleep 5
done

The above code is executed repeatedly until the file myfile is created(touch).

5.3 for loop

Syntax :

for variable in list
do
execute commands
done

Example 5.3.1:

…
for X in 1 2 3 4 5
do
echo “The value of X is $X”;
done

Example 5.3.2:

#!/bin/bash

LIMIT=10

for ((a=1; a <= LIMIT ; a++))

do

echo -e “$a”

done

6. special symbols and their meanings w.r.t shell scripts

$* – This denotes all the parameters passed to the script at the time of its execution. Which

includes $1, $2 and so on.
$0 – Name of the shell script being executed.
$# – Number of arguments specified in the command line.
$? – Exit status of the last command.

$$ – contains the process ID of the current shell.

The above symbols are known as positional parameters. Let me explain the positional parameters with the aid of an example. Suppose I have a shell script called my_script.sh . Now I execute this script in the command line as follows :

$ ./my_script.sh linux is a robust OS

… as you can see above, I have passed 5 parameters to the script. In this scenario, the values of the positional parameters are as follows:

$* – will contain the values ‘linux’,’is’,’a’,’robust’,’OS’.

$0 – will contain the value my_script.sh – the name of the script being

executed.

$# – contains the value 5 – the total number of parameters.

$$ – contains the process ID of the current shell. You can use this parameter while giving unique names to any temporary files that you create at the time of execution of the shell.

$1 – contains the value ‘linux’

$2

- contains the value ‘is’

… and so on.

7. Read statement

Make your shell script interactive. read will let the user enter values while the script is being executed. When a program encounters the read statement, the program pauses at that point. Input entered through the keyboard id read into the variables following read, and the program execution continues.

Example 7.1: Interactive

#!/bin/bash
echo “Enter your name : “
read NAME
echo “Hello $NAME , Have a nice day.”

Exit status of the last command

Every command returns a value after execution. This value is called the exit status or return value of the command. A command is said to be true if it executes successfully, and false if it fails. This can be checked in the script using the $? positional parameter.

Example x: Multiplication Table

#!/bin/bash

Y=1

while [ $Y -le 12 ]; do

X=1

while [ $X -le 12 ]; do

printf “% 4d” $(( $X * $Y ))

let X++

done

echo “”

let Y++

done

8. Function

We can break our code into small chunks called functions, and call them by name in the main program. This approach helps in debugging, code re-usability, etc.

Syntax:

<name of function> ()

{ # start of function

statements

} # end of function

Functions are invoked by citing their names in the main program, optionally followed by arguments.

Example 8.1:

#!/bin/bash

#******************************************************

# This script uses function

#******************************************************

sumcalc ()

{

SUM=$[$1 + $2]

}

echo -e “Enter the first number:\c”

read NUM1

echo -e “Enter the second number:\c”

read NUM2

sumcalc $NUM1 $NUM2

echo “Output from function sumcalc: $SUM”

Oracle 9i Funda1

Database Administration Tasks

• Plane and create database
• Manage database availability
• Manage physical and logical structures
• Manage storage based on design
• Manage security
• Network administration
• Backup and Recovery
• Database tuning

Oracle Server = + Oracle Instance + Oracle database

Oracle Instance = Memory structures + Background process

Oracle Database = data files, control files, redo log files

SGA – (System Global Area), SGA_MAX_SIZE

- Shared pool, SHARED_POOL_SIZE

1. Library cache

- Execution plan

- Parse Tree

- Parsed and compiled program units and procedures

2. Data Dictionary cache

- Information about user account data, data file names, segment names, extent locations, table description and user privileges

- Database buffer cache,

- DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE

- DB_CACHE_ADVICE values ON,OFF,READY, for predicting different cache

size behavior, information is stored in v$db_cache_advice

- Redo log buffer, LOG_BUFFER

- Primary purpose is recovery

- records all changes made to the database block

- change records within are called redo entries

- Larger pool (optional), LARGE_POOL_SIZE

- Java pool (optional), JAVE_POOL_SIZE

PGA – (Program Global Area)

- is a memory region that contains data and control information for single server process or a background process

- PGA includes following info

1. Sort area –

2. Session information – includes user privileges and performance statistics

for the session

3. Cursor state –

4. Stack space – contains other session variables

DBWn writes when following events occurs:

1. Checkpoint

2. Dirty buffer threshold reached

3. No free buffer

4. Timeout

5. RAC ping request

6. Tablespace offline

7. Tablespace read only

8. Tablespace BEGIN BACKUP

9. Table DROP or TRUNCATE

LGWR writes under following situations

1. At commit

2. When one-third full

3. When there 1 MB of redo

4. Every 3 seconds

5. Before DBWn writes

SMON: Responsibilities

1. Instance recovery

- Rolls forward changes in the redo logs

- Open database for user access

- Rollback uncommitted transactions

2. Coalescess free space every 3 seconds in the data files

3. Deallocates temporary segments

PMON: Cleans up after failed process by

1. Rolling back the user’s current transaction

2. Releasing the locks

3. Release other resources

4. Restarts dead dispatchers

CKPT : Checkpoint occurs in the following situations

1. At every log switch

2. shut down normal, transactional, or immediate

3. when forced by setting the parameter FAST_START_MTTR_TARGET

4. when manually requested by the DBA

5. ALTER TABLESPACE [OFFLINE NORMAL |READ ONLY|BEGIN BACKUP] cause checkpoint on specific data files

Check points are implemented for the following reason:

1. it ensures that data blocks in memory that changes frequently are written to data files regularly

2. all changes up to the checkpoints have been recorded in the data files, redo log entries before the checkpoints no longer need to be applied to the data files if instance recovery is required

At checkpoint following information is written:

1. checkpoint number into datafile headers

2. checkpoint number, log sequence number, archived log names, and system change numbers into the control file

If LOG_CHECKPOINTS_TO_ALERT parameter is set to TRUE checkpoint information is recorded in the altert_SID.log file

Oracle Managed Files:

DB_CREATE_FILE_DEST – default location for data files

DB_CREATE_ONLINE_LOG_DEST_N – default location for online redo logs and control files, up to max 5 locations

Startup nomount

- only during database creation or control file re-creation

Starting an instance includes following tasks

1. Reading initialization file in the following order

- spfileSID.ora. if not found then

- spfile.ora

- initSID.ora

2. SGA is allocated

3. Background processes are started

4. alert_SID.log and trace files opened

mounting the database

Database in mount during the following tasks

- renaming data files

- enabling or disabling archiving

- performing full database recovery

Mounting a database includes following tasks

1. Associate a database with a previously started instance

2. Locating and opening the control files specified in the parameter file

3. Reads the control file to obtain the names and status or datafiles and online redo log files

Opening the database

Opening the database includes the following tasks

1. opening the online data files

2. opening the redo log files

3. If necessary SMON initiates instance recovery

Shutdown normal

1. no new connection can be made
2. waits for all users to disconnect
3. data and redo buffers are written to disk
4. background processes are terminated
5. SGA is removed from the memory
6. closes and dismounts the database
7. next startup does not require a instance recovery

Shutdown Transactional

1. No new connection

2. waits until all the transaction ends

3. a shutdown immediate occurs

shutdown immediate

1. does not wait for users to disconnect
2. rolls back all active transactions and disconnects all transactions
3. closes and dismounts the database, next startup instance reconvery not required

shutdown abort

1. all active sessions are terminated
2. database and redo buffers are not written to disk
3. uncommitted transactions are not rolled back
4. instance is terminated without closing the files
5. database is not closed or dismounted
6. next startup requires instance recovery

Alert log keeps a record of following information

1. when the database was started and shutdown
2. a list of all non default initialization parameters
3. the start up of the background processes
4. the thread being used by the instance
5. the log sequence number LGWR is writing to
6. Information about the log switch
7. Creation of tablespaces and undo segments
8. alter statements that have been used
9. information regarding the error messages such as ora-600 etc

Base tables are create during database creation, sql.bsq

Data Dictionary views are created using catalog.sql

Dynamic performance views: information is accessed from

1. memory
2. control file

Control files

- can be multiplexed up to 8 times

- size is influenced by the following keywords in create database or create ontolfile

commands

- MAXLOGFILES

- MAXLOGMEMBERS

- MAXLOGHISTORY

- MAXDATAFILES

- MAXINSTANCES

- Control file contains the following entries

1. Database name and identifier
2. Time stamp of the database creation
3. tablespace names
4. Names and locations of the data files and red log files
5. Current redo log file sequence number
6. Checkpoint information
7. Begin and end of undo segments
8. Redo log archive information
9. Backup information

- The control file consists of two types of section

1. Reusable – stores rman backup information, used in a circular manner.
2. Not reusable

Multiplexing control file using spfile

1. Alter spfile

Sql> alter system set control_file=’/oradata1/control/control1.ctl’, ’/oradata2/control/control2.ctl’ scope=spfile;

2. shutdown normal

Sql> shutdown

3. create addition control file

$ cp /oradata1/control/control1.ctl’, ’/oradata2/control/control2.ctl’

4. start the databse

Sql> startup

Multiplexing control file using pfile

1. shutdown normal

Sql> shutdown

2. create addition control file

$ cp /oradata1/control/control1.ctl’, ‘/oradata2/control/control2.ctl’

3. Add new control file name to init.ora

control_file=’/oradata1/control/control1.ctl’, ’/oradata2/control/control2.ctl’

4. start the databse

Sql> startup

Backup control file

Sql> alter database backup controlfile to ‘/backup/controlfile.bkp’;

Sql> alter database backup controlfile to trace;

Add redo group

Alter database add logfile group 3 (‘/oradata/redolog/redo3a.log’, ‘/oradata/redolog/redo3b.log’) size 1m;

Alter database add logfile member

‘/oradata/redolog/redo1c.log’, to group 1,

‘/oradata/redolog/redo2c.log’ to group 2,

‘/oradata/redolog/redo3c.log’ to group 2;

Drop log file group

Alter database drop logfile group 3;

– the os files are not deleted

Drop log file member

Alter database drop logfile member ‘/oradata/redolog/redo3c.log’ ;

– the os files are not deleted

Status of the redo file

1. unused – log group never been used. When log file was just added

2. current – implies online redo group is active

3. active – indicates log group is active but not the current. It is needed for crash recovery

4. inactive – log group is no longer needed for instance recovery. It may or may not be archived.

Forcing Log switch

ALTER SYSTEM SWITH LOGFILE;

Clearing Log file

ALTER DATABASE CLEAR LOGFILE GROUP 5;

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5

Useful SQL statements

1. datafiles

SELECT ‘cp ‘||NAME ||‘   /localdisk/oradb/’||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

)

 

2. Rename Datafiles 

SELECT ‘ALTER DATABASE RENAME FILE ”’||NAME ||”’   TO  ”/localdisk/oradb/’||SUBSTR(NAME,INSTR(NAME,‘/’,-1,1)+1 )||”’;’

FROM (

       SELECT NAME FROM V$DATAFILE

       UNION ALL

       SELECT MEMBER FROM V$LOGFILE

)

 

 

3.    datafiles 

SELECT ‘cp ‘||NAME ||‘   /localdisk/oradb/’||SUBSTR(NAME,INSTR(NAME,‘/’,-1,1)+1 )||‘ &’

FROM V$DATAFILE;

 

4. controlfiles

SELECT ‘cp ‘||NAME ||‘   /localdisk/oradb/’||SUBSTR(NAME,INSTR(NAME,‘/’,-1,1)+1 )||‘ &’

FROM V$CONTROLFILE;

 

 

5. Redo files

SELECT ‘cp ‘||MEMBER ||‘   /localdisk/oradb/’||SUBSTR(MEMBER,INSTR(MEMBER,‘/’,-1,1)+1 )||‘ &’ FROM V$LOGFILE;

 

 

SELECT ‘ALTER DATABASE RENAME FILE ”’||name||”’ TO ”/localdisk/oradb/’||substr(name,instr(name,‘/’,-1,1)+1 )||”’;’  FROM V$DATAFILE;

 

SELECT ‘ALTER DATABASE RENAME FILE ”’||member||”’ TO ”/localdisk/oradb/’||substr(member,instr(member,‘/’,-1,1)+1 )||”’;’  FROM V$LOGFILE;

 

 

 

select ‘ Alter database rename file ”’||name ||”’ to ”’||

decode(substr(name,1,instr(name,‘/’,-1,1)),

‘/u01/oradb/’,‘/localdisk/oradb/’,

‘/u02/oradb/’,‘/localdisk/oradb/’,

‘/u03/oradb/’/localdisk/oradb/‘,

‘/u04/oradb/’,‘/localdisk/oradb/’

 

)||substr(name,instr(name,‘/’,-1,1)+1) ||”’ ;’ name

from v$datafile;

 

 

 

 

 

 

 

 

 

 

 

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