Earlier I have already demonstrated adding and managing custom services in a RAC environment in a blog post and a video.
But what if you are running Single Instance and not using Grid Infrastructure? The srvctl command in Grid Infrastructure is what you'd use to add and manage services in RAC and Oracle Restart environments. But without Grid Infrastructure, you can fall back on DBMS_SERVICE.
The DBMS_SERVICE API has been available since Oracle 8i -- when Services were introduced.
Here is a quick demo of some facilities with DBMS_SERVICE.
1. Adding a Custom Service into a PDB :
Connecting to the service via tnsnames.
2. Disconnecting all connected users on the Service
(Instead of DBMS_SERVICE.IMMEDIATE, we could also specify DBMS_SERVICE.POST_TRANSACTION).
3. Shutting down a Service without closing the PDB :
Does restarting the Database, restart this custom service?
I had to reSTART this custom service ('HR') after the PDB was OPENed.
Services is a facility that has been available since 8i non-OPS. However, Services were apparently only being used by most sites in RAC environments.
Services allow you to run multiple "applications" (each application advertised as a Service) within the same (one) database.
Note that, in a RAC environment, srvctl configuration of Services can configure auto-restart of the Service.
UPDATE : A Video using a different PDB (in the same CDB) and ServiceName is posted here.
.
.
.
But what if you are running Single Instance and not using Grid Infrastructure? The srvctl command in Grid Infrastructure is what you'd use to add and manage services in RAC and Oracle Restart environments. But without Grid Infrastructure, you can fall back on DBMS_SERVICE.
The DBMS_SERVICE API has been available since Oracle 8i -- when Services were introduced.
Here is a quick demo of some facilities with DBMS_SERVICE.
1. Adding a Custom Service into a PDB :
$sqlplus system/oracle@NEWPDB
SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 22:52:21 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Jul 10 2017 22:22:30 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show con_id
CON_ID
------------------------------
4
SQL>
SQL> execute dbms_service.create_service('HR','HR');
PL/SQL procedure successfully completed.
SQL> execute dbms_service.start_service('HR');
PL/SQL procedure successfully completed.
SQL>
Connecting to the service via tnsnames.
SQL> connect hemant/hemant@HR Connected. SQL> show con_id CON_ID ------------------------------ 4 SQL>
2. Disconnecting all connected users on the Service
$sqlplus system/oracle@NEWPDB SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 23:02:47 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sat Aug 05 2017 23:02:28 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> SQL> execute dbms_service.disconnect_session(- > service_name=>'HR',disconnect_option=>DBMS_SERVICE.IMMEDIATE); PL/SQL procedure successfully completed. SQL> In the HEMANT session connected to HR : SQL> show con_id ERROR: ORA-03113: end-of-file on communication channel Process ID: 5062 Session ID: 67 Serial number: 12744 SP2-1545: This feature requires Database availability. SQL>
(Instead of DBMS_SERVICE.IMMEDIATE, we could also specify DBMS_SERVICE.POST_TRANSACTION).
3. Shutting down a Service without closing the PDB :
SQL> execute dbms_service.stop_service('HR');
PL/SQL procedure successfully completed.
SQL>
SQL> connect hemant/hemant@HR
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE.
SQL>
Does restarting the Database, restart this custom service?
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 343936920 bytes
Database Buffers 478150656 bytes
Redo Buffers 7974912 bytes
Database mounted.
Database opened.
SQL> alter pluggable databas all open;
alter pluggable databas all open
*
ERROR at line 1:
ORA-02000: missing DATABASE keyword
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> connect hemant/hemant@HR
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE.
SQL>
SQL> connect system/oracle@NEWPDB
Connected.
SQL> execute dbms_service.start_service('HR');
PL/SQL procedure successfully completed.
SQL> connect hemant/hemant@HR
Connected.
SQL>
I had to reSTART this custom service ('HR') after the PDB was OPENed.
Services is a facility that has been available since 8i non-OPS. However, Services were apparently only being used by most sites in RAC environments.
Services allow you to run multiple "applications" (each application advertised as a Service) within the same (one) database.
Note that, in a RAC environment, srvctl configuration of Services can configure auto-restart of the Service.
UPDATE : A Video using a different PDB (in the same CDB) and ServiceName is posted here.
.
.
.
2 comments:
Hello Hemant,
I have seen your contact from the blogs and I wanted to reach out to see if you have any solution for the my questions.
Any help is highly appreciated.
Our Oracle production database (11.2.0.4) running on Linux has a Tablespace which consists of two datafiles (stores Application Audit log not DB audit logs) one with 31GB which is 100% full and other new datafile has been added with 10GB autoextend to maxsize of 20GB (in which 8GB has already been used out of 20GB). The application audit log table was created on 2014.
The database is growing drastically since a month and the retention they want to keep is for 7 years. Whenever they run a report, they want the data from the past 7 years to be accessed.
Is there any way that these application audit logs can be moved from database and kept at OS level by archiving them or any other methods where the database growth comes down by compressing?
Anonymous,
If you have the Enterprise Edition and have purchased the Partitioning Option, you would partition the audit log table by date. You can compress individual partitions as well (ALTER TABLE tablename MOVE PARTITION partitionname COMPRESS (you'd have to rebuild index partitions and global indexes as ROWIDs change). You can also move the older partitions to low cost storage.
Post a Comment