ORA-00823: Specified value of sga_target greater than sga_max_size - How to solve this error in a cluster environment

I accidentally changed the values of sga_target with large value than the sga_max_size and did a shutdonw in one of the cluster database,

SQL> alter system set sga_target=3g scope=spfile;

System altered. 

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,OLAP, Advanced Analytics and Real Application Testing options

[oracle@exdb01trace]$ srvctl stop database -d GGOSB
while starting the database  up I encountered following error . 

[oracle@exdb01trace]$ srvctl start database -d GGOSB
PRCR-1079 : Failed to start resource ora.GGOSB.db
CRS-5017: The resource action "ora.GGOSB.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-00823: Specified value of sga_target greater than sga_max_size
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/exdb02/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.GGOSB.db' on 'exdb02' failed
CRS-5017: The resource action "ora.GGOSB.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-00823: Specified value of sga_target greater than sga_max_size
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/exdb01/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.GGOSB.db' on 'exdb01' failed
CRS-2632: There are no more servers to try to place resource 'ora.GGOSB.db' on that would satisfy its placement policy
[oracle@exdb01trace]$ 

I tried to recreate the pfile from spfile 

SQL> CREATE PFILE FROM SPFILE;
CREATE PFILE FROM SPFILE
*
ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> create pfile='/tmp/initGGOSB1.ora' from memory;
create pfile='/tmp/initGGOSB1.ora' from memory
                                        *
ERROR at line 1:
ORA-00922: missing or invalid option


None of them worked as my spfile is stored in ASM ,  I spend some time on google  and I found following syntax creating the pfile as  expected.
SQL> create pfile='/tmp/initGGOSB1.ora' from spfile='+RECOC1/GGOSB/PARAMETERFILE/spfile.15366.905354331';

File created.

SQL> exit
Disconnected
[oracle@exdb01dbs]$ cat  /tmp/initGGOSB1.ora
GGOSB1.__data_transfer_cache_size=0
GGOSB2.__data_transfer_cache_size=0
GGOSB1.__db_cache_size=301989888
GGOSB2.__db_cache_size=318767104
GGOSB1.__java_pool_size=16777216
GGOSB2.__java_pool_size=16777216
GGOSB1.__large_pool_size=16777216
GGOSB2.__large_pool_size=16777216
GGOSB1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
GGOSB2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
GGOSB1.__pga_aggregate_target=1073741824
GGOSB2.__pga_aggregate_target=1073741824
GGOSB1.__sga_target=2147483648
GGOSB2.__sga_target=2147483648
GGOSB1.__shared_io_pool_size=16777216
GGOSB2.__shared_io_pool_size=33554432
GGOSB1.__shared_pool_size=1728053248
GGOSB2.__shared_pool_size=1694498816
GGOSB1.__streams_pool_size=0
GGOSB2.__streams_pool_size=0
*._clusterwide_global_transactions=FALSE
*.audit_file_dest='/u01/app/oracle/admin/GGOSB/adump'
*.audit_trail='db'
*.cluster_database=TRUE
*.compatible='12.1.0.2.0'
*.control_file_record_keep_time=31
*.control_files='+DATAC1/GGOSB/CONTROLFILE/control1.ora','+RECOC1/GGOSB/CONTROLFILE/control2.ora'
*.db_block_size=8192
*.db_create_file_dest='+DATAC1'
*.db_create_online_log_dest_1='+DATAC1'
*.db_create_online_log_dest_2='+RECOC1'
*.db_domain=''
*.db_name='GGOSB'
*.db_recovery_file_dest='+RECOC1'
*.db_recovery_file_dest_size=536870912000
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=GGOSBXDB)'
*.enable_pluggable_database=false
GGOSB1.instance_number=1
GGOSB2.instance_number=2
*.open_cursors=300
*.parallel_min_servers=2
*.pga_aggregate_limit=1572864000
*.pga_aggregate_target=1073741824
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sga_max_size=2147483648
*.sga_target=3221225472
*.star_transformation_enabled='TRUE'
GGOSB2.thread=2
GGOSB1.thread=1
GGOSB2.undo_tablespace='UNDOTBS2'
GGOSB1.undo_tablespace='UNDOTBS1'
[oracle@exdb01dbs]$ 

Copy newly created pfile to dbs directory

[oracle@exdb01 dbs]$ cp /tmp/initGGOSB1.ora initGGOSB1.ora

Edit the values of *.sga_max_size to 3g

[oracle@exdb01 dbs]$ vi initTFOSB1.ora

Start database using edited pfile 

SQL> sartup mount pfile='initGGOSB1.ora';
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2929552 bytes
Variable Size            1786756208 bytes
Database Buffers         1392508928 bytes
Redo Buffers               39030784 bytes
Database mounted.
SQL>
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 3G
sga_target                           big integer 3G
unified_audit_sga_queue_size         integer     1048576
SQL>
SQL> alter database open;

Database altered.

SQL>
Create new spfile on one of the disk group
SQL> create spfile='+RECOC1' from pfile;

File created.

SQL> shut immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@exdb01dbs]$

Rename  the old pfile and  edit it
[oracle@exdb01 dbs]$ mv initGGOSB1.ora initGGOSB1.ora_old
[oracle@exdb01 dbs]$ vi initGGOSB1.ora
[oracle@exdb01 dbs]$ cat initGGOSB1.ora
spfile='+RECOC1/GGOSB/PARAMETERFILE/spfile.12740.914436113' [oracle@exdb01 dbs]$
Copy new init file to second node
[oracle@exdb01 dbs]$ pwd
/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs
[oracle@exdb01 dbs]$ scp initGGOSB1.ora exdb02:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initGGOSB2.ora
initGGOSB1.ora                                                                                                                                                                                         100%   60     0.1KB/s   00:00
[oracle@exdb01 dbs]$
Start the database
[oracle@exdb01 dbs]$ srvctl status database -d GGOSB
Instance GGOSB1 is not running on node exdb01
Instance GGOSB2 is not running on node exdb02
[oracle@exdb01 dbs]$ srvctl start database -d GGOSB
[oracle@exdb01 dbs]$ srvctl status service -d GGOSB
Service pGGOSBapp is running on instance(s) GGOSB1,GGOSB2
Service pGGOSBoem is running on instance(s) GGOSB1,GGOSB2
Service pGGOSBops is running on instance(s) GGOSB1,GGOSB2
[oracle@exdb01 dbs]$

All good :)

Solution for ORA-16191: Primary log shipping client not logged on standby in DataGuard 

If you change SYS password with ALTER USER SYS IDENTIFIED BY NEWPASSWORD on the primary database of a dataguard environment, Logs should applied on standby side and sychronization between primary and standby database is fine .But once you restart your standby database, primary side stops to transfer archivelogs to standby and you will see an error on primary database alertlog file like:
------------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------------
check whether you are able to log into the standby from the primary using sqlplus or not 

sqlplus sys@STANDBY as sysdba  --- you can't 

This is because "If you issue the ALTER USER statement to change the password for SYS, both the password stored in the data dictionary and the password stored in the password file are updated." So your password file is updated in primary side but not in standby side.

In this situation set your password file in standby server with:

$orapwd file=orapw$ORACLE_SID password=***** entries=5 ignorecase=Y (don't forget to move/delete old one)
or you can copy the primay database password file and rename it at standby side 

Conclusion: If you're going to change your sys password in a dataguard environment you must set the password files with new password in both primary and standby servers.

Its advisable to follow the steps below when you are planning to change the SYS password in PRIMARY 

Step 1: Defer the remote archival destination (log_archive_dest_state_n) parameter in primary. In case of RAC defer LOG_ARCHIVE_DEST_STATE_n in all nodes.
Step 2: change the password in primary 
Step 3: Recreate or copy the password file in standby. In case of RAC shutdown all instance of standby before recreating the password file.
Step 4: Enable the remote archival destination (log_archive_dest_state_n) parameter in primary

pwd file enhancement in oracle 12c
Until Oracle Database 11g password file of Oracle Database and ASM instance had to be stored in regular filesystem of windows or unix. However, starting from Oracle Database 12c R1 Oracle Database and ASM instance password file can be stored in ASM storage. This gives a great advantage in terms of ease of management of password file in Real Application Clusters (RAC) environment. When using ASM, in earlier versions of RAC, password files had to be stored in individual servers where RAC instances are running. For RAC environments password file can be stored in shared ASM disk group. For this, the compatible.asm disk group attribute must be set to 12.1 or higher for the disk group where the password is to be stored. The SYSASM or SYSDBA privilege is required to manage the Oracle ASM and database password files.

ORA-02020: too many database links in use

Each session in your database has a limited number of database links that can be opened at the same time.If you access a database link in a session, then the link remains open until you close the session.

Cause for ora-02020: The current session has exceeded the INIT parameter 'open_links' maximum.

Action: Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases.

OPEN_LINKS cannot be modified in real-time, so you will have to change it in the spfile or pfile and bounce the database.

SQL> show parameter open_links

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4
open_links_per_instance integer 4

SQL>alter system set open_links=10 scope=spdile;

Then bounce the database

NOTE:You can also try to close the dblink if possible

SQL>ALTER SESSION CLOSE DATABASE LINK linkname;

If you are not sure how many database links are opened up concurrently by your session's database application, you can query v$dblink.

SQL> select in_transaction, count(*) from v$dblink group by in_transaction;

IN_ COUNT(*)
--- ----------
YES 1