OracleNotes

Blog

  • ODC Appreciation Day: Two Cool 12c Security Features #ThanksODC #ThanksOTN

    There are quite a lot of new features in the 12c database that I like and have discussed and talked about them at various events. On this #ThanksODC day, I would like to take the opportunity to thank Oracle Technology Network (or Oracle Developer Community) and Oracle ACE Program for all the support they provide to the DBA and Developer community to share and gain knowledge.

    Two simple addition to the 12c database makes my life much easier on the security front. The requirement was to lock the user accounts that are not logged into the database for consecutive 60 days.

    1.  Last Login Time 

    In releases prior to Oracle Database 12c 12.1 to find out when a user last successfully logged into the database, we need to enable session auditing, which has some overhead and the DBA needs to set up routines to clean the audit table, etc. In 11g database, I had to enable session auditing (keep the session audit records for at least 61 days) and write a SQL querying DBA_USERS and DBA_AUDIT_SESSION to list out all the active users who did not use the database for 60 days.

    In 12.1, Oracle automatically registers the successful login time in SYS.USER$ table and is visible in DBA_USERS. The LAST_LOGIN column of DBA_USERS shows the last login time of the user in the database. In 12.1 database, my requirement is met by writing a simple SQL against DBA_USERS using the LAST_LOGIN column! No auditing required. The result is used to build dynamic SQL to lock each account using ALTER USER ACCOUNT LOCK statement (similar to 11g).

    BTW, have you noticed, when you login to the database using SQL*Plus, you will see the last login time displayed as well.

    2.  Inactive Account Lock

    My requirement got completely automated in 12.2 database. Oracle introduced the INACTIVE_ACCOUNT_TIME parameter for user profiles. The INACTIVE_ACCOUNT_TIME profile parameter locks a user account that has not logged in to the database instance in a specified number of days. The default value for INACTIVE_ACCOUNT_TIME is 35. The minimum setting is 15 and the maximum is 24855.

    Thanks, Oracle!

    #ThanksOTN

    #ThanksODC

    And thank you, @OracleBase, for doing the appreciation day again!

  • Oracle Tidbits – September 2017 #oratidbit

    “Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter, and Google+ during weekdays in September 2017. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well… Hope you find these helpful to learn something new or to remind you of its existence and use.

    #oratidbit #db12cR2 The V$MANAGED_STANDBY view is deprecated in Oracle Database 12c Release 2 (12.2.0.1). Use the new view V$DATAGUARD_PROCESS.
    #oratidbit #db12cR2 Starting in Oracle Database 12c Release 2 (12.2), the Advanced Replication feature of Oracle Database is unsupported. Start using Oracle GoldenGate.
    #oratidbit #db12cR2 DBUA in Database 12cR2 has pause and continue functionality: You can stop the upgrade, and continue the upgrade at a later time.
    #oratidbit #db12cR2 init parameter DATA_GUARD_SYNC_LATENCY defines the maximum amount of time (in seconds) that the primary database may wait before disconnecting subsequent destinations after at least one synchronous standby has acknowledged receipt of the redo.
    #oratidbit #db12cR2 init parameter ENABLED_PDBS_ON_STANDBY specifies a subset of pluggable databases (PDBs) for replication on a physical standby of a multitenant container database (CDB). In prior releases, you had to specify either all PDBs or none.
    #oratidbit #db12cR2 Oracle Diagnostic Pack can be used with an Oracle Active Data Guard standby database that is open read-only.
    #oratidbit #db12cR2 When a physical standby database is converted into a primary, STANDBY_DB_PRESERVE_STATES init parameter lets you keep any sessions connected to the standby during the switchover/failover.
    #oratidbit The Enterprise Manager Command Line Interface (EM CLI) enables to access Enterprise Manager functionality through a command-line interface or scripts. EMCLI operates in standard command mode, interactive mode or script mode.
    #oratidbit OEM13c EMCLI client is downloaded from your OMS – https:///em/public_lib_download/emcli/kit/emclikit.jar. To be able to do scripting download emcliadvancedkit.jar.
    #oratidbit The Oracle Database Provider for DRDA is a network front-end that enables client programs to connect to Oracle Database using the Distributed Relational Database Architecture (DRDA) protocol.
    #oratidbit Starting with #DB12cR2, Oracle Net listener supports multiple redirects with the parameter, ALLOW_MULTIPLE_REDIRECTS_listener_name. It supports seamless migration of a PDB from a local database to Public Cloud.
    #oratidbit Starting with #db12cR2, data compression can be set in the sqlnet.ora file using parameters SQLNET.COMPRESSION and SQLNET.COMPRESSION_LEVELS. Setting these parameters in the sqlnet.ora file affects all the connections using the sqlnet.ora file.
    #oratidbit The Automatic Diagnostic Repository (ADR) is a systemwide tracing and logging central repository. The repository is a file-based hierarchical data store for depositing diagnostic information, including network tracing and logging information.
    #oratidbit #db12cR2 Oracle RAC Reader Nodes facilitate Oracle Flex Cluster architecture by allocating a set of read/write instances running Online Transaction Processing (OLTP) workloads and a set of read-only database instances across Hub Nodes and Leaf Nodes in the cluster. In this architecture, updates to the read-write instances are immediately propagated to the read-only instances on the Leaf Nodes, where they can be used for online reporting or instantaneous queries.
    #oratidbit Start and stop Oracle instances with Oracle Enterprise Manager, SQL*Plus, or SRVCTL. OEM and SRVCTL provide options to start and stop all of the instances in an Oracle RAC database with a single step.
    #oratidbit If you stop database instances using “crsctl stop crs” or “crsctl stop cluster -all”, databases are stopped with “shutdown abort”.
    #oratidbit Oracle database startup looks for initialization parameter file in $ORACLE_HOME/dbs directory. The file names checked are spfile<sid>.ora, spfile.ora, init<sid>.ora, in order.
    #oratidbit In #db12cR2 the optimizer uses the UNION-ALL operator to perform the OR expansion. Earlier, CONCATENATION operator was used.
    #oratidbit DBMS_STATS.GET_STATS_HISTORY_RETENTION function shows the current optimizer statistics history retention value. DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY function retrieves the oldest time stamp when statistics history is available.