Enable Archivelog Mode Oracle 12c RAC Database

I was working on enabling archivelog mode on Oracle Rac 12c database. You can follow below step by step procedure. If you even want to look at disabling archive log mode click on the link( Disable Archivelog Mode Oracle 12c RAC Database).

[oracle@Sajidserver1~]$srvctl status database -d SAJIDDB
Instance SAJIDDB1 is running on node Sajidserver1
Instance SAJIDDB2 is running on node Sajidserver2


[oracle@Sajidserver1 ~]$ sqlplus / as sysdba

SYS@SAJIDDB1>alter system set cluster_database=false scope=spfile sid='*';

System altered.

SYS@SAJIDDB1>exit



[oracle@Sajidserver1 ~]$ srvctl stop database -d SAJIDDB -o immediate

[oracle@Sajidserver1 ~]$ sqlplus / as sysdba

SYS@SAJIDDB1>startup mount;
ORACLE instance started.

Database mounted.

SYS@SAJIDDB1>archive log list;
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            +FLASHDG
Oldest online log sequence     1221
Next log sequence to archive   1222
Current log sequence           1222


SYS@SAJIDDB1>ALTER DATABASE ARCHIVELOG;

Database altered.

SYS@SAJIDDB1>alter system set cluster_database=true scope=spfile sid='*';

System altered.

SYS@SAJIDDB1>shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SYS@SAJIDDB1>exit

[oracle@Sajidserver1 ~]$ srvctl start database -d SAJIDDB

[oracle@Sajidserver1 ~]$ sqlplus / as sysdba

SYS@SAJIDDB1>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FLASHDG
Oldest online log sequence     1228
Current log sequence           1229

SYS@SAJIDDB1>exit


[oracle@Sajidserver1 ~]$ srvctl status database -d SAJIDDB
Instance SAJIDDB1 is running on node Sajidserver1
Instance SAJIDDB2 is running on node Sajidserver2

Disable Archivelog Mode Oracle 12c RAC Database

I was working on very basic DBA task to disable the archive log mode in oracle 12c RAC DATABASE, below  is the step by step procedure.

[oracle@Sajidserver1~]$srvctl status database -d SAJIDDB
Instance SAJIDDB1 is running on node Sajidserver1
Instance SAJIDDB2 is running on node Sajidserver2


[oracle@Sajidserver1 ~]$ sqlplus / as sysdba

SYS@SAJIDDB1>alter system set cluster_database=false scope=spfile sid='*';

System altered.

SYS@SAJIDDB1>exit

[oracle@Sajidserver1 ~]$ srvctl stop database -d SAJIDDB -o immediate

[oracle@Sajidserver1 ~]$ sqlplus / as sysdba

SYS@SAJIDDB1>startup mount;
ORACLE instance started.

Database mounted.

SYS@SAJIDDB1>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FLASHDG
Oldest online log sequence     25
Next log sequence to archive   0
Current log sequence           26


SYS@SAJIDDB1>ALTER DATABASE NOARCHIVELOG;

Database altered.

SYS@SAJIDDB1>alter system set cluster_database=true scope=spfile sid='*';

System altered.

SYS@SAJIDDB1>shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SYS@SAJIDDB1>exit

[oracle@Sajidserver1 ~]$ srvctl start database -d SAJIDDB

[oracle@Sajidserver1 ~]$ sqlplus / as sysdba


SYS@SAJIDDB1>archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            +FLASHDG
Oldest online log sequence     26
Current log sequence           27

SYS@SAJIDDB1>exit


[oracle@Sajidserver1 ~]$ srvctl status database -d SAJIDDB
Instance SAJIDDB1 is running on node Sajidserver1
Instance SAJIDDB2 is running on node Sajidserver2

#########
Issues:
#########

1. You need to set cluster_database parameter from container database. If not you will see below error:

SYS@SAJIDDB1>alter session set container=sajid_pdb;
SYS@SAJIDDB1>alter system set cluster_database=false scope=spfile sid='*';
alter system set cluster_database=false scope=spfile sid='*'
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

2. Be prepared to drop any retore points to disable archive log.

SYS@SAJIDDB1>alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points

SYS@SAJIDDB1>SELECT name, guarantee_flashback_database, scn FROM v$restore_point;

SYS@SAJIDDB1>drop restore point <Restore_Point_Name>;

Restore point dropped.

SYS@SAJIDDB1>ALTER DATABASE FLASHBACK OFF;

Database altered.

Applying PSU 12.1.0.2.160719 (Jul2016) for GI and DB Oracle RAC

I was installing the latest Oracle PSU 12.1.0.2.160719 (Jul2016) on 3 node cluster and happen to run into below issues. Do refer the readme file before executing the PSU.


If you need further in details do refer to Apply the latest PSU5 for 12.1.0.2 RAC Database
(http://sajidkhadarabad.blogspot.com/2015/08/apply-latest-psu5-for-12102-rac-database.html) (Scroll down) in my blog .

##############
Issue 1
##############

1. Permission Issue

Applying patch(es) to "/usr/12c/grid" ...
Command "/usr/12c/grid/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_grid_patchList -local  -invPtrLoc /usr/12c/grid/oraInst.loc -oh /usr/12c/grid -silent -ocmrf /usr/12c/grid/OPatch/ocm/bin/ocm.rsp" execution failed:
UtilSession failed:
Prerequisite check "CheckApplicable" failed.

Log file Location for the failed command: /usr/12c/grid/cfgtoollogs/opatch/opatch.log

For more details, please refer to the log file "/usr/12c/grid/cfgtoollogs/opatchauto/23273629/opatch.debug.log".

Apply Summary:
Following patch(es) are successfully installed:
DB Home: /usr/oracle/product/12c:

opatchauto ran into some warnings during patch installation (Please see log file for details):
DB Home: /usr/oracle/product/12c: 23054246,23054327

Following patch(es) failed to be installed:
GI Home: /usr/12c/grid: 21436941,23054246,23054327,23054341
Command failure exception

opatchauto failed with error code 2.


Looked in to the log specifications and edited the permission for dropdb.pl from oracle:oinstall to grid:oinstall and run the opatchauto resume.

[root@SajidServer OPatch]# ./opatchauto resume

Have a cup of coffee, till that time it will be completed.

OPatch Automation Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.

OPatchauto Version : 12.1.
OUI Version        : 12.1.
Running from       : /usr/grid

OPatchauto will attempt to resume the last run session. This might take several minutes...

opatchauto log file: /usr/grid/cfgtoollogs/opatchauto/23273629/opatch.deploy.log

Executing command:
/usr/grid/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_grid -local  -invPtrLoc /usr/grid/oraInst.loc -oh /usr/grid -silent -ocmrf /usr/grid/OPatch/ocm/bin/ocm.rsp
 as owner "grid"
... above command successful.

Executing command:
/usr/grid/rdbms/install/rootadd_rdbms.sh
 as owner "root"
... above command successful.


OPatchauto was able to resume from the previous patching session and complete successfully.

opatchauto succeeded.

#############
Issue 2
#############

2. Once Patching is completed Listener status will be down in OEM, even if you try to start them manually on the server it will fail with below error.

lsnrctl start listener

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-SEP-2016 02:15:55

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Starting /usr/grid/bin/tnslsnr: please wait...

TNS-12537: TNS:connection closed
TNS-12560: TNS:protocol adapter error
TNS-00507: Connection closed
Linux Error: 29: Illegal seek <<<< here


Update your listener.ora file across all the nodes and bounce the listener on which you are facing the issue.

CRS_NOTIFICATION_LISTENER=OFF
CRS_NOTIFICATION_LISTENER_SCAN1=OFF
CRS_NOTIFICATION_LISTENER_SCAN2=OFF
CRS_NOTIFICATION_LISTENER_SCAN3=OFF

lsnrctl start listener from Grid home.

This has been fixed in Oct2016 12.1.0.2.161018 patch. Please follow Oracle Doc ID (2171506.1).

Moving ASM FILES between remote ASM instance by ASMCMD

We were working on database duplication for standby to copy the ASM related files from one cluster to other and encountered few issues. I first encountered it on 12c related to ASM file copy, asmcmd remote copy works through listener connection. ASMCMD remote connection can fail with below generic error.

1. Sysasm remote connection does not work. 
2. Incorrect password given for asmsnmp user. 
3. Remote ASM Instance password file missing.
4. Not able to reach remote host. 
5. Remote host listener is down. 
6. Remote ASM Instance is not registered with listener and running non-default port.

I have faced the below issue:

ASMCMD> cp +TESTDG/<DB_NAME>/t_435129223.143.435129224 asmsnmp/<pwd>@NODE1+asm1:+TESTDG/<DB_NAME>/t_435129223.143
ASMCMD-8201: (Bad argc for RDBMS:ASMCMD-8201).

[8201] means unable to connect remote ASM Instance. 

It can even raise,

ASMCMD> cp +TESTDG/<DB_NAME>/t_435129223.143.435129224 asmsnmp/<pwd>@NODE1+asm1:+TESTDG/<DB_NAME>/t_435129223.143

ASMCMD-8016: copy source '+TESTDG/<DB_NAME>/t_435129223.143.435129224' and target '+TESTDG/<DB_NAME>/t_435129223.143' failed
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 486
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

Solution:

select * from v$pwfile_users;

grant sysasm to asmsnmp;

RMAN-06214: When trying to delete Datafile Copy or Control File Copy

Sometimes even though your rman backups are running in a good state you happen to find the below errors in backup log file.


RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   /backup/<dbname>/<piece_name>


RMAN-06207: WARNING: 22 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy /backup/<dbname>/<control_file_name>


Solution:

RMAN> crosscheck copy;
RMAN> delete expired copy;

Delete or Drop Oracle Database 12c

You can choose among multiple options to drop Oracle databases. 

1. DBCA (Silent Method or GUI)

2. Command Line Interface (startup mount restrict and then issue drop database)

3. Manually cleaning up files

In 12c using dbca you can even drop in particular pluggable databases too using the option Manage Pluggable databases and then delete pluggable database. In this post I will provide screenshot's to  drop database in 12c.

Set your environment variables properly (ORACLE_HOME) and invoke dbca and select Delete Database

 At this stage you can check the name of the DB and pdbseed  those will be getting deleted including control files, system, sysaux, undotbs, users, temp after that hit finish

 At this stage it will even throw a pop up screen to open the database in restrict mode. You can ignore it and hit next.

Done with database deletion including pluggable database in it. 

It will even clean up oratab entries, but you need to manually delete pdbseed directory and log dump directories (bdump, cdump)

If it a RAC system you even need to clean up the ASM diskgroups associated with the database manually.

If you would like to drop the database from sql command prompt, you can perform the below steps.

1. shutdown immediate

2. startup mount exclusive

3. alter system enable restricted session

4. drop database

Install Oracle Golden Gate 12c

Download the Golden Gate software based on your operating system from OTN and unzip the contents of the zip file. Make sure to have xming or vnc server installed to invoke GUI. Installation is pretty straight forward. In this particular scenario am working on installing Oracle GoldenGate version 12.1.2.0.0 on a Linux machine. You need to install Oracle Golden Gate on both source as well as destination servers. Ensure your profiles are set properly on the server. Lets start the install it takes less than 10 minutes.


SajidServer:oracle$ cd /usr/oracle/gg12c

SajidServer:oracle$ ls -ltr
drwxr-xr-x 3 oracle oinstall      4096 Mar 21 08:58 fbo_ggs_Linux_x64_shiphome
-rw-r--r-- 1 oracle oinstall      1559 Mar 21 18:12 OGG-12.2.0.1-README.txt
-rw-r--r-- 1 oracle oinstall    282294 Mar 21 18:13 OGG-12.2.0.1.1-ReleaseNotes.pdf
-rwxrwxr-x 1 oracle oinstall 475611228 Mar 21 12:26 fbo_ggs_Linux_x64_shiphome.zip

SajidServer:oracle$ cd fbo_ggs_Linux_x64_shiphome/Disk1

SajidServer:oracle$ ls
install  response  runInstaller  stage

SajidServer:oracle$ ./runInstaller

Enter right Software Location  for Golden Gate and Database home, hit next

Done With the Install.

Uninstall Oracle Golden Gate 12c

If you happen to uninstall Oracle Golden Gate 12c then you can perform below steps for clean uninstall of Oracle Golden Gate 12c.



1. Log on to the server
2. set profile to Oracle Home
3. Drop the Golden Gate schema in both source and target databases.
4. Set profile for Golden Gate
5. Invoke GGSCI and stop Extract or Replicats if any then stop the Golden Gate Manager
6. Run deinstall.sh

Deinstall.sh will clean up all the Golden Gate binaries including the directory you have created for Golden Gate.


[Sajid Server:oracle$] pwd
<Golden Gate Home>/deinstall

[Sajid Server:oracle$]./deinstall.sh

ALERT: Ensure all the processes running from the current Oracle Home are shutdown prior to running this software uninstallation script.

Proceed with removing Oracle GoldenGate home: <Golden Gate Home> (yes/no)? [no]
yes
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 18725 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-02-05_01-47-48PM. Please wait ...Oracle Universal Installer, Version 11.2.0.3.0 Production
Copyright (C) 1999, 2011, Oracle. All rights reserved.

Starting deinstall


Deinstall in progress (Friday, February 5, 2016 1:48:07 PM EST)
............................................................... 100% Done.

Deinstall successful

End of install phases.(Friday, February 5, 2016 1:48:35 PM EST)
End of deinstallations
Please check '/usr/oraInventory/logs/silentInstall2016.log' for more details.

At this point you are done with the uninstall.

Applying PSU 12.1.0.2.160119 (JAN2016) for GI and DB Oracle RAC

This article is created just as a refferal to apply the PSU 12.1.0.2.160119 (JAN2016) for 3 node cluster and few tiff's which I have faced. Do refer the readme file before executing the PSU. Perform below steps before you execute the Patch.

1. Take a backup of Optach lsinventory on each node for GRID as well as ORACLE user.
2. Download the latest and gretest patches.
3. Create the ocm response files.
4. Have proper permission set for the patches.

If you need further in details do refer to Apply the latest PSU5 for 12.1.0.2 RAC Database ( http://sajidkhadarabad.blogspot.com/2015/08/apply-latest-psu5-for-12102-rac-database.html ) (Scroll down) in my blog .

So lets jump in and start executing the patch.

[root@SajidServer01 ~]# cd /usr/12.1.0.2/grid/OPatch/
[root@SajidServer01 OPatch]# ./opatchauto apply  <Location of your Patch> -ocmrf  <Location of your ocm.rsp file>
OPatch Automation Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.

OPatchauto Version : 12.1.0.1.10
OUI Version        : 12.1.0.2.0
Running from       : /usr/12.1.0.2/grid

opatchauto log file: /usr/12.1.0.2/grid/cfgtoollogs/opatchauto/22191349/opatch_gi_deploy.log

Parameter Validation: Successful

Configuration Validation: Successful

Patch Location: /usr/oracle/patches/psu/22191349
Grid Infrastructure Patch(es): 21436941 21948341 21948344 21948354
DB Patch(es): 21948344 21948354


The following patch(es) are duplicate patches with patches installed in the Oracle Home.
 [ 21436941]
You have already installed same patch(es) with same UPI(s) or same version(s).
These patch(es) will be skipped.

Opatchauto skipped installing the above patch in /usr/12.1.0.2/grid


The following patch(es) are duplicate patches with patches installed in the Oracle Home.
 [ 21359755]
You have already installed same patch(es) with same UPI(s) or same version(s).
These patch(es) will be skipped.

Opatchauto skipped installing the above patch in /usr/12.1.0.2/grid


The following patch(es) are duplicate patches with patches installed in the Oracle Home.
 [ 20831110]
You have already installed same patch(es) with same UPI(s) or same version(s).
These patch(es) will be skipped.

Opatchauto skipped installing the above patch in /usr/12.1.0.2/grid


The following patch(es) are duplicate patches with patches installed in the Oracle Home.
 [ 20299023]
You have already installed same patch(es) with same UPI(s) or same version(s).
These patch(es) will be skipped.

Opatchauto skipped installing the above patch in /usr/12.1.0.2/grid


The following patch(es) are duplicate patches with patches installed in the Oracle Home.
 [ 19769480]
You have already installed same patch(es) with same UPI(s) or same version(s).
These patch(es) will be skipped.

Opatchauto skipped installing the above patch in /usr/12.1.0.2/grid

Patch Validation: Successful
Grid Infrastructure home:
/usr/12.1.0.2/grid
DB home(s):
/usr/oracle/product/12.1.0.2



Performing prepatch operations on CRS Home...

Applying patch(es) to "/usr/oracle/product/12.1.0.2" ...
Patch "/usr/oracle/patches/psu/22191349/21948344" successfully applied to "/usr/oracle/product/12.1.0.2".
Patch "/usr/oracle/patches/psu/22191349/21948354" successfully applied to "/usr/oracle/product/12.1.0.2".

Applying patch(es) to "/usr/12.1.0.2/grid" ...
Patch "/usr/oracle/patches/psu/22191349/21436941" applied to "/usr/12.1.0.2/grid" with warning.
Patch "/usr/oracle/patches/psu/22191349/21948341" applied to "/usr/12.1.0.2/grid" with warning.
Patch "/usr/oracle/patches/psu/22191349/21948344" applied to "/usr/12.1.0.2/grid" with warning.
Patch "/usr/oracle/patches/psu/22191349/21948354" applied to "/usr/12.1.0.2/grid" with warning.

Performing postpatch operations on CRS Home...

SQL changes, if any, are applied successfully on the following database(s): SajidDB

Apply Summary:
Following patch(es) are successfully installed:
DB Home: /usr/oracle/product/12.1.0.2: 21948344,21948354

opatchauto ran into some warnings during patch installation (Please see log file for details):
GI Home: /usr/12.1.0.2/grid: 21436941,21948341,21948344,21948354

opatchauto completed with warnings.


Hallelujah patch installation is done.

########
Issue 1:
########
Clusterware is either not running or not configured. You have the following 2 options:

1. Configure and start the Clusterware on this node and re-run the tool
2. Run the tool with '-oh <GI_HOME>' to first patch the Grid Home, then invoke tool with '-database <oracle database name>' or '-oh <RAC_HOME>' to patch the RAC home
Parameter Validation: FAILED

Opatchauto Session failed: Parameter validation failed
Exception in thread "main" java.lang.RuntimeException: java.io.IOException: Stream closed
        at oracle.opatchauto.gi.GILogger.writeWithoutTimeStamp(GILogger.java:432)
        at oracle.opatchauto.gi.GILogger.printStackTrace(GILogger.java:447)
        at oracle.opatchauto.gi.OPatchauto.main(OPatchauto.java:97)
Caused by: java.io.IOException: Stream closed
        at java.io.BufferedWriter.ensureOpen(BufferedWriter.java:98)
        at java.io.BufferedWriter.write(BufferedWriter.java:203)
        at java.io.Writer.write(Writer.java:140)
        at oracle.opatchauto.gi.GILogger.writeWithoutTimeStamp(GILogger.java:426)
        ... 2 more

Workaround:

Make sure rootcrs.pl is in lock mode. Reboot the cluster services and do a clean patch install as super user.

#########
Issue 2:
#########
Running prerequisite checks…
Prerequisite check “Check Patch Applicable On Current Platform” failed.
Patch is not applicable on current platform.
Platform ID needed is : 46
Platform IDs supported by patch are: 226
UtilSession failed: Prerequisite check “CheckPatchApplicableOnCurrentPlatform” failed.
OPatch failed with error code 73

Workaround:

export OPATCH_PLATFORM_ID=226 and kickoff the patch again