Shell Script Basics

 Shell Script Basics

Let us start the process in this Blog i am going to explain  Shell Script Basics and shell script  if condition statement ,else if condition statement, and or operators in bash shell 

Introduction

Shell: When ever you login to  a unix system you are placed in a program called the shell acts as a command interpreter , it takes each command and passes it to the operating system kernel to be acted upon it then displays the results of this operation on completion on your screen

DIFFERENT FLAVOURS OF SHELL IN UNIX

Bourne Shell: Bourne Shell scripts to specify the shell to use for the scripts. The default prompt on the Unix for this is $

#!/bin/bsh

C Shell:C Shell Scripts to specify the shell to use for the scripts.The default prompt on the Unix for this is %,  

#!/bin/csh

Korn Shell: Korn Shell Scripts  to specify the shell to use for the scripts.The default prompt on the Unix for this is $,

#!/bin/ksh

Shell Script: Shell Scripts collection of command which are executed in a order given. There are conditional statement and looping also available like if ,while which helps in finding if a particular value is greater than another value .

To write any comments in the shell scripts ,it has to be written with # preceded

Example

# Author of the script is

There are some variables Shell Script Basics which are set internally by the shell and which are available to the user

$1 – $9 Variables are the positional parameters.

$0 Name of the command currently being executed.

$# Number of positional arguments given to this invocation of the shell.

$? Exit status of the last command executed is given as a decimal string. When a command completes successfully, it returns the exit status of 0 (zero), otherwise it returns a non-zero exit status.

$$ Process number of this shell – useful for including in filenames, to make them unique.

$! Process id of the last command run in the background.

$* String containing all the arguments to the shell, starting at $1.

Shell scripts and functions are both interpreted. This means they are not compiled.

Commands in Shell

All shell have a number of built in command which are executed in the shell owns process like echo ,cd,  when you enter a command first it will check the built in shell command echo or cd  or it is directly interpreted by the shell,if the command begins with  / shell assumes that command is absolute path name.

unix commands are executabale binary files located in directories with the name bin (for binary) many of the commands that are located in the directory /usr/bin.

Typical path variable might be 

/bin:/usr/bin:/usr/local/utils/bin:$HOME/bin

Shell Script Example

cat chaitanya.sh

#Written by ChaitanyaOracleDbaBlog

TODAY=date '+%m%d%y_%H%M'

echo "This is Chaitanya  at" $TODAY

The Name of each script must reflect its use,and each script must have a suffix that describes the what type of script it is “.sh” for Bourne shell ,“.ksh” for Korn shell or “.cgi” for Common Gateway Interface scripts

Lets start the first shell script

#!/bin/ksh   # This script displays the date, time, username and

# current directory.

# Author Chaitanya

echo "Current date and time is:"

date

echo

echo "Your username is: `whoami` \\n"

echo "Your current directory is: \\c"

pwd echo "Your system name is :`hostname`\\n"   

How to execute the shell script 

Once the shell scripts are created ,they can be executed in two ways 

1. We specify the shell type and then the script name 

 sh  chaitanya.sh

2.We change the permission of the shell scripts to perform execution

 chmod  +x chaitanya.sh

 ./ chaitanya.sh

Bash if statement syntax

1.Bash if ...then..fi statement

if [ conditional expression ]

then

statement1

statement2

.

fi

Bash if then fi example

#!/bin/bash

count=300

if [ $count -eq 300 ]

then

  echo "The Count is 300"

fi

2. Bash if ..then..else..fi statement  syntax

If [ conditional expression ]

then

statement1

statement2

.

else

statement3

statement4

.

fi

Bash if ..then..else..fi statement  Example

#!/bin/bash

count=299

if [ $count -eq 300 ]

then

  echo " The Count is 300"

else

  echo "The Count is not 300"

fi

3. Bash If....elif...else..fi statement syntax

If [ conditional expression1 ]

then

statement1

statement2

.

elif [ conditional expression2 ]

then

statement3

statement4

.

else

statement5

fi

Bash If....elif...else..fi statement Example

#!/bin/bash

count=299

if [ $count -eq 300 ]

then

  echo "Count is 300"

elif [ $count -gt 300 ]

then

  echo "Count is greater than 300"

else

  echo "Count is less than 300"

fi

4. Bash If..then..else..if..then..fi..fi.. syntax

If [ conditional expression1 ]

then

statement1

statement2

.

else

if [ conditional expression2 ]

then

statement3

.

fi

fi

Bash If..then..else..if..then..fi..fi.. Example

#!/bin/bash

count=299

if [ $count -eq 300 ]

then

  echo " The Count is 300"

else

  if [ $count -gt 300 ]

  then

    echo "The Count is greater than 300"

  else

  echo "The Count is less than 300"

  fi

fi

Test for numbers

-eq ------->   equal to--------->   x==y

-ge-------->  greater than or equal to--->  x>=y

-gt --------> greater than    -----> x>y     

-le ---------> less than or equal to  ------>   x<=y

-lt ---------> less than------> x<y

-ne ---------> not equal to ---->   x!=y     

AND and OR  operator 

&& ------> This stand for AND condition( if both the conditions are true then whole Condition will be true)

[[ $1 == yes && -r $1.txt ]]

|| --------> This stand for OR condition (if only one Condition is true then whole Condition will be true

[[ $1 == yes || -r $1.txt ]]

Note : Info on Shell Script Basics it may be differ in your environment like production,testing,development  and naming conventions etc

THANKS FOR VIEWING MYBLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

NID Utility in Oracle Database to Change DBID or DBNAME or Both

 

NID Utility in Oracle Database to Change DBID or DBNAME or Both

Introduction

In this Blog i am going to explain NID Utility in Oracle Database to Change DBID or DBNAME or Both The DBNEWID(NID) utility is introduced in oracle database this NID commands is the oracle program  that changes the SID of the database to use this utility we need SYS Account Password and the new SID for the Database

The NID utility in oracle always  you to change only the DBNAME or DBID or both DBNAME and DBID in the same command

Let us Start the Process NID Utility in Oracle Database to Change DBID or DBNAME or Both

CHANGE ONLY THE DBID USING NID UTILITY

Here we will only changing the DBID of the oracle database

Step 1: Backup Database

 rman target /

 backup database;

 exit

Step2:  Shutdown Immediate

sqlplus / as sysdba

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>exit

Step 3: Startup mount

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 3764747643 bytes

Fixed Size                  1253583 bytes

Variable Size             357873300 bytes

Database Buffers          243860700 bytes

Redo Buffers                6471103 bytes

Database mounted.

SQL> exit

Step 4: Open in one session and run NID utility(DBNEWID) with sysdba privileges to change the DBID

nid TARGET=SYS/password@proddb

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

 

Password:

Connected to database  PRODDB(DBID=466474)

 

Connected to server version 11.2.0

 

Control Files in database:

    +DATA/PRODDB/control01.ctl

    +FLASH/PRODDB/control02.ctl

   

 

Change database ID ? (Y/[N]) => Y

 

Proceeding with operation

Changing database ID from 466474 to 466474424

 

Database ID for database PRODB changed to 466474424

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Successfully changed database  ID.

DBNEWID - Completed successfully.

Step 5: Start up the database with open resetlogs

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 3764747643 bytes

Fixed Size                  1253583 bytes

Variable Size             357873300 bytes

Database Buffers          243860700 bytes

Redo Buffers                6471103 bytes

Database mounted.

SQL> alter database open resetlogs;

CHANGE ONLY THE DBNAME

Here we will changing  only the DBNAME in oracle database

 

Step1: Backup Database

rman target /

backup database;

exit

Step 2 : shutdown Immediate

sqlplus / as sysdba

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>exit

Step 3: Startup mount

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 3764747643 bytes

Fixed Size                  1253583 bytes

Variable Size             357873300 bytes

Database Buffers          243860700 bytes

Redo Buffers                6471103 bytes

Database mounted.

SQL> exit

Step 4: Open one session and run NID with sysdba privilege

nid TARGET=SYS/password@chaitu_123 DBNAME=proddb2 SETNAME=Y

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

 

Password:

Connected to database PRODDB (DBID=466474)

 

Connected to server version 11.2.0

 

Control Files in database:

    +DATA/PRODDB/control01.ctl

    +FLASH/PRODDB/control02.ctl

   

 

Change database name ? (Y/[N]) => Y

 

Proceeding with operation

Database name changed to PRODDB2

 

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name.

DBNEWID - Completed succesfully.

The value of the DBNAME is the new db_name of the database

SETNAME must be set to Y .the default is N and causes the DBID to be changed also

Step 5: Set the DB_NAME initialization paramater in the initialization parameter file to the new database name

Step 6: Create a new password file using orapwd

Step 7: Startup the database (with resetlogs)

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 3764747643 bytes

Fixed Size                  1253583 bytes

Variable Size             357873300 bytes

Database Buffers          243860700 bytes

Redo Buffers                6471103 bytes

Database mounted.

CHANGE BOTH DBID AND DBNAME

Here we will change the both DBID and DBNAME in oracle database

Step 1: Backup Database

rman target /

backup database;

exit

Step 2: shutdown immediate

sqlplus / as sysdba

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>exit

Step 3: startup mount

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 3764747643 bytes

Fixed Size                  1253583 bytes

Variable Size             357873300 bytes

Database Buffers          243860700 bytes

Redo Buffers                6471103 bytes

Database mounted.

SQL> exit

Step 4: Open one session and run NID with sysdba privilige

nid TARGET=SYS/password@chaitu_123 DBNAME=proddb2

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

Password:

Connected to database PRODDB (DBID=466474)

Connected to server version 11.2.0

Control Files in database:

+DATA/PRODDB/control01.ctl

+FLASH/PRODDB/control02.ctl

Change database name and ID ? (Y/[N]) => Y

Proceeding with operation

Database name changed to PRODDB2

Modify parameter file and generate a new password file before restarting.

Database ID for database EXPTEST_DB2 changed to 466474424

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Successfully changed database name and ID.

DBNEWID - Completed successfully.

the value of the DBNAME is the new dbname of the database

Step 5: After DBNEWID sucessfully changes the DBID ,shutdown immediate

Step 6: Set the DB_NAME initilation parameter in the initilization parameter file to the new database name

Step 7: Create the new password file with orapwd

Step 8: Startup the database with open resetlogs

sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 3764747643 bytes

Fixed Size                  1253583 bytes

Variable Size             357873300 bytes

Database Buffers          243860700 bytes

Redo Buffers                6471103 bytes

Database mounted.

SQL> alter database open resetlogs;

Note: Info on NID Utility in Oracle Database to Change DBID or DBNAME or Both it may be differ in your environment like production,testing,development etc and naming conventions 

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOWMEOR SUBSCRIBE ME

Lock Account Automatically with INACTIVE_ACCOUNT_TIME

Lock Account Automatically with  INACTIVE_ACCOUNT_TIME

Introduction

In Oracle 12.2 Release We can use the INACTIVE_ACCOUNT_TIME resource parameter in profile to automatically lock the account of a database user who has not logged in to the database instance in a specified number of days.In Production database or Testing database or Development database

1. By default, it is set to UNLIMITED.

2. The minimum setting is 15 and the maximum is 24855.

SQL> select RESOURCE_NAME,limit from dba_profiles where profile='DEFAULT';

 

RESOURCE_NAME                               LIMIT

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

COMPOSITE_LIMIT                                UNLIMITED

SESSIONS_PER_USER                            UNLIMITED

CPU_PER_SESSION                             UNLIMITED

CPU_PER_CALL                                   UNLIMITED

LOGICAL_READS_PER_SESSION                   UNLIMITED

LOGICAL_READS_PER_CALL                      UNLIMITED

IDLE_TIME                                        UNLIMITED

CONNECT_TIME                                UNLIMITED

PRIVATE_SGA                                     UNLIMITED

FAILED_LOGIN_ATTEMPTS                       10

PASSWORD_LIFE_TIME                          180

PASSWORD_REUSE_TIME                         UNLIMITED

PASSWORD_REUSE_MAX                          UNLIMITED

PASSWORD_VERIFY_FUNCTION                    NULL

PASSWORD_LOCK_TIME                          1

PASSWORD_GRACE_TIME                         7

INACTIVE_ACCOUNT_TIME                       UNLIMITED ----------- > This is the resource_name introduced in oracle 12.2.

 

17 rows selected.

 

To make an account lock automatically after 30 days of inactivity, Create a profile by setting INACTIVE_ACCOUNT_TIME to 30 and Set the profile to that user.

 

   CREATE PROFILE "ENDUSERINACTIVE"

    LIMIT

         COMPOSITE_LIMIT UNLIMITED

         SESSIONS_PER_USER UNLIMITED

         CPU_PER_SESSION UNLIMITED

         CPU_PER_CALL UNLIMITED

         LOGICAL_READS_PER_SESSION UNLIMITED

         LOGICAL_READS_PER_CALL UNLIMITED

         IDLE_TIME UNLIMITED

         CONNECT_TIME UNLIMITED

         PRIVATE_SGA UNLIMITED

         FAILED_LOGIN_ATTEMPTS 10

         PASSWORD_LIFE_TIME 1552000/86400

         PASSWORD_REUSE_TIME UNLIMITED

         PASSWORD_REUSE_MAX UNLIMITED

         PASSWORD_VERIFY_FUNCTION NULL

         PASSWORD_LOCK_TIME 86400/86400

         PASSWORD_GRACE_TIME 604800/86400

         INACTIVE_ACCOUNT_TIME 30;

 

SQL>  select RESOURCE_NAME,limit from dba_profiles where profile='ENDUSERINACTIVE' and resource_name='INACTIVE_ACCOUNT_TIME';

 

RESOURCE_NAME                               LIMIT

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

INACTIVE_ACCOUNT_TIME                       30

 

SQL> CREATE USER chaitanya identified by chaitanya123 profile ENDUSERINACTIVE;

 

User created.

If you try to give a value less than 15, it will throw error like – ORA-02377: invalid profile limit INACTIVE_ACCOUNT_TIME

 

   CREATE PROFILE "ENDUSERINACTIVE"

    LIMIT

         COMPOSITE_LIMIT UNLIMITED

         SESSIONS_PER_USER UNLIMITED

         CPU_PER_SESSION UNLIMITED

         CPU_PER_CALL UNLIMITED

         LOGICAL_READS_PER_SESSION UNLIMITED

         LOGICAL_READS_PER_CALL UNLIMITED

         IDLE_TIME UNLIMITED

         CONNECT_TIME UNLIMITED

         PRIVATE_SGA UNLIMITED

         FAILED_LOGIN_ATTEMPTS 10

         PASSWORD_LIFE_TIME 15552000/86400

         PASSWORD_REUSE_TIME UNLIMITED

         PASSWORD_REUSE_MAX UNLIMITED

         PASSWORD_VERIFY_FUNCTION NULL

         PASSWORD_LOCK_TIME 86400/86400

         PASSWORD_GRACE_TIME 604800/86400

         INACTIVE_ACCOUNT_TIME 10;

 

   CREATE PROFILE "ENDUSERINACTIVE"

*

ERROR at line 1:

ORA-02377: invalid profile limit INACTIVE_ACCOUNT_TIME

Note: Info On Lock Acoount Automatically with INACTIVE_ACCOUNT_TIME it may be differ in your environment like production,testing ,development and naming conventions etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

 

ORA-12985 Tablespace Users is Read Only, Cannot Drop Column

 ORA-12985 Tablespace Users is Read Only, Cannot Drop Column 

Introduction

If you try to Drop a column from a table in a read only ,you got an error like this ORA-12985 Tablespace Users is Read Only, Cannot Drop Column if you really drop the column ,you must put the tablespace into READ WRITE mode  

Probem:

While dropping a column, in a paticular owner and particlar object below got an errorORA-12985 Tablespace Users is Read Only, Cannot Drop Column ,while drop a column in tablespace

 SQL> alter table chaitanyadba.master03 drop (OWNER,OBJECT_NAME);

alter table chaitanyadba.master03 drop (OWNER,OBJECT_NAME)

*

ERROR at line 1:

ORA-12985 Tablespace Users is Read Only, Cannot Drop Column 

Now let us Start the process to find out the Solution

The Object which is trying to drop belongs to a table sapce is in read only mode

Step 1 : Find the tablespace of that particular table

SQL> select tablespace_name from dba_segments where segment_name='MASTER03';

 

TABLESPACE_NAME

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

USERS

Step 2 : Find the staus of the tablespace

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

 

TABLESPACE_NAME                STATUS

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

USERS                                       READ ONLY

 

Step 3: Inorder to Drop a column ,We need to make the tablespace READ and WRITE mode only  

SQL> ALTER TABLESPACE users READ WRITE;

 

Tablespace altered.

 

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

 

TABLESPACE_NAME                STATUS

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

USERS                                          ONLINE

 

SQL> alter table chaitanyadba.master03 drop (OWNER,OBJECT_NAME);

 

Table altered.

Note : Info on ORA-12985 Tablespace Users is Read Only, Cannot Drop Column  it may differin your environment like production,testing,development and naming conventions etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

 

 

How to Kill a Session In Oracle Database

 How to Kill a Session In Oracle Database

Introduction

In this blog How to Kill a Session In Oracle Database  we can kill oracle session by using the sql command alter system kill session and also many ways to kill the session why we we kill the process bacause  inactive and holding locks, process for long time it will occupies more memory and more resources the users are unable to login the database or hanging the system it will take take long time 

 Now let us start the process How to Kill a Session In Oracle Database

The syntax to kill a session in oracle database 

ALTER SYSTEM KILL SESSION ‘SID,SERIAL#’ IMMEDIATE;

Here sid,serial# can be obtained from v$session view

select sid,serial# from v$session where username like 'CHAITANYA'

Step 1: first get the sid and serial# of the session;

Here the session is executing the query SELECT * FROM CHAITANYADBA;

Use the below query to get the sid and serial# of this sql query.

COL SQL_TEXT format a45

SQL>  SELECT a.sid,a.serial#,substr(b.sql_text,1,200) sql_text from v$sql b,

     v$session a where a.sql_id=b.sql_id and  upper(b.sql_text)

     like '%CHAITANYADBA%' and upper(b.sql_text) not like '%V$SQL%';  2  

 

       SID    SERIAL# SQL_TEXT

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

33 26316 select * from chaitanyadba

 

 

Now kill session :

 

SQL>  alter system kill session '33,26316'  immediate;

 

System altered.

For Oracle RAC Database

You can login to the same instance where session is running and then run the above alter system kill session command or you can use the  below command also

ALTER SYSTEM KILL SESSION 'SID,SERIAL#, @INSTANCE_ID';

where instance_id is the instance where the current session is running this command is useful  when you want to kill multiple session from instance in oracle rac database,generally this command is used for want to clear session which is inactive and holding locks ,long running session if the command is not able to kill the session as it has to undo lot of transaction, it will return as marked for killed , once the undo is over ,it will killed itself ,if the session is not doing  undo but it is stuck some where ,you can kill the sever process in th ebackground to clear the session

SQL> ALTER SYSTEM KILL SESSION ’31,3123';

ALTER SYSTEM KILL SESSION ’31,3123'

*

ERROR at line 1:

ORA-00031: session marked for kill

SQL> select username, status from v$session where SID=12;

USERNAME STATUS

——————– ——–

CHAITANYA  KILLED

 How to kill the server process associated with session

NON RAC DATABASE

SELECT s.sid, s.serial#, p.spid

FROM v$session s, v$process p

WHERE s.paddr = p.addr

AND username = 'CHAITANYA';

RAC DATBASE

SELECT s.inst_id, s.sid, s.serial#, p.spid

FROM gv$session s, gv$process p

WHERE s.paddr = p.addr

AND s.inst_id = p.inst_id

AND username = 'CHAITANYA';

Once you executing this sql query we will get the SPID yo can login to database server and kill the SPID

ps -ef | gep <SPID>

Confirm this is  oracle database shadow proces and kill it

kill -9 <spid>

DISCONNECT SESSION

There is another command which can be usedto kill oracle session

alter system disconnect session 'SID,SERIAL#' POST_TRANSACTION | IMMEDIATE;

USEFUL QUERIES FOR KILL ORACLE SESSION

Query to generate kill session command for all sessions with given schema name

select 'alter system kill session ' ||''''|| sid||','|| serial#||''''||';' from v$session where SCHEMANAME='CHAITANYA'

Here i am using CHAITANYA is the schema like scott

Query to generate kill oracle session command for all session with the given module and status being inactive

col event format a30

col module format a15

col program format a30

set lines 100

select 'alter system kill session ' ||''''|| sid||','|| serial#||''''||';'

from v$session_wait sw, v$session s

where sw.sid = s.sid

and sw.sid in (select sid from v$session where module like '%&module%')

and s.status='INACTIVE';

Query to Genearte kill session command for all session which are connecting with sqlplus

select 'alter system kill session ' ||''''|| s.sid||','|| s.serial#||''''||';'

from v$session s where program like '%sqlplus@%'

Query to check killed session in oracle

select sid, serial#, status, username , module, form

from v$session s where status like '%KILLED%'

Query to remove killed session in oracle database

Non RAC database

SELECT 'kill -9 '|| p.spid

FROM v$session s, v$process p

WHERE s.paddr = p.addr

AND s.status = 'KILLED';

RAC database

SELECT 'kill -9 '|| p.spid

FROM gv$session s, gv$process p

WHERE s.paddr = p.addr

AND s.inst_id = p.inst_id

AND s.status = 'KILLED';

Query to check inactive session in oracle database

select sid, serial#, status, username , module, form

from v$session s where status like '%INACTIVE%'

Query to kill inactive session in oracle

select 'alter system kill session ' ||''''|| s.sid||','|| s.serial#||''''||';'

from v$session s where status like '%INACTIVE%'

IDENTIFY THE SESSION USING THE GV$SESSION and GV$PROCESS VIEWS AS FOLLOWS

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

       s.sid,

       s.serial#,

       --s.sql_id,

       p.spid,

       s.username,

       s.program

FROM   gv$session s

       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND';

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM

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

         1         30         25 3859       PROD       chaitanya@chaitu-12gr2.localdomain (TNS V1-V2)

         1         13        387 3834       SYS        abhiram@chaiu-12gr2.localdomain (TNS V1-V2)

         1         30        487 4663                  manasa@chaitu-12gr2.localdomain (Z003)

         1         28        225 4665                  pavan@chaitu-12gr2.localdomain (Z001)

Note: Info on How to Kill a Session In Oracle Database it maybe differ in your enviroment like production,testing ,development and naming conventions etc 

THANKS FOR VIEWING MYBLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Steps to Create Physical Standby Database in Oracle

Steps to Create Physical Standby Database in Oracle 

Introduction

Here Steps to Create Physical Standby Database in Oracle  will build a physical standby setup. Our assumption is primary database is already up and running fine And ORACLE_HOME is installed on standby server.

SERVER ----->PRIMARY---->primary_host

DB_UNIQUE_NAME---->PRIMARY---->PRODDB

SERVER ----->STANDBY---->standby_host

DB_UNIQUE_NAME---->STANDBY---->PRODSTAN

PRIMARY

Make sure database is archive log mode and enable force logging

PRIMARY > select log_mode from v$database;

 

LOG_MODE

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

ARCHIVELOG

 

 Enable force loggin:

 FORCE LOGGING is required, every changes in database will go to redo logs, which will be applied on standby system

 

SQL > ALTER DATABASE FORCE LOGGING;

 

Database altered.

[PRIMARY] Add standby logfiles;

 In standby setup, archives log from primary shipped to standby and applied there. But if the primary database crashes, then the online redo logs will stay in primary side. As these logs hasn’t been archives, means those won’t be applied to standby, Which results in data loss. So if we add standby logfile, Data guard writes the Primary’s current redo log to a “standby redo log” allowing complete recovery in case of Primary site is lost.

SQL > select GROUP#,BYTES/1024/1024,thread# from v$log;

 

    GROUP# BYTES/1024/1024    THREAD#

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

         1             138          1

         2             138          1

         3             138          1

         4             138          1

         5             138          1

         6             138          1

         7             138          1

         8             138          1

 

8 rows selected.

SQL >col member for a45

SQL > set pagesize 200

SQL > set lines 200

SQL >select GROUP#,MEMBER from v$logfile;

    GROUP# MEMBER

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

         1 /u01/redo1/PROD/redo_01a.dbf

         1 /u01/redo2/PROD/redo_01b.dbf

         2 /u01/redo1/PROD/redo_02a.dbf

         2 /u01/redo2/PROD/redo_02b.dbf

         3 /u01/redo1/PROD/redo_03a.dbf

         3 /u01/redo2/PROD/redo_03a.dbf

         4 /u01/redo1/PROD/redo_04a.dbf

         4 /u01/redo2/PROD/redo_04a.dbf

         5 /u01/redo1/PROD/redo_05a.dbf

         5 /u01/redo2/PROD/redo_05a.dbf

         6 /u01/redo1/PROD/redo_06a.dbf

         6 /u01/redo2/PROD/redo_06a.dbf

         7 /u01/redo1/PROD/redo_07a.dbf

         7 /u01/redo2/PROD/redo_07b.dbf

         8 /u01/redo1/PROD/redo_08a.dbf

         8 /u01/redo2/PROD/redo_08b.dbf

Dynamics query to generate the sqls.

 

SQL > select 'alter database add standby logfile '''||

regexp_substr(MEMBER,'/.+/')||'prodstan_'||

regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||

''' size '||bytes||';' "Create Standby redo"

from v$logfile lf , v$log l

where l.group# = lf.group#

/  2    3    4    5    6    7

Create Standby redo

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_01a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_01b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_02a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_02b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_07a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_07b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_08a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_08b.dbf' size 124217729;

 

16 rows selected.

SQL> alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_01a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_01b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_02a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_02b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_07a.dbf' size 134217728;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_07b.dbf' size 134217728;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_08a.dbf' size 134217728;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_08b.dbf' size 134217728;

 

 

 

database altered

[PRIMARY] create password file

cd $ORACLE_HOME/dbs

 

orapwd file=orapwPRODDB password=prod123 entries=30

[PRIMARY]Configure listener and tns entries as below.

 

cat tnsnames.ora

PRODDB =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = primary-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODDB)))

PRODSTAN =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = standby-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODSTAN)))

 

 

cat listener.ora

 

LISTENER_PRODDB =

   (address_list =

    (address =

           (protocol = tcp)

           (host     = primary-host)

           (port     = 1571)

      )

)

SID_LIST_LISTENER_PRODDB =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PRODDB)

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

      (SID_NAME = PRODDB)

    )

)

 

lsnrctl start LISTENER_PRODDB

 

[PRIMARY]Do below changes in spfile of primary

 log_archive_config parameter enables sending and receiving of archive logs to remote dest

DG_CONFIG - need to specify the list of db_unique_name of both primary and standby.

alter system set log_archive_config='DG_CONFIG=(PRODDB,PRODSTAN)' scope=BOTH;

 

--- archive destination in local(primary server)

alter system set log_archive_dest_1='LOCATION=/u01/arch/PROD VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDB'  scope=BOTH;

 

---log_archive_dest_2 parameter defines the archive destination of standby

--- ASYNC - This is used for maximum performance mode(DEFAULT) .Means redo logs generated in primary need not be shipped/applied in standby

alter system set log_archive_dest_2='SERVICE=PRODSTAN LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTAN' scope=BOTH;

 

alter system set log_archive_dest_state_2='ENABLE'  scope=BOTH;

alter system set log_archive_dest_state_1='ENABLE'  scope=BOTH;

 

 Tns entry name for primary

alter system set fal_client='PRODDB'  scope=BOTH;

 

--- tns entry name for primary -- i.e It will fetch the archives from standby .

alter system set fal_server='PRODSTAN'  scope=BOTH;

[PRIMARY]So after the changes the spfile in primary will look as below.

 strings spfilePRODDB.ora

 

*.audit_trail='DB'

*.compatible='11.2.0'

*.control_files='/u01/data1/PROD/control1.ora','/u01/data2/PROD/control2.ora','/u01/undo/PROD/control3.ora'

*.core_dump_dest='/u01/admin/PROD/cdump'

*.db_block_size=8193

*.db_cache_size=3009M

*.db_file_multiblock_read_count=63

*.db_keep_cache_size=512M

*.db_name='PRODDB'

*.db_unique_name='PRODDB'

*.diagnostic_dest='/u01/admin/PROD/diag'

*.dml_locks=100

*.fal_client='PRODDB'

*.fal_server='PRODSTAN'

*.instance_name='PRODDB'

*.job_queue_processes=2

*.log_archive_config='DG_CONFIG=(PRODDB,PRODSTAN)'

*.log_archive_dest_1='LOCATION=/u01/arch/PROD VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDB'

*.log_archive_dest_2='SERVICE=PRODSTAN LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTAN'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='arch_PROD_%t_%s_%r.arc'

*.log_buffer=31367168# log buffer update

*.log_checkpoint_interval=300000

*.max_dump_file_size='unlimited'

*.nls_date_format='DD-MON-RR'

*.os_authent_prefix='NULL'

*.pga_aggregate_target=1500M

*.processes=600

*.remote_dependencies_mode='SIGNATURE'

*.remote_login_passwordfile='EXCLUSIVE'

*.resource_limit=TRUE

*.sga_max_size=5152M# internally adjusted

*.shared_pool_size=512M

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=30000

*.undo_tablespace='UNDO'

STANDBY

[STANDBY ] create directory structure same as that point

mkdir -p /u01/data1/PROD/

mkdir -p /u01/data2/PROD/

mkdir -p /u01/arch/PROD

mkdir -p /u01/undo/PROD

mkdir -p /u01/admin/PROD/cdump

mkdir -p /u01/admin/PROD/diag

[STANDBY]pfile and password file creation in $ORACLE_HOME/dbs location:

 

 

-----copy the password file from production and rename to standby name.

cd $ORACLE_HOME/dbs

scp oracle@primary-host:/u01/app/oracle/product/11.2.0/dbs/orapwPRODDB orapwPRODSTAN

 

-----create an init file with minimum parameter as below

 

vi initPRODSTAN.ora

 

DB_NAME=PRODDB

DB_UNIQUE_NAME=PRODSTAN

control_files='/u01/data1/PROD/control1.ora','/u01/data2/PROD/control2.ora','/u01/undo/PROD/control3.ora'

fal_client='PRODSTAN'

fal_server='PRODDB'

db_file_name_convert='/u01/data1/PROD/','/u01/data1/PROD/','/u01/data2/PROD/','/u01/data2/PROD/'

log_file_name_convert = '/u01/redo1/PROD/,'/u01/redo1/PROD/','/u01/redo2/PROD/','/u01/redo2/PROD/'

standby_file_management='AUTO'

log_archive_config='DG_CONFIG=(PRODDB,PRODSTAN)'

log_archive_dest_1='LOCATION=/u01/arch/PRODDB VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODSTAN'

log_archive_dest_2='service=PRODDB ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=PRODDB'

 [STANDBY] Listener and tns setup  

cat tnsnames.ora

PRODDB =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = primary-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODDB)))

PRODSTAN =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = standby-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODSTAN)))

 

cat listener.ora

 

LISTENER_PRODSTAN =

   (address_list =

    (address =

           (protocol = tcp)

           (host     = standby-host)

           (port     = 1571)

      )

)

SID_LIST_LISTENER_PRODSTAN =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PRODSTAN)

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

      (SID_NAME = PRODSTAN)

    )

)

 

lsnrctl start LISTENER_PRODSTAN

[STANDBY]start the standby database in nomount state:

SQL > startup nomount

ORACLE instance started.

 

Total System Global Area 5415597569 bytes

Fixed Size                  3170303 bytes

Variable Size             805970240 bytes

Database Buffers         3502926846 bytes

Redo Buffers                3530176 bytes

[STANDBY]Now check the connectivity between primary and standby using sys password

 

 

-----In primary:

 

$ sqlplus sys/prod123@PRODSTAN as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 21 15:17:56 2020

 

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

 

  IN STANDBY:

 

sqlplus sys/prod123@PRODDB as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 21 15:17:56 2020

 

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

 

 STANDBY:

[STANDBY]Now connect to rman and run the duplicate command as mentioned below.

 

rman target sys/prod123@PRODDB auxiliary sys/prod123@PRODSTAN

 

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue June 16 08:42:46 2020

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

connected to target database: PRODDB (DBID=728741429)

connected to auxiliary database: PRODDB (not mounted)

 

rman >

 

run

{

allocate channel primy1 type disk;

allocate channel primy2 type disk;

allocate channel primy3 type disk;

allocate channel primy4 type disk;

allocate channel primy5 type disk;

allocate channel primy6 type disk;

allocate channel primy7 type disk;

allocate auxiliary channel stanby1 type disk;

allocate auxiliary channel stanby2 type disk;

allocate auxiliary channel stanby3 type disk;

allocate auxiliary channel stanby4 type disk;

allocate auxiliary channel stanby5 type disk;

allocate auxiliary channel stanby6 type disk;

allocate auxiliary channel stanby7 type disk;

allocate auxiliary channel stanby8 type disk;

 

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE  nofilenamecheck;

}

 

[STANDBY]Once duplication is done. Start the recovery

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

DATABASE ALTERED.

 

SQL > select process,status,sequence# from v$managed_standby:

If you wish you open the standby with read only mode as active dataguard, then use below one.

sql> alter datbase open;

 

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

 

 The physcial standby setup is ready for use

Note: Info on Physical standby datbase in oracle it may be differ in your environment like production,testing,development and directory structures and na,ming conventions etc 

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Dataguard Broker Configuration DG Broker in Oracle 12c

 Dataguard Broker Configuration DG Broker in Oracle 12c 

Introduction

Dataguard BrokerDG Broker in Oracle 12c  is used to automate monitoring and controlling standby setups. it is very useful when we have multiple standby systems

In this DG Broker in Oracle 12c blog we are going to expalin how to enable db broker configuration in Oracle 12c in existing standby setup. before going to start the process dg broker make sure standby setup  is ready

Prerequisites

PRIMARY UNIQUE DB NAME---------->PROCDB

STANDBY UNIQUE DB NAME------------>PROCSTAN

Now let us start the process

Step 1 : Set dg_broker_start to true [ON BOTH PRIMARY DB  AND STANDBY DB]

PROCDB> alter system set dg_broker_start=true sid='*';

 

System altered.

 

 

PROCSTAN> alter system set dg_broker_start=true sid='*';

 

System altered.

Step 2:  Add primary db configuration[ ON PRIMARY]

$dgmgrl

DGMGRL for Solaris: Release 12.2.0.1.0 - Production on Sun May 3 12:22:13 2020

 

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

 

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sysdg

Password:

Connected to "PROCDB"

Connected as SYSDG.

 

DGMGRL> CREATE CONFIGURATION 'procdb_dg' AS PRIMARY DATABASE IS 'PROCDB' CONNECT IDENTIFIER IS PROCDB;

Configuration "procdb_dg" created with primary database "PROCDB"

 

 

DGMGRL> SHOW CONFIGURATION

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB - Primary database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

DISABLED

Step 3: Add standby Configuration[ ON PRIMARY]

DGMGRL>  add database PROCSTAN as connect identifier is PROCSTAN;

Database "procstan" added

DGMGRL> show configuration

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB    - Primary database

    procstan - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

DISABLED

If you are getting error as ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set, then

To fix this error, disable any log_archive_dest_n parameter set on standby( excluding log_archive_dest_1)

On standby:

SQL> alter system set log_Archive_dest_2=”;

System altered.

Step4: Enable configuration [ON PRIMARY]

DGMGRL> show configuration

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB    - Primary database

    procstan - Physical standby database

      Warning: ORA-16792: configurable property value is inconsistent with member setting

 

Fast-Start Failover: DISABLED

 

Configuration Status:

WARNING   (status updated 57 seconds ago)

Now lets trouble shoot the ORA-16792 error, we are getting error standby database

Check the properties for inconsistency

 

DGMGRL> show database 'procstan' InconsistentProperties;

INCONSISTENT PROPERTIES

   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

         PROCSTAN  StandbyFileManagement               MANUAL                                    MANUAL

         PROCSTAN     ArchiveLagTarget                    0                                         0

         PROCSTAN   LogArchiveMaxProcesses                    4                                         4

         PROCSTAN   LogArchiveMinSucceedDest                    1                                         1

         PROCSTAN   DataGuardSyncLatency                    0                                         0

         PROCSTAN      LogArchiveTrace                    0            (missing)                    0

         PROCSTAN     LogArchiveFormat         %t_%s_%r.dbf            (missing)         %t_%s_%r.dbf

 

Here we can see one critical parameter StandbyFileManagement is set to be MANUAL. to fix the error set to auto and try again

SQL> alter system set standby_file_management=AUTO scope=both;

 

System altered.

Enable the configuration again

DGMGRL> enable configuration

Enabled.

 

DGMGRL> show database 'procstan' InconsistentProperties;

INCONSISTENT PROPERTIES

   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL> show configuration

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB    - Primary database

    procstan - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS   (status updated 46 seconds ago)

Dg broker setup is ready and we can do switchover and failover using dgmgrl  easily

 

Note : Info on Databroker configuration it may  be differ in your environment like production,testing,development and etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME