Online database info

Monitoring Data Guard Log Shipping using Shell Script with Mail alert

Version:- Oracle 11g
All the below scripts are stored and will run from oracle user & from Primary Database.

[oracle@server1 ~]$ vi /home/oracle/dg.sql
Set linesize 140
set head off
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(SELECT NAME DB_NAME FROM V$DATABASE),
(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,'.')-1))))) HOSTNAME FROM V$INSTANCE
),
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' ),
(SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' ),
(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');
exit;

:wq

Now lets create the shell-script which will monitor the log gap in between of primary and standby database:-
From oracle user.
[oracle@server1 ~]$ vi /home/oracle/dgmonitor.sh
sqlplus -S / as sysdba @/home/oracle/dg.sql > /home/oracle/loggap.txt
loggap=`cat /home/oracle/loggap.txt | awk '{print $6}'`
if [ $loggap -ne 0 ]; then
mail -s "Attention!! There is a loggap between Primary and Standby Database! " yourmailid@gmail.com
fi
exit 0

:wq

[oracle@server1 ~]$ chmod 777 dgmonitor.sh

Now schedule it on crontab to execute the script for every 15mins :-
[oracle@server1 ~]$ crontab -e
*/15 * * * * /home/oracle/dgmonitor.sh > /dev/null

How to change DBNAME in Oracle 11g ?

Oracle version :- 11.2.0.1.0
Database Sid : prim

Step 1.
Find out current dbname:-
[oracle@server1 ~]$ sqlplus / as sysdba

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string prim

Step 2.
Shutdown & start database in mount stage.

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

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 318769144 bytes
Database Buffers 88080384 bytes
Redo Buffers 4308992 bytes
Database mounted.

Step 3.Changing dbname .

We are changing the dbname from prim to stand using NID utility.

[oracle@server1 ~]$ nid target=sys/password dbname=stand

DBNEWID: Release 11.2.0.1.0 - Production on Sun Dec 28 00:40:20 2014

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

Connected to database PRIM (DBID=4130009889)

Connected to server version 11.2.0

Control Files in database:
/u01/app/oracle/oradata/prim/control01.ctl
/u01/app/oracle/flash_recovery_area/prim/control02.ctl

Change database ID and database name PRIM to STAND? (Y/[N]) => y

Proceeding with operation
Changing database ID from 4130009889 to 1595074421
Changing database name from PRIM to STAND
Control File /u01/app/oracle/oradata/prim/control01.ctl - modified
Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - modified
Datafile /u01/app/oracle/oradata/prim/system01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/prim/sysaux01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/prim/undotbs01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/prim/users01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/prim/example01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/prim/temp01.db - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/prim/control01.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to STAND.
Modify parameter file and generate a new password file before restarting.
Database ID for database STAND changed to 1595074421.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

Step 4. Modify parameter file and generate a new password file and restart the dbase.
[oracle@server1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstand password=redhat force=y

Now create a new pfile from the existing spfile.
SQL> create pfile from spfile;

File created.

[oracle@server1 ]$cd $ORACLE_HOME/dbs
[oracle@server1 dbs]$ cp initprim.ora initstand.ora
change *.db_name='prim' to *.db_name='stand' in init file.

Step 5. Open the database with Resetlogs option.

[oracle@server1 dbs]$ export ORACLE_SID=stand
[oracle@server1 dbs]$ sqlplus / as sysdba
SQL> startup nomount from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora' ;
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 339740664 bytes
Database Buffers 67108864 bytes
Redo Buffers 4308992 bytes

SQL> ALTER SYSTEM SET DB_NAME=STAND SCOPE=SPFILE;

System altered.

SQL> SHUT IMMEDIATE;
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora' ;
File created.

SQL> STARTUP mount;

ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 339740664 bytes
Database Buffers 67108864 bytes
Redo Buffers 4308992 bytes
Database mounted.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
stand

SQL> show parameter db_name;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string STAND

How to change DBID in Oracle 11g ?

Oracle version :- 11.2.0.1.0
Database Sid : prim

While we clone the database, the DB ID remains same as like the source database, so if we need to change it to the different DB ID, then use the follwing
method.Also when we change DBID of the database all previous backups will become unusable and we must open the database with RESETLOGS option.
Once you change the DBID make sure you take database backup immediately.

Step 1.
Find out current dbid :-
SQL> select dbid from v$database;

DBID
----------
4130009889

Step 2.
Shutdown & start database in mount stage.

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

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 318769144 bytes
Database Buffers 88080384 bytes
Redo Buffers 4308992 bytes
Database mounted.
SQL>

Step 3.Now change the dbid using NID Utility.
[oracle@server1 ~]$ nid target=/

DBNEWID: Release 11.2.0.1.0 - Production on Thu Nov 19 09:11:21 2015

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

Connected to database PRIM (DBID=4130009889)

Connected to server version 11.2.0

Control Files in database:
/u01/app/oracle/oradata/prim/control01.ctl
/u01/app/oracle/flash_recovery_area/prim/control02.ctl

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

Proceeding with operation
Changing database ID from 4130009889 to 4188450681
Control File /u01/app/oracle/oradata/prim/control01.ctl - modified
Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - modified
Datafile /u01/app/oracle/oradata/prim/system01.db - dbid changed
Datafile /u01/app/oracle/oradata/prim/sysaux01.db - dbid changed
Datafile /u01/app/oracle/oradata/prim/undotbs01.db - dbid changed
Datafile /u01/app/oracle/oradata/prim/users01.db - dbid changed
Datafile /u01/app/oracle/oradata/prim/example01.db - dbid changed
Datafile /u01/app/oracle/oradata/prim/temp01.db - dbid changed
Control File /u01/app/oracle/oradata/prim/control01.ctl - dbid changed
Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - dbid changed
Instance shut down

Database ID for database PRIM changed to 4188450681.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

Step 4.Start the database in mount stage and open it using RESETLOGS option.
[oracle@server1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 19 09:13:39 2015

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 318769144 bytes
Database Buffers 88080384 bytes
Redo Buffers 4308992 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select dbid from v$database;

DBID
----------
4188450681

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
prim

So we see the dbname has been changed into new one. Done..

P.S.After changing the DBID make sure you take database backup immediately.Since all the previous backups are nolonger can be used.

How to find out assigned roles and privileges given to an user ?

SQL> COL "USER,HIS ROLES AND PRIVILEGES" FORMAT a100
set linesize 300 pages 1000
SELECT
LPAD(' ', 5*level) || granted_role "USER,HIS ROLES AND PRIVILEGES"
FROM
(
SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER('SOUMYA')
UNION
SELECT grantee,granted_role
FROM dba_role_privs
UNION
SELECT grantee,privilege
FROM dba_sys_privs
)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;

USER,HIS ROLES AND PRIVILEGES
----------------------------------------------------------------------------------------------------
SOUMYA
CONNECT
CREATE SESSION
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE

12 rows selected.

How to find out character set in Oracle 11g ?

How to findout characterset in Oracle 11g ?

SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

VALUE$
--------------------------------------------------------------------------------
WE8MSWIN1252

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.1.0

20 rows selected.

Sample tnsnames.ora file for Linux

Oracle Version: oracle 11g
Oracle SID: orcl
OS Version:- RHEL 6
Hostname:- server1.example.com
Server IP: 192.168.72.120

$ vi $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /i01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

Sample listener.ora file for Linux

Oracle Version: oracle 11g
Oracle SID: orcl
OS Version:- RHEL 6
Hostname:- server1.example.com
Server IP: 192.168.72.120

$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
)

LISTENER =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521))
)

ADR_BASE_LISTENER = /u01/app/oracle