Database and Systems Administrator

Install JAVA

The instructions below are for installing version Java 8 Update 73 (8u73). If you are installing another version, make sure you change the version number appropriately when you type the commands at the terminal

Example: For Java 8u79 replace 8u73 with 8u79

Note that, as in the preceding example, the version number is sometimes preceded with the letter 'u' and sometimes it is preceded with an underbar, for example jre1.8.0_73

1. Become root by running su and entering super-user password

2. Change to the directory in which you want to install. Type:

cd directory_path_name

For example to install the software in /usr/java/ directory, Type:

cd /usr/java

3. Uninstall any earlier installations of the Java packages

rpm -e package_name

4. Install the package

rpm -ivh jre-8u73-linux-x64.rpm

To upgrade a package

rpm -Uvh jre-8u73-linux-x64.rpm

5. Delete '.rpm' file if you want to save disk space

6. Exit the root shell. No need

ORA-00106: cannot startup/shutdown database when connected to a dispatcher

Problem Description

We are running a multi-threaded server and are trying to shutdown the database. But it fails through below message

ORA-00106: cannot startup/shutdown database when connected to a dispatcher

In simplest, ORA-00106 means following according to oracle documentation

Cause: An attempt was made to startup/shutdown database when connected to a shared server via a dispatcher.
Action: Re-connect as user INTERNAL without going through the dispatcher.
For most cases, this can be done by connect to INTERNAL without specifying a network connect string

Cause of the Problem
The problem happened because you are trying to shutdown the database while connected to a shared server process.
Because you cannot startup or shutdown a database while connected to a shared server process via a dispatcher.
To shutdown or startup a database you must connect via a dedicated server process.

Solution of the Problem

In order to startup/shutdown a database, you must connect via a dedicated server process and not a shared process.
If you are connecting to database server through TNS entry then add the following entry to your tnsnames.ora file in the address_list section:

(SERVER=DEDICATED)

Example of a TNS entry that establish a dedicated connection:

service_name=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = PROTOCOL_NAME)
(Host = server_name)
(Port = port_number)
)
)
(CONNECT_DATA =
(SID = sid_name)
(SERVER=DEDICATED)
)
)

Another solution is login to the server and issue startup/shutdown from that computer by connecting as sysdba without any TNS entry

Catuppst.sql fails with "GGUSER.DDLREPLICATION" does not exist after upgrading the database

ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of GGUSER.DDLREPLICATION
ORA-04067: not executed, package body "GGUSER.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSER.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSER.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSER.DDLREPLICATION"

Followed by this, utlrp.sql would also result the same error.

Database components "Oracle XML Database" and "Oracle Database Packages and T" (CATPROC) will be in INVALID state.

Solution:

According to below Metalink note

ORA-03113: During an Upgrade while Running Catuppst.sql ORA-6508 "GGUSER.DDLREPLICATION" in alert.log (Doc ID 1506156.1)

we need to disable the system trigger that was enable for DDL replication during Golden gate setup.

SQL> alter trigger sys.GGS_DDL_TRIGGER_BEFORE disable;

Now, re run catuppst.sql and utlrp.sql

Once this goes successfully, enable the trigger again.

SQL> alter trigger sys.GGS_DDL_TRIGGER_BEFORE enable;

Install JDK / JRE in Oracle Linux / RHEL

To install Java in RHEL or Oracle Linux based Desktop machine, open a browser and navigate to Java SE official download page and grab the latest .rpm binary package in your system.

Install Java JDK / JRE

After Java .rpm file download completes, navigate to the directory where Java package has been downloaded and install Java JDK/JRE by issuing the below command

# yum install jdk-<Version>_linux-x64_bin.rpm

# yum install jre-<Version>_linux-x64_bin.rpm

Setup Java Environment Variables

Next, insert Java environment variables and executable files path into your system $PATH variable by issuing the below commands
This method ensures that Java environment variables and executable will be accessible system wide

-------- Set JAVA_HOME and Path Variable JDK --------

# export JAVA_HOME=/usr/java/jdk-<Version>
# export PATH=$PATH:/usr/java/jdk-<Version>/bin

-------- Set JAVA_HOME and Path Variable JRE --------

# export JAVA_HOME=/usr/java/jre-<Version>
# export PATH=$PATH:/usr/java/jre-<Version>/bin

Check Java JDK / JRE Version

# java --version
java <Version{
Java(TM) SE Runtime Environment (build <Version>)
Java HotSpot(TM) 64-Bit Server VM (build <Version>, mixed mode)

OLEDB provider "OraOLEDB.Oracle" for linked server

SQL Server Linked Server to Oracle Fails with Cannot Initialize Data Source Object error

You have a running SQL Server and you need to create a linked server to Oracle.
You follow these steps

Install Oracle client in the SQL Server
Setup tnsnames.ora and sqlnet.ora files
Test connection to Oracle using tnsping and sqlplus
Add Linked Server

Now when you test it, you get a long error message saying

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORATEST"
OLEDB provider "OraOLEDB.Oracle" for linked server "ORATEST" returned message "ORA-12154: TNS: Could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)

This has been an issue with Linked Server to Oracle from the beginning of time. SQL Server does not see Oracle driver after you install it until you restart the SQL Server.

Restart your SQL Server service your linked server will start working.

How to resolve ORA-24247: network access denied by access control list (ACL)

sqlplus sys as sysdba

SQL>SELECT * FROM dba_network_acl_privileges;

SQL>select * from dba_network_acls;

SQL>GRANT EXECUTE ON UTL_TCP TO scott;
SQL>GRANT EXECUTE ON UTL_SMTP TO scott;
SQL>GRANT EXECUTE ON UTL_MAIL TO scott;

Assign Privileges to already present ACL

BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'acl_for_accd.xml',
principal => 'scott',
is_grant => true,
privilege => 'connect');
COMMIT;
END;
/

BEGIN
dbms_network_acl_admin.assign_acl (
acl => 'acl_for_accd.xml',
host => 'IP Address of Email Server',
lower_port => 25,
upper_port => 80
);
COMMIT;
END;
/

OR

BEGIN
dbms_network_acl_admin.assign_acl (
acl => 'acl_for_accd.xml',
host => '*',
);
COMMIT;
END;
/

Oracle Data Pump Backup Day Wise

#!/bin/sh

export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/dbhome_1
export BKP_HOME=$ORACLE_BASE/data_pump
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID = oratrg

echo 'Exporting Full Oracle Database Backup....'

expdp hr/hr directory=<directory name> dumpfile=FULL-$(date +%A).dmp logfile=FULL-$(date +%A).log full=y

tar -cvzf $BKP_HOME/FULL-$(date +%A).tar.gz $BKP_HOME/FULL-$(date +%A).dmp
cat $BKP_HOME/FULL-$(date +%A).log

echo 'Transferring Oracle Export Dump file to Remote location ....'

cd $BKP_HOME
smbclient //<Remote_Location>/D$ -U <Windows Domain Name>/<User>%<Password-c "put FULL-$(date +%A).log"
smbclient //<Remote_Location>/D$ -U <Windows Domain Name>/<User>%<Password -c "put FULL-$(date +%A).tar.gz"

Export Database using smbclient

#!/bin/sh

export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/dbhome_1
export BKP_HOME=$ORACLE_BASE/data_pump
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID = oratrg

echo 'Exporting Full Oracle Database Backup....'

expdp hr/hr directory=data_pump dumpfile=full.dmp logfile=full.log reuse_dumpfiles=y full=y

tar -cvzf $BKP_HOME/full.tar.gz $BKP_HOME/full.dmp
cat $BKP_HOME/full.log

echo 'Transferring Oracle Export Dump file to Remote location ....'

cd $BKP_HOME
smbclient //<Remote_Location>/D$ -U <Windows Domain Name>/<User>%<Password -c "put full.dmp"
smbclient //<Remote_Location>/D$ -U <Windows Domain Name>/<User>%<Password-c "put full.log"
smbclient //<Remote_Location>/D$ -U <Windows Domain Name>/<User>%<Password -c "put full.tar.gz"

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

Uninstall Oracle product
Install JAVA
Set Environmental Variables for JAVA_HOME and JRE_HOME
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;