Oracle 12c SEC_CASE_SENSITIVE_LOGON and ORA-01017

The init.ora/spfile parameter SEC_CASE_SENSITIVE_LOGON got deprecated since Oracle Database 12.1.0.1
This means, we don’t do any further developments to it, you shouldn’t change it from its default TRUE and if you still do you’ll receive a nice warning during STARTUP of your database:

SQL> alter system set sec_case_sensitive_logon=false scope=spfile;
System altered.

SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Recently a user asked me if we’d changed the behavior of this parameter in Oracle Database 12c Release 2 as he receives now an ORA-01017: Invalid username or password error when having SEC_CASE_SENSITIVE_LOGON=FALSE with every user except SYSDBAs

In which situations may you receive an ORA-01017?

This is outlined in the Oracle 12.1 documentation already:
•Ensure that the SEC_CASE_SENSITIVE_LOGON parameter is not set to FALSE if the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set to 12 or 12a
This is because the more secure password versions used for this mode only support case-sensitive password checking.
For compatibility reasons, Oracle Database does not prevent the use of FALSE for SEC_CASE_SENSITIVE_LOGON when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a
Setting SEC_CASE_SENSITIVE_LOGON to FALSE when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a causes all accounts to become inaccessible.

The key is the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER and here’s the difference between Oracle Database 12.1 and Oracle Database 12.2:
•Oracle Database 12.1: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 11 out of the box
•Oracle Database 12.2: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 12 out of the box

Behavior difference Oracle 12.1 vs Oracle 12.2

See this simple example after switching SEC_CASE_SENSITIVE_LOGON=FALSE in both databases (as shown above):
•Oracle Database 12.1.0.2:

SQL> alter user system identified by oracle;
User altered.

SQL> connect system/oracle
Connected.

•Oracle Database 12.2.0.1:

SQL> alter user system identified by oracle;
User altered.

SQL> connect system/oracle
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE

How to workaround the ORA-01017?

First of all you need to edit your sqlnet.ora adding (or lowering) the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a value below 12
But if you try to connect directly after restarting your listener you will receive the same ORA-01017 again
The secret is mentioned in the above documentation link as well, you will have to recreate the user’s password if you need the logon process to work as it did work before Oracle Database 12.2

sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Check in DBA_USERS

SQL> select username, password_versions 
from DBA_USERS 
where username='SYSTEM';

USERNAME      PASSWORD_VERSIONS
------------- ---------------------
SYSTEM        11G 12C

There’s no “10G” mentioned. This will prevent the connection.

Solution: You will have to specify the password again respective ALTER the user(s):

SQL> alter user system identified by oracle;
User altered.

SQL> select username, password_versions 
from DBA_USERS 
where username='SYSTEM';

USERNAME       PASSWORD_VERSIONS
-------------- ----------------------
SYSTEM         10G 11G 12C

SQL> connect system/oracle
Connected.

How to check if Replication components are installed on your SQL Server

If you need to check as to whether or not SQL Replication components are installed on a specific instance of your SQL Server
Execute the following command on that instance which will give you the answer

EXEC sp_ms_replication_installed

ODBC error: SQLSTATE 37000 native database error 4060

Now, when you go to start the extract/replicat, you may get the following error:
ERROR OGG-00551 Database operation failed: Couldn’t connect to [ dns ]. ODBC error: SQLSTATE 37000 native database error 4060. [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database “db_name” requested by the login. The login failed.
The error message is a little bit misleading. It tells you that the process cannot connect to the database which you were able to connect to from the GGSCI command prompt with no issue.

Why is this?
The issue lies in the fact that the manager (MGR) process is running as a service and does not have the correct permissions to access the database from the service.

In searching MOS for this error, I was found Note ID: 1633138.1.
In this note, notice that this issue is known as of Oracle GoldenGate version 12.1.2.x.x. The note also provides you a fix to this issue. In simple terms, since the manager process is running as a service; additional permissions have to be granted to manger.

To grant the SYSADMIN privilege for the manager process follow the below sequence of steps (on windows after all):
1. Manager is installed as service:
Open SQL Server Management studio -> Security ->login>select NT AUTHORITY\SYSTEM ->Right Click -> Properties–>Server Role –>Enable sysadmin role
2. ggsci>stop mgr
3. ggsci>start mgr
4. ggsci>start extract <extract-name>

After granting the sysadmin role, the extract will start.

SQLPLUS.EXE counterprocess error =1392


Resolution
  1. Uninstall Oracle product
  2. Install JAVA
  3. Set Environmental Variables for JAVA_HOME and JRE_HOME
  4. Install Oracle Product again

iptables: No config file. [WARNING]

service iptables start

iptables: No config file. [WARNING]

Resolution
a mitigation is to run, as root, the following 3 commands:
yum install system-config-firewall-base
lokkit --default=server
service iptables restart

ORA-19905: log_archive_format must contain %s, %t and %r

ORA-19905:- this error generate due to incorrect Archive log format in oracle Release 12.1.0.0.1.0.

Problem:-
 SQL> startup;
 ORA-19905: log_archive_format must contain %s, %t and %r

 SQL> startup mount;
 ORA-19905: log_archive_format must contain %s, %t and %r

Solution:
Also solution is given in metalink note 283507.1

  - create pfile from spfile using below command

 SQL> create pfile from spfile;

 File created.

 - got to oracle pfile location and edit the 'log_archive_format' parameter

 [oracle@dns1 prmdb]$ cd /u01/app/oracle/product/12.1.0.0/db_1/dbs/

 [oracle@dns1 dbs]$ vi  initprmdb.ora

 -----------------before update----------------------------------

 prmdb.__db_cache_size=1979711488
 prmdb.__java_pool_size=16777216
 prmdb.__large_pool_size=16777216
 prmdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
 prmdb.__pga_aggregate_target=2097152000
 prmdb.__sga_target=3154116608
 prmdb.__shared_io_pool_size=0
 prmdb.__shared_pool_size=1073741824
 prmdb.__streams_pool_size=33554432
 *.audit_file_dest='/u01/app/oracle/admin/prmdb/adump'
 *.audit_trail='db'
 *.compatible='12.1.0.0.0.0'
 *.control_files='/u01/app/oracle/oradata/prmdb/control01.ctl','/u01/app/oracle/oradata/prmdb/control02.ctl'
 *.cursor_sharing='FORCE'
 *.db_block_size=8192
 *.db_domain=''
 *.db_name='prmdb'
 *.diagnostic_dest='/u01/app/oracle'
 *.dispatchers='(PROTOCOL=TCP) (SERVICE=prmdbXDB)'
*.log_archive_dest_1='LOCATION=/u01/Archive'
*.log_archive_format='arch_%t_%s'
 *.memory_max_target=0
 *.memory_target=0
 *.open_cursors=1500
 *.pga_aggregate_target=2097152000
 *.processes=1000
 *.remote_login_passwordfile='EXCLUSIVE'
 *.session_cached_cursors=1000
 *.sga_target=3154116608
 *.timed_statistics=TRUE
 *.undo_tablespace='UNDOTBS1'

--------------------After update------------------------------------
 prmdb.__db_cache_size=1979711488
 prmdb.__java_pool_size=16777216
 prmdb.__large_pool_size=16777216
 prmdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
 prmdb.__pga_aggregate_target=2097152000
 prmdb.__sga_target=3154116608
 prmdb.__shared_io_pool_size=0
 prmdb.__shared_pool_size=1073741824
 prmdb.__streams_pool_size=33554432
 *.audit_file_dest='/u01/app/oracle/admin/prmdb/adump'
 *.audit_trail='db'
 *.compatible='12.1.0.0.0.0'
 *.control_files='/u01/app/oracle/oradata/prmdb/control01.ctl','/u01/app/oracle/oradata/prmdb/control02.ctl'
 *.cursor_sharing='FORCE'
 *.db_block_size=8192
 *.db_domain=''
 *.db_name='prmdb'
 *.diagnostic_dest='/u01/app/oracle'
 *.dispatchers='(PROTOCOL=TCP) (SERVICE=prmdbXDB)'
*.log_archive_dest_1='LOCATION=/u01/Archive'
*.log_archive_format='arch_%r_%t_%s'
 *.memory_max_target=0
 *.memory_target=0
 *.open_cursors=1500
 *.pga_aggregate_target=2097152000
 *.processes=1000
 *.remote_login_passwordfile='EXCLUSIVE'
 *.session_cached_cursors=1000
 *.sga_target=3154116608
 *.timed_statistics=TRUE
 *.undo_tablespace='UNDOTBS1'

 [oracle@dns1 dbs]$ sqlplus

 SQL*Plus: Release 12.1.0.0.1.0 Production on Mon Mar 3 01:32:10 2014

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

 Enter user-name: /as sysdba
 Connected to an idle instance.

SQL>create spfile from pfile;
File created.

 SQL> startup
 ORACLE instance started.

 Total System Global Area 5261217792 bytes
 Fixed Size                  2222560 bytes
 Variable Size            3271558688 bytes
 Database Buffers         1979711488 bytes
 Redo Buffers                7725056 bytes
 Database mounted.
 Database opened.

 SQL> archive log list;
 Database log mode              Archive Mode
 Automatic archival             Enabled
 Archive destination            /u01/Archive
 Oldest online log sequence     760
 Next log sequence to archive   762
 Current log sequence           762

 SQL> alter system switch logfile;
 System altered.

 SQL> /
 System altered.

 SQL> archive log list;
 Database log mode              Archive Mode
 Automatic archival             Enabled
 Archive destination            /u01/Archive

ORA - 04063: package body "SYS.DBMS_REGISTRY_SYS" has errors while running utlrp...

SQL>@utlrp.sql
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors

BEGIN dbms_registry_sys.validate_components; END;

*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.DBMS_REGISTRY_SYS"
ORA-06512: at line 1

Solution:

SQL>conn sys as sysdba
SQL>shutdown immediate
SQL>startup upgrade

RUN catalog, catproc and utlrp script from SQL Prompt

SQL>shutdown immediate
SQL>startup
SQL>select comp_name,version,status from dba_registry;

JDK Error while Starting and Connecting to Oracle GoldenGate Studio 12c

[oracle@oraprm/bin] cd $GG_HOME
[oracle@oraprm/bin] pwd
/u01/oracle/product/12.1.0.2/gg_home/oggstudio/bin

[oracle@oraprm/bin] ./oggstudio

Type the full pathname of a JDK installation (or Ctrl+C to quit), the path will be stored in /home/oracle/.oggstudio/12.2.1.2.0/product.conf

[oracle@oraprm/bin]  which javac
this will show JAVA Paths

Go to "product.conf"

[oracle@oraprm/bin] cd /home/oracle/.oggstudio/12.2.1.2.0

[oracle@oraprm/bin] gedit product.conf

In "product.conf" set parameter to latest Java Version

SetJavaHome /usr/java/latest

Save the File

[oracle@oraprm/bin] cd $GG_HOME
[oracle@oraprm/bin] pwd
/u01/oracle/product/12.1.0.2/gg_home/oggstudio/bin

[oracle@oraprm/bin] ./oggstudio

OR

[oracle@oraprm/bin]cd $HOME
[oracle@oraprm]gedit .bash_profile
export JAVA_HOME = /usr/java/latest

[oracle@oraprm/bin] cd $GG_HOME
[oracle@oraprm/bin] pwd
/u01/oracle/product/12.1.0.2/gg_home/oggstudio/bin

[oracle@oraprm/bin] ./oggstudio

ORA-27492: unable to run job scheduler

My Oracle application have stopped transferring data to another table.
After doing a bit of troubleshooting,I have narrowed the issue down to the scheduled job PERSON.FIG_TIME_DAILY
The procedure that is called by this job works fine when I run in manually
However, this scheduled job hasn't successfully run in over a day
I tried dropping the job entirely and rebuilding it using the DDL from the same job in my testing environment, but that didn't seem to work.

Unable to run job "PERSON.FIG_TIME_DAILY" scheduler unavailable

From the online research I've done, most users have resolved this problem with some variation of the command

SQL>show parameter  job_queue_processes;
the value was default set to 1000

Than i Google out and run the following

SQL>select attribute_name,  value
from dba_scheduler_global_attribute
where attribute_name = 'SCHEDULER_DISABLED';

ATTRIBUTE_NAME     VALUE
------------------ -----
SCHEDULER_DISABLED TRUE

-- Attribute vanishes when set to false (run as sysdba)

SQL>exec dbms_scheduler.set_scheduler_attribute ( 'SCHEDULER_DISABLED', 'FALSE' );

After this Scheduler started Working

Introduction and Uses

Oracle Dataguard is a disaster recovery solution from Oracle that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.

Uses of Oracle Dataguard

a) Oracle Dataguard ensures high availability, data protection and disaster recovery for enterprise data

b) Dataguard provides a comprehensive set of services that create, maintain, manage and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions

c) With Dataguard, administrators can optionally improve production database performance by offloading resource – intensive backup and reporting operations to standby systems.

Why are OPS$ accounts a security risk in a client/server environment

If you allow people to log in with OPS$ accounts from Windows Workstation, you cannot be sure who they really are. With terminals, you can rely on operating system passwords with Windows you cannot.

If you set REMOTE_OS_AUTHENT = TRUE in your init.ora file, Oracle assumes that remote OS has authenticated the user.

If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password

IDENTIFIED EXTERNALLY will only be in effect from the local host.

Also, if you are using "OPS$" as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY

What is the difference between restoring and recovering?

Restoring involves copying backup files from secondary storage (backup media) to disk.

This can be done to replace damaged files or to copy/move a database to a new location.

Recovery is the process of applying redo log to the database to roll it forward.

One can roll – forward until a specific point – in – time (before the disaster occurred) or roll – forward until the last transaction recorded in the log files.

SQL> connect SYS as SYSDBA
SQL> RECOVER DATABASE UNTIL TIME '2016 – 03 – 06:16:00:00' USING BACKUP CONTROLFILE; 

What strategies are available for backing – up an Oracle database?

The following methods are valid for backing – up an Oracle database 

Export / Import – Exports are "logical" database backups in that they extract logical definitions and data from the database to a file.

Cold or Offline Backups – Shutdown the database and backup up all data, log and control files

Hot or Online Backups – If the databases are available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files

Also remember to back up the control files and archived redo log files.

RMAN Backups – While the database is Offline or Online use "RMAN" utility to back up database

Oracle DataPump - ExpDp

It is advisable to use more than one of these methods to backup your database.

For example, if you choose to do Online database backups; also cover yourself by doing database exports.

Also test all backup and recovery scenarios carefully. It is better to be safe than sorry. 

Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files etc.


If your database is in ARCHIVELOG mode, you also need to backup archived log files.

Why and when should one backup database?

Backup and recovery is one of the most important aspects of a DBA job. 
If you lose your company's data, you could very well lose your job.
Hardware and software can always be replaced, but your data may be irreplaceable!
Normally one would schedule a hierarchy of daily, weekly and monthly backups, however consult with your users before deciding on a backup schedule.
  1. Backup frequency normally depends on the following factors
  2. Rate of data change/ transaction rate
  3. Database availability/ Can you shutdown for cold backups? 
  4. Criticality of the data/ Value of the data to the company
  5. Read – only table space needs backing up just once right after you make it read only
  6. If you are running in archivelog mode you can backup parts of a database over an extended cycle of days
  7. If archive logging is enabled one needs to backup archived log files timeously to prevent database freezes
Carefully plan backup retention periods. 
Ensure enough backup media (tapes) are available and that old backups are expired in – time to make media available for new backups.
Offsite vaulting is also highly recommended.
Frequently test your ability to recover and document all possible scenarios.
Remember, it's the little things that will get you. Most failed recoveries are a result of organizational errors and miscommunications

Can one resize tablespaces and datafiles

One can manually increase or decrease size of a datafile from Oracle 7.2 using command.

SQL>ALTER DATABASE DATAFILE 'filename2' RESIZE 100M; 

Because one can change the sizes of data files, one can add more space to your database without adding more datafiles. This is beneficial if concerned about reaching the maximum number of datafiles allowed in your database
Manually reducing the sizes of datafiles allows one to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements. 

Also, datafiles can be allowed to automatically extend if more space is required.

Command 

SQL>CREATE TABLESPACE pcs_data_ts
DATAFILE 'c: \ora_apps\pcs\pcsdata1.dbf' SIZE 3M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
DEFAULT STORAGE (INITIAL 10240
NEXT 10240
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT; 

What is Fine Grained Auditing?

Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table.

A list of defined policies can be obtained from DBA_AUDIT_POLICIES
Audit records are stored in DBA_FGA_AUDIT_TRAIL

Example:
Add policy on table with auditing condition...
execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
Must ANALYZE, this feature works with CBO (Cost Based Optimizer)

SQL>analyze table EMP compute statistics;
SQL>select * from EMP where c1 = 11; -- Will trigger auditing
SQL>select * from EMP where c1 = 09; -- No auditing

Now we can see the statements that triggered the auditing condition...
SQL>select sqltext from sys.fga_log$;
SQL>delete from sys.fga_log$; 

When should one rebuild an index?

You can run 'ANALYZE INDEX VALIDATE STRUCTURE' command on the affected indexes; each invocation of this command creates a single row in the INDEX_STATS view. 
This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE 
The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS' 

How does one create a RMAN recovery catalog?

Start by creating a database schema (usually called RMAN)

Assign an appropriate table space to it and grant it recovery_catalog_owner role.

sqlplus sys
SQL>create user RMAN identified by RMAN;
SQL> alter user RMAN default table space tools temporary tablespace temp;
SQL> alter user RMAN quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to RMAN;

Next, log in to RMAN and create the catalog schema.

Prior to Oracle 8i this was done by running catRMAN.sql script.

RMAN> catalog RMAN/RMAN
RMAN>create catalog tablespace tools;
RMAN> exit; 

You can now continue by registering your databases in the catalog.

RMAN catalog RMAN/RMAN target backdba/backdba
RMAN> register database; 

I've lost some Rollback Segments, how can I get my DB back

Restart your database with following INIT.ORA parameter _Corrupted_rollback_segments = (rbs01, rbs01, rbs03, rbs04) if one of your rollback segments is corrupted.

You can then drop the corrupted rollback segments and create it from scratch.

Caution is advised when enabling this parameter, as uncommitted transactions will be marked as committed.

One can very well end up with lost or inconsistent data!!!

Please contact Oracle Support before using it.
 _Corrupted_rollback_segments = (rbs01, rbs01, rbs03, rbs04) 

I've lost my REDO LOG files, how can I get my DB back?

The following INIT.ORA parameter _allow_resetlogs_corruption = true may be required if your current redo logs are corrupted or blown away.

Caution is advised when enabling this parameter as you might end up losing your entire database

Please contact Oracle Support before using it. _allow_resetlogs_corruption = true 

How to fix: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

Open your terminal and type mysql -u root -p Enter your password. Hopefully your MySQL is logged in now.