Learn DB Concepts with me...

Subscribe to Learn DB Concepts with me... feed
“​Perfection is not attainable, but if we chase perfection we can catch excellence” - Arvind Toorpuarvind toorpunoreply@blogger.comBlogger200125
Updated: 4 hours 40 min ago

chr function and its values - CHR and ASCII values

Wed, 2016-03-23 15:11
chr function returns the ascii letter for that integer. We know that there are 255 ascii characters defined.

SQL> select chr(65) as CHR from dual;

CHR

A

Below code print all 255 ascii characters

Sample code to check the values :

begin
  for i in 1..255 loop
      dbms_output.put_line( 'CHR('||i||')' ||'=='|| chr(i) );
    end loop;
    end;
    /

This output might differ actually based on the chacterset you have choosen while installing you Database.

DBMS_OUTPUT :


   CHR(1)==
CHR(2)==
CHR(3)==
CHR(4)==
CHR(5)==
CHR(6)==
CHR(7)==
CHR(8)==
CHR(9)==   
CHR(10)==

CHR(11)==
CHR(12)==
CHR(13)==
CHR(14)==
CHR(15)==
CHR(16)==
CHR(17)==
CHR(18)==
CHR(19)==
CHR(20)==
CHR(21)==
CHR(22)==
CHR(23)==
CHR(24)==
CHR(25)==
CHR(26)==
CHR(27)==
CHR(28)==
CHR(29)==
CHR(30)==
CHR(31)==
CHR(32)==
CHR(33)==!
CHR(34)=="
CHR(35)==#
CHR(36)==$
CHR(37)==%
CHR(38)==&
CHR(39)=='
CHR(40)==(
CHR(41)==)
CHR(42)==*
CHR(43)==+
CHR(44)==,
CHR(45)==-
CHR(46)==.
CHR(47)==/
CHR(48)==0
CHR(49)==1
CHR(50)==2
CHR(51)==3
CHR(52)==4
CHR(53)==5
CHR(54)==6
CHR(55)==7
CHR(56)==8
CHR(57)==9
CHR(58)==:
CHR(59)==;
CHR(60)==<
CHR(61)===
CHR(62)==>
CHR(63)==?
CHR(64)==@
CHR(65)==A
CHR(66)==B
CHR(67)==C
CHR(68)==D
CHR(69)==E
CHR(70)==F
CHR(71)==G
CHR(72)==H
CHR(73)==I
CHR(74)==J
CHR(75)==K
CHR(76)==L
CHR(77)==M
CHR(78)==N
CHR(79)==O
CHR(80)==P
CHR(81)==Q
CHR(82)==R
CHR(83)==S
CHR(84)==T
CHR(85)==U
CHR(86)==V
CHR(87)==W
CHR(88)==X
CHR(89)==Y
CHR(90)==Z
CHR(91)==[
CHR(92)==\
CHR(93)==]
CHR(94)==^
CHR(95)==_
CHR(96)==`
CHR(97)==a
CHR(98)==b
CHR(99)==c
CHR(100)==d
CHR(101)==e
CHR(102)==f
CHR(103)==g
CHR(104)==h
CHR(105)==i
CHR(106)==j
CHR(107)==k
CHR(108)==l
CHR(109)==m
CHR(110)==n
CHR(111)==o
CHR(112)==p
CHR(113)==q
CHR(114)==r
CHR(115)==s
CHR(116)==t
CHR(117)==u
CHR(118)==v
CHR(119)==w
CHR(120)==x
CHR(121)==y
CHR(122)==z
CHR(123)=={
CHR(124)==|
CHR(125)==}
CHR(126)==~
CHR(127)==
CHR(128)==€
CHR(129)==
CHR(130)==‚
CHR(131)==ƒ
CHR(132)==„
CHR(133)==…
CHR(134)==†
CHR(135)==‡
CHR(136)==ˆ
CHR(137)==‰
CHR(138)==Š
CHR(139)==‹
CHR(140)==Œ
CHR(141)==
CHR(142)==Ž
CHR(143)==
CHR(144)==
CHR(145)==‘
CHR(146)==’
CHR(147)==“
CHR(148)==”
CHR(149)==•
CHR(150)==–
CHR(151)==—
CHR(152)==˜
CHR(153)==™
CHR(154)==š
CHR(155)==›
CHR(156)==œ
CHR(157)==
CHR(158)==ž
CHR(159)==Ÿ
CHR(160)==
CHR(161)==¡
CHR(162)==¢
CHR(163)==£
CHR(164)==¤
CHR(165)==¥
CHR(166)==¦
CHR(167)==§
CHR(168)==¨
CHR(169)==©
CHR(170)==ª
CHR(171)==«
CHR(172)==¬
CHR(173)==­
CHR(174)==®
CHR(175)==¯
CHR(176)==°
CHR(177)==±
CHR(178)==²
CHR(179)==³
CHR(180)==´
CHR(181)==µ
CHR(182)==¶
CHR(183)==·
CHR(184)==¸
CHR(185)==¹
CHR(186)==º
CHR(187)==»
CHR(188)==¼
CHR(189)==½
CHR(190)==¾
CHR(191)==¿
CHR(192)==À
CHR(193)==Á
CHR(194)==Â
CHR(195)==Ã
CHR(196)==Ä
CHR(197)==Å
CHR(198)==Æ
CHR(199)==Ç
CHR(200)==È
CHR(201)==É
CHR(202)==Ê
CHR(203)==Ë
CHR(204)==Ì
CHR(205)==Í
CHR(206)==Î
CHR(207)==Ï
CHR(208)==Ð
CHR(209)==Ñ
CHR(210)==Ò
CHR(211)==Ó
CHR(212)==Ô
CHR(213)==Õ
CHR(214)==Ö
CHR(215)==×
CHR(216)==Ø
CHR(217)==Ù
CHR(218)==Ú
CHR(219)==Û
CHR(220)==Ü
CHR(221)==Ý
CHR(222)==Þ
CHR(223)==ß
CHR(224)==à
CHR(225)==á
CHR(226)==â
CHR(227)==ã
CHR(228)==ä
CHR(229)==å
CHR(230)==æ
CHR(231)==ç
CHR(232)==è
CHR(233)==é
CHR(234)==ê
CHR(235)==ë
CHR(236)==ì
CHR(237)==í
CHR(238)==î
CHR(239)==ï
CHR(240)==ð
CHR(241)==ñ
CHR(242)==ò
CHR(243)==ó
CHR(244)==ô
CHR(245)==õ
CHR(246)==ö
CHR(247)==÷
CHR(248)==ø
CHR(249)==ù
CHR(250)==ú
CHR(251)==û
CHR(252)==ü
CHR(253)==ý
CHR(254)==þ
CHR(255)==ÿ

Categories: DBA Blogs

Step by Step installation oracle 12c database on Linux 6 (centos)

Sun, 2016-03-20 16:24
Assumptions :

  • You have a some flavor of Linux operating system installed (I have used centos 6 in this example).
  • If you cant afford a separate machine you can use Virtual box or stemware software to visualize your desktop or laptop.
  • Assuming that you have downloaded oracle 12 software onto linux machine. If not you can download from this link Software-Download
  • You have full/required privileges on you Linux host.

Oracle Installation Prerequisites


In order to perform the installtion of oracle 12c software on Linux box you need to perform some pre-reqs, which can be done automatically or through manual updates.Please follow below instructions.

Automatic Setup

If you plan to use the "oracle-rdbms-server-12cR1-preinstall" package to perform all your prerequisite setup, issue the following command.

# yum install oracle-rdbms-server-12cR1-preinstall -y


It will be a good option to to do an update.


# yum update






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

MANUAL SETUP

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


If you have not used the "oracle-rdbms-server-12cR1-preinstall" package to perform all prerequisites, you will need to manually perform the following setup tasks.


Add or amend the following lines in the "/etc/sysctl.conf" file.

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Add the following lines to the "/etc/security/limits.conf" file.

oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768

MANUALLY INSTALL PACKAGES FROM INTERNET OR FROM CD DRIVE(below is to install from INTERNET) :

# From Public Yum or ULN
 Yum install binutils,  compat-libcap1,  compat-libstdc++-33,  compat-libstdc++-33.i686,  gcc,  gcc-c++,  glibc,  glibc.i686,  glibc-devel,  glibc-devel.i686,  ksh,  libgcc,  libgcc.i686,  libstdc++,  libstdc++.i686,  libstdc++-devel,  libstdc++-devel.i686,  libaio,  libaio.i686,  libaio-devel,  libaio-devel.i686,  libXext,  libXext.i686,  libXtst,   libXtst.i686,  libX11,   libX11.i686,  libXau,  libXau.i686,   libxcb,  libxcb.i686,  libXi,  libXi.i686,  make,  sysstat,  unixODBC,  unixODBC-devel
Create the new groups and users as per your requirement. For my case just to keep it simple lets use 3 groups & oracle user.

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper

useradd -u 54321 -g oinstall -G dba,oper oracle.

Set SELINUX to permissive or diable it if this is test env.
Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=permissive

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/12.1/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

LOGIN AS ORACLE USER AND 
Add the following lines at the end of the "/home/oracle/.bash_profile" file.

# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=ol6-121.localdomain
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1/db_1
export ORACLE_SID=orcl

export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Change your directory to location where you have downloaded oracle software.




Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.

./runInstaller



I don't want any update to uncheck to receive updates.





Lets create a server class.





To keep this simple I am going to select typical installation.



Make sure you have selected the home path correct. 




After This step you will be notified if you have any pre-req failures. Make sure you have cleared them all. Missing ksh package can be ignored as this is a known bug. Oracle is expecting a specific version of ksh & i have a latest pkg. Assuming that you have all cleared up.




Now select install.


Now you will be prompted to execute shell scripts before the installation of software is complete. I have missed that prompt screen but it will ask you to execute below 2 shell scripts as root user. see below screen



After executing hit OK and it will continue to install oracle DB software.




You will see this screen after installation is complete.





That's it you have completed your oracle 12c database software installation. You can query as below




Please drop your comments below if you found this blog helpful to you.

Categories: DBA Blogs

ENABLE REAL TIME APPLY ON STANDBY DATABASE

Sat, 2016-03-19 17:30
*********************************************************************
    REAL TIME APPLY OF ARCHIVE LOG FILES IN READ ONLY MODE:
*********************************************************************

SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/app/oracle/oraarch/
Oldest online log sequence     250
Next log sequence to archive   0
Current log sequence           252

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> alter database open read only;

Database altered.


***********************************************************************************
    NOW ON PRIMARY DATABASE I CREATE TABLE IN ONE OF USER ADD INSERT SOME DATA
***********************************************************************************

[oracle@Linux01 Desktop]$ sqlplus /"As sysdba"

SQL> select name,open_mode,db_unique_name,PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

NAME          OPEN_MODE                   DB_UNIQUE_NAME   PROTECTION_MODE        PROTECTION_LEVEL
--------     --------------------    ---------         --------------------         ---------
ORCL          READ ONLY WITH APPLY     ORCLSTB1        MAXIMUM PERFORMANCE      MAXIMUM PERFORMAN



SQL> conn atoorpu/XXXXX;
Connected.
SQL> create table abcd (name varchar2(20));

Table created.

SQL> insert into abcd values ('arvind');

1 row created.

SQL> commit;

Commit complete.


SQL> alter system switch logfile;

System altered.

SQL> insert into abcd values ('reddy');

1 row created.

SQL> commit;

Commit complete.

SQL> create table abcds as select * from abcd;

Table created.

***********************************************************************************
    NOW ON STANDBY DATABASE LETS QUERY SAME DATA SOME DATA
***********************************************************************************

[oracle@Linux02 Desktop]$ sqlplus /"As sysdba"

SQL> select name,open_mode,db_unique_name,PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

NAME          OPEN_MODE                   DB_UNIQUE_NAME   PROTECTION_MODE        PROTECTION_LEVEL
--------     --------------------    ---------         --------------------         ---------
ORCL          READ ONLY WITH APPLY     ORCLSTB1        MAXIMUM PERFORMANCE      MAXIMUM PERFORMAN

SQL> conn atoorpu/XXXXX;
Connected.
SQL> select * from abcd;

NAME
--------------------
arvind

SQL> select * from abcd;

NAME
--------------------
arvind
reddy

Categories: DBA Blogs

OPEN STANDBY DATABASE IN READ ONLY

Sat, 2016-03-19 10:16
SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> select name,db_unique_name,open_mode,DATABASE_ROLE from v$database;

NAME   DB_UNIQUE_NAME  OPEN_MODE       DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
ORCL   ORCLSTB1  MOUNTED       PHYSICAL STANDBY

 SQL> alter database open read only;

Database altered.

SQL> select name,db_unique_name,open_mode,DATABASE_ROLE from v$database;

NAME   DB_UNIQUE_NAME  OPEN_MODE       DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
ORCL   ORCLSTB1  READ ONLY       PHYSICAL STANDBY


SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY


SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE  OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY


SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CLOSING
ARCH      CLOSING
ARCH      CONNECTED
ARCH      CLOSING
ARCH      CLOSING
RFS      IDLE
RFS      IDLE
RFS      IDLE
RFS      IDLE
MRP0      APPLYING_LOG

 
Categories: DBA Blogs

Drop all objects in your current schema

Thu, 2016-03-17 14:49

**********************************************************************
You can use this simple pl-sql block to drop all objects in your current schema.
**********************************************************************

declare
VSQL varchar2(4000);
OBJ_NAME varchar2(100);
OBJ_TYPE varchar2(100);
OBJ_OWNER varchar2(100);
cursor c1 is select object_type,object_name from user_objects where object_type in ('TABLE','VIEW');

begin

open c1;
loop
fetch c1 into OBJ_TYPE,OBJ_NAME;
exit when c1%NOTFOUND;
IF OBJ_TYPE='TABLE' 
THEN
VSQL:=' drop '||OBJ_TYPE||' '||OBJ_NAME||' cascade constraints';
DBMS_OUTPUT.PUT_LINE(VSQL);
execute IMMEDIATE VSQL;
ELSE  
VSQL:=' drop '||OBJ_TYPE||' '||OBJ_NAME;
DBMS_OUTPUT.PUT_LINE(VSQL);
execute IMMEDIATE VSQL;
END IF;
end loop;
close c1;
end;
/




Note :

Remove "where object_type in ('TABLE','VIEW')" to drop all objects from current user.
Or you can edit to include only type of objects you want to be dropped.

You can Alter OBJECT TYPE any of below:


'TABLE','VIEW','SYNONYM','SEQUENCE','PROCEDURE','TRIGGER'
Categories: DBA Blogs

Restricting database access to IP addeess using sqlnet.ora

Wed, 2016-03-16 16:15
**************************************************************
                      FROM MY CLIENT MACHINE:
**************************************************************

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:20

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

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)

**************************************************************
NOW I want my DB to be only be accessible from this machine and restrict all other. For this I will need to make changed to my DB server sqlnet.ora :
**************************************************************

ON DB SERVER:

oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tcp.validnode_checking = yes  ## I have just added this line to test if it works

[oracle@Linux03 admin]$ vi sqlnet.ora
[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:20:03

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-00584: Valid node checking configuration error
 TNS-12560: TNS:protocol adapter error

As expected it doesn't work as listener needs to know where to accept or deny connections from.
Now I tweak the sqlnet.ora file to below

[oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03)  ## I only want to accept connections from my localhost i.e Linux03

RESTART THE LISTENER NOW.

[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:21:06

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully

 (OR)

[oracle@Linux03 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:36

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[oracle@Linux03 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:41

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-MAR-2016 15:28:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

****************************************************
NOW I TRY TO CONNECT FROM CLIENT AGAIN :
****************************************************

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:57

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

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
TNS-12537: TNS:connection closed

C:\Windows\System32>


Nope it doesn't allow me to connect.
*************************************************************
Lets try to remove the tcp.validnode_checking & tcp.invited_nodes and see
**************************************************************

[oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:31:58

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[oracle@Linux03 admin]$ vi sqlnet.ora
[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:32:16

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:36

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

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)

**************************************************************************
SUCCESS YOU HAVE SUCCESFULLY IMPLEMENTED ANOTHER LAYER OF SECURITY ON DB SERVER
*************************************************************************


tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03,HOSTNAME1,HOSTNAME2 ...)

YOU CAN ALSO EXPLICITLY EXCLUDE NODE LIKE THIS :

tcp.excluded_nodes = (192.168.100.101,HOSTNAME2,)

But : If you only specify invited nodes with tcp.invited_nodes, all other nodes will be excluded, so there is really no reason to do both. The same is true for excluded nodes. If you put tcp.excluded_nodes = (192.168.100.101,192.168.100.160) then IP containing 192.168.100.101 and 192.168.100.160 will be excluded/denied to connect to database as a database user while allowing others to connect.





Categories: DBA Blogs

prvf-0002 : could not retrieve local node name

Wed, 2016-03-02 09:43

prvf-0002 : could not retrieve local node name



PRVF-0002 : could not retrieve local node name
check if the hostname is correct in sysconfig/network & /etc/hosts files:
[oracle@Linux03 ~]$ cat /etc/sysconfig/network | grep HOSTNAME
HOSTNAME=Linux03

[oracle@Linux03 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

[oracle@Linux03 ~]$ vi /etc/hosts

[oracle@Linux03 ~]$ su root
Password:
[root@Linux03 oracle]# vi /etc/hosts

[root@Linux03 oracle]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.215.xxx.xx Linux03   <<<--- added hostname and ip


An alternate solution is to add the hostname in bash_profile file in home dir:
[oracle@Linux03 ~]$ cat .bash_profile | grep HOSTNAME
export ORACLE_HOSTNAME=Linux03
Categories: DBA Blogs

delete noprompt obsolete archive log - RMAN

Thu, 2016-02-25 10:25

RMAN> report obsolete;

using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          183    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc
Archive Log          189    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_141_896707677.arc
Archive Log          190    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_145_896707677.arc
Archive Log          191    16-FEB-16        

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          183    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc
Archive Log          189    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_141_896707677.arc
Archive Log          190    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_145_896707677.arc
Archive Log          191    16-FEB-16          
    
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc RECID=183 STAMP=903955885
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_141_896707677.arc RECID=189 STAMP=903956192
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_145_896707677.arc RECID=190 STAMP=903956192
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_148_896707677.arc RECID=191 STAMP=903956192

Deleted 4 objects

Categories: DBA Blogs

Restore and recover database from RMAN backup - DB in No archive log mode

Thu, 2016-02-25 10:19
Simple example for restore and recover database from RMAN backup.

Assuming that : I have a need to restore my DB as some important tables/data was dropped/removed and its really mission critical. I have already got some RMAN backups from past.

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

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1728053248 bytes
Fixed Size                  3046512 bytes
Variable Size            1224737680 bytes
Database Buffers          486539264 bytes
Redo Buffers               13729792 bytes
Database mounted.

C:\Users\oracle>rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 19 12:46:12 2016

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

connected to target database: ORCLDB (DBID=1196XXXX70)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
20      B  F  A DISK        05-FEB-15       1       1       NO         TAG20160205T155541
21      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
22      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
23      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
24      B  F  A DISK        05-FEB-15       1       1       NO         TAG20160205T160507


RMAN> restore database from TAG='FULL_ORCLDB';

Starting restore at 19-FEB-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to E:\APP\ORADATA\ORCLDB\UNDOTBS01.DBF
:
:
:
channel ORA_DISK_1: restoring datafile 00119 to E:\APP\ORADATA\TLAPP.DBF
channel ORA_DISK_1: reading from backup piece E:\APP\BACKUP\ORCLDB_BKP_0UQT63BQ_1_1
channel ORA_DISK_1: piece handle=E:\APP\BACKUP\ORCLDB_BKP_0UQT63BQ_1_1 tag=FULL_ORCLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:15
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 00120 to E:\APP\ORADATA\TLLARGE.DBF
channel ORA_DISK_1: restoring datafile 00121 to E:\APP\ORADATA\TLWORK.DBF
channel ORA_DISK_1: restoring datafile 00122 to E:\APP\ORADATA\WAAPP.DBF
channel ORA_DISK_1: restoring datafile 00123 to E:\APP\ORADATA\ORCLDB\PSMATVW.DBF
channel ORA_DISK_1: reading from backup piece E:\APP\BACKUP\ORCLDB_BKP_0VQT63ER_1_1
channel ORA_DISK_1: piece handle=E:\APP\BACKUP\ORCLDB_BKP_0VQT63ER_1_1 tag=FULL_ORCLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:36
Finished restore at 19-FEB-15

Now lets try opening DB (I know it wont work but lets try..)

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\APP\ORADATA\ORCLDB\SYSTEM01.DBF'

Now lets Recover DB

RMAN> recover database;

Starting recover at 19-FEB-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 3390 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO03.LOG
archived log for thread 1 with sequence 3391 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO01.LOG
archived log for thread 1 with sequence 3392 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO02.LOG
RMAN-08187: WARNING: media recovery until SCN 69107847 complete
Finished recover at 19-FEB-15



SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL>


We must open the database in open resetlogs mode. in order for recover to be completed. (WE CAN DO OPEN RESET LOGS FROM RMAN or SQLPLUS PROMPT)
RMAN> alter database open resetlogs;

Statement processed

RMAN> exit


Recovery Manager complete.

C:\Users\oracle>
Categories: DBA Blogs

database switch over using dgmgrl

Thu, 2016-02-25 09:59
Perform a switch over test:


ON PRIMARY DB SERVER :

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  READ WRITE       ORCL      PRIM

ON STANDBY DB SERVER :

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  MOUNTED       ORCLSTB1      PHYSICAL STANDBY


LETS CONNECT TO DGMGRL AND SWITCH OVER ROLES

DGMGRL> switchover to 'ORCLSTB1';
Performing switchover NOW, please wait...
Operation requires a connection to instance "ORCLSTB1" on database "ORCLSTB1"
Connecting to instance "ORCLSTB1"...
Connected.
New primary database "ORCLSTB1" is opening...
Operation requires startup of instance "ORCLPRIM" on database "ORCL"
Starting instance "ORCLPRIM"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
start up and mount instance "ORCLPRIM" of database "ORCL"


ON OLD PRIMARY DB SERVER (ORCL) :

I have to start it manually coz dgmgrl was unable to connect to lsnr after role transfer.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  885211136 bytes
Fixed Size    2258320 bytes
Variable Size  566233712 bytes
Database Buffers  310378496 bytes
Redo Buffers    6340608 bytes
Database mounted

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  MOUNTED       ORCL      PHYSICAL STANDBY


LETS CONFIRM THE DB_ROLE BY QUERYING STANDBY DATABASE

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  READ WRITE       ORCLSTB1      PRIMARY

Categories: DBA Blogs

Pages