Get multiple Oracle SIDs into variable without using oratab

Here's the script for the lazy ones. After script I have explained how the script actually works. 

This script requires connection as sqlplus / as sysdba without giving password

#
# 1.0   17.09.2015      Mika Heino
#
# List Oracle SIDs in server in human readable output
# and connect SQL plus as sysdba
# 

db=`ps -ef | grep [p]mon  | awk {'print $8'} | sed -e 's,ora_pmon_,,' | sed -e s,asm_pmon_,,`
for i in $db ; do
  if [ "$i" == "sed" ] || [ "$i" == *ASM* ]
  then
     continue
  else
         export ORAENV_ASK=NO
         export ORACLE_SID=$i
        . oraenv
        export ORAENV_ASK=YES
   sqlplus -S / as sysdba @your_sql_script
  fi
done

What it actually does? 

ps -ef | grep [p]mon -- List all active pmon processes. Use [] to avoid grep coming into the result list

awk {'print $8'} -- print from output only the 8th column, which is the process name

sed -e 's,ora_pmon_,,' -- remove 'ora_pmon_' text from output

sed -e s,asm_pmon_,, -- remove 'asm_pmon_' text from output

Final output looks like this. It will still output 'sed' text two times, but this will be removed in the clumsy if -clause

[oracle@server ~]$ ps -ef | grep [p]mon  | awk {'print $8'} | sed -e 's,ora_pmon_,,' | sed -e s,asm_pmon_,,

+ASM1

INSTANCE2

INSTANCE3

INSTANCE4

sed

sed

This output is then inserted into variable $db. 

db=`ps -ef | grep [p]mon  | awk {'print $8'} | sed -e 's,ora_pmon_,,' | sed -e s,asm_pmon_,,`

Do for loop for all values within the variable 

for i in $db ; do

Skip and continue if the value is 'sed' or 'ASM' 

  if [ "$i" == "sed" ] || [ "$i" == *ASM* ]
  then
     continue
  else

Otherwise export the Oracle environment, use . oraenv and execute the desired SQL script. 

         export ORAENV_ASK=NO
         export ORACLE_SID=$i
        . oraenv
        export ORAENV_ASK=YES

If your environment has passwords, use them right here using variables.

   sqlplus -S / as sysdba @your_sql_script
  fi
done

Checklist for Windows 2012 Cluster installations -- SQL Server cluster installation taking forever

Failover Cluster Manager will hang unless following KB is installed (on Windows Server 2012)
https://support.microsoft.com/en-us/kb/2803748

If you can't use VMware/Hyper-V mounts, transfer SQL Server installation medias to C:\temp\ as ISO file and mount them as Z: or anything else as chosen cluster disks letter to avoid errors

SQL Server installation needs be started from cmd by using switches listed in KB: https://support.microsoft.com/en-us/kb/953748 if Windows Cluster has errors in it

• Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster on first node
• Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode on second node

When the installer is started, it will take VERY LONG time (HOURS) to process any operation unless admin shares are enabled

To enable admin shares do following

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Policies\System
Change the value of LocalAccountTokenFilterPolicy to 1

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Lanmanserver\Parameters
Change the value of AutoShareWks to 1 
Change the value of AutoShareServer to 1

Patching SQL Servers with SCCM (Microsoft System Center Configuration Manager)

I've been looking from Google opinions about whether to do or not do SQL Server patching with SCCM. I've only found one topic from SQL Server Central (http://www.sqlservercentral.com/Forums/Topic1589012-1550-1.aspx -- might require userid to see) with one answer 'I do not recommend'.

This was not the answer which I was looking for so I went ahead together with our company's SCCM guys and made some test patching of SQL Server's (2008/2008 R2 and 2012 -versions). Those patch runs executed without any hassle and issues.

I'll agree that this process is missing the part where the application connection is tested afterwards and no backup is taken before patching. These are though things that can be tackled by regular backup schedule. Application connection testing would require end-to-end monitoring, but usually rebooting the application server(s) will re-establish the db connection and would end to errors if the connection is not working.

So if you're like me and trying to find courage from Google to start patching SQL Server by using SCCM I can assure you that it's not a big of deal and the trade off is quite huge. No more time on moving the Service Pack's into servers and executing them manually. We got several (tens -- not hundreds) of internal SQL Server's back into shape in a day.

How to delete spfile entries in Oracle 11gr2

Do not use '' or 'NULL' values. Use instead following reset -value

alter system reset parameter scope sid='*';

e.g.

alter system reset db_file_name_convert scope=spfile sid='*';

How to create static listener for Data Guard in Grid Infrastructure Standby environment

My environment has Grid Infrastructure installed and hence two separate homes. Please note that this server works as Data Guard node with the intention to be used as Active Data Guard -mode.

GRID_HOME = /oracle/app/11.2.0/grid
DB_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1/

Primary Database = primsid
Primary SERVICE_NAME = PRIMARYSVC
Data Guard database instance name = DGSID
Data Guard database unique name = primsiddg
Data Guard SERVICE_NAME = DGSVC

What I wanted:

I wanted to create static listener for my Data Guard database, because I wanted to create the Data Guard -database from using the 'Active duplicate' -mode. This requires the standby database can be connected using listener connection even though the database is in NOMOUNT -state.

How to achieve this;

1) Do no create listener.ora in the GRID_HOME. Leave it empty.
2) Create listener.ora in the DB_HOME. These are my settings. Please note ORACLE_HOME and ADR_BASE_LISTENER - values.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DGSVC)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = DGSID)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg-server.domain.com)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /oracle/app/oracle/product/11.2.0/dbhome_1

3) Start the listener GRID_HOME with grid environment variables on

4) Check listener status

oracle@dg-server:/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin$ listener status

LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 14-DEC-2014 17:34:58

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg-server.domain.com)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Solaris: Version 11.2.0.4.0 - Production

Start Date                14-DEC-2014 17:33:42

Uptime                    0 days 0 hr. 1 min. 15 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File         /oracle/app/oracle/product/11.2.0/dbhome_1/diag/tnslsnr/dg-server/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg-server)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

  Instance "+ASM", status READY, has 1 handler(s) for this service...

Service "DGSVC" has 2 instance(s).

  Instance "DGSID", status UNKNOWN, has 1 handler(s) for this service...

  Instance "DGSID", status BLOCKED, has 1 handler(s) for this service...

The command completed successfully

In case you see double services like this "BLOCKED" -- check your local_listener -value at database and clear the value.

SQL> alter system set local_listener = '';

Jarjestelma on muutettu.

SQL> alter system register;


Jarjestelma on muutettu.

5) If you have instance with UNKNOWN -status, you're ok. UNKNOWN is the status which we want.
6) Start database with pfile on nomout -state
SQL> startup nomount pfile = 'initPRIMSID.ora';
ORACLE instance started.

Total System Global Area 6415736832 bytes
Fixed Size                  2239440 bytes
Variable Size            1136661552 bytes
Database Buffers         5272240128 bytes

Redo Buffers                4595712 bytes
7) Restore controlfiles
RMAN> restore controlfile from 'PRIMSID_stby.ctl';

8) Start active duplication
oracle@dg-server:/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin$ rman TARGET sys/pw@PRIMARYSVC AUXILIARY sys/pw@DGSVC

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Dec 14 17:46:34 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRIMSID (DBID=1185966409)
connected to auxiliary database: DGSID (not mounted)

How to move disks inside ASM diskgroups

Simple commands (without any details go as follow). Modify the datafile names to suit your own needs. These files do not use OMF and hence full filename needs to said during RMAN commands.

Locate file which needs to be moved;
SQL> SELECT file_name FROM dba_data_files;


Take the file offline;
SQL> ALTER DATABASE DATAFILE '+DATA/database/datafile/db_name_data01.dbf' OFFLINE;


Log on to RMAN
OS> rman target / 


Copy the datafile to new location
RMAN> COPY DATAFILE '+DATA/database/datafile/db_name_data01.dbf' TO '+DATA_DISK_GROUP_1/database/datafile/db_name_data01.dbf';


Log on to SQL to rename the file again
SQL> ALTER DATABASE RENAME FILE '+DATA/database/datafile/db_name_data01.dbf' TO '+DATA_DISK_GROUP_1/database/datafile/db_name_data01.dbf';


Log on to RMAN
OS> rman target /

Switch datafile
RMAN> SWITCH DATAFILE '+DATA_DISK_GROUP_1/database/datafile/db_name_data01.dbf' TO COPY;

Log on to SQL  to recover and put the file online again.

SQL> RECOVER DATAFILE '+DATA_DISK_GROUP_1/database/datafile/db_name_data01.dbf';
SQL> ALTER DATABASE DATAFILE '+DATA_DISK_GROUP_1/database/datafile/db_name_data01.dbf' ONLINE;

Done.

How to modify /etc/resolv.conf on Solaris 11

Instead using nano/vi, you'll need to use configuration tool to modify /etc/resolv.conf -file on Solaris 11

Fireup the configuration tool
root@yourserver:/svccfg -s dns/client

listprop config -- lists current configuration
delprop xxxx -- deletes current configuration
setprop xxxx -- sets current configuration

exit -- when done

After this restart the network/dns services

svcadm refresh dns/client
svcadm restart dns/client
svcadm refresh name-service/switch