Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************Got the opportunity to pen a Book on Database Cloud Services.......More details to follow.***************

Title : Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2020

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


Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Azure Certified Administrator

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Tuesday, March 12, 2019

Create new service in Oracle Cloud DBCS system





shaikprod@shaikprod ~/.ssh
$ ssh opc@129.213.136.114
The authenticity of host '129.213.136.114 (129.213.136.114)' can't be establishe                              d.
RSA key fingerprint is SHA256:M1nYMnHul6e0Mpb3llWcJovCwyJBVD+iNOLQz6q1niI.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '129.213.136.114' (RSA) to the list of known hosts.

[opc@ocidb ~]$ sudo su - oracle

[oracle@ocidb ~]$ ps -ef| grep smon
grid     72807     1  0 16:39 ?        00:00:00 asm_smon_+ASM1
oracle   82804 82133  0 17:31 pts/0    00:00:00 grep smon
oracle   86482     1  0 17:01 ?        00:00:00 ora_smon_ocidb

[oracle@ocidb ~]$



SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      ocidb_iad1sp.sub05111533480.vcn
                                                 0511153348.oraclevcn.com
SQL> select service_id,name,pdb,con_id from cdb_services order by con_id;

SQL> set linesize 1000
SQL> select service_id,name,pdb,con_id from cdb_services order by con_id;

SERVICE_ID NAME                                                             PDB                                                    CON_ID
---------- ---------------------------------------------------------------- ----------------------------------                     ---------------------------------------------------------------------------------------------- ----------
         1 SYS$BACKGROUND                                                   CDB$ROOT                                                    1
         6 ocidb_iad1sp.sub05111533480.vcn0511153348.oraclevcn.com           CDB$ROOT                                                    1
         5 ocidbXDB                                                          CDB$ROOT                                                    1
         2 SYS$USERS                                                        CDB$ROOT                                                    1
         7 ocidb_pdb1.sub05111533480.vcn0511153348.oraclevcn.com             ocidb_PDB1                                                   3




SQL>  col name for a20
SQL> select con_id,name,open_mode from v$containers;

    CON_ID NAME                 OPEN_MODE
---------- -------------------- ----------
         3 ocidb_PDB1            READ WRITE



SQL> alter session set container=ocidb_PDB1;

Session altered.


SQL> col pdb for a20
SQL> select service_id,name,pdb,con_id from cdb_services order by con_id;

SERVICE_ID NAME                                                                   PDB                      CON_ID
---------- ---------------------------------------------------------------------- -------------------- ----------
         7 ocidb_pdb1.sub05111533480.vcn0511153348.oraclevcn.com                   ocidb_PDB1                     3



SQL> exec dbms_service.CREATE_SERVICE('shaikpdb','shaik_pdb.sub05111533480.vcn0511153348.oraclevcn.com');

PL/SQL procedure successfully completed.



SQL> select service_id,name,pdb,con_id from cdb_services order by con_id;

SERVICE_ID NAME                                                                   PDB                      CON_ID
---------- ---------------------------------------------------------------------- -------------------- ----------
         7 ocidb_pdb1.sub05111533480.vcn0511153348.oraclevcn.com                   ocidb_PDB1                     3
         1 shaikpdb                                                               ocidb_PDB1                     3



SQL> exec dbms_service.start_service('shaikpdb');

PL/SQL procedure successfully completed.

SQL> alter system register;

System altered.



SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile altered.

SQL> create user user99 identified by WelCome99;

User created.


SQL> grant create session to user99;

Grant succeeded.



SQL> alter pluggable database save state;

Pluggable database altered.




SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 12-MAR-2019 17:43:42

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                12-MAR-2019 16:40:06
Uptime                    0 days 1 hr. 3 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.2.0.1/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ocidb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.39)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "83e996f4d58506f6e0532700000a2d76.sub05111533480.vcn0511153348.oraclevcn.com" has 1 instance(s).
  Instance "ocidb", status READY, has 2 handler(s) for this service...
Service "ocidbXDB.sub05111533480.vcn0511153348.oraclevcn.com" has 1 instance(s).
  Instance "ocidb", status READY, has 1 handler(s) for this service...
Service "ocidb_iad1sp.sub05111533480.vcn0511153348.oraclevcn.com" has 1 instance(s).
  Instance "ocidb", status READY, has 2 handler(s) for this service...
Service "ocidb_pdb1.sub05111533480.vcn0511153348.oraclevcn.com" has 1 instance(s).
  Instance "ocidb", status READY, has 2 handler(s) for this service...
Service "shaik_pdb.sub05111533480.vcn0511153348.oraclevcn.com" has 1 instance(s).
  Instance "ocidb", status READY, has 2 handler(s) for this service...
The command completed successfully

SQL>


SQL> !
[oracle@ocidb ~]$  sqlplus user99/WelCome99@ocidb:1521/shaik_pdb.sub05111533480.vcn0511153348.oraclevcn.com

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 12 17:48:15 2019

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


Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL> show user
USER is "USER99"






[oracle@ocidb ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

[oracle@ocidb ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ocidb =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ocidb.sub05111533480.vcn0511153348.oraclevcn.com)(PORT = 1521))


ocidb_IAD1SP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ocidb.sub05111533480.vcn0511153348.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ocidb_iad1sp.sub05111533480.vcn0511153348.oraclevcn.com)
    )
  )

shaikpdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ocidb.sub05111533480.vcn0511153348.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = shaik_pdb.sub05111533480.vcn0511153348.oraclevcn.com)
    )
  )



[oracle@ocidb ~]$ tnsping shaikpdb

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 12-MAR-2019 17:50:06

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

Used parameter files:
/u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocidb.sub05111533480.vcn0511153348.oraclevcn.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = shaik_pdb.sub05111533480.vcn0511153348.oraclevcn.com)))
OK (0 msec)


[oracle@ocidb ~]$ sqlplus user99@shaikpdb

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 12 17:50:16 2019

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

Enter password:
Last Successful login time: Tue Mar 12 2019 17:48:15 +00:00

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production


SQL> show user
USER is "USER99"
SQL>