Saturday, August 6, 2016

Steps to remove incorrect entry from fstab in linux

Steps to remove incorrect entry from fstab in linux


Some time when there is in correct entry in the /etc/fstab then the linux server will not startup.
To fix this follow the below.


1. At the time of boot , in grub prompt hit the 'a' to append.
2. Enter  init=/bin/bash   at the end.
3. The system will now take you at bash#  prompt.
4. Enter  as    bash#  mount -o remount,rw /
5. Now open the /etc/fstab using vi and edit it and save it.
6. reboot


Saturday, March 19, 2016

Recovery of datafie without backup.

Recovery of datafie without backup.
-----------------------------------

Eg:-


-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 20 10:43:00 2016

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


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

SQL> select name from V$database;

NAME
---------
SGDATA

SQL> select name from V$datafile;

NAME
---------------------------------------------
/optware/oracle/oradata/sgdata/system01.dbf
/optware/oracle/oradata/sgdata/sysaux01.dbf
/optware/oracle/oradata/sgdata/undotbs01.dbf
/optware/oracle/oradata/sgdata/users01.dbf
/optware/oracle/oradata/sgdata/manzoor01.dbf

SQL> create tablespace ahamed datafile '/optware/oracle/oradata/sgdata/ahamed01.dbf' size 50M;

Tablespace created.

SQL> create table emp_1 (empno number, name varchar2(30)) tablespace ahamed;

Table created.

SQL> begin
  2  for i  in 1..1000 loop
  3  insert into emp_1 values (i, dbms_random.string('U',30));
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from emp_1;

  COUNT(*)
----------
      1000

SQL> select name from V$datafile;

NAME
---------------------------------------------
/optware/oracle/oradata/sgdata/system01.dbf
/optware/oracle/oradata/sgdata/sysaux01.dbf
/optware/oracle/oradata/sgdata/undotbs01.dbf
/optware/oracle/oradata/sgdata/users01.dbf
/optware/oracle/oradata/sgdata/manzoor01.dbf
/optware/oracle/oradata/sgdata/ahamed01.dbf

6 rows selected.

SQL> ! rm -f /optware/oracle/oradata/sgdata/ahamed01.dbf

SQL> shut abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2226336 bytes
Variable Size             335546208 bytes
Database Buffers         1224736768 bytes
Redo Buffers                7499776 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/optware/oracle/oradata/sgdata/ahamed01.dbf'


SQL> select status from V$instance;

STATUS
------------
MOUNTED



-- We dont have the backup of the datafile but the database is in archive log mode and
all the archives and redo logs which are generated after the datafile creation is availble in that case
we can just create a datafile using below command, the size and to which tablespace is belong and
other stuff will be picked by oracle automatically using controlfile / data dictionary.


SQL> alter database create datafile '/optware/oracle/oradata/sgdata/ahamed01.dbf';

Database altered.

SQL> alter database recover datafile '/optware/oracle/oradata/sgdata/ahamed01.dbf';

Database altered.

SQL> alter database open;

Database altered.


--DB is up now.

Another way as below.



SQL> ! rm -f /optware/oracle/oradata/sgdata/ahamed01.dbf

SQL> shut abort;
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2226336 bytes
Variable Size             335546208 bytes
Database Buffers         1224736768 bytes
Redo Buffers                7499776 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/optware/oracle/oradata/sgdata/ahamed01.dbf'


-- We can also use below command if the old path is having some issues and we required the file to be created on the new path.


syntax :- alter database cretae  datafile '/old-path' as '/new-path';
          alter database recover datafile '/new-path';


SQL> alter database create datafile '/optware/oracle/oradata/sgdata/ahamed01.dbf' as '/optware/oracle/oradata/sgdata/ahamed01_new.dbf';

Database altered.

SQL> alter database recover datafile '/optware/oracle/oradata/sgdata/ahamed01_new.dbf';

Database altered.

SQL> alter database open;

Database altered.

Monday, June 8, 2015

Sharing NFS filesystem in linux



Sharing NFS filesystem in linux
-------------------------------

1. Source server - standalone2
2. Target  server- rhel11gr2rac1


Sharing NFS file system will be very helpful in real time environment, its enables a particular
file-system to be mounted across the servers for easy access.

Lets see an example on show to share the NFS.



On Source server.
-----------------

1. Set up the yum repository environment.

2. Install the NFS rpm's using yum


[root@standalone2 ~]# yum -y install nfs-utils nfs-utils-lib
Loaded plugins: security
Setting up Install Process
Package 1:nfs-utils-1.0.9-54.el5.x86_64 already installed and latest version
Package nfs-utils-lib-1.0.8-7.6.el5.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package nfs-utils-lib.i386 0:1.0.8-7.6.el5 set to be updated
--> Processing Dependency: libgssapi.so.2(libgssapi_CITI_2) for package: nfs-utils-lib
--> Processing Dependency: libgssapi.so.2 for package: nfs-utils-lib
--> Running transaction check
---> Package libgssapi.i386 0:0.10-2 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
 Package                                     Arch                               Version                                      Repository                            Size
========================================================================================================================================================================
Installing:
 nfs-utils-lib                               i386                               1.0.8-7.6.el5                                Server                                55 k
Installing for dependencies:
 libgssapi                                   i386                               0.10-2                                       Server                                22 k

Transaction Summary
========================================================================================================================================================================
Install       2 Package(s)
Upgrade       0 Package(s)

Total download size: 77 k
Downloading Packages:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                   652 kB/s |  77 kB     00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : libgssapi                                                                                                                                        1/2
  Installing     : nfs-utils-lib                                                                                                                                    2/2

Installed:
  nfs-utils-lib.i386 0:1.0.8-7.6.el5

Dependency Installed:
  libgssapi.i386 0:0.10-2

Complete!

3. Create an directory and create some sample files.

[root@standalone2 public]# mkdir /shared_nfs

[root@standalone2 public]# cd /shared_nfs/
[root@standalone2 shared_nfs]# echo "File1" > file1.txt
[root@standalone2 shared_nfs]# echo "File2" > file2.txt
[root@standalone2 shared_nfs]# echo "File3" > file3.txt

[root@standalone2 shared_nfs]# ls -lrt
total 12
-rw-r--r-- 1 root root 6 Jun  9 03:35 file1.txt
-rw-r--r-- 1 root root 6 Jun  9 03:35 file2.txt
-rw-r--r-- 1 root root 6 Jun  9 03:35 file3.txt


4. Now we need to make the /shared_nfs directory to be shared, we need to update that in export file.

[root@standalone2 shared_nfs]# vi /etc/exports

/shared_nfs 192.168.0.20(rw,sync)



There /shared_nfs is the directory which we are going to share, 192.68.0.20 is the ip address of the target server,
(rw is read / write , or you can update as ro for read only , sync is for sync).



5. Start or restart the NFS service on the server.

[root@standalone2 public]# service nfs restart
Starting NFS services:                                     [  OK  ]
Starting NFS quotas:                                       [  OK  ]
Starting NFS daemon:                                       [  OK  ]
Starting NFS mountd:                                       [  OK  ]


if rhel6 if you get below error then start the rpcbind

[root@apache-datnd1 shared_nfs]# service nfs start
Starting NFS services:                                     [  OK  ]
Starting NFS quotas: Cannot register service: RPC: Unable to receive; errno = Connection refused
rpc.rquotad: unable to register (RQUOTAPROG, RQUOTAVERS, udp).
                                                           [FAILED]
Starting NFS mountd:                                       [FAILED]
Starting NFS daemon: rpc.nfsd: writing fd to kernel failed: errno 111 (Connection refused)
rpc.nfsd: unable to set any sockets for nfsd
                                                           [FAILED]


[root@apache-datnd1 shared_nfs]# service rpcbind start
Starting rpcbind:                                          [  OK  ]
[root@apache-datnd1 shared_nfs]#
[root@apache-datnd1 shared_nfs]#
[root@apache-datnd1 shared_nfs]#
[root@apache-datnd1 shared_nfs]# service nfs start
Starting NFS services:                                     [  OK  ]
Starting NFS quotas:                                       [  OK  ]
Starting NFS mountd:                                       [  OK  ]
Starting NFS daemon:                                       [  OK  ]





On Target Server
----------------

1. Install the NFS related rpm on the target server.



[root@rhel11gr2rac1 pub]# yum -y install nfs-utils nfs-utils-lib
Loaded plugins: rhnplugin, security
This system is not registered with RHN.
RHN support will be disabled.
Setting up Install Process
Package nfs-utils-lib-1.0.8-7.6.el5.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package nfs-utils.x86_64 1:1.0.9-54.el5 set to be updated
---> Package nfs-utils-lib.i386 0:1.0.8-7.6.el5 set to be updated
--> Processing Dependency: libgssapi.so.2(libgssapi_CITI_2) for package: nfs-utils-lib
--> Processing Dependency: libgssapi.so.2 for package: nfs-utils-lib
--> Running transaction check
---> Package libgssapi.i386 0:0.10-2 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
 Package                                     Arch                                 Version                                       Repository                         Size
========================================================================================================================================================================
Installing:
 nfs-utils-lib                               i386                                 1.0.8-7.6.el5                                 ser                                55 k
Updating:
 nfs-utils                                   x86_64                               1:1.0.9-54.el5                                ser                               404 k
Installing for dependencies:
 libgssapi                                   i386                                 0.10-2                                        ser                                22 k

Transaction Summary
========================================================================================================================================================================
Install       2 Package(s)
Upgrade       1 Package(s)

Total download size: 480 k
Downloading Packages:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                    23 MB/s | 480 kB     00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : libgssapi                                                                                                                                        1/4
  Installing     : nfs-utils-lib                                                                                                                                    2/4
  Updating       : nfs-utils                                                                                                                                        3/4
  Cleanup        : nfs-utils                                                                                                                                        4/4

Installed:
  nfs-utils-lib.i386 0:1.0.8-7.6.el5

Dependency Installed:
  libgssapi.i386 0:0.10-2

Updated:
  nfs-utils.x86_64 1:1.0.9-54.el5

Complete!


2. You can display the nfs which are shared from the source server using below.

[root@rhel11gr2rac1 shared_from_192.168.0.30]# showmount -e 192.168.0.30
Export list for 192.168.0.30:
/public     192.168.0.20
/shared_nfs 192.168.0.20


-- We could see that 2 file systems are shared from the source server.


3. Create a new directory on the taret server and you can use that to mount the filesystem.

[root@rhel11gr2rac1 shared_from_192.168.0.30]# mkdir /shared_file_nfs


[root@rhel11gr2rac1 shared_from_192.168.0.30]# mount 192.168.0.30:/shared_nfs /shared_file_nfs
[root@rhel11gr2rac1 shared_from_192.168.0.30]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              21G   18G  2.7G  87% /
/dev/sda1             289M   17M  258M   6% /boot
tmpfs                 816M     0  816M   0% /dev/shm
/dev/sdb1              25G   14G  9.8G  59% /oracle
192.168.0.30:/shared_nfs
                       21G   14G  6.4G  68% /shared_file_nfs



-- Now we could see that the NFS is mounted on the target server.












Thursday, June 4, 2015

Linux Runlevels and using it for Automated Startup or Shutdown



This example is based on the version Linux 5.

A runlevel defines the state of the machine after boot. In Simple we can say as the mode in which you want the system to be restated, it is just like in windows while the system is booting up we will have option like start with safe mode, Networking and so on.

Different runlevels are typically assigned to the single-user mode,
multiuser mode without network services started, multiuser mode with network services started, system shutdown, and system reboot system states.

The different run level action can be viewed from as below.

# cat /etc/inittab


#####################################################################
# Default runlevel. The runlevels used by RHS are:
#   0 - halt (Do NOT set initdefault to this)
#   1 - Single user mode
#   2 - Multiuser, without NFS (The same as 3, if you do not have networking)
#   3 - Full multiuser mode
#   4 - unused
#   5 - X11
#   6 - reboot (Do NOT set initdefault to this)
#####################################################################


As you can see for 0 and 6 it is mentioned as do not set, this is because we dont want our system to shutdown (runlevel 0) or
reboot (runlevel 6) immediately once our system boots.

To know the default run level in which your system is configured type as below.

[root@standalone2 rc0.d]# runlevel
N 5

We get the output as 5 which means by default the system is configured to start at run level 5.

you can also see that there are directories like rc0.d, rc1.d, rc2.d, rc3.d, rc4.d, rc5.d, rc6.d under the /etc folders, these directories contains the files/scripts which to be called during the runlevel. Normally the files under this will start with S which is for start and K which is for to kill the process/service.

Using these run levels you can configure your script to start/stop services during shutdown/start/reboot of the system.


Now here my system is running in runlevel 5, now i need to schedule a script to do some action based on runlevels.

Parameter will be passed as start with S and parameter stop will be passed with K automatically by the system.


Now we need to configure the script like as below.

Runlevel 0  - stop
Runlevel 1  - stop
Runlevel 2  - stop
Runlevel 3  - stop
Runlevel 4  - stop
Runlevel 5  - start
Runlevel 6  - stop


Below is the sample script. Named as testme

#################################################################
#!/bin/bash
#
#
case $1 in
"start")
        echo `date` >> /home/oracle/startup.log
        echo 'startup' >> /home/oracle/startup.log
        ;;
"stop")
        echo `date` >> /home/oracle/shutdown.log
        echo 'shutdown' >> /home/oracle/shutdown.log
        ;;
esac
#################################################################

Copy the sciprt inside the /etc/init.d directory.

We will give a sequence number as 40 for this script.

Now we need to create a symbolic link file under the appropirate run level directories.


[root@standalone2 init.d]# cd /etc/rc0.d/
[root@standalone2 rc0.d]# ln -s /etc/init.d/testme K40testme
[root@standalone2 rc0.d]# ls -lrt K40testme
lrwxrwxrwx 1 root root 18 Jun  4 04:10 K40testme -> /etc/init.d/testme

Create the same in all the directories except rc5.d.

K40testme ( Refers K for kill which will pass the parameter as stop , 40 is the sequence or order in which the script
has to be executed, testme is the mainfile)

[root@standalone2 rc1.d]# cd ../rc2.d/
[root@standalone2 rc2.d]# ln -s /etc/init.d/testme K40testme
[root@standalone2 rc2.d]# cd ../rc3.d/
[root@standalone2 rc3.d]# ln -s /etc/init.d/testme K40testme
[root@standalone2 rc3.d]# cd ../rc4.d/
[root@standalone2 rc4.d]# ln -s /etc/init.d/testme K40testme
[root@standalone2 rc4.d]# cd ../rc6.d/
[root@standalone2 rc6.d]# ln -s /etc/init.d/testme K40testme

and in rc5.d create as below.

[root@standalone2 rc5.d]# ln -s /etc/init.d/testme S40testme
[root@standalone2 rc5.d]# ls -lrt S40*
lrwxrwxrwx 1 root root 18 Jun  4 04:13 S40testme -> /etc/init.d/testme



As you can see the testme script is showing as off for all the run levels except 5.

Lets test the script now.

Now am running runing runlevel 6 (init 6) which will reboot the system, also as per
our script it should first call the stop and while booting it will call the runlevel 5 so
our script should start.

[root@standalone2 rc5.d]# init 6

== Once the server is up we could see that the script has been executed successfully.

[oracle@standalone2 ~]$ cd /home/oracle
[oracle@standalone2 ~]$ ls -lrt startup.log
-rw-r--r-- 1 root root 144 Jun  4 05:23 startup.log

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

Instead of manually creating links we can use chkconfig to automate the process.



Here we will use script to bring up an oracle database online when system starts.

Oracle database comes with default script dbstart and dbshut will can be used for this purpose.

to use this, create a script as below.


#!/bin/bash
#
# chkconfig: 35 99 10
# description: Auto start/stop of database
#
# Set ORA_HOME to the Oracle Home where the lsnrctl and dbstart
# commands can be found
ORA_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
# Set ORA_OWNER to the owner of the Oracle software
ORA_OWNER=oracle
LCKFILE=/var/lock/subsys/ora_db_start
case "$1" in
    'start')
           # Start the listener:
  touch $LCKFILE
           su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
           # Start the databases:
           su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
    ;;
    'stop')
           # Stop the listener:
  rm -f $LCKFILE
           su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
           # Stop the databases:
           su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
    ;;
esac



Save the filename as ora_db_start in /etc/init.d


Now we need to set the run levels of this.

You can either manually create links under the rc folders or can use chkconfig which will automatically creates
the links files.

# chkconfig --add ora_db_start


-- you can see that the script ora_db_start it contains chkconfig: 35 99 10 where 35 is the 3 and 5 runlevels which
should be on and other run levels should be off, 99 is the Start sequence and 10 is the kill sequence.


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

You can also create a user defined script if required, as per below.


Create a script as oracle_db under /etc/init.d/ with below contents.

#!/bin/bash
#
# chkconfig: 35 99 10
# description: Starts and stops the Oracle listener and database
#
# Define variables for use in this script
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
ORA_OWNER=oracle
case "$1" in
    'start')
           touch /var/lock/subsys/oracle_db
           # Start the databases:
           su - oracle -c $ORACLE_HOME/bin/db_startup.sh
    ;;
    'stop')
           rm -f /var/lock/subsys/oracle_db
           # Stop the databases:
           su - oracle -c $ORACLE_HOME/bin/db_shutdown.sh
        ;;
esac



Create the below scripts as oracle user under the $ORACLE_HOME/bin directory.


db_startup.sh

#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
export ORACLE_SID=testdb
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus -s "/ as sysdba" <> /u01/app/oracle/product/11.2.0.3/dbhome_1/db_startup.log
startup;
exit;
EOF


db_shutdown.sh

#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
export ORACLE_SID=testdb
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus -s "/ as sysdba" <> /u01/app/oracle/product/11.2.0.3/dbhome_1/db_shutdown.log
shutdown immediate;
exit;
EOF


Now set the run levels for the script.


[root@standalone2 ~]# chkconfig --add oracle_db

[root@standalone2 ~]# chkconfig --list oracle_db
oracle_db       0:off   1:off   2:off   3:on    4:off   5:on    6:off

As you can see the run level are set on for 3 and 5 and for the remaining it is off, you can also
verify the symbolic link files.


Now Lets check whether it is working as expected, currently the db is up and running.


[root@standalone2 ~]# init 6


--Once the server is up we could see that the db is up and running.

verifying the logs.

[oracle@standalone2 dbhome_1]$ ls -lrt *.log

-rw-r--r-- 1 oracle oinstall   65 Jun  4 08:07 db_shutdown.log
-rw-r--r-- 1 oracle oinstall  466 Jun  4 08:10 db_startup.log

[oracle@standalone2 dbhome_1]$ cat db_shutdown.log
Database closed.
Database dismounted.
ORACLE instance shut down.


[oracle@standalone2 dbhome_1]$ cat db_startup.log
ORACLE instance started.

Total System Global Area  680607744 bytes
Fixed Size                  2231472 bytes
Variable Size             406848336 bytes
Database Buffers          268435456 bytes
Redo Buffers                3092480 bytes
Database mounted.
Database opened.
ORACLE instance started.


-- Run levels are working fine as expected.










Wednesday, January 28, 2015

Recovering the primary database using the backup taken from the Standby Database

Recovering the primary database using the backup taken from the Standby Database
===============================================================================

1) Take the backup from the standby database.

[oracle@standalone2 bkp]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 29 03:49:35 2015

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

connected to target database: SOURCE (DBID=2931643800)

RMAN> backup as compressed backupset full database format '/u01/app/oracle/bkp/full_bkp_%T_%U.bkp' ;

Starting backup at 29-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/source/undotbs01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/source/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/source/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/source/test_tbls_01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/source/users01.dbf
channel ORA_DISK_1: starting piece 1 at 29-JAN-15
channel ORA_DISK_1: finished piece 1 at 29-JAN-15
piece handle=/u01/app/oracle/bkp/full_bkp_20150129_16pttekb_1_1.bkp tag=TAG20150129T035243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:06
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-JAN-15
channel ORA_DISK_1: finished piece 1 at 29-JAN-15
piece handle=/u01/app/oracle/bkp/full_bkp_20150129_17ptteq5_1_1.bkp tag=TAG20150129T035243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 29-JAN-15

RMAN> exit


Recovery Manager complete.


-- Now copy the backup pieces to the primary site.

[oracle@standalone2 bkp]$ ls -lrt
total 284192
-rw-r----- 1 oracle oinstall 289587200 Jan 29 03:55 full_bkp_20150129_16pttekb_1_1.bkp
-rw-r----- 1 oracle oinstall   1130496 Jan 29 03:55 full_bkp_20150129_17ptteq5_1_1.bkp

[oracle@standalone2 bkp]$ scp * oracle@rhel11gr2rac1:/oracle/bkp/
oracle@rhel11gr2rac1's password:
full_bkp_20150129_16pttekb_1_1.bkp                                                                                                    100%  276MB   6.4MB/s   00:43
full_bkp_20150129_17ptteq5_1_1.bkp                                                                                                    100% 1104KB   1.1MB/s   00:00


-- Now in primary database remove one datafile (Test case)


SQL> select name from V$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/source/system01.dbf
/u01/app/oracle/oradata/source/sysaux01.dbf
/u01/app/oracle/oradata/source/undotbs01.dbf
/u01/app/oracle/oradata/source/users01.dbf
/u01/app/oracle/oradata/source/test_tbls_01.dbf


SQL> ! rm -f /u01/app/oracle/oradata/source/test_tbls_01.dbf

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 12508
Session ID: 37 Serial number: 57


SQL> startup
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             289407256 bytes
Database Buffers           16777216 bytes
Redo Buffers                4747264 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/source/test_tbls_01.dbf'


-- Now lets recover the datafile.


[oracle@rhel11gr2rac1 bkp]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 29 12:29:16 2015

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

connected to target database: SOURCE (DBID=2931643800, not open)

RMAN> catalog start with '/oracle/bkp/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/bkp/

List of Files Unknown to the Database
=====================================
File Name: /oracle/bkp/full_bkp_20150129_16pttekb_1_1.bkp
File Name: /oracle/bkp/full_bkp_20150129_17ptteq5_1_1.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/bkp/full_bkp_20150129_16pttekb_1_1.bkp
File Name: /oracle/bkp/full_bkp_20150129_17ptteq5_1_1.bkp


RMAN> restore datafile 5;

Starting restore at 29-JAN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 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 00005 to /u01/app/oracle/oradata/source/test_tbls_01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/bkp/full_bkp_20150129_16pttekb_1_1.bkp
channel ORA_DISK_1: piece handle=/oracle/bkp/full_bkp_20150129_16pttekb_1_1.bkp tag=TAG20150129T035243
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 29-JAN-15

RMAN> recover datafile 5;

Starting recover at 29-JAN-15
using channel ORA_DISK_1

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

Finished recover at 29-JAN-15

RMAN> exit;

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


SQL> select status from V$instance;

STATUS
------------
MOUNTED



SQL> alter database open;

Database altered.


SQL> alter database datafile 5 online;

Database altered.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST_TBLS                      ONLINE

6 rows selected.

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


Flashback usage on Data guard for Data Recover and Re-instate Standby database after failover

Flashback usage in Standby for Data recovery
==================================

1) Enable flashback on primary and standby database.



SQL> select DB_UNIQUE_NAME, FLASHBACK_ON from V$database;

DB_UNIQUE_NAME                 FLASHBACK_ON
------------------------------ ------------------
source                         YES


SQL> select DB_UNIQUE_NAME, FLASHBACK_ON from V$database;

DB_UNIQUE_NAME                 FLASHBACK_ON
------------------------------ ------------------
sourstby                       YES



Primary
=======

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      5000

SQL> delete from test_user.emp where rownum < 2000;

1999 rows deleted.

SQL> commit;

Commit complete.


SQL> alter system switch logfile;

System altered.

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      3001



In Standby
==========

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      3001


-- In primary it has been deleted erroronously.

Lets restore the database back in the standby site.


SQL> alter database recover managed standby database cancel;

Database altered.


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit

SQL> startup mount;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             230687000 bytes
Database Buffers           75497472 bytes
Redo Buffers                4747264 bytes
Database mounted.

SQL> flashback database to timestamp to_timestamp('29-JAN-15 00:30:00','DD-MON-YY HH24:MI:SS') ;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      5000

-- Now export the table and import in the primary.



[oracle@standalone2 ~]$ exp file=emp_tbl.dmp tables=test_user.emp log=emp_tbl.log

Export: Release 11.2.0.3.0 - Production on Thu Jan 29 02:27:44 2015

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


Username: / as sysdba


About to export specified tables via Conventional Path ...
Current user changed to TEST_USER
. . exporting table                            EMP       5000 rows exported
Export terminated successfully without warnings.


[oracle@standalone2 ~]$ exp file=emp_tbl.dmp tables=test_user.emp log=emp_tbl.log

Export: Release 11.2.0.3.0 - Production on Thu Jan 29 02:27:44 2015

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


Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to TEST_USER
. . exporting table                            EMP       5000 rows exported
Export terminated successfully without warnings.


scp the dmp file to the prod.


Truncate the table in prod


SQL> truncate table test_user.emp;

Table truncated.


-- Import the dmp file.

[oracle@rhel11gr2rac1 oracle]$ imp file=emp_tbl.dmp tables=emp fromuser=test_user touser=test_user ignore=y

Import: Release 11.2.0.3.0 - Production on Thu Jan 29 10:39:25 2015

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

Username: / as sysdba

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

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing TEST_USER's objects into TEST_USER
. . importing table                          "EMP"       5000 rows imported
Import terminated successfully without warnings.


Now start the MRP process in standby

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      3001
SQL> /

  COUNT(*)
----------
      3001

SQL> /

  COUNT(*)
----------
      3001

SQL> /
/select count(*) from test_user.emp
*
ERROR at line 1:
ORA-08103: object no longer exists

SQL> /

  COUNT(*)
----------
      5000

-- Now both the db are in sync





====*********************************************================================================================



Flashback usage to re-instate the standby database after the failover
=====================================================================


Create standby redologs on both primary and standby logs.

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/source/stdb_redo0.log') size 52428800;

Database altered.

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/source/stdb_redo05.log') size 52428800;

Database altered.

SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/source/stdb_redo06.log') size 52428800;

Database altered.


SQL> select * from V$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
         4 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED
         5 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED
         6 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED


-- Now

Standby

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/source/stdb_redo0.log') size 52428800;

Database altered.

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/source/stdb_redo05.log') size 52428800;

Database altered.

SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/source/stdb_redo06.log') size 52428800;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

    THREAD       LAST
---------- ----------
         1        123


SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SQL> alter database open;

Database altered.


SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
2937170


On the primary flashback the database to the above scn.



SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             301990168 bytes
Database Buffers            4194304 bytes
Redo Buffers                4747264 bytes
Database mounted.


SQL> flashback database to scn 2937170;

Flashback complete.


SQL> alter database flashback off;

Database altered.

-- Flashback off will delete all the flashback logs, since we will be creating a standby controlfile and mounting it, it will
overwrite all the flashback details will become obsolete, so we can turn off the flashback.


SQL> alter database flashback off;

Database altered.

=== For conver this database to physical standby either we can create a standby controlfile on the database and over write
the existing controlfiles or can execute the command to conver to physcial stanby

===============================================================================
SQL> alter database create standby controlfile as '/home/oracle/ctl_file.ctl';

Database altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> exit

SQL> startup mount;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             301990168 bytes
Database Buffers            4194304 bytes
Redo Buffers                4747264 bytes
Database mounted.

SQL> select database_role from V$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

-- Overwrite the existing controlfile with the created standby controlfile.

[oracle@rhel11gr2rac1 ~]$ cp ctl_file.ctl /u01/app/oracle/oradata/source/control01.ctl
[oracle@rhel11gr2rac1 ~]$ cp ctl_file.ctl /u01/app/oracle/fast_recovery_area/source/control02.ctl

==============================(or)=============================================

SQL> alter database convert to physical standby ;

Database altered.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             230687000 bytes
Database Buffers           75497472 bytes
Redo Buffers                4747264 bytes
Database mounted.

SQL> select database_role from V$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY


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



-- Now enable the archive dest 2 in the current primay database.

SQL> show parameter dest_state_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE


-- Now start the MRP in the current standby db.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.



SQL> select database_role from V$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY



SQL> alter database recover managed standby database cancel;

Database altered.


SQL> alter database flashback on;

Database altered.


SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


In prod

======

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      3001


SQL> delete from test_user.emp where rownum < 1000;

999 rows deleted.

SQL> commit;

Commit complete.


In Current Standby:=
====================

Now check the propogation in standby.

SQL> select count(*) from test_user.emp;

  COUNT(*)
----------
      3001

SQL> /

  COUNT(*)
----------
      2002


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




Tuesday, January 27, 2015

Recovering Physical Standby using Incremental backup of Primary

Recovering Physical Standby using Incremental backup of Primary
===============================================================

Scenario :- Physical standby went out of sync from the primary as some of the archive logs are missing,
even those archivelog is deleted in the primary as well and no backup is available. To overcome this
we can take the incremental backup from primary and apply to the standby to keep it is in sync.


Primary DB

SQL> select current_scn from V$database;

CURRENT_SCN
-----------
    2923116


SQL> select max(sequence#) from V$archived_log where applied = 'YES' and dest_id = 2;

MAX(SEQUENCE#)
--------------
            91


Standby DB
----------

SQL> select current_scn from V$database;

CURRENT_SCN
-----------
    2921713


SQL> select process, STATUS, sequence# from V$managed_standby where process like '%MRP%';

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      WAIT_FOR_GAP         92


SQL> SELECT * FROM v$ARCHIVE_GAP;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1            92            113


--


As per the above we could see that there is a gap between 93 and 113 sequence which is missing on the standby site,
the current scn in standby site is 2921713, around 20 archive logs are missing also it is not availble in backup
in the primary site. Lets take the incremental backup from the primary site.


RMAN> run
{
allocate channel a1 device type disk format '/oracle/scn_incre_bkp_%U_%T.bkp';
backup incremental from scn 2921713 database;
}

using target database control file instead of recovery catalog
allocated channel: a1
channel a1: SID=34 device type=DISK

Starting backup at 28-JAN-15

backup will be obsolete on date 04-FEB-15
archived logs will not be kept or backed up
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/source/undotbs01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/source/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/source/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/source/test_tbls_01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/source/users01.dbf
channel a1: starting piece 1 at 28-JAN-15
channel a1: finished piece 1 at 28-JAN-15
piece handle=/oracle/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp tag=TAG20150128T102454 comment=NONE
channel a1: backup set complete, elapsed time: 00:02:05

backup will be obsolete on date 04-FEB-15
archived logs will not be kept or backed up
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
including current control file in backup set
channel a1: starting piece 1 at 28-JAN-15
channel a1: finished piece 1 at 28-JAN-15
piece handle=/oracle/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp tag=TAG20150128T102454 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JAN-15
released channel: a1


-- Take the current controlfile trace for standby.


SQL> alter database create standby controlfile as '/oracle/for_standby_ctl.ctl';

Database altered.


--


scp the controlfile and the backup piece to the standby location.

[oracle@rhel11gr2rac1 oracle]$ scp *.bkp oracle@standalone2.manzoor.com:/u01/app/oracle/bkp/
oracle@standalone2.manzoor.com's password:
scn_incre_bkp_13ptrh7n_1_1_20150128.bkp                                                                                               100% 2552KB   2.5MB/s   00:00
scn_incre_bkp_14ptrhbl_1_1_20150128.bkp                                                                                               100% 9792KB   9.6MB/s   00:01

[oracle@rhel11gr2rac1 oracle]$ scp for_standby_ctl.ctl oracle@standalone2.manzoor.com:/u01/app/oracle/bkp/
oracle@standalone2.manzoor.com's password:
for_standby_ctl.ctl                                                                                                                   100% 9744KB   9.5MB/s   00:01
[oracle@rhel11gr2rac1 oracle]$


On standby, stop the MRP process , shut down db and start with the controlfile which been copied from the primary site.


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------------------------------------------------------------------
control_files                        string      /u01/app/oracle/oradata/source/control01.ctl, /u01/app/oracle/fast_recovery_area/source/control02.ctl

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


-- Overwrite the old controlfiles with the new one.



[oracle@standalone2 bkp]$ cp for_standby_ctl.ctl /u01/app/oracle/oradata/source/control01.ctl
[oracle@standalone2 bkp]$ cp for_standby_ctl.ctl /u01/app/oracle/fast_recovery_area/source/control02.ctl


[oracle@standalone2 bkp]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 28 03:04:45 2015

Copyright (c) 1982, 2011, 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     313159680 bytes

Fixed Size                     2227944 bytes
Variable Size                226492696 bytes
Database Buffers              79691776 bytes
Redo Buffers                   4747264 bytes


-- catalog the backup piece


RMAN> catalog start with '/u01/app/oracle/bkp/scn';

Starting implicit crosscheck backup at 28-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
Crosschecked 17 objects
Finished implicit crosscheck backup at 28-JAN-15

Starting implicit crosscheck copy at 28-JAN-15
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 28-JAN-15

searching for all files in the recovery area
cataloging files... files cataloged

searching for all files that match the pattern /u01/app/oracle/bkp/scn

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/bkp/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp
File Name: /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/bkp/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp
File Name: /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp

-- Start the recover.

RMAN> recover database;

Starting recover at 28-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/source/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/source/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/source/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/source/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/source/test_tbls_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp tag=TAG20150128T102454
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

starting media recovery

archived log for thread 1 with sequence 116 is already on disk as file /u01/app/oracle/archive/1_116_868359284.dbf
archived log file name=/u01/app/oracle/archive/1_116_868359284.dbf thread=1 sequence=116
unable to find archived log
archived log thread=1 sequence=117
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/28/2015 03:07:01
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 117 and starting SCN of 2928165


-- Now the restore is completed.

-- Lets check the scn now.

SQL> select current_scn from V$database;

CURRENT_SCN
-----------
    2928164

-- Now start the MRP process, both primary and standby are in sync now.