simple RMAN full backup script

oracle@dbtestserver $ cat /export/home/oracle/scripts/rman_ORCL.ksh
#!/bin/ksh
mailTo="dba_group@oratech.com"

logdir=/rman_bkp/oradata/ORCL/logs

#set the oracle environment

ORACLE_HOME=/ora_home/oradata/product/11.2.0.3/dbhome_1
ORACLE_SID=ORCL
export ORACLE_HOME
export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH
export PATH

#check if user is oracle

USER=`/usr/ucb/whoami`
if [ $USER != "oracle" ]
then
  echo "ERROR: This script must be run as the oracle user"
  echo "Exiting... `date`"
  exit 1
fi


# --------------------------------------------------------------------------------

#       backup now

# --------------------------------------------------------------------------------
rman target /  catalog rman11g/rman11g@rmandnpd<< EOF >>$logdir/"rman_ORCL_full_`date +%d_%m_%Y`".log

CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE = 2G;
sql 'alter system switch logfile';
run
{
allocate channel dsk1 type disk format '/rman_bkp/oradata/ORCL/%U';
allocate channel dsk2 type disk format '/rman_bkp/oradata/ORCL/%U';
backup database;
crosscheck archivelog all;
backup archivelog all delete input;
}
exit
EOF

/bin/find /rman_bkp/oradata/ORCL/ \*_1 -mtime +1 -exec rm {} \;
AS_OF=`date +%d_%m_%Y`
#mail $mailTo <"$logdir/rman_ORCL_full_`date +%d_%m_%Y`".log
mail ${mailTo} <<EOF
From:dbtestserver
To: dba_ora@oratech.com
Subject: ORCL Delivery INFO - Full RMAN Backup of ORCL db
`cat $logdir/rman_ORCL_full_${AS_OF}.log`
EOF

Recovering missing system datafile in oracle

Prerequisite: need to take full RMAN backup of the database plus archivelogs. 


Check the datafiles location.

SQL> select file_name from dba_data_files;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/sample01.dbf

remove the system datafile from physical location using os commands like rm

[oracle@localhost ~]$ cd /u01/app/oracle/oradata/orcl

[oracle@localhost orcl]$ rm system01.dbf 

[oracle@localhost orcl]$ ls -lrth

total 2.3G
-rw-r----- 1 oracle oinstall 5.1M Apr  3 00:42 users01.dbf
-rw-r----- 1 oracle oinstall  51M Apr  8 03:56 redo02.log
-rw-r----- 1 oracle oinstall  21M Apr  8 03:58 temp01.dbf
-rw-r----- 1 oracle oinstall 249M Apr  8 03:59 example01.dbf
-rw-r----- 1 oracle oinstall 641M Apr  8 04:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 571M Apr  8 04:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall  51M Apr  8 04:06 redo01.log
-rw-r----- 1 oracle oinstall 9.3M Apr  8 04:06 control01.ctl


If system datafile is missed or corrupted, database will not shut down, but no operations permissible

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 8 04:06:38 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE


SQL> create table rr(name varchar(10));
create table rr(name varchar(10))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

In order to restore system datafile we need to bring down the database using shut abort, bcz shut immediate will not work

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 8 04:17:27 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

shutdown database by aborting the instance.

SQL> shut abort
ORACLE instance shut down.


connect to rman and start up instance in mount stage.

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 8 04:18:18 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area    1033670656 bytes

Fixed Size                     1341056 bytes
Variable Size                704645504 bytes
Database Buffers             322961408 bytes
Redo Buffers                   4722688 bytes

Now restore datafile.

RMAN> restore datafile 1;

Starting restore at 08-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_04_08/o1_mf_nnndf_TAG20150408T035923_bl8pl3p8_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_04_08/o1_mf_nnndf_TAG20150408T035923_bl8pl3p8_.bkp tag=TAG20150408T035923
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 08-APR-15


recover datafile.

RMAN> recover datafile 1;

Starting recover at 08-APR-15
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 08-APR-15

open the database.

RMAN> alter database open;

database opened

Disabling firewales in linux

You need to login as root user into linux


[root@localhost ~]# service iptables status
Table: filter
Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination
1    RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0

Chain FORWARD (policy ACCEPT)
num  target     prot opt source               destination
1    RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination

Chain RH-Firewall-1-INPUT (2 references)
num  target     prot opt source               destination
1    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0
2    ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0           icmp type 255
3    ACCEPT     esp  --  0.0.0.0/0            0.0.0.0/0
4    ACCEPT     ah   --  0.0.0.0/0            0.0.0.0/0
5    ACCEPT     udp  --  0.0.0.0/0            224.0.0.251         udp dpt:5353
6    ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0           udp dpt:631
7    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:631
8    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED
9    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:21
10   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22
11   REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited


[root@localhost ~]# service iptables stop
Flushing firewall rules:                                   [  OK  ]
Setting chains to policy ACCEPT: filter                    [  OK  ]
Unloading iptables modules:                                [  OK  ]

[root@localhost ~]# service iptables status
Firewall is stopped.

[root@localhost ~]# service iptables save

Missing non system datafile recover using RMAN backup

Prerequisite: need to take full RMAN backup of the database.

 
-Connect to RMAN commnad prompt.


[oracle@localhost trace]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 1 12:06:44 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1341150024)

-Put the data file in to offline, Here we can use missing datafile name or datafile number. I am using datafile number.


RMAN> sql 'alter database datafile 5 offline';

using target database control file instead of recovery catalog

sql statement: alter database datafile 5 offline

-Restore the datafile from backup.

RMAN> restore datafile 5;

Starting restore at 01-APR-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_04_01/o1_mf_nnndf_TAG20150401T114744_bkq3dbcn_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_04_01/o1_mf_nnndf_TAG20150401T114744_bkq3dbcn_.bkp tag=TAG20150401T114744

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 01-APR-15

-Recover datafile

RMAN> recover datafile 5;

Starting recover at 01-APR-15

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 01-APR-15

-put the datafile into online.

RMAN> sql 'alter database datafile 5 online';

sql statement: alter database datafile 5 online

RMAN> exit

-Recovery Manager complete. 

SQL> select status from v$datafile;

STATUS

-------

SYSTEM

ONLINE

ONLINE

ONLINE

ONLINE

ONLINE

6 rows selected.

Recover non system datafile with backup

Recovering missing non system datafile.  

Prerequisites: database must be in archivelog mode, and need to have hotbackup. Click Here to know how to take hot backup.

-For non system datafile recovery no need to shutdown database.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 09:11:09 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/example01.dbf

/u01/app/oracle/oradata/sample01.dbf

6 rows selected.

[oracle@localhost hotbk]$ cd /u01/app/oracle/oradata/ orcl

[oracle@localhost orcl]$ ls -lrt

total 2340652

-rw-r----- 1 oracle oinstall  20979712 Apr  1 08:20 temp01.dbf

-rw-r----- 1 oracle oinstall  52429312 Apr  1 09:39 redo02.log

-rw-r----- 1 oracle oinstall   5251072 Apr  1 09:45 users01.dbf

-rw-r----- 1 oracle oinstall 260186112 Apr  1 09:51 example01.dbf

-rw-r----- 1 oracle oinstall 765468672 Apr  1 10:04 system01.dbf

-rw-r----- 1 oracle oinstall 671096832 Apr  1 10:05 undotbs01.dbf

-rw-r----- 1 oracle oinstall 576724992 Apr  1 10:05 sysaux01.dbf

-rw-r----- 1 oracle oinstall  52429312 Apr  1 10:07 redo01.log

-rw-r----- 1 oracle oinstall   9748480 Apr  1 10:07 control01.ctl

-Now remove any one non system datafile, here i am deleting example01.dbf datafile from os level. If any non system datafile is missing database will not shut down it will run as usual,   we can find error in alertlog.

[oracle@localhost orcl]$ rm example01.dbf

Lets check in alerlog file.

[oracle@localhost ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

[oracle@localhost trace]$ tail -20f alert_orcl.log

   DELETE EXPIRED commands.

************************************************************************

Wed Apr 01 09:43:59 2015

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ckpt_3422.trc:

ORA-01171: datafile 5 going offline due to error advancing checkpoint

ORA-01116: error in opening database file 5

ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Wed Apr 01 09:44:01 2015

Checker run found 1 new persistent data failures

-Check the datafile status in database.

[oracle@localhost trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 09:44:27 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select FILE#,STATUS from v$datafile;

     FILE# STATUS

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

         1 SYSTEM

         2 ONLINE

         3 ONLINE

         4 ONLINE

         5 RECOVER

         6 ONLINE

6 rows selected.

SQL> exit

-Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

-restore datafile from backup location.

[oracle@localhost trace]$ cd

[oracle@localhost ~]$ cd /u01/app/hotbk/

[oracle@localhost hotbk]$ cp example01.dbf /u01/app/oracle/oradata/orcl/

-Connect to database and recover the datafile.

[oracle@localhost hotbk]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 09:49:51 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select FILE#,STATUS from v$datafile;

     FILE# STATUS

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

         1 SYSTEM

         2 ONLINE

         3 ONLINE

         4 ONLINE

         5 RECOVER

         6 ONLINE

SQL> alter database datafile 5 offline;

Database altered.

SQL> recover datafile 5;

Media recovery complete.

SQL> alter database datafile 5 online;

Database altered.

SQL> select FILE#,STATUS from v$datafile;

     FILE# STATUS

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

         1 SYSTEM

         2 ONLINE

         3 ONLINE

         4 ONLINE

         5 ONLINE

         6 ONLINE

6 rows selected.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

===============================================================

oracle network(listener,tnsnames,sqlnet,TNS_ADMIN)

Oracle Network Configuration

In its most basic form, Oracle uses three files (listener.ora, tnsnames.ora and sqlnet.ora) for network configuration. This article gives an example of each file as a starting point for simple network configuration.

Listener.ora

Tnsnames.ora

Sqlnet.ora

The tnsnames.ora and listener.ora are the two most important configuration files when it comes into configuring the listener.

Listener.ora:

The listerner.ora file contains server side network configuration parameters. It can be found in the ‘$ORACLE_HOME/network/admin’ directory on the server. Here is an example of a listener.ora file.

The oracle listener is cause of many issues when attempting to configure it for use. Because the listener is usually configured and then forgotten about it’s sometimes overlooked and only learnt about when there are errors. So I thought it would be good idea to cover some basics along with some error messages that you might come across which involve the listener and configuration of the listener.ora and tnsnames.ora files.

How the listener works:

The listener.ora file is resides in server side which allows you to connect to the server from client side. It listens for client side requests on an IP address that you specify in the listener.ora file on the server, using a default port of 1521. The port is also configurable. Once you make a request to connect to the database from your client machine, the listener will authenticate you with the database and then hand off your connection to the database server itself so that you are now connected directly to the server. At that point it doesn’t matter if the listener is working or not because you no longer pass any information through the listener. In short, the listener just passes off your connection to the database if you give the correct address, username and password and have sufficient privileges to make a connection to the DB.

Sample listener as bellow:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP) (HOST =192.168.1.12) (PORT = 1521))

      )

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ORCL.WORLD)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = ORCL)

    )

   )

After the "listener.ora" file is amended the listener should be restarted or reloaded to allow the new configuration to take effect.

$ lsnrctl stop

$ lsnrctl start

$ lsnrctl reload

Tnsnames.ora

The "tnsnames.ora" file contains client side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of a "tnsnames.ora" file.

The TNSNames.ora file is used to list alias names, IP addresses or DNS names and protocols to use when connecting to the listener. In simple terms, it’s like an address book for how your client will connect to the database. It has all the information needed to contact the listener.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.128)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

From this entry you can find out quite a lot of information. It shows you that there is an alias called TESTDB which is just the name that you would use in your connection string to connect to the DB.

Now let’s take a look at the listener.ora file and how that is configured.

To test the connection we can use tnsping <service name>

Here service name is name which is used in tnsnames.ora file. The example for the above tnsnames.ora is

$tnsping orcl

The successful test of the connection as follows.

[oracle@localhost ~]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-FEB-2015 05:14:35

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.128)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))

OK (140 msec)

SQLNET.ora

   The sqlnet.ora file is the profile configuration file. It resides on the client machines and the database server. Profiles are stored and implemented using this file. The database server can be configured with access control parameters in the sqlnet.ora file. These parameters specify whether clients are allowed or denied access based on the protocol.

    By default, the sqlnet.ora file is located in the $ORACLE_HOME/network/admin directory. The sqlnet.ora file can also be stored in the directory specified by the TNS_ADMIN environment variable.

It can be found in the "$ORACLE_HOME/network/admin" directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of a "sqlnet.ora" file.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

NAMES.DEFAULT_DOMAIN = <domain name>

# the following entry is necessary on Windows if OS authentication is required.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

A common setting to have in this file is the NAMES.DEFAULT_DOMAIN parameter, which controls what is appended to any connection string. It’s easiest to show this with an example so take a look at these connection strings which would usually be executed from a command prompt to connect to the database:

$Sqlplus user/password@orcl

$Sqlplus user/password@orcl.dev.com

The difference between the two is that the 1st one specifies a domain and the 2nd one doesn’t. If you had a SQLNET.ora file which had the entry NAMES.DEFAULT_DOMAIN = DEV.INT.COM in it then both of these connection strings would work. The idea is that by using this parameter you don’t have to specify the full database name and domain name each time you connect.

Testing:

Once the files are present in the correct location and amended as necessary the configuration can be tested using SQL*Plus by attempting to connect to the database using the appropriate username (SCOTT), password (TIGER) and service (ORCL).

$tnsping orcl

Now connect to database with service name.

$ sqlplus scott/tiger@orcl

Changing Location($TNS_ADMIN):

Default Location of those three files $ORACLE_HOME/network/admin if we need  to change this location we need to set TNS_ADMIN

Ex: if we need to place listener and tnsnames in /u01/TNS need to do follow.

export TNS_ADMIN=/u01/TNS

Copy listener,tnsnames and sqlnet.ora to this location. Restart the listener.