Oracle 11g Logo

RMAN-06900 RMAN-06901 ORA-19921

Received RMAN-06900,RMAN-06901 and ORA-19921 while logged in to RMAN prompt to verify daily backups. (OS:Redhat linux 6.5)

WARNING message:

[oracle@primary logs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 26 10:34:39 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BLADE1 (DBID=3381279798)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded
RMAN>

On further diagnosing and suggested by OERR utility, I have realized there is one old active session from Aug 23, as mentioned below.

[oracle@primary ~]$ oerr ORA 19921
19921, 00000, "maximum number of %s rows exceeded"
// *Cause: The maximum number of rows in the V$RMAN_STATUS or V$RMAN_OUTPUT
// table has been exceeded.
// *Action: Close some of existing and unused RMAN connections and sessions.

So lets find out old session with ps -ef:

[oracle@primary logs]$ ps -ef|grep rman
oracle 543 32693 0 10:50 pts/4 00:00:00 grep rman
oracle 19783 19782 47 Aug23 ? 1-07:13:42 rman target /

There is one old active session above, OS ID: 19783, since 23 aug.

[oracle@primary ~]$ date
Fri Aug 26 10:57:13 IST 2016
[oracle@primary logs]$ kill -9 19783

After successfully killed old session, we are no more facing mentioned list of warnings, as below:

[oracle@primary logs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 26 10:50:54 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BLADE1 (DBID=3381279798)
RMAN>

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

rename/relocate SYSTEM, SYSAUX or User Tablespace datafiles

This is traditional approach to move/rename SYSTEM, SYSAUX or users tablespace. In this method, we need database downtime as we can’t take SYSTEM or SYSAUX tablespace offline.

I recommend full cold backup before performing mentioned activity.

Consider scenario where I want to move SYSTEM and SYSAUX tablespace to new location.

Step I:

After considering full cold backup of your database, shutdown database with normal or immediate mode only.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step II:

After shut down database normally, I am going to copy datafiles from original location to its new location.

Following query will provide tablespace name and its datafiles details.

SQL> select file_name,tablespace_name from dba_data_files;

Copying datafiles:

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/system01.dbf /u01/app/oracle/oradata/RTS_NEW/system01.dbf
[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/sysaux01.dbf /u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf

After copy, verify size of datafiles on both locations to check whether copy successful or not.

Step III:

Start database in mount mode:

[oracle@PR oradata]$ sqlplus / as sysdba 
SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
Database mounted.

Step IV:

After successful mount, rename the datafiles to its locations as below:

SQL> alter database rename file '/u01/app/oracle/oradata/RTS/system01.dbf' to '/u01/app/oracle/oradata/RTS_NEW/system01.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/RTS/sysaux01.dbf' to '/u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf';
Database altered.

Step V:

Open database:

SQL> alter database open;
Database altered.

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS READ WRITE

Verify that system and sysaux datafiles are moved/renamed successful to new location.

SQL> select file_name,tablespace_name from dba_data_files;
/u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/RTS_NEW/system01.dbf SYSTEM

SYSTEM and SYSAUX datafiles relocated successfully to new location.

This can be done with the help of RMAN, but only non-system tablespaces can be relocated. Click me to know more about it.

OR

You can relocate datafiles without getting offline in oracle 12c.

Thanks, Stay Tune. 🙂

Oracle 11g Logo

ORA-00600: internal error code, arguments:[4194] ORA-00603: ORACLE server session terminated by fatal error. PMON terminating the instance due to error 472

Oracle instance terminated immediate after instance startup due to following mentioned oracle error:

Cause:
A mismatch has been detected between Redo records and rollback (Undo) records.
We are validating the Undo record number relating to the change being applied against the maximum undo record number recorded in the undo block. This error is reported when the validation fails. This error may indicate a rollback segment corruption.
Note: This may require a recovery from a database backup depending on the situation.

Adrci logs are as follows:

Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_smon_11168.trc (incident=1014790):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
replication_dependency_tracking turned off (no async multimaster replication found)
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014790/db1_smon_11168_i1014790.trc
2016-06-30 11:13:35.355000 +05:30
Starting background process QMNC
QMNC started with pid=25, OS id=11249
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Completed: ALTER DATABASE OPEN
2016-06-30 11:13:36.558000 +05:30
db_recovery_file_dest_size of 163840 MB is 1.12% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery stopped at EOT rba 13618.106.16
Block recovery completed at rba 13618.106.16, scn 0.3980712368
Doing block recovery for file 3 block 160
Resuming block recovery (PMON) for file 3 block 160
Block recovery from logseq 13618, block 56 to scn 3980712329
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.59.16, scn 0.3980712330
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_smon_11168.trc:
ORA-01595: error freeing extent (10) of rollback segment (3))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Starting background process SMCO
SMCO started with pid=29, OS id=11268
Trace dumping is performing id=[cdmp_20160630111337]
2016-06-30 11:13:37.819000 +05:30
Starting background process CJQ0
CJQ0 started with pid=28, OS id=11276
2016-06-30 11:13:41.579000 +05:30
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j001_11285.trc (incident=1014942):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014942/db1_j001_11285_i1014942.trc
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j004_11291.trc:
2016-06-30 11:13:43.111000 +05:30
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.106.16, scn 0.3980712374
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j000_11283.trc (incident=1014934):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014934/db1_j000_11283_i1014934.trc
Trace dumping is performing id=[cdmp_20160630111343]
2016-06-30 11:13:45.937000 +05:30
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.106.16, scn 0.3980712374
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j000_11283.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j001_11285.trc (incident=1014943):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014943/db1_j001_11285_i1014943.trc
2016-06-30 11:13:46.903000 +05:30
Trace dumping is performing id=[cdmp_20160630111346]
2016-06-30 11:13:48.279000 +05:30
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j001_11285.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Trace dumping is performing id=[cdmp_20160630111349]
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1019488/db1_j001_11285_i1019488.trc
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1019488/db1_j001_11285_i1019488.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Trace dumping is performing id=[cdmp_20160630111351]
2016-06-30 11:13:53.489000 +05:30
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.106.16, scn 0.3980712374
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_pmon_11144.trc (incident=1014702):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014702/db1_pmon_11144_i1014702.trc
2016-06-30 11:13:55.766000 +05:30
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_pmon_11144.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 11144): terminating the instance due to error 472
Instance terminated by PMON, pid = 11144

Workaround:
Startup database in mount mode:

SQL> startup mount;

Check current undo tablespace:

SQL> show parameter undo_tablespace;

Alter undo management to ‘MANUAL’:

SQL> alter system set undo_management='MANUAL' scope=spfile;

Bounce database to open mode, meanwhile please verify adrci logs, if there is problem.

SQL> shut immediate;
 SQL> startup;

Create new undo tablespace if everything is fine in adrci logs:

SQL> create undo tablespace newundotbs datafile '/u02/oradata/db1/newundotbs01.dbf' size 5G autoextend on next 300M maxsize 31G;

Change default undo tablespace to new one:

SQL> alter system set undo_tablespace='NEWUNDOTBS' scope=spfile;

Alter undo management to ‘AUTO’:

SQL> alter system set undo_management='AUTO' scope=spfile;

Bounce the database:

SQL> shut immediate;
SQL> startup;

Verify current undo tablespace:

SQL> show parameter undo_tablespace

done.

Thank you.
Stay Tune. 🙂

Oracle 11g Logo

Oracle VM Manager 3.4.1 Installation on Oracle Linux 6 Update 5 (OL 6.5)

Please follow the step by step approach to install Oracle VM manager 3.4.1 on Oracle Linux 6.5

Step-I

Update host file /etc/hosts file by fully qualified name, as mentioned below. In my case “OVMmanager” is host name.

[root@OVMmanager ~]# vi /etc/hosts
192.168.239.135     OVMmanager.localdomain     OVMmanager

Step-II

Perform oracle VM manager prerequisites with the help of “createOracle.sh” script, provided by oracle itself in OVM manager installer.

[root@OVMmanager OVMManagerInstaller3.4.1_b1369]# ll
total 157510
drwxr-xr-x. 7 root root 8192 Apr 5 22:36 components
-r-xr-x---. 1 root root 11556 Apr 5 22:35 createOracle.sh
-rw-r--r--. 1 root root 6960 Apr 5 22:36 EULA
-rw-r--r--. 1 root root 6960 Apr 5 22:36 LICENSE
-rw-r--r--. 1 root root 230 Apr 5 22:35 oracle-validated.params
-r-xr-x---. 1 root root 156958046 Apr 5 22:36 ovmm-installer.bsx
-rw-r--r--. 1 root root 4291939 Apr 5 22:32 OvmSDK_3.4.1.1369.zip
-r-xr-x---. 1 root root 1919 Apr 5 22:36 runInstaller.sh
-rw-r--r--. 1 root root 372 Apr 5 22:36 sample.yml
-r--r--r--. 1 root root 2031 Apr 5 22:36 TRANS.TBL
[root@OVMmanager OVMManagerInstaller3.4.1_b1369]# ./createOracle.sh
Adding group 'oinstall' with gid '54321' ...
Adding group 'dba'
Adding user 'oracle' with user id '54321', initial login group 'dba', supplementary group 'oinstall' and home directory '/home/oracle' ...
Changing ownership of '/home/oracle' to oracle:dba
Creating user 'oracle' succeeded ...
For security reasons, no default password was set for user 'oracle'. If you wish to login as the 'oracle' user, you will need to set a password for this account.
Verifying user 'oracle' OS prerequisites for Oracle VM Manager ...
oracle soft nofile 8192
oracle hard nofile 65536
oracle soft nproc 2048
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
oracle soft core unlimited
oracle hard core unlimited
Setting user 'oracle' OS limits for Oracle VM Manager ...
Altered file /etc/security/limits.conf
Original file backed up at /etc/security/limits.conf.orabackup
Verifying & setting of user limits succeeded ...
Creating mountpoint '/u01' ...
Modifying iptables for OVM
Adding rules to enable access to:
 7002 : Oracle VM Manager https
 123 : NTP
 10000 : Oracle VM Manager CLI Tool
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
iptables: Applying firewall rules: [ OK ]
iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ]
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
iptables: Applying firewall rules: [ OK ]
Rules added.

Step-III
Install Oracle VM manager:

[root@OVMmanager OVMManagerInstaller3.4.1_b1369]# ./runInstaller.sh
Oracle VM Manager Release 3.4.1 Installer
Oracle VM Manager Installer log file:
/var/log/ovmm/ovm-manager-3-install-2016-06-20-195709.log
Please select an installation type:
 1: Install
 2: Upgrade
 3: Uninstall
 4: Help

Select Number (1-4): 1

Verifying installation prerequisites ...
*** WARNING: Recommended memory for the Oracle VM Manager server installation using Local MySql DB is 7680 MB RAM

Starting production with local database installation ...

One password is used for all users created and used during the installation.
Enter a password for all logins used during the installation:
Enter a password for all logins used during the installation (confirm):

Please enter your fully qualified domain name, e.g. ovs123.us.oracle.com, (or IP address) of your management server for SSL certification generation 192.168.239.135 [OVMmanager.localdomain]: OVMmanager.localdomain

Verifying configuration ...

Start installing Oracle VM Manager:
 1: Continue
 2: Abort
Select Number (1-2): 1

Step 1 of 7 : Database Software ...
Installing Database Software...
Retrieving MySQL Database 5.6 ...
Unzipping MySQL RPM File ...
Installing MySQL 5.6 RPM package ...
Configuring MySQL Database 5.6 ...
Installing MySQL backup RPM package ...

Step 2 of 7 : Java ...
Installing Java ...

Step 3 of 7 : WebLogic and ADF ...
Retrieving Oracle WebLogic Server 12c and ADF ...
Installing Oracle WebLogic Server 12c and ADF ...
Applying patches to Weblogic ...
Applying patch to ADF ...

Step 4 of 7 : Oracle VM ...
Installing Oracle VM Manager Core ...
Retrieving Oracle VM Manager Application ...
Extracting Oracle VM Manager Application ...

Retrieving Oracle VM Manager Upgrade tool ...
Extracting Oracle VM Manager Upgrade tool ...
Installing Oracle VM Manager Upgrade tool ...

Retrieving Oracle VM Manager CLI tool ...
Extracting Oracle VM Manager CLI tool...
Installing Oracle VM Manager CLI tool ...
Installing Oracle VM Manager WLST Scripts ...

Step 5 of 7 : Domain creation ...
Creating domain ...

Step 6 of 7 : Oracle VM Tools ...
Retrieving Oracle VM Manager Shell & API ...
Extracting Oracle VM Manager Shell & API ...
Installing Oracle VM Manager Shell & API ...

Retrieving Oracle VM Manager Wsh tool ...
Extracting Oracle VM Manager Wsh tool ...
Installing Oracle VM Manager Wsh tool ...

Retrieving Oracle VM Manager Tools ...
Extracting Oracle VM Manager Tools ...
Installing Oracle VM Manager Tools ...

Retrieving ovmcore-console ...
Installing ovmcore-console RPM package ...
Copying Oracle VM Manager shell to '/usr/bin/ovm_shell.sh' ...
Installing ovm_admin.sh in '/u01/app/oracle/ovm-manager-3/bin' ...
Installing ovm_upgrade.sh in '/u01/app/oracle/ovm-manager-3/bin' ...

Step 7 of 7 : Start OVM Manager ...
Enabling Oracle VM Manager service ...
Shutting down Oracle VM Manager instance ...
Starting Oracle VM Manager instance ...

Please wait while WebLogic configures the applications...
Trying to connect to core via ovmwsh (attempt 1 of 20) ...
Trying to connect to core via ovmwsh (attempt 2 of 20) ...
Trying to connect to core via ovmwsh (attempt 3 of 20) ...
Trying to connect to core via ovm_shell (attempt 1 of 5)...
Oracle VM Manager installed.

Installation Summary
--------------------
Database configuration:
 Database type : MySQL
 Database host name : localhost
 Database name : ovs
 Database listener port : 49500
 Database user : ovs

Weblogic Server configuration:
 Administration username : weblogic

Oracle VM Manager configuration:
 Username : admin
 Core management port : 54321
 UUID : 0004fb00000100003ddbd8af4ce345c8

Passwords:
There are no default passwords for any users. The passwords to use for Oracle VM Manager, Database, and Oracle WebLogic Server have been set by you during this installation. In the case of a default install, all passwords are the same.

Oracle VM Manager UI:
 https://OVMmanager.localdomain:7002/ovm/console
Log in with the user 'admin', and the password you set during the installation.

For more information about Oracle Virtualization, please visit: http://www.oracle.com/virtualization/

Oracle VM Manager installation complete.
Please remove configuration file /tmp/ovm_config71Qdn2.
[root@OVMmanager OVMManagerInstaller3.4.1_b1369]#

Oracle VM manager installation succeeded.

You will find Installation Summary right after installation comprises of MySQL database details, Weblogic Server configuration details and Oracle VM Manager configuration details.

Access Oracle VM Manager UI (console) with https://OVMmanager.localdomain:7002/ovm/console

Note: you need to confirm security settings before access Oracle VM manager.

Oracle VM manager login screen:

Note: Log in with the user ‘admin’, and the password you set during the installation.

Oracle VM manager - login

Oracle VM console:

Oracle VM manager - console

 

Thank you.
Stay Tune. 🙂

Oracle 11g Logo

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS and KUPW$WORKER.DISPATCH_WO ORA-01187: cannot read from file because it failed verification tests

Data pump export backup failed due to below mentioned oracle errors. It was failed due to verification tests failed on temp file: temp01. As a part of solution, I have delete old temporary tablespace and added new temporary tablespace with new temp file.

Data Pump Export Error Logs:

Export: Release 11.2.0.1.0 - Production on Mon Jun 20 11:47:01 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_09": system/******** directory=data_pump_bkup dumpfile=full_db_export.dmp logfile=export.log full=y
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS []
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '/u02/oradata/db1/temp01.dbf'
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8159
----- PL/SQL Call Stack -----
object line object
handle number name
0x1ed213be8 19028 package body SYS.KUPW$WORKER
0x1ed213be8 8191 package body SYS.KUPW$WORKER
0x1ed213be8 8980 package body SYS.KUPW$WORKER
0x1ed213be8 1651 package body SYS.KUPW$WORKER
0x1b97859d0 2 anonymous block
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WO
ORA-01187: cannot read from file because it failed verification te
ORA-01110: data file 201: '/u02/oradata/db1/temp01.dbf'
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8159
----- PL/SQL Call Stack -----
object line object
handle number name
0x1ed213be8 19028 package body SYS.KUPW$WORKER
0x1ed213be8 8191 package body SYS.KUPW$WORKER
0x1ed213be8 8980 package body SYS.KUPW$WORKER
0x1ed213be8 1651 package body SYS.KUPW$WORKER
0x1b97859d0 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_09" stopped due to fatal error at 11:

Solution:

Here is article on Delete old temporary tablespace and add new temporary tablespace with new temp file.

Thank you. Stay Tune. 🙂

Oracle 11g Logo

TNS-12535: TNS:operation timed out AND TNS-00505: Operation timed out

My one of the client recently implemented firewall for security purpose, it was successful activity. When all the application server connected to database server, below mentioned error logged into an alert log file frequently. It was clear clue, firewall messed up with the client-server connections.

It was the problem with Firewall “idle session timeout” value. If the client-server connection idle for more than defined value then it was forcefully dropped by firewall.
Error log:

Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
Time: 13-APR-2016 19:26:50
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
 ns secondary err code: 12560
 nt main err code: 505 
TNS-00505: Operation timed out
 nt secondary err code: 110
 nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.xxx.xxx.xxx)(PORT=xxxx))

Work around for TNS-12535 and TNS-00505:

We can remove or increase the firewall setting for maximum idle time. but it’s not recommended, because who know how much time client-server connection going to be idle.

So, append following parameter to RDBMS_HOME/network/admin/sqlnet.ora file on the database server. Resulting, database server check application server every <n> minutes to see if it is up by sending packets. So the connections are found as active by firewall and they will not terminated forcefully.

SQLNET.EXPIRE_TIME=n
Where <n> is a non-zero value set in minutes.

Reference: Doc ID 1628949.1

Your suggestions and comments are highly appreciated, if any.

Thank you. 🙂

Oracle 11g Logo

Incremental backup from SCN – Oracle 11g Manual Data Guard Part-IV

Previously we covered How to open Manual Physical Standby Database in READ ONLY mode.

Let us discuss one scenario and then you will realize the use of Incremental backup from SCN.

Scenario:

Consider the production database configured with Manual data guard, and unfortunately any archive log file gets corrupted or lost. Here we solely depend on that corrupted archive log to continue database replication, in this case we can fix the problem with RMAN incremental backup from SCN. Input SCN from standby database current state and backup incremental from that SCN from Primary database, and recover standby database. By this method, we no more need that corrupted archive log file.

OR

There is huge archive log sequence gap between primary and standby database, and we need to recover it in one go.

Before taking Incremental backup from SCN, we need to perform some pre-requisites on primary and standby database server, are as follows:

First:

Check listener status, it should be running and listening to databases.

[oracle@PR ~]$ lsnrctl status

Second:

Check database mode and role, On primary site, database should be in READ WRITE mode and role should be PRIMARY.

On standby site, database should be in MOUNTED mode and role should be PHYSICAL STANDBY.

Steps to take incremental backup from SCN:

Step-I

On primary database, take standby controlfile backup:

SQL> alter database create standby controlfile as '/home/oracle/backup/standby_control.ctl';
Database altered.

Step-II

Get current SCN from Standby database:

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
 957835

Step-III

On primary database, take RMAN incremental backup from SCN, take SCN as an input from Step-II:

[oracle@PR ~]$ rman target /
RMAN> backup device type disk incremental from scn 957835 database format '/home/oracle/backup/Inc_backup_%U';
Starting backup at 13-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
backup will be obsolete on date 20-MAY-16
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/RTS/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/RTS/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/RTS/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/RTS/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-MAY-16
channel ORA_DISK_1: finished piece 1 at 13-MAY-16
piece handle=/home/oracle/backup/Inc_backup_05r5f6en_1_1 tag=TAG20160513T045757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
using channel ORA_DISK_1
backup will be obsolete on date 20-MAY-16
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 13-MAY-16
channel ORA_DISK_1: finished piece 1 at 13-MAY-16
piece handle=/home/oracle/backup/Inc_backup_06r5f6g7_1_1 tag=TAG20160513T045757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-MAY-16

Step-IV

Transfer backup to standby database server:

[oracle@DR backup]$ pwd
/home/oracle/backup
[oracle@DR backup]$ ll
total 59728
-rw-r--r--. 1 oracle oinstall 41615360 May 13 04:58 Inc_backup_05r5f6en_1_1
-rw-r--r--. 1 oracle oinstall 9797632 May 13 04:58 Inc_backup_06r5f6g7_1_1
-rw-r--r--. 1 oracle oinstall 9748480 May 13 04:36 standby_control.ctl

Step-V

On Standby database, Restore Incremental backup:

one:

Shutdown standby database and startup in nomount mode:

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 448793376 bytes
Database Buffers 301989888 bytes
Redo Buffers 2768896 bytes

Two:

Restore standby controlfile:

[oracle@DR ~]$ rman target /
RMAN> restore controlfile from '/home/oracle/backup/standby_control.ctl';
Starting restore at 13-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/RTS/control01.ctl
output file name=/u01/app/oracle/oradata/RTS/control02.ctl
Finished restore at 13-MAY-16

Three:

Alter database to MOUNT mode:

SQL> alter database mount;
Database altered.

Four:

Catalog RMAN incremental backup files to RMAN repository(In my case, i.e. controlfile):

[oracle@DR ~]$ rman target /
RMAN> catalog start with '/home/oracle/backup/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/backup/
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/Inc_backup_06r5f6g7_1_1
File Name: /home/oracle/backup/standby_control.ctl
File Name: /home/oracle/backup/Inc_backup_05r5f6en_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/backup/Inc_backup_06r5f6g7_1_1
File Name: /home/oracle/backup/standby_control.ctl
File Name: /home/oracle/backup/Inc_backup_05r5f6en_1_1

Five:

Recover standby database:

RMAN> recover database;
Starting recover at 13-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/RTS/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/RTS/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/RTS/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/RTS/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/Inc_backup_05r5f6en_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/Inc_backup_05r5f6en_1_1 tag=TAG20160513T045757
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
starting media recovery
unable to find archived log
archived log thread=1 sequence=0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/13/2016 05:13:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 0 and starting SCN of 972205

Above recovery successful and expecting next archive log and starting SCN 972205, which is greater than old current SCN of standby database.( Ref: Step-II )
Ensure archive log gap sequence between primary and standby database, it should be zero or one or two, because logfiles might have switched due to heavy load on your production primary database.

Cheers!! Standby database successfully recovered from incremental backup. Now onward you can continue replication with new archive log files.

 

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on failover(Activate) of standby database.

Thank you 🙂

Oracle 11g Logo

Manual Physical Standby Failover – Activate Standby Database – Oracle 11g Manual Data Guard Part-V

In case of disaster with Primary Database site, or not available for production for any reason then we can activated standby database as a primary production database.

Note: If you are trying to activate standby database for production use, please get permission from senior authority to do so.

Previously, we covered “Incremental backup from SCN – Oracle 11g Manual Data Guard Part-IV”.

On Standby database, steps to perform failover OR Activate standby database:

One:

Check database listener, it should be running:

[oracle@DR ~]$ lsnrctl status

Two:

Check database mode and role, It should be MOUNTED and PHYSICAL STANDBY:

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

Three:

Copy archive logs from primary database, those yet to apply on standby database before activate it.

SQL> recover standby database until cancel;
AUTO

Four:

Activate standby database as primary database:

[oracle@DR ~]$ sqlplus / as sysdba
SQL> alter database activate standby database;
Database altered.

Five:

Shutdown standby database and startup:

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 448793376 bytes
Database Buffers 301989888 bytes
Redo Buffers 2768896 bytes
Database mounted.
Database opened.

Cheers!! Standby database successfully activated as Primary database. Failover successful.

Post activity check, ensure database mode and role, it should be READ WRITE and PRIMARY.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ WRITE PRIMARY

Note: Please perform full database backup after failover.
Note: Once old primary database is available for use then you can reconfigure standby database.

Your suggestions and comments are highly appreciated, if any.

Stay tune 🙂

Oracle 11g Logo

How to open Manual Physical Standby Database in READ ONLY mode – Oracle 11g Manual Data Guard Part-III

You can use Manual Physical Standby database for query access for query offloading and reporting purposes by making a standby database to read only mode, resulting we can use the primary database exclusively for core purpose.(i.e. OLTP)

Note:

Simply you can’t apply archive logs to standby database server when database is in READ ONLY mode, but you can continue to transfer your archive logs to standby database server, Once your standby database back to MOUNT mode than you can apply your remaining archive logs.

Previously, We covered Manual data guard switch over and switch back activity step by step approach.

Steps are as follows:

Step-I

Verify database name, its mode and role by following command:

SQL> select name,open_mode,database_role from v$database;

The database is in mount state and its role is Physical Standby, as expected, good to go.

Step-II

Transfer all remaining archive logs from the primary database server to standby database server and apply it (If there is an archive log gap), before opening a standby database to READ ONLY mode. For more information on it, you can refer Step – XVI from my one of post on “Configuring Manual Data Guard”.

Step-III

Delete existing tempfile[s]:

select tf.name from v$tablespace ts,v$tempfile tf where ts.TS#=tf.TS# and ts.name=’TEMP’;
alter database tempfile ‘Above_temp_file[s]’ drop;

Step-IV

Open your Standby database in READ ONLY mode from MOUNTED mode:

SQL> alter database open read only;

Step-V

Create new tempfile[s]:

SQL> alter tablespace TEMP add tempfile '/path/temp02.dbf' size 200m autoextend on maxsize unlimited;

Step-VI

Repeat Step-I to verify standby database mode and its role.

 

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on Incremental backup from SCN.

Thank you. 🙂

Oracle 11g Logo

Switch over and switch back – Oracle 11g Manual Data Guard Part-II

In my Previous article we have covered How to configure oracle 11g Manual Data Guard, Now we will look into switch-over and switch-back activity.

Switch-over Pre-requisites:

One:

Check listener status on primary and standby database.

Primary Database Listener:

[oracle@PR ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:49:31
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PR.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 11-MAY-2016 22:44:06
Uptime 0 days 0 hr. 5 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
Services Summary...
Service "RTS" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
Service "RTSXDB" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
The command completed successfully

Standby Database Listener:

[oracle@DR ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:51:55
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DR.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 11-MAY-2016 22:45:18
Uptime 0 days 0 hr. 6 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DR/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DR.localdomain)(PORT=1521)))
Services Summary...
Service "RTSDR" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
The command completed successfully

Two:

Check database mode on primary and standby database.

Primary Database Mode:

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS READ WRITE

Standby Database Mode:

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS MOUNTED

Three:

Check database role on primary and standby database.

Primary Database:

SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
RTS PRIMARY

Standby Database:

SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
RTS PHYSICAL STANDBY

Four:

No datafiles should be in recovery mode on Primary and Standby database.

SQL> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('RECOVER');
no rows selected

Five:

No datafiles should be in offline mode on Primary and Standby database.

SQL> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('OFFLINE');
no rows selected

Six:

No datafiles should be in backup mode on Primary and Standby database.

SQL> select file# from v$backup where status='ACTIVE';
no rows selected

Switch-over steps:

Step-A:

Shutdown primary database:
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step-B:

Copy control file to temporary location on primary database.

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/prtemp/
[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/prtemp/

Step-C:

Copy SPFile to temporary location on primary database.

[oracle@PR ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/prtemp/

Step-D:

Copy Redo log files to temporary location on primary database.

[oracle@PR ~]$ cd /u01/app/oracle/oradata/RTS/
[oracle@PR RTS]$ ll *.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:08 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo03.log
[oracle@PR RTS]$ cp *.log /home/oracle/prtemp/

Step-E:

Copy needed archive log to DR site.

[oracle@PR RTS]$ cd /home/oracle/archdir/
[oracle@PR archdir]$ scp * oracle@DR:/home/oracle/archdir/

Step-F:

Get primary database to READ ONLY mode.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 473959200 bytes
Database Buffers 276824064 bytes
Redo Buffers 2768896 bytes
Database mounted.
SQL> alter database open read only;
Database altered.

Step-G:

Apply all needed archive logs to standby database.

SQL> recover standby database until cancel;
ORA-00279: change 957836 generated at 04/26/2016 02:49:33 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_15_910140016.dbf
ORA-00280: change 957836 for thread 1 is in sequence #15
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 967269 generated at 04/26/2016 23:34:52 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_16_910140016.dbf
ORA-00280: change 967269 for thread 1 is in sequence #16
ORA-00278: log file '/home/oracle/archdir/1_15_910140016.dbf' no longer needed for this recovery
ORA-00279: change 967272 generated at 04/26/2016 23:34:53 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_17_910140016.dbf
ORA-00280: change 967272 for thread 1 is in sequence #17
ORA-00278: log file '/home/oracle/archdir/1_16_910140016.dbf' no longer needed for this recovery
.
..
...
ORA-00279: change 969653 generated at 05/09/2016 23:48:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_29_910140016.dbf
ORA-00280: change 969653 for thread 1 is in sequence #29
ORA-00278: log file '/home/oracle/archdir/1_28_910140016.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/home/oracle/archdir/1_29_910140016.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Step-H:

Shutdown standby database.

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Step-I:

Copy control file to temporary location on standby database.

[oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/drtemp/
[oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/drtemp/

Step-J:

Copy SPFile to temporary location on standby database.

[oracle@DR dbs]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/drtemp/

Step-K:

On Standby database, copy all files(from /home/oracle/drtemp/ directory) to primary database server(to /home/oracle/from_dr directory).

[oracle@DR ~]$ cd /home/oracle/drtemp/
[oracle@DR drtemp]$ ll
total 19044
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control02.ctl
-rw-r-----. 1 oracle oinstall 2560 May 11 23:27 spfileRTS.ora
[oracle@DR drtemp]$ scp * oracle@PR:/home/oracle/from_dr

Step-L:

On Primary database, copy all files(from /home/oracle/prtemp/ directory) to standby database server(to /home/oracle/from_pr directory).

[oracle@PR ~]$ cd /home/oracle/prtemp/
[oracle@PR prtemp]$ ll
total 172656
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control02.ctl
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo03.log
-rw-r-----. 1 oracle oinstall 2560 May 11 23:10 spfileRTS.ora
[oracle@PR prtemp]$ scp * oracle@DR:/home/oracle/from_pr

Step-M:

On Standby database, replace copied files(source directory: /home/oracle/prtemp/) to its appropriate locations:

[oracle@DR from_pr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl
[oracle@DR from_pr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl
[oracle@DR from_pr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora
[oracle@DR from_pr]$ cp *.log /u01/app/oracle/oradata/RTS/

Step-N:

Perform role switching on old standby database: (old standby database to new Primary database)

one:

Startup mount old standby database.

[oracle@DR ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 473959200 bytes
Database Buffers 276824064 bytes
Redo Buffers 2768896 bytes
Database mounted.

Two:

Recover database.

SQL> recover database;
Media recovery complete.

Three:

Drop tempfiles(From temporary tablespace) from old standby database. (Note: If this is your first switch over then you may skip this step)

Four:

Open database:

SQL> alter database open;
Database altered.

Five:

Create new tempfile:

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/RTS/temp02.dbf' size 100m autoextend on maxsize unlimited;
Tablespace altered.

On Primary database, replace copied files(source directory: /home/oracle/drtemp/) to its appropriate locations:

[oracle@PR from_dr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl
[oracle@PR from_dr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl
[oracle@PR from_dr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora

Shutdown old primary database.

SQL> shut immediate;

Step-O:

Perform role switching on old primary database: (old primary database to new standby database)

One:

Start database with nomount state:

SQL> startup nomount;

Two:

Alter database to mount state with standby database:

SQL> alter database mount standby database;
Database altered.

Cheers!! Switch over activity successfully carried out, now lets perform post checks on both the database servers:

Post checks:

Check database mode and role on new primary database, it shall be READ WRITE and Primary.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ WRITE PRIMARY

Check database mode and role on new standby database, it shall be MOUNTED and PHYSICAL STANDBY.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

We are done with switch over activity.

Switch-back activity steps:

There is no difference in switch-over and switch-back steps. Use above steps to do it.
Cheers!!

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on How to open Standby Database in Read ONLY mode.

Thank you. 🙂

Oracle 11g Logo

Steps to configure Manual Data Guard – Oracle 11g Manual Data Guard Part-I

This article is about step by step approach to configure Manual Physical Standby Data Guard in Oracle 11g. In my case, Ingredients to simulate Manual Physical Standby Manual data guard environment are as below:

2 VM’s, PR and DR with enough CPU and RAM in order to run oracle database.

Primary server configuration:

  • CentOS 6.5
  • Server name: PR
  • IP: 192.168.239.130
  • Oracle 11g software plus oracle instance.
  • Oracle SID/Global_name: RTS
  • Oracle db_unique_name: RTS

Standby server configuration:

  • CentOS 6.5
  • Server name: DR
  • IP: 192.168.239.131
  • Oracle 11g software only.
  • Oracle SID/Global name: RTS
  • Oracle db_unique_name: RTSDR

Note:

Oracle version on Primary and Standby should be identical. i.e. In my case it’s 11.2.0.1.0
db_unique_name on standby and primary database should be different.
Primary and Standby server should ping each other by IP as well as its server name. In order to ping with its server name, edit /etc/hosts file accordingly.

PR /etc/hosts file:

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.239.130    PR.localdomain    PR
192.168.239.131    DR

DR /etc/hosts file:

[root@dr ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.239.131    DR.localdomain    DR
192.168.239.130    PR

Lets start the configuration:

Step -I

Verify your database is in Archive Log Mode or NO archive log mode, if not than my one of the post will help you to configure your Oracle 11g database is in archive log mode. Primary database need to be in Archive log mode.

Step -II

Enable Force Logging on Primary database(Hostname:PR)

Following query will help you find out your database is in force logging or not:

SQL> column force_logging format a15
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------
NO

Following query will help you to enable force logging:

SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------
YES

Step – III

Backup primary database with the help of RMAN:

 [oracle@PR ~]$ rman target /
 connected to target database: RTS (DBID=1470135364)
 RMAN> run {
 allocate channel c1 device type disk format '/home/oracle/prdg/bkup/%U';
 backup as compressed backupset full database plus archivelog;
 release channel c1;
 }

Copy RMAN backup to standby database(Hostname: DR)

 [oracle@PR ~]$ cd /home/oracle/prdg/bkup
 [oracle@PR bkup]$ ll
 total 266332
 -rw-r-----. 1 oracle oinstall 12165120 Apr 18 03:55 09r3ah13_1_1
 -rw-r-----. 1 oracle oinstall 259457024 Apr 18 03:56 0ar3ah17_1_1
 -rw-r-----. 1 oracle oinstall 1097728 Apr 18 03:56 0br3ah3s_1_1
 -rw-r-----. 1 oracle oinstall 4096 Apr 18 03:56 0cr3ah3v_1_1
 [oracle@PR bkup]$ scp * oracle@DR:/home/oracle/drdg/bkup/

Step – IV

Create Standby Controlfile on Primary database(Hostname:PR)

SQL> alter database create standby controlfile as '/home/oracle/prdg/stndby_control.ctl';
Database altered.

Copy Standby Controlfile to standby database(Hostname: DR)

[oracle@PR ~]$ scp /home/oracle/prdg/stndby_control.ctl oracle@DR:/u01/app/oracle/oradata/RTS/control01.ctl
[oracle@PR ~]$ scp /home/oracle/prdg/stndby_control.ctl oracle@DR:/u01/app/oracle/oradata/RTS/control02.ctl

Step – V

Create parameter file(i.e. initRTS.ora) on Primary database(Hostname:PR). If you are not using spfile than skip this step.

SQL> create pfile='/home/oracle/prdg/initRTS.ora' from spfile;

Copy parameter file(i.e. initRTS.ora) to standby database(Hostname: DR)

[oracle@PR ~]$ scp /home/oracle/prdg/initRTS.ora oracle@DR:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Step – VI

Copy password file to standby database(Hostname: DR). (Note: create password file if not created for your database.)

[oracle@PR ~]$ cd $ORACLE_HOME/dbs
[oracle@PR dbs]$ ll orapwRTS
-rw-r-----. 1 oracle oinstall 2048 Apr 18 03:23 orapwRTS
[oracle@PR dbs]$ scp orapwRTS oracle@DR:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Step – VII

Create Required Directory Structure on standby database server(Hostname: DR). In my case I am going to replicate same directory structure as mentioned in parameter file.

=== initRTS.ora ===
 *.audit_file_dest='/u01/app/oracle/admin/RTS/adump'
 *.control_files='/u01/app/oracle/oradata/RTS/control01.ctl','/u01/app/oracle/oradata/RTS/control02.ctl'
 *.diagnostic_dest='/u01/app/oracle'
 *.log_archive_dest_1='LOCATION=/home/oracle/archdir'
 === End ===

Directory structure on standby database server.(Hostname: DR)

 [oracle@DR ~]$ mkdir -p /u01/app/oracle/admin/RTS/adump
 [oracle@DR ~]$ mkdir -p /u01/app/oracle/oradata/RTS/
 [oracle@DR ~]$ mkdir -p /u01/app/oracle
 [oracle@DR ~]$ mkdir -p /home/oracle/archdir

Step – VIII

Configure tnsname.ora and listener.ora and Start listener on standby database(Hostname: DR)

 === tnsnames.ora ===
 LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = DR.localdomain)(PORT = 1521))
 )
 )
 ADR_BASE_LISTENER = /u01/app/oracle
 === End ===
 ===listener.ora ===
 RTS =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTS)
 )
 )
 RTSDR =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = DR)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTSDR)
 )
 )
 === End ===

Step – IX

Add following parameters to initRTS.ora file on Standby database. (Hostname: DR)

*.db_unique_name='RTSDR'

In case of your datafile and logfile location on standby is different from primary than add following parameter to initRTS.ora file.

*.db_file_name_convert=('location on primary','location on Stanndby')
*.log_file_name_convert=('location on primary','location on Stanndby')

Step – X

Connect Standby Database

 $ export ORACLE_SID=RTS
 sqlplus / as sysdba
 SQL> startup nomount;
 ORACLE instance started.
 Total System Global Area 960372736 bytes
 Fixed Size 2219152 bytes
 Variable Size 562037616 bytes
 Database Buffers 390070272 bytes
 Redo Buffers 6045696 bytes
 SQL>

Step – XI

Create spfile from existing parameter file (i.e.initRTS.ora) and bounce the database:

 SQL> create spfile from pfile;
 SQL> Shut immediate;
 SQL> startup mount;

Step – XI

Restore and recover database with the help of RMAN backed up files from primary database. (Step – III)

 $ rman target /

On standby database, Practically there is no backup taken from RMAN, but if you issue following command to list out existing backups, than RMAN will show you full database backup information of primary database.(Step – III) Because RMAN repository is stored in control file and we have copy pasted standby control file from primary to standby database.

 RMAN> list backup of database summary;
 using target database control file instead of recovery catalog
 List of Backups
 ===============
 Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
 ------- -- -- - ----------- --------------- ------- ------- ---------- ---
 10 B F A DISK 18-APR-16 1 1 YES TAG20160418T035519

Just cross check and delete invalid entry from control file with the help of following RMAN commands:

 RMAN> crosscheck backup;
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=25 device type=DISK
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/01r3vadq_1_1 RECID=1 STAMP=910141882
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/02r3vadu_1_1 RECID=2 STAMP=910141886
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/03r3vaga_1_1 RECID=3 STAMP=910141963
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/04r3vagc_1_1 RECID=4 STAMP=910141964
 Crosschecked 4 objects
RMAN> delete noprompt force expired backup;

Step – XII

Catalog all RMAN backup file entry to control file with following:

 RMAN> catalog start with '/home/oracle/drdg/bkup/';
 using target database control file instead of recovery catalog
 searching for all files that match the pattern /home/oracle/drdg/bkup/
 List of Files Unknown to the Database
 =====================================
 File Name: /home/oracle/drdg/bkup/0ar3ah17_1_1
 File Name: /home/oracle/drdg/bkup/09r3ah13_1_1
 File Name: /home/oracle/drdg/bkup/0cr3ah3v_1_1
 File Name: /home/oracle/drdg/bkup/0br3ah3s_1_1
 Do you really want to catalog the above files (enter YES or NO)? yes
 cataloging files...
 cataloging done
 List of Cataloged Files
 =======================
 File Name: /home/oracle/drdg/bkup/0ar3ah17_1_1
 File Name: /home/oracle/drdg/bkup/09r3ah13_1_1
 File Name: /home/oracle/drdg/bkup/0cr3ah3v_1_1
 File Name: /home/oracle/drdg/bkup/0br3ah3s_1_1

Step – XIII

Restore and recover database with the help of following RMAN commands:

 run
 {
 restore database;
 recover database;
 }

Above recovery session will be end up with RMAN error message like mentioned below. Recovery successful.

 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of recover command at 04/26/2016 02:18:57
 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 954414

Step – XIV

Shutdown database and Startup Standby Database(Mount mode):

 sqlplus / as sysdba
 SQL> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup nomount;
 ORACLE instance started.
 Total System Global Area 960372736 bytes
 Fixed Size 2219152 bytes
 Variable Size 562037616 bytes
 Database Buffers 390070272 bytes
 Redo Buffers 6045696 bytes
SQL> alter database mount standby database;
 Database altered.

Step – XV

Cheers!! Our Manual Data Guard configuration have configured successfully.

Verify Manual Data Guard configuration on Standby database with the help of following SQL query:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS MOUNTED PHYSICAL STANDBY

Issue same above SQL query on primary database:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS READ WRITE PRIMARY

Step – XVI

Verify your Manual Data Guard configuration is working properly or not with the help of generating and applying archive logs on standby database:

Step – A

Generate multiple archive logs on primary database, issue following SQL query multiple times:

 SQL> alter system switch logfile;
 System altered.
 SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
 MAX(SEQUENCE#)
 --------------
 7

Step – B

Transfer above generated archive logs to standby database server @ path: /home/oracle/archdir/ (As per parameter: *.log_archive_dest_1 set for standby database)

scp * oracle@DR:/home/oracle/archdir/

//Above SCP linux command will transfer all the archive logs from mentioned directory. It will overwrite old files. (Note: I used this shortcut because I am demonstrating you on my test environment, In case of production, please copy only newly generated files)

Step – C

Issue following SQL to query for maximum of sequence number of applied archived log on standby database.(Hostname: DR)

 SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
 MAX(SEQUENCE#)
 --------------
 3

Apply copy pasted archive logs on standby database as below,

 SQL> recover standby database until cancel;
 ORA-00279: change 1008623 generated at 04/18/2016 03:56:47 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_18_908844551.dbf
 ORA-00280: change 1008623 for thread 1 is in sequence #18
 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 AUTO
 ORA-00279: change 957138 generated at 04/26/2016 02:20:59 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_5_910140016.dbf
 ORA-00280: change 957138 for thread 1 is in sequence #5
 ORA-00278: log file '/home/oracle/archdir/1_4_910140016.dbf' no longer needed for this recovery
 ORA-00279: change 957141 generated at 04/26/2016 02:21:00 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_6_910140016.dbf
 ORA-00280: change 957141 for thread 1 is in sequence #6
 ORA-00278: log file '/home/oracle/archdir/1_5_910140016.dbf' no longer needed for this recovery
 ORA-00279: change 957144 generated at 04/26/2016 02:21:01 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_7_910140016.dbf
 ORA-00280: change 957144 for thread 1 is in sequence #7
 ORA-00278: log file '/home/oracle/archdir/1_6_910140016.dbf' no longer needed for this recovery
 ORA-00279: change 957147 generated at 04/26/2016 02:21:01 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_8_910140016.dbf
 ORA-00280: change 957147 for thread 1 is in sequence #8
 ORA-00278: log file '/home/oracle/archdir/1_7_910140016.dbf' no longer needed for this recovery
 ORA-00308: cannot open archived log '/home/oracle/archdir/1_8_910140016.dbf'
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3

All transfered archive logs have applied on standby database successfully, now check maximum of sequence number of applied archived log.

 SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
 MAX(SEQUENCE#)
 --------------
 7

Maximum of sequence number of applied archived log on primary and standby is same, our configuration successful. Cheers!!!

Your comment and suggestion are highly appreciated.

Stay Tuned with my next article on “Switch over and switch back – Oracle 11g Manual Data Guard Part-II“.

Thank you. 🙂

Oracle 11g Logo

How to configure oracle 11g database in archivelog mode

Connect database as sys user to configure it in archivelog mode.

[oracle@PR ~]$ sqlplus / as sysdba

Verify your database is in archivelog log mode or not, In our case obviously not.

SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

OR

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     1
Current log sequence           3

To configure database in archivelog mode, follow the steps below:

Step-I: Shutdown database:

SQL> shutdown immediate;

Step-II: Startup your database in mount mode:

SQL> startup mount;

Step-III: Configure database in archivelog:

SQL> alter database archivelog;

Step-IV: Open database in read write mode:

SQL> alter database open;

Database is in archive log mode now, you can verify with following SQL command, Result shows Database log mode is “Archive Mode” and “Automatic archival” is Enabled.

SQL> archive log list;
Database log mode               Archive Mode
Automatic archival              Enabled
Archive destination             /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence      1
Next log sequence to archive    3
Current log sequence            3

You can change archive log destination with following:

SQL> alter system set log_archive_dest_1='LOCATION=/home/oracle/archdir' scope=both;

Following command shows, the database is in archivelog mode:

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG

 

Your comment and suggestion are highly appreciated.

Thank you. Stay Tune. 🙂

Oracle 11g Logo

ORA-25153: “Temporary Tablespace is Empty” although temporary tablespace and tempfile is available

According to oracle docs, the cause of the oracle error is to attempt was made to use space in a temporary tablespace with no files(datafile). And based on that action suggested is to add tempfile to the temporary tablespace with ADD TEMPFILE command.

In my case, Temporary tablespace already exists with sufficient free space into it. So I verified DEFAULT temporary tablespace, It was set to invalid temporary tablespace name.

The issue had resolved by setting default temporary tablespace to valid one.

Following query will help you find out default temporary tablespace:

SQL> select property_name, property_value from database_properties where

property_name='DEFAULT_TEMP_TABLESPACE';

Following query will help you to set default temporary tablespace:

SQL> alter database default temporary tablespace NEWTEMP;

Thanks, Comments are highly appreciated.
Stay Tune. 🙂

Oracle 11g Logo

RMAN-06026: some targets not found – aborting restore, RMAN-06023: no backup or copy of datafile X found to restore

RMAN restore would failed with RMAN-06026 and RMAN-06023 after successfully restoration of controlfile. Logs are as follows:

Error log:

RMAN> restore database;
Starting restore at 15-FEB-16
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/15/2016 14:52:59
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

As per oracle error RMAN-06023 means RMAN cannot find a backup for that datafile in its repository, so I cross verified RMAN backup files physically on disk as well as into RMAN repository with following RMAN commands. The backups are marked as AVAILABLE. Everything was pretty perfect.

RMAN> list backup of database summary;

AND

RMAN> list backup of datafile 1;
RMAN> list backup of datafile 2;
RMAN> list backup of datafile 3;
RMAN> list backup of datafile 4;

OR

RMAN> list backup of archivelog sequence;

After drilled down everything, I came to know that there are some backup files in FRA that are belongs to OLD INCARNATION than available backup CURRENT INCARNATION.
The problem is here, while restoring RMAN backups, RMAN implicitly perform cross verification to FRA(If FRA defined in controlfile) and if new files encounter it will be catalog(registered) automatically. This prevents database from restoring backups that belong to old CURRENT incarnation. Resulting RMAN would not allowed to restored this case. Backup incarnation and the CURRENT incarnation in controlfile should be same.

There are 3 Solutions to this error:

First: You can move old RMAN backup files(that belongs to old incarnation) to outside FRA and begin your restoration again with restoration of controlfile.

 

Second: Disabled FRA for the temporary purpose while RMAN backup being restore.

SQL> shutdown immediate;
SQL> create pfile from spfile;
comment *.db_recovery_file_dest and *.db_recovery_file_dest_size in pfile.
SQL> startup nomount pfile='.... init.ora'

Restart the restore controlfile and then restore/recover database commands…

 

Third: Resetting incarnation to the older incarnation, as below. In my case, following work around works like a charm.

RMAN> reset database to incarnation 3;
database reset to incarnation 3

After resetting database incarnation, restore database successful.

Folks, Your valuable comments are highly appreciated.

Thank you,
Stay Tune. 🙂

Oracle 11g Logo

ORA-01031: insufficient privileges – “SQLPLUS / as sysdba” working, but “SQLPLUS sys/sys@YOUTH as sysdba” is not working.

I have faced ORA-01031 oracle error after migrating oracle database from oracle 10g (10.2.0.4.0) to Oracle 11g (11.2.0.1.0)
Error log is as follows:

[oracle@newyouth dbs]$ sqlplus sys/sys@blade1 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:15:50 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges

But, I can connect the database with SYS user as follow, but not able to connect SYS user with credentials cum connection string(as mentioned above).

[oracle@newyouth dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:15:29 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
  • Oracle error itself pretty self explanatory, according to error I have cross checked all the permissions but it doesn’t work for me.
  • Listener.ora and tnsnames.ora files also verified and found correct.
  • Oracle environment variables (like ORACLE_SID,ORACLE_HOME and PATH) also set appropriately with no mistake.
  • REMOTE_LOGIN_PASSWORDFILE parameter set to EXCLUSIVE in pfile/spfile.

Password file also created with following command.

orapwd FILE=orapwblade1 PASSWORD=sys entries=30

Finally, I got the clue from v$pwfile_users dictionary view, This view lists users who have been granted SYSDBA and SYSOPER privileges, So following query must fetch something as I have already created password file. But no luck. Oops.
SQL> select * from v$pwfile_users;
no rows selected

I have double check “$ORACLE_HOME/dbs” directory and came to know there was naming convention mistake with password file(Case sensitivity with ORACLE SID).

[oracle@newyouth dbs]$ ll
total 24
-rw-rw----. 1 oracle oinstall 1544 Feb 10 04:00 hc_BLADE1.dat
-rw-r--r--. 1 oracle oinstall 1018 Feb 10 03:58 initBLADE1.ora
-rw-r-----. 1 oracle oinstall 24 Feb 8 02:24 lkBLADE1
-rw-r-----. 1 oracle oinstall 5120 Feb 10 04:16 orapwblade1
-rw-r-----. 1 oracle oinstall 3584 Feb 10 04:00 spfileBLADE1.ora

So as a part of the solution to my scenario, I have dropped an existing password file and created new one by following command.

orapwd FILE=orapwBLADE1 PASSWORD=sys entries=30

Finally, I got access and v$pwfile_user dictionary view fetched SYS user entry.

[oracle@newyouth dbs]$ sqlplus sys/sys@blade1 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:18:50 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

Cheers!!
Stay Tune. 🙂

sybase logo

How to uninstall SAP ASE – Sybase 16 from linux

In this post, I am going to demonstrate step by step approach to remove SAP Adaptive Server Enterprise ( Sybase ) 16 from Linux host.

As a part of prerequisites kindly Stop all ASE services before instantiate uninstall.

Login as SYBASE user in order to uninstall sybase:

[root@Centsrv01 ASESuite]# su - sybase

Change directory to ‘/opt/sybase/sybuninstall/ASESuite’ and instantiate ‘uninstall’ as below. And follow the instruction and provide the inputs as needed in order to remove SAP ASE.

Uninstallation logs are as follows:

-bash-4.1$ cd /opt/sybase/sybuninstall/ASESuite
-bash-4.1$ ./uninstall

Graphical installers are not supported by the VM. The console mode will be used instead...

===============================================================================
SAP Adaptive Server Enterprise (created with InstallAnywhere)
-------------------------------------------------------------------------------

Preparing CONSOLE Mode Uninstallation...
===============================================================================
Uninstall SAP Adaptive Server Enterprise
----------------------------------------

About to uninstall...

SAP Adaptive Server Enterprise 16.0 GA PL01

This will remove features installed by InstallAnywhere. It will not remove
files and folders created after the installation.

PRESS  TO CONTINUE:

===============================================================================
Uninstall Options
-----------------

ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS  TO ACCEPT THE DEFAULT:

->1- Completely remove all features and components.
2- Choose specific features that were installed by InstallAnywhere.

Please choose one of the following options:: 1

===============================================================================
Pre-Uninstall Summary
---------------------

SAP Adaptive Server Enterprise will be uninstalled from the following location:

/opt/sybase

with the following features:

Adaptive Server Enterprise
Additional ASE Language Modules
Open Client
DB-Library
Embedded SQL/C
Embedded SQL/Cobol
XA Interface Library for ASE Distributed Transaction Manager
Additional Connectivity Language Modules
ASE Extension Module for Python
ASE Database Driver for PERL
ASE Extension Module for PHP
jConnect 16.0 for JDBC
Interactive SQL
QPTune
SySAM License Utilities
SySAM License Server
SAP Control Center

PRESS  TO CONTINUE:

Remote Command and Control Agent for Adaptive Server
Management User Interface for SAP Adaptive Server
SNMP Support for Adaptive Server
ASE ODBC Driver
OData Server for ASE
PRESS  TO CONTINUE:

===============================================================================
Uninstalling ESQL/C 16.0 PL02...Success
Uninstalling SySAM License Server 2.3...Success
Uninstalling ASE Extension Module for PHP 16.0 PL02...Success
Uninstalling XA Interface Library for ASE Distributed Transaction Manager 16.0
PL02...Success
Uninstalling ESQL/Cobol 16.0 PL02...Success
Uninstalling Adaptive Server Enterprise Additional Language Modules 16.0 GA
PL01...Success
Uninstalling QPTune 3.0...Success
Uninstalling ASE Database Driver for PERL 16.0 PL02...Success
Uninstalling Management User Interface for SAP Adaptive Server Enterprise
3.3...Success
Uninstalling OData Server for ASE 16.0 GA...Success
Uninstalling ASE Extension Module for Python 16.0 PL02...Success
Uninstalling Additional Connectivity Language Modules 16.0 PL02...Success
Uninstalling Interactive SQL 16.0 PL01...Success
Uninstalling SAP Adaptive Server Enterprise 16.0 GA PL01...Success
Uninstalling DB-Library 16.0 PL02...Success
Uninstalling SAP ASE ODBC Driver 16.0 PL02...Success
Uninstalling Install/Extract Java 16.0 GA PL01...Success
Uninstalling SAP CT-DBCAPI 16.0 PL02...Success
Uninstalling SySAM License Utilities 2.3...Success
Uninstalling SAP jConnect 16.0 PL01...Success
Uninstalling Open Client 16.0 PL02...Success
Uninstalling Shared Library 1.0.0.5...Success
Uninstalling Connectivity Language Modules 16.0 PL02...Success
Uninstalling SCC - Remote Command and Control Agent for Adaptive Server 16.0 GA
PL01...Success
Uninstalling Language Modules 16.0 GA...Success
Uninstalling SCC - SNMP Support for Adaptive Server 2.0.0.8...Success
Uninstalling SAP Control Center Management Server 3.3...Success
Uninstalling SAP Java Runtime Environment 7.1.011...Success

===============================================================================
Uninstalling...
---------------
...*
*
*************************
*************************
*************************
************************
...*
*
*************************
*************************
*************************
************************

===============================================================================
Delete User Files
-----------------

The uninstaller has successfully uninstalled items created during installation.
However, the following files still exist in the install directory. These files
were created outside of the installer.

/opt/sybase/SYBASE.csh
/opt/sybase/interfaces
/opt/sybase/SCC-3_3/log/repository.log
/opt/sybase/SCC-3_3/log/gateway-stats.log
/opt/sybase/SCC-3_3/log/alert-server.log
/opt/sybase/SCC-3_3/log/gateway.log
/opt/sybase/SCC-3_3/log/collection-stats.log
/opt/sybase/SCC-3_3/log/agent.log
/opt/sybase/install/sample_response.txt
/opt/sybase/install/sysam_utilities/bin/sysamcap
/opt/sybase/install/sysam_utilities/bin/cpuinfo
/opt/sybase/install/sysam_utilities/bin/lmutil
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/ja_JP/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/th_th/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/pt_PT/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/es_ES/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/zh_cn/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/pl_pl/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/es_es/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/pl_PL/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/fr_fr/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/fr_FR/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/pt_pt/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/de_de/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/de_DE/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/ja_jp/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/zh_CN/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/ko_KR/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/en_us/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/th_TH/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/ko_kr/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/en_US/sylapi.lcu
/opt/sybase/install/setup.bin
/opt/sybase/install/archives/ie_java_mm/ie_java.iam.zip
/opt/sybase/install/archives/ie_java_mm/archives/version.txt
/opt/sybase/install/archives/ie_java_mm/archives/comp_size.properties
/opt/sybase/install/archives/version.txt
/opt/sybase/install/archives/ase_agent_mm/ase_agent.iam.zip
/opt/sybase/install/archives/ase_agent_mm/archives/version.txt
/opt/sybase/install/archives/ase_agent_mm/archives/comp_size.properties
/opt/sybase/install/archives/open_client_mm/archives/version.txt
/opt/sybase/install/archives/open_client_mm/archives/comp_size.properties
/opt/sybase/install/archives/open_client_mm/open_client.iam.zip
/opt/sybase/install/archives/conn_perl_mm/conn_perl.iam.zip
/opt/sybase/install/archives/conn_perl_mm/archives/version.txt
/opt/sybase/install/archives/conn_perl_mm/archives/comp_size.properties
/opt/sybase/install/archives/ase_snmp_agent_mm/archives/version.txt
/opt/sybase/install/archives/ase_snmp_agent_mm/archives/comp_size.properties
/opt/sybase/install/archives/ase_snmp_agent_mm/ase_snmp_agent.iam.zip
/opt/sybase/install/archives/dbisql_mm/archives/version.txt
/opt/sybase/install/archives/dbisql_mm/archives/comp_size.properties
/opt/sybase/install/archives/dbisql_mm/dbisql.iam.zip
/opt/sybase/install/archives/jconnect16_mm/archives/version.txt
/opt/sybase/install/archives/jconnect16_mm/archives/comp_size.properties
/opt/sybase/install/archives/jconnect16_mm/jconnect.iam.zip
/opt/sybase/install/archives/sysam_util_mm/sysam_util.iam.zip
/opt/sybase/install/archives/sysam_util_mm/archives/version.txt
/opt/sybase/install/archives/sysam_util_mm/archives/comp_size.properties
/opt/sybase/install/archives/sybpsu
/opt/sybase/install/archives/shared_lib_mm/archives/version.txt
/opt/sybase/install/archives/shared_lib_mm/archives/comp_size.properties
/opt/sybase/install/archives/shared_lib_mm/shared_lib.iam.zip
/opt/sybase/install/archives/SYBASE_ASE_XE.lic
/opt/sybase/install/archives/esqlc_mm/archives/version.txt
/opt/sybase/install/archives/esqlc_mm/archives/comp_size.properties
/opt/sybase/install/archives/esqlc_mm/esqlc.iam.zip
/opt/sybase/install/archives/asemanifest.mf
/opt/sybase/install/archives/sylapi/libsylapij.so
/opt/sybase/install/archives/esqlcobol_mm/esqlcobol.iam.zip
/opt/sybase/install/archives/esqlcobol_mm/archives/version.txt
/opt/sybase/install/archives/esqlcobol_mm/archives/comp_size.properties
/opt/sybase/install/archives/ase_mm/archives/version.txt
/opt/sybase/install/archives/ase_mm/archives/comp_size.properties
/opt/sybase/install/archives/ase_mm/ase.iam.zip
/opt/sybase/install/archives/dbcapi_mm/archives/version.txt
/opt/sybase/install/archives/dbcapi_mm/archives/comp_size.properties
/opt/sybase/install/archives/dbcapi_mm/dbcapi.iam.zip
/opt/sybase/install/archives/dblib_mm/dblib.iam.zip
/opt/sybase/install/archives/dblib_mm/archives/version.txt
/opt/sybase/install/archives/dblib_mm/archives/comp_size.properties
/opt/sybase/install/archives/lang_mm/lang.iam.zip
/opt/sybase/install/archives/lang_mm/archives/version.txt
/opt/sybase/install/archives/lang_mm/archives/comp_size.properties
/opt/sybase/install/archives/odbc_mm/archives/version.txt
/opt/sybase/install/archives/odbc_mm/archives/comp_size.properties
/opt/sybase/install/archives/odbc_mm/odbc.iam.zip
/opt/sybase/install/archives/ase_add_lm_mm/archives/version.txt
/opt/sybase/install/archives/ase_add_lm_mm/archives/comp_size.properties
/opt/sybase/install/archives/ase_add_lm_mm/ase_add_lm.iam.zip
/opt/sybase/install/archives/sysam_server_mm/sysam_server.iam.zip
/opt/sybase/install/archives/sysam_server_mm/archives/version.txt
/opt/sybase/install/archives/sysam_server_mm/archives/comp_size.properties
/opt/sybase/install/archives/SySAMLicenseInfo.xml
/opt/sybase/install/archives/conn_add_lm_mm/conn_add_lm.iam.zip
/opt/sybase/install/archives/conn_add_lm_mm/archives/version.txt
/opt/sybase/install/archives/conn_add_lm_mm/archives/comp_size.properties
/opt/sybase/install/archives/odata_mm/odata.iam.zip
/opt/sybase/install/archives/odata_mm/archives/version.txt
/opt/sybase/install/archives/odata_mm/archives/comp_size.properties
/opt/sybase/install/archives/SYBASE_ASE_DE.lic
/opt/sybase/install/archives/sapjre71_mm/archives/version.txt
/opt/sybase/install/archives/sapjre71_mm/archives/comp_size.properties
/opt/sybase/install/archives/sapjre71_mm/sapjre71.iam.zip
/opt/sybase/install/archives/conn_lm_mm/conn_lm.iam.zip
/opt/sybase/install/archives/conn_lm_mm/archives/version.txt
/opt/sybase/install/archives/conn_lm_mm/archives/comp_size.properties
/opt/sybase/install/archives/conn_php_mm/conn_php.iam.zip
/opt/sybase/install/archives/conn_php_mm/archives/version.txt
/opt/sybase/install/archives/conn_php_mm/archives/comp_size.properties
/opt/sybase/install/archives/conn_python_mm/archives/version.txt
/opt/sybase/install/archives/conn_python_mm/archives/comp_size.properties
/opt/sybase/install/archives/conn_python_mm/conn_python.iam.zip
/opt/sybase/install/archives/asemap_mm/archives/version.txt
/opt/sybase/install/archives/asemap_mm/archives/comp_size.properties
/opt/sybase/install/archives/asemap_mm/asemap.iam.zip
/opt/sybase/install/archives/xa_mm/xa.iam.zip
/opt/sybase/install/archives/xa_mm/archives/version.txt
/opt/sybase/install/archives/xa_mm/archives/comp_size.properties
/opt/sybase/install/archives/qptune_mm/archives/version.txt
/opt/sybase/install/archives/qptune_mm/archives/comp_size.properties
/opt/sybase/install/archives/qptune_mm/qptune.iam.zip
/opt/sybase/install/archives/scc_server_mm/scc_server.iam.zip
/opt/sybase/install/archives/scc_server_mm/archives/version.txt
/opt/sybase/install/archives/scc_server_mm/archives/comp_size.properties
/opt/sybase/install/archives/locales/unicode/sylapi/ja_JP/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/th_th/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/pt_PT/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/es_ES/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/zh_cn/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/pl_pl/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/es_es/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/pl_PL/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/fr_fr/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/fr_FR/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/pt_pt/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/de_de/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/de_DE/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/ja_jp/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/zh_CN/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/ko_KR/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/en_us/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/th_TH/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/ko_kr/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/en_US/sylapi.lcu
/opt/sybase/install/sysam_setup/sample_response.txt
/opt/sybase/install/sysam_setup/setup.bin
/opt/sybase/install/sysam_setup/archives/sysam_util_mm/sysam_util.iam.zip
/opt/sybase/install/sysam_setup/archives/sysam_util_mm/archives/comp_size.prope
rties
/opt/sybase/install/sysam_setup/archives/sysam_server_mm/sysam_server.iam.zip
/opt/sybase/install/sysam_setup/archives/sysam_server_mm/archives/comp_size.pro
perties
/opt/sybase/install/ThirdPartyLegal/FreeDownloadTermsMasterList.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.CSI.2.9_M5_And_Ab
ove.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.jConnect.16.0.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.SDK.16.0.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.SQLAnywhereODBCDr
iverManager.11.0.1.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.ASE.16.0.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.SCC.3.3.pdf
/opt/sybase/SYBASE.env
/opt/sybase/interf.old
/opt/sybase/SYBASE.sh
/opt/sybase/SYSAM-2_0/licenses/SYBASE.opt
/opt/sybase/SYSAM-2_0/licenses/SYBASE_ASE_DE.lic
/opt/sybase/ASE-16_0/sybase01.013
/opt/sybase/ASE-16_0/sybase01.018
/opt/sybase/ASE-16_0/sybase01.015
/opt/sybase/ASE-16_0/sybase01.krg
/opt/sybase/ASE-16_0/sybase01.019
/opt/sybase/ASE-16_0/sybase01.007
/opt/sybase/ASE-16_0/sybase01.001
/opt/sybase/ASE-16_0/install/RUN_sybase01_BS
/opt/sybase/ASE-16_0/install/sybase01.log
/opt/sybase/ASE-16_0/install/RUN_sybase01
/opt/sybase/ASE-16_0/install/sybase01_BS.log
/opt/sybase/ASE-16_0/sybase01.011
/opt/sybase/ASE-16_0/sybase01.cfg
/opt/sybase/ASE-16_0/sybase01.002
/opt/sybase/ASE-16_0/sybase01.016
/opt/sybase/ASE-16_0/init/logs/srvbuild1024.001-sybase01.rs
/opt/sybase/ASE-16_0/init/logs/configASELog.log
/opt/sybase/ASE-16_0/init/logs/configBSLog.log
/opt/sybase/ASE-16_0/init/logs/srvbuild1024.001
/opt/sybase/ASE-16_0/init/logs/srvbuild1024.002-sybase01_BS.rs
/opt/sybase/ASE-16_0/init/logs/srvbuild1024.002
/opt/sybase/ASE-16_0/sybase01.014
/opt/sybase/ASE-16_0/sybase01.017
/opt/sybase/ASE-16_0/sybase01.012
/opt/sybase/ASE-16_0/sysam/SYBASE.properties
/opt/sybase/ASE-16_0/sysam/SYB.properties
/opt/sybase/ASE-16_0/sysam/CENTSRV01.properties
/opt/sybase/ASE-16_0/sysam/sybase01.properties
/opt/sybase/ASE-16_0/sysam/sysam.properties.template
/opt/sybase/ASE-16_0/sybase01.bak
/opt/sybase/ASE-16_0/sybase01.005
/opt/sybase/ASE-16_0/sybase01.010
/opt/sybase/ASE-16_0/sybase01.004
/opt/sybase/ASE-16_0/sybase01.008
/opt/sybase/ASE-16_0/sybase01.009
/opt/sybase/ASE-16_0/sybase01.006
/opt/sybase/ASE-16_0/sybase01.003
/opt/sybase/.bash_history
/opt/sybase/ODATA-16_0/bin64/dbosrv16.ini
/opt/sybase/DBISQL-16_0/bin/dbisql.ini
/opt/sybase/DBISQL-16_0/bin/dbisql.000
/opt/sybase/DBISQL-16_0/bin/dbisql_64.rep
/opt/sybase/dir/sysprocs.dat
/opt/sybase/dir/sybsysdb.dat
/opt/sybase/dir/master.dat
/opt/sybase/dir/tempdbdev.dat

Do you want to delete all of these files? (Y/N): Y

===============================================================================
Uninstall Complete
------------------

Some items could not be removed.

-bash-4.1$

Note:

Remove additional files manually that you have created or modified after Sybase installation.

In my case, I have created “sybase” directory in /var/ and /opt/.

 -bash-4.1$ cd /opt/
 -bash-4.1$ rm -rf sybase/
 -bash-4.1$ cd /var/
 -bash-4.1$ rm -rf sybase/

 

Congratulations! SAP ASE is now completely removed from Linux host.

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

TNS-01201: Listener cannot find executable string for SID string – Listener failed to start

Usually TNS-01201 listener error pop up when oracle can’t find its executable under ORACLE_HOME variable mentioned in listener.ora file. After investigating listener.ora file, Found same, ORACLE_HOME was set to wrong path. After setting proper path listener started successfully.

Have a look on error logs:

Error Log: While starting listener

[oracle@PR admin]$ lsnrctl start
...
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
TNS-01201: Listener cannot find executable /u01/oracle/product/11.2.0/dbhome_1/bin/oracle for SID RTS
Listener failed to start. See the error message(s) above...

Error Log: While tnsping

[oracle@PR admin]$ tnsping RTS
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS) (UR=A)))
TNS-12541: TNS:no listener

listener.ora file:

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = RTS)
   (ORACLE_HOME = /u01/oracle/product/11.2.0/dbhome_1)
   (SID_NAME = RTS)
  )
 )
LISTENER =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))
 )
ADR_BASE_LISTENER = /u01/app/oracle

Solution:

Checkout above RED highlighted lines, mentioned ADR_BASE_LISTENER path is different then ORACLE_HOME. So after applying changes, listener started successfully. As follows:

[oracle@PR admin]$ lsnrctl start
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PR)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 29-AUG-2015 09:22:35
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
Services Summary...
Service "RTS" has 1 instance(s).
Instance "RTS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

tnsping also successful to RTS SID

[oracle@PR admin]$ tnsping RTS
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS) (UR=A)))
OK (0 msec)

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

ORA-01182: cannot create database file X – file is in use or recovery ORA-01111: name for data file X is unknown – rename to correct file

On Physical Standby Data Guard, Following command failed with ORA-01182. Error itself self explanatory.
As a part of solution, you just need to End up recovery sessions and bounce back your database to Mount mode and issue SQL command again.

Error Log:

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/prod/dbs/UNNAMED00100' as '/u03/Oradata/Prod_OTHER_0072.DAT';
alter database create datafile '/u01/app/oracle/product/11.2.0/prod/dbs/UNNAMED00100' as '/u03/Oradata/Prod_OTHER_0072.DAT'
*
ERROR at line 1:
ORA-01182: cannot create database file 10 - file is in use or recovery
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10:
'/u01/app/oracle/product/11.2.0/prod/dbs/UNNAMED00100'

You just can’t create a datafile that already being online or is being recovered.

Solution:

SQL> shutdown immediate;
SQL> startup mount;
SQL>  < Issue above SQL Command >;

 

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

ORA-27047: unable to read the header block of file Linux-x86_64 Error: 25: Inappropriate ioctl for device ORA-27048: skgfifi: file header information is invalid ORA-27072: File I/O error

While recovering my manual physical standby data guard configuration, Every time oracle would apply all archive logs to standby database and end up with following mentioned errors.

As a part of troubleshooting, I have Verified:

  • Archive log sequence number on both the side but that was in place.
  • Block corruptions on datafiles level on both sides. It was clean.
  • Physical storage blocks, It was also clean.
  • Almost verify everything, that i can.

After scratching my head for several hours, I found non oracle files situated on FRA. After moving it from FRA, Recovery process successfully carried out. Non oracle file was only culprit to the following mentioned errors. It was very strange case I have faced in my life.

Solution:

Remove all non oracle files from FRA.

Error logs:

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 15198931 generated at 07/23/2015 17:55:02 needed for thread 1
ORA-00289: suggestion :
/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5180_bv1qlby6_.arc
ORA-00280: change 15198931 for thread 1 is in sequence #5180
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 15199107 generated at 07/23/2015 18:00:02 needed for thread 1
ORA-00289: suggestion :
/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5181_bv1qvq3n_.arc
ORA-00280: change 15199107 for thread 1 is in sequence #5181
ORA-00278: log file '/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5180_bv1qlby6_.arc' no longer needed for this recovery
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27048: skgfifi: file header information is invalid
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1
Additional information: 392
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27048: skgfifi: file header information is invalid
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1
Additional information: 392
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropri

Thanks,

Stay Tune. 🙂

Oracle 12c Logo

ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use

ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use

ORA error message itself is self explanatory, When undo tablespace is in use and we will try to drop that tablespace than oracle pop up with ORA-30013 error.

Solution:
Wait until active transactions are COMMITED or ROLLED BACK. If so, you can easily drop mentioned tablespace.
OR
You can kill that transaction if it unnecessary.

Consider following example:

SQL> create tablespace newundotbs datafile '/u01/app/oracle/oradata/prod/newundotbs1.dbf' size 200M autoextend on maxsize 500M;
Tablespace created.
SQL> alter system set UNDO_TABLESPACE=NEWUNDOTBS scope=spfile;
System altered.

Following command will failed because there is active transactions.

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Query dba_rollback_segs to know list of online segments:

SQL> select tablespace_name, owner, segment_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1' and status='ONLINE';

Retrive SID and SERIAL# from below mentioned query in order to kill session with ‘alter system kill session’ command.

SQL> select SID, substr(username,1,10) username,serial#,segment_name from v$transaction,dba_rollback_segs,v$session where saddr=ses_addr and xidusn=segment_id;
SQL> alter system kill session 'SID,SERIAL#';
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.

Tablespace successfully dropped.

Thanks
Stay Tune 🙂