Search This Blog

Total Pageviews

Sunday 22 May 2016

Oracle Activate Physical Standby Database

Oracle Activate Physical Standby Database

On Primary ... 

alter system set log_archive_dest_state_2='defer' scope=both;

select name, open_mode, database_role from v$database;




On Standby ...

-bash-3.2$ . oraenv
ORACLE_SID = [prims] ?
The Oracle base remains unchanged with value /u01/app/oracle
-bash-3.2$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 14 20:40:21 2016

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

Connected to an idle instance.

SQL> startup mount ;
ORACLE instance started.

Total System Global Area  680607744 bytes
Fixed Size                  2256352 bytes
Variable Size             469762592 bytes
Database Buffers          205520896 bytes
Redo Buffers                3067904 bytes
Database mounted.


SQL> alter database activate standby database;

Database altered.

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


Database dismounted.
ORACLE instance shut down.

SQL> startup ;
ORACLE instance started.

Total System Global Area  680607744 bytes
Fixed Size                  2256352 bytes
Variable Size             469762592 bytes
Database Buffers          205520896 bytes
Redo Buffers                3067904 bytes
Database mounted.


Database opened.

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PRIM      READ WRITE           PRIMARY

SQL> def
DEFINE _DATE           = "14-MAY-16" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "prims" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
DEFINE _EDITOR         = "/usr/bin/vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000400" (CHAR)


Now Database is open ...

SQL> select * from dual;

D
-
X

Oracle 11g/12c automatically start/stop on Linux after server reboot

Oracle 11g/12c automatically start /stop on Linux  after server reboot 



cat /etc/oratab

#
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
ORAL12:/u01/app/oracle/product/12.1.0/db_1:Y   <<<<<-- for autostart set to Y




create following file on this dir as oracle  

[oracle@ora12c scripts]$ pwd
/home/oracle/scripts


[oracle@ora12c scripts]$ ls -ltr
total 8
-rwxr--r--. 1 oracle oinstall 266 May 21 19:52 ora_start.sh
-rwxr--r--. 1 oracle oinstall 261 May 21 19:53 ora_stop.sh


[oracle@ora12c scripts]$ cat ora_start.sh
#!/bin/bash

# script to start the Oracle database, listener and dbconsole

. ~/.bash_profile

# start the listener and the database
$ORACLE_HOME/bin/dbstart $ORACLE_HOME

# start the Enterprise Manager db console --- Change based on your requirement 
#$ORACLE_HOME/bin/emctl start dbconsole

exit 0


[oracle@ora12c scripts]$ cat ora_stop.sh
#!/bin/bash

# script to stop the Oracle database, listener and dbconsole

. ~/.bash_profile

# stop the Enterprise Manager db console   --- Change based on your requirement 
# $ORACLE_HOME/bin/emctl stop dbconsole

# stop the listener and the database
#$ORACLE_HOME/bin/dbshut $ORACLE_HOME

exit 0



give execute rights to the scripts:

chmod u+x ora_start.sh ora_stop.sh



create this file as root !!!



create "oracle" file in  /etc/init.d/

[root@ora12c ~]# cat  /etc/init.d/oracle
#!/bin/bash
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.

# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_OWNER=oracle
RETVAL=0

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "/home/oracle/scripts/ora_start.sh"
        touch /var/lock/subsys/oracle
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "/home/oracle/scripts/ora_stop.sh"
        rm -f /var/lock/subsys/oracle
        ;;
    *)
        echo $"Usage: $0 {start|stop}"
        RETVAL=1
esac
exit $RETVAL



as root 
chmod 750 /etc/init.d/oracle


then as a root add service 

chkconfig --add oracle

[root@ora12c ~]# chkconfig --list|grep -i oracle
oracle          0:off   1:off   2:off   3:on    4:on    5:on    6:off

chkconfig --list | grep '3:on\|4:on\|5:on'  |awk '{print $1}' | sort

Then check start and stop as a root 

[root@ora12c ~]# service oracle start
Processing Database instance "ORAL12": log file /u01/app/oracle/product/12.1.0/db_1/startup.log

ps -ef|egrep -i 'tns|smon'

oracle    9981     1  0 11:46 ?        00:00:00 /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr LISTENER -inherit
oracle   10102     1  0 11:46 ?        00:00:00 ora_smon_ORAL12
root     10435  7926  0 11:53 pts/1    00:00:00 grep -E -i tns|smon


service oracle stop
Processing Database instance "ORAL12": log file /u01/app/oracle/product/12.1.0/db_1/shutdown.log



now checking  on reboot
[root@ora12c ~]# init 6 ;



[root@ora12c ~]# ps -ef|egrep -i 'tns|smon'
root        15     2  0 12:47 ?        00:00:00 [netns]
oracle    4191     1  1 12:48 ?        00:00:01 /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr LISTENER -inherit
oracle    4341     1  0 12:49 ?        00:00:00 ora_smon_ORAL12
root      4603  4283  0 12:50 pts/1    00:00:00 grep -E -i tns|smon




listener.ora File

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.XXX.0.36)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=ORAL12)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME=ORAL12))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)
      (PROGRAM=extproc)))

=======
or


[root@vbgeneric init.d]# pwd
/etc/init.d

[root@vbgeneric init.d]# ls -ltr oracle
-rwxr-xr-x 1 root root 1957 Jun  2  2016 oracle



 cat oracle
#!/bin/sh
#
# chkconfig: 2345 99 99

#
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_HOME_LISTENER=$ORACLE_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
#export JAVA_HOME=$ORACLE_HOME/jdk
export PATH=$PATH:$ORACLE_HOME/bin:$JAVA_HOME/bin
export ORACLE_SID=orcl12c
#export ORACLE_TRACE=Y

export PATH=$JAVA_HOME/bin:$ORACLE_HOME/bin:$PATH

# Source function library.
. /etc/rc.d/init.d/functions

# See how we were called.
case "$1" in
  start)
        echo "1" > /proc/sys/net/ipv4/ip_forward

        # Route 80 -> 8888 for XDB
        iptables -t nat -A PREROUTING -m tcp  -p tcp --dport 80 -j REDIRECT --to-port 8888
        iptables -t nat -A PREROUTING -m tcp  -p tcp --dport 21 -j REDIRECT --to-port 2121

        su  oracle -c "$ORACLE_HOME/bin/lsnrctl start"
        su  oracle -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
        su  oracle -c "echo alter pluggable database all open';'|$ORACLE_HOME/bin/sqlplus / as sysdba"
        su  oracle -c "/home/oracle/bin/ords.sh start /home/oracle/ords/ords.war"
        ;;
  stop)
        su  oracle -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl stop"
        su  oracle -c "/home/oracle/bin/ords.sh stop /home/oracle/ords/ords.war"
        ;;
  restart|reload)
        su  oracle -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl stop"
        su  oracle -c "/home/oracle/bin/ords.sh stop /home/oracle/ords/ords.war"
        su  oracle -c "$ORACLE_HOME/bin/lsnrctl start"
        su  oracle -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
        su  oracle -c "echo alter pluggable database all open';'|$ORACLE_HOME/bin/sqlplus / as sysdba"
        su  oracle -c "/home/oracle/bin/ords.sh start /home/oracle/ords/ords.war"
        ;;
  status)
        $ORACLE_HOME/bin/lsnrctl status
        ;;
  *)
        echo $"Usage: $0 {start|stop|restart|reload}"
        exit 1
esac

exit 0





Oracle DBA

anuj blog Archive