Tuesday, March 22, 2016

Creating Oracle Database Using dbca

Database Configuration Assistant (dbca) is an Oracle provided tool to create a new database. In this document I would explain how to create a new 12c database using dbca. Steps and screens are almost same for other Oracle database versions and different OS platforms.

1)
For Unix based platforms, log in as “oracle” user and initiate dbca by writing “dbca” using command line. Make sure your environment variables are set properly. For windows , initiate dbca from start menu.
 

For Windows, initiate from Start Menu


From first screen, select appropriate option of your choice. If this is the first database on the server, we only see 2 options enabled. For create a new database, we select “Create a Database” and click Next.

2)
On seconds scree, I have selected “Advance” so that all steps could be explained. In 11g, you will not see this screen.



3)
Select database type you want to create. In Custom Database option, you would need to provide details of database options you want to install.

4)
Provide database name and SID. Click the check box if you want to create this database as a container database (also provide pluggable database name with this), In 11g there is no “Create As Container Database” option on this screen.

5)
Enterprise Manager Database Express can also be configured with database creation. Click Next.

6)
Provide passwords for admin users and click Next

7)
If you already have Grid Infrastructure configured and listener is running, dbca would show currently running listener. You can click check box if you want to configure a listener from database home.

8)
Select Automatic Storage Management (ASM) if you have a diskgroup created to store the database. Alternatively you can store database on file system. Also specify ASM or file system for Fast Recovery Area (optional)

9)
Click Next

10)
Specify SGA settings or alternatively you can select Automatic Memory Management. From Sizing tab, you can specify number of process and database block size (For Database option I selected in step 3 above, block size is 8192 and can not be changed from this screen). From character set tab, you may also change database and national characterset. Connection Mode tab gives you choice of selecting Dedicated Server or Shared Server mode for database connections. Click Next


11)
Click Next.
dbca would perform prerequisite checks on next step. Resolve any issue if dbca lists any.

12)
Click Finish

13)
Monitor the progress
.

14)
Click Exit, and then Close.

Database creation has completed successfully and ready to use.

Monday, March 14, 2016

Changing Listener and SCAN Listener Ports

In this article I will explain how we modify Listener and SCAN Listener ports in RAC environment. For this article; in a 2 nodes RAC on Windows, my database listeners are listening ports 1521 and scan listeners are running on port 1522. I will change listeners’ ports to 1531 and scan listeners’ ports to 1532.

Please note that we no longer need to modify listener.ora file to modify the listeners’ ports. We will use srvctl command to modify listener ports and this is the way we should do this in a RAC environment.

To do the same in Linux environment, we would need to log in as “grid” user to perform these changes. For Windows, I will log in as Administrator user
C:\Users\dba_user>crsctl stat res -t
--------------------------------------------------------------------------------

NAME           TARGET  STATE        SERVER                   STATE_DETAILS

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

Local Resources
--------------------------------------------------------------------------------

ora.DATA.dg
               ONLINE  ONLINE       racnodea

               ONLINE  ONLINE       racnodeb

ora.FRA.dg
               ONLINE  ONLINE       racnodea

               ONLINE  ONLINE       racnodeb

ora.LISTENER.lsnr
               ONLINE  ONLINE       racnodea

               ONLINE  ONLINE       racnodeb

ora.OCRDG.dg
               ONLINE  ONLINE       racnodea

               ONLINE  ONLINE       racnodeb

ora.asm
               ONLINE  ONLINE       racnodea          Started

               ONLINE  ONLINE       racnodeb          Started

ora.gsd
               OFFLINE OFFLINE      racnodea

               OFFLINE OFFLINE      racnodeb

ora.net1.network
               ONLINE  ONLINE       racnodea

               ONLINE  ONLINE       racnodeb

ora.ons
               ONLINE  ONLINE       racnodea

               ONLINE  ONLINE       racnodeb

ora.registry.acfs
               ONLINE  ONLINE       racnodea

               OFFLINE OFFLINE      racnodeb

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

Cluster Resources
--------------------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       racnodea

ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       racnodeb

ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       racnodeb

ora.cvu
      1        ONLINE  ONLINE       racnodeb

ora.oc4j
      1        ONLINE  ONLINE       racnodeb

ora.mydbq.db
      1        ONLINE  ONLINE       racnodea          Open

      2        ONLINE  ONLINE       racnodeb          Open

ora.scan1.vip
      1        ONLINE  ONLINE       racnodea

ora.scan2.vip
      1        ONLINE  ONLINE       racnodeb

ora.scan3.vip
      1        ONLINE  ONLINE       racnodeb

ora.racnodea.vip
      1        ONLINE  ONLINE       racnodea

ora.racnodeb.vip
      1        ONLINE  ONLINE       racnodeb
As you see I have 3 scan listeners from which LISTENER_SCAN1 is running on node1 and LISTENER_SCAN2 and LISTENER_SCAN2 are running on node2. I will demonstrate port changing on node1 (racnodea) only as method to change on other node(s) will be same.

Let’s check the current configuration of the listeners and scan listeners. I am executing these commands from RACNODEA (node1)
C:\Users\dba_user>srvctl config listener
Name: LISTENER
Network: 1, Owner: nt authority\system
Home: <CRS home>
End points: TCP:1521

C:\Users\dba_user>srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node racnodea
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node racnodeb
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node racnodeb

C:\Users\dba_user>srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node racnodea
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node racnodeb
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node racnodeb

C:\Users\dba_user>srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1522
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1522
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1522

C:\Users\dba_user>srvctl config scan
SCAN name: racdb-scan.mydomain.com, Network: 1/10.133.125.0/255.255.255.0/public
SCAN VIP name: scan1, IP: /racdb-scan.mydomain.com/10.133.125.145
SCAN VIP name: scan2, IP: /racdb-scan.mydomain.com/10.133.125.144
SCAN VIP name: scan3, IP: /racdb-scan.mydomain.com/10.133.125.143

We can also use lsnrctl command to check the status and currently registered services with the listener and scan listener
C:\Users\dba_user>lsnrctl status listener

LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 27-NOV-2015 10:51:21

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
Start Date                15-NOV-2015 20:29:43
Uptime                    11 days 14 hr. 21 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\apps\11.2.0\grid\network\admin\listener.ora
Listener Log File         D:\apps\11.2.0\grid\log\diag\tnslsnr\racnodea\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\LISTENERipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.133.125.139)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.133.125.141)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+asm1", status READY, has 1 handler(s) for this service...
Service "mydbq" has 1 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
Service "mydbq.mydomain.com" has 1 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
Service "mydbqXDB" has 1 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Users\dba_user>D:\apps\11.2.0\grid\BIN\LSNRCTL.EXE status listener_scan1

LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 27-NOV-2015 10:56:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
Start Date                15-NOV-2015 20:29:45
Uptime                    11 days 14 hr. 26 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\apps\11.2.0\grid\network\admin\listener.ora
Listener Log File         D:\apps\11.2.0\grid\log\diag\tnslsnr\racnodea\listener_scan1\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\LISTENER_SCAN1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.133.125.145)(PORT=1541)))
Services Summary...
Service "mydbq" has 2 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
  Instance "mydbq2", status READY, has 1 handler(s) for this service...
Service "mydbq.apac.wdpr.disney.com" has 2 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
  Instance "mydbq2", status READY, has 1 handler(s) for this service...
Service "mydbqXDB" has 2 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
  Instance "mydbq2", status READY, has 1 handler(s) for this service...
The command completed successfully

Now let’s modify the ports of listener and scan listener on node 1( RACNODEA). Execute same commands on other node(2) to modify ports of other listener(s) and scan listener(s).
C:\Users\dba_user>D:\apps\11.2.0\grid\BIN\srvctl modify listener -l listener -p 1531

C:\Users\dba_user>D:\apps\11.2.0\grid\BIN\srvctl config listener
Name: LISTENER
Network: 1, Owner: nt authority\system
Home: <CRS home>
End points: TCP:1531

C:\Users\dba_user>D:\apps\11.2.0\grid\BIN\srvctl modify scan_listener -p 1532
C:\Users\dba_user>srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1532
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1522
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1522

Now we would need to restart listener as well as scan listener to reflect these changes in ports.
C:\Users\dba_user>D:\apps\11.2.0\grid\BIN\srvctl stop listener
C:\Users\dba_user>D:\apps\11.2.0\grid\BIN\srvctl start listener

C:\Users\dba_user>D:\apps\11.2.0\grid\BIN\srvctl stop scan_listener
C:\Users\dba_user>D:\apps\11.2.0\grid\BIN\srvctl start scan_listener

After restart, sessions now can connect with database using new port number. We can also check status of listener and scan listener using “lsnrctl” command, and see if these are listening on new ports

C:\Users\dba_user>lsnrctl status listener

LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 27-NOV-2015 10:51:21

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
Start Date                15-NOV-2015 20:29:43
Uptime                    11 days 14 hr. 21 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\apps\11.2.0\grid\network\admin\listener.ora
Listener Log File         D:\apps\11.2.0\grid\log\diag\tnslsnr\racnodea\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\LISTENERipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.133.125.139)(PORT=1531)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.133.125.141)(PORT=1531)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+asm1", status READY, has 1 handler(s) for this service...
Service "mydbq" has 1 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
Service "mydbq.mydomain.com" has 1 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
Service "mydbqXDB" has 1 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Users\dba_user>D:\apps\11.2.0\grid\BIN\LSNRCTL.EXE status listener_scan1

LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 27-NOV-2015 10:56:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
Start Date                15-NOV-2015 20:29:45
Uptime                    11 days 14 hr. 26 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\apps\11.2.0\grid\network\admin\listener.ora
Listener Log File         D:\apps\11.2.0\grid\log\diag\tnslsnr\racnodea\listener_scan1\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\LISTENER_SCAN1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.133.125.145)(PORT=1532)))
Services Summary...
Service "mydbq" has 2 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
  Instance "mydbq2", status READY, has 1 handler(s) for this service...
Service "mydbq.apac.wdpr.disney.com" has 2 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
  Instance "mydbq2", status READY, has 1 handler(s) for this service...
Service "mydbqXDB" has 2 instance(s).
  Instance "mydbq1", status READY, has 1 handler(s) for this service...
  Instance "mydbq2", status READY, has 1 handler(s) for this service...
The command completed successfully

Wednesday, March 2, 2016

ORA-01017: invalid username/password - While Starting Instance using srvctl

If you face ORA-01017, following could be the reasons of this error.
  • Username has been specified wrongly – check the username.
  • Password has been specified wrongly – check the password.
  • If you see this error while logging in as user SYS, check if password file exists. If not, re-create the password file or enable OS authentication.
  • If you are using srvctl to start an instance/database in a RAC or non-RAC environment, and you face ORA-01017, it would mean that OS authentication is not properly set in your environment. Once I faced this message on my Linux based 11.2.0.4 RAC and the reason of this error was: because parameter SQLNET.AUTHENTICATION_SERVICES was set to NTS (even value “NTS” is wrong for Linux environment) in the SQLNET.ORA file of GRID home. I removed this parameter and then restarting of CRS resolved this issue. If this parameter has a value of “none” you may face same issue. So, just remove this parameter and restart CRS