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

Solution for ORA-00245: control file backup failed; target is likely on a local file system

In case you encounter following RMAN errors
2014.08.24 20:30:38 ORA-00245: control file backup failed; target is likely on a local file system

It's because you placed your control file backups on local disk and that's as Oracle error defines, not suitable solution RAC environment. You may check you'r current configuration by issuing RMAN> show all; -command.

Solution for this error easy, just re-configure RMAN to place a snapshot controlfile copy to a shared location (e.g. ASM disk group):

RMAN> configure snapshot controlfile name to '+DBPROD_DATA/CONTROLFILE/snapcf_dbname.f';

or

RMAN> configure snapshot controlfile name to '+DBPROD_DATA/CLUSTER/CONTROLFILE/snapcf_dbname.f';

depending on your setup.

Useful AIX commands for Oracle DBA

Change oracle user password
root@server:/admin > echo oracle:password | chpasswd 

Clear the prompt for change
root@server:/admin > pwdadm -c oracle

Change user maxage setting to never expire for oracle
root@server:/admin > chuser maxage=0 oracle

Check current settings for oracle -user
root@server:/admin > grep -p "oracle:" /etc/security/user
oracle:
        admin = false
        loginretries = 0
        histsize = 0
        maxage = 0
        umask = 22

Adding ASM disks through SQL*Plus

List ASM diskgroup and make note of diskgroup name

SQL> !cat list_asm_diskgroups.sql
set lines 200
column GROUP_NUMBER format a30
column NAME format a20
column SECTOR_SIZE format a20
column STATE format a10
column TYPE format a10
SELECT GROUP_NUMBER as NO, NAME,SECTOR_SIZE as SS ,BLOCK_SIZE,ALLOCATION_UNIT_SIZE,STATE,TYPE,TOTAL_MB,FREE_MB FROM V$ASM_DISKGROUP;

SQL> @list_asm_diskgroups.sql

        NO NAME                         SS BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE      TYPE         TOTAL_MB    FREE_MB

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

         1 GRID                        512       4096              1048576 MOUNTED    NORMAL          30720      29794

         2 XXPROD_DATA                 512       4096              4194304 MOUNTED    EXTERN        2048000    1202260

         3 XXPROD_FRA                  512       4096              4194304 MOUNTED    EXTERN         102400      88680

List possible candidate disks

SQL> !cat show_asm_candidates.sql

column name format a20

column header_status format a10

column path format a30

SELECT name, header_status, path FROM V$ASM_DISK

WHERE HEADER_STATUS='CANDIDATE';

SQL> @show_asm_candidates.sql

NAME                 HEADER_STA PATH

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

                     CANDIDATE  /dev/rhdisk56

                     CANDIDATE  /dev/rhdisk55

Add candidate ASM disks by issuing following commands

SQL> ALTER DISKGROUP XXPROD_DATA ADD DISK '/dev/rhdisk55' NAME XXPROD_FRA_0001 SIZE 102400 M REBALANCE POWER 11;

Diskgroup altered.

SQL> ALTER DISKGROUP XXPROD_DATA ADD DISK '/dev/rhdisk56' NAME XXPROD_FRA_0002 SIZE 102400 M REBALANCE POWER 11;

Done.

How to do MySQL Community Edition tape backups

Instead of using mysqldump for taking backups from MySQL Community Edition you might want to try combining your tape backup system with pre - and postscripts which will do the as mysqldump

1. Execute tape backup system presscript which will
• log on as mysqlroot into mysql
• execute 'flush tables with read lock' - command
2. Do a file system backup or LVM snapshot of mysql /data
3. Execute tape backup system postscript which will
•   log on as mysqlroot into mysql
•   execute ‘unlock tables’ -command

Configuring SCAN and local listener for Active/Passive 11gr2 Oracle Grid Infrastructure cluster

1. Create cluster
• https://blogs.oracle.com/xpsoluxdb/entry/clusterware_11gr2_setting_up_an_activepassive_failover_configuration
• http://gjilevski.com/2012/01/09/build-active-passive-ha-configuration-for-single-instance-database-with-oracle-gi-11-2-0-3/
• http://mathijsbruggink.wordpress.com/2013/07/04/thoughts-on-adding-a-single-instance-to-oracle-clusterware-grid-infra/#more-308
2. Open firewall ports to application servers (if needed)
• I opened firewall ports to default TCP-1521 and to non-default port as my SCAN address was running on non-default port. 
3. Create TNS-entries for Database
• set SCAN-address as the hostname, use FQDN -name
4. Register scan address as remote_listener
5. Register local hostname as local_listener
• some servers can't understand 'localhost' so I used the servers real name e.g. dbserver-01.domain.fi
• there's an issue with this. Because active-passive cluster can failover, you must ensure that the the local_listener value is updated during failover in the ActionScript
6. Check output by 'lsnrctl services' from Database home, not GRID home.

You don't need to touch VIP-addresses at any point.