Oracle 11.2 DBconsole: ERROR TargetManager: TIMEOUT when compute dynamic properties for target

Errors in emagent.trc

2014-09-27 11:15:08,109 Thread-11772 WARN  command: Job Subsystem Timeout set at 600 seconds
2014-09-27 11:15:08,125 Thread-11772 WARN  upload: Upload manager has no Failure script: disabled
2014-09-27 11:15:08,140 Thread-11772 WARN  metadata: Metric VirtualHosts does not have any data columns
2014-09-27 11:15:08,140 Thread-11772 WARN  metadata: Metric collectSnapshot does not have any data columns
2014-09-27 11:15:08,187 Thread-11772 WARN  metadata: Metric hung_system_traces does not have any data columns
2014-09-27 11:15:08,249 Thread-6148 WARN  TargetManager: Query returned 0 rows (only one expected) for the dynamic property from_cluster
2014-09-27 11:17:08,263 Thread-10144 ERROR TargetManager: TIMEOUT when compute dynamic properties for target db11g
2014-09-27 11:17:08,263 Thread-10144 ERROR TargetManager: nmeetm.c : Target db11g has a failed critical dynamic property
2014-09-27 11:17:08,263 Thread-10144 ERROR TargetManager: Target Manager: Target {db11g, oracle_database} is broken: dynamic properties can not be calculated in time.

Solution:

Update emd.properties file:

# changed from default 120
dynamicPropsComputeTimeout_oracle_database=240

TNS-12637: Packet received failed (high logon time)

DB version Oracle Database 11.2.0.4

Error in alert.log

Fatal NI connect error 12637, connecting to:
 (LOCAL=NO)

  VERSION INFORMATION:
    TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
    Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.4.0 - Production
    Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.4.0 - Production
  Time: 27-SEP-2014 10:23:08
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12637
    
TNS-12637: Packet received failed
    ns secondary err code: 12532
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
opiodr aborting process unknown ospid (10820) as a result of ORA-609
Sat Sep 27 10:28:09 2014

Error in $ORACLE_HOME\host12345_nsm\sysman\log\emagent.trc

2014-09-27 09:25:28,546 Thread-7180 ERROR fetchlets.oslinetok: Process stdout = starting...0 
starting 1...
starting 2...
got dbversion..started...
 dbversion is 11dbversion is not 9
2014-09-27 09:25:28,546 Thread-7180 ERROR fetchlets.oslinetok: Process stderr = em_error=Could not connect to dbsnmp/(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host12345)(Port=1521))(CONNECT_DATA=(SID=nsm)))
            : ORA-12637: Packet received failed (DBD ERROR: OCIServerAttach)

Solution:

Try to update sqlnet.ora file

DEFAULT_SDU_SIZE=2048

Resize Oracle 11g redo log files

Check logfile groups and status:

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TI NEXT_CHANGE# NEXT_TIM
---------------- ------------- -------- ------------ --------
         1          1       4441   52428800        512          1 NO
INACTIVE              36466120 06.09.14     36478293 06.09.14

         2          1       4442   52428800        512          1 NO
CURRENT               36478293 06.09.14   2,8147E+14

         3          1       4440   52428800        512          1 NO
INACTIVE              36431037 06.09.14     36466120 06.09.14


SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------

IS_
---
         3         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO03.LOG
NO

         2         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO02.LOG
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------

IS_
---

         1         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO01.LOG
NO


SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE
 
Add an additional logfile group (this is optional, but the database should have at least 2 groups during resize activity):

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 'E:\APP\ORACLE\ORADATA\NSM\REDO04.LOG' S IZE 200M;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1         50 INACTIVE
         2         50 CURRENT
         3         50 INACTIVE
         4        200 UNUSED

Recreate logfile groups:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         2         50 CURRENT
         3         50 INACTIVE
         4        200 UNUSED
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 'E:\APP\ORACLE\ORADATA\NSM\REDO01.LOG' SIZE 200M REUSE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 UNUSED
         2         50 CURRENT
         3         50 INACTIVE
         4        200 UNUSED

Make a logswitch and a checkpoint to advance with the CURRENT and ACTIVE logfile:

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 CURRENT
         2         50 ACTIVE
         3         50 INACTIVE
         4        200 UNUSED

SQL> ALTER SYSTEM CHECKPOINT;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 CURRENT
         2         50 INACTIVE
         3         50 INACTIVE
         4        200 UNUSED

Recreate other logfile groups reusing exiting redo log files:

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 CURRENT
         3         50 INACTIVE
         4        200 UNUSED

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 'E:\APP\ORACLE\ORADATA\NSM\REDO02.LOG' SIZE 200M REUSE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 CURRENT
         2        200 UNUSED
         3         50 INACTIVE
         4        200 UNUSED

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 'E:\APP\ORACLE\ORADATA\NSM\REDO03.LOG' SIZE 200M REUSE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 CURRENT
         2        200 UNUSED
         3        200 UNUSED
         4        200 UNUSED

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 ACTIVE
         2        200 CURRENT
         3        200 UNUSED
         4        200 UNUSED

Remove additional logfile group:

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 ACTIVE
         2        200 CURRENT
         3        200 UNUSED

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 ACTIVE
         2        200 ACTIVE
         3        200 CURRENT

SQL> ALTER SYSTEM CHECKPOINT;
SQL> SELECT GROUP#, BYTES/1024/1024 MB, STATUS FROM V$LOG;

    GROUP#         MB STATUS
---------- ---------- ----------------
         1        200 INACTIVE
         2        200 INACTIVE
         3        200 CURRENT

SQL> SELECT * FROM V$LOGFILE;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------

IS_
---
         3         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO03.LOG
NO

         2         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO02.LOG
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------

IS_
---

         1         ONLINE
E:\APP\ORACLE\ORADATA\NSM\REDO01.LOG
NO


SQL>EXIT

DELETE REDOLOG4.LOG FILE FROM OS

RAC ASM 11.2 Recreate diskgroup

ATTENTION: Before dropping a disk group you must move all files from this disk group (database files, asm files, cluster files).


1. Check if all objects were moved from diskgroup

[grid@rac1 ~]$ sqlplus / as sysasm
SQL> SELECT D.NAME,C.INSTANCE_NAME,C.DB_NAME,C.STATUS
FROM GV$ASM_DISKGROUP D , GV$ASM_CLIENT C
WHERE D.GROUP_NUMBER=C.GROUP_NUMBER
AND D.NAME = 'VOTE_TMP';  2    3    4  

no rows selected

SQL> SELECT D.NAME,C.NAME,C.voting_file
FROM V$ASM_DISKGROUP D , V$ASM_DISK C
WHERE D.GROUP_NUMBER=C.GROUP_NUMBER
AND D.NAME = 'VOTE_TMP';  2    3    4  

NAME                   NAME                  V
------------------------------ ------------------------------ -
VOTE_TMP                   DISK6                  Y
VOTE_TMP                   DISK7                  Y
VOTE_TMP                   DISK8                  Y

2.    Dismount diskgroup from all asm instances except one

SQL> alter diskgroup vote_tmp dismount;

Diskgroup altered.

3.    Connect on instance with asm diskgroup mounted and drop diskgroup

SQL> drop diskgroup vote_tmp including contents;

Diskgroup dropped.

4.    Check disks are with header_status = former for disks of dropped diskgroup

SQL> select disk_number, header_status, mode_status, state, path from v$asm_disk;

DISK_NUMBER HEADER_STATU MODE_ST STATE
----------- ------------ ------- --------
PATH
--------------------------------------------------------------------------------
      0 FORMER     ONLINE  NORMAL
ORCL:DISK6

      1 FORMER     ONLINE  NORMAL
ORCL:DISK7

      2 FORMER     ONLINE  NORMAL
ORCL:DISK8


DISK_NUMBER HEADER_STATU MODE_ST STATE
----------- ------------ ------- --------
PATH
--------------------------------------------------------------------------------
      0 MEMBER     ONLINE  NORMAL
ORCL:DISK1

      0 MEMBER     ONLINE  NORMAL
ORCL:DISK2

      1 MEMBER     ONLINE  NORMAL
ORCL:DISK3


DISK_NUMBER HEADER_STATU MODE_ST STATE
----------- ------------ ------- --------
PATH
--------------------------------------------------------------------------------
      2 MEMBER     ONLINE  NORMAL
ORCL:DISK4

      0 MEMBER     ONLINE  NORMAL
ORCL:DISK5


8 rows selected.

5.    Create new diskgroup using former disks (better with asmca or OEM)

CREATE DISKGROUP CDATA NORMAL REDUNDANCY FAILGROUP DISK8 DISK 'ORCL:DISK8' NAME DISK8 SIZE 1023 M FAILGROUP DISK7 DISK 'ORCL:DISK7' NAME DISK7 SIZE 1023 M FAILGROUP DISK6 DISK 'ORCL:DISK6' NAME DISK6 SIZE 1023 M ATTRIBUTE 'compatible.asm' = '11.2'

RAC ASM 11.2 Change ASM SPFILE location

Locate asm spfile location:

[grid@rac1 ~]$ asmcmd spget
+VOTE/rac/asmparameterfile/registry.253.853798947
[grid@rac1 ~]$ /u01/app/11.2.0/grid/bin/gpnptool get |  xmllint --format - | egrep 'SPFile'
  <orcl:ASM-Profile id="asm" DiscoveryString="" SPFile="+VOTE/rac/asmparameterfile/registry.253.853798947"/>

Copy the spfile to the new location .
NOTE: This step is working only if you specify the exact path and name for spfile (see bellow the errors). This command doesn't use OMF.

[grid@rac1 ~]$ asmcmd
ASMCMD> cd vote/rac/asmparameterfile
ASMCMD> ls
REGISTRY.253.853798947
ASMCMD> spcopy +vote/rac/asmparameterfile/REGISTRY.253.853798947 +vote_tmp
ORA-15056: additional error message
ORA-17502: ksfdcre:4 Failed to create file +vote_tmp/REGISTRY.253.853798947
ORA-15177: cannot operate on system aliases
ORA-06512: at line 7 (DBD ERROR: OCIStmtExecute)
 
NOTE: Spcopy will not use OMF. Have to specify the full path and name for new spfile


1. Move ASM spfile using ASMCMD

Check spfile location:

[grid@rac1 ~]$ asmcmd spget
+VOTE_TMP/rac/asmparameterfile/registry.253.854289061

Copy spfile with -u option - to update GPnP profile in RAC environment:

[grid@rac1 ~]$ asmcmd spcopy -u +VOTE_TMP/rac/asmparameterfile/registry.253.854289061 +VOTE/rac/asmparameterfile/spfileASM.ora
[grid@rac1 ~]$ asmcmd spget
+VOTE/rac/asmparameterfile/spfileASM.ora

Restart CRS stack to verify that ASM starts with new spfile:
 
[grid@rac1 ~]$ su -
Password: 
-bash-4.1# cd /u01/app/11.2.0/grid/bin/
-bash-4.1# ./crsctl stop crs
-bash-4.1# ./crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

Check new spfile:

[grid@rac2 ~]$ asmcmd spget
+VOTE/rac/asmparameterfile/spfileASM.ora

Restart CRS stack on the other node and check spfile location.


[grid@rac1 ~]$ asmcmd spget
+VOTE/rac/asmparameterfile/spfileASM.ora

Remove old spfile

[grid@rac1 ~]$ asmcmd rm +VOTE_TMP/rac/asmparameterfile/registry.253.854289061



2. Copy ASM SPFILE using SQL Plus.

View spfile location:

[grid@rac1 ~]$ sqlplus / as sysasm

SQL> show parameter spfile;   

spfile                     string     +VOTE/rac/asmparameterfile/reg istry.253.853798947
SQL> create pfile from spfile;

File created.

SQL> create spfile='+VOTE_TMP' from pfile;

File created.

SQL> show parameter spfile;

spfile                     string     +VOTE/rac/asmparameterfile/registry.253.853798947
SQL>exit

Check location of the new spfile:

[grid@rac1 dbs]$ asmcmd
ASMCMD> cd +vote_tmp/rac/asmparameterfile
ASMCMD> ls
REGISTRY.253.854289061

Restart clusterware stack on first node:

[grid@rac1 ~]$ su -
Password: 
-bash-4.1# cd /u01/app/11.2.0/grid/bin/
-bash-4.1# ./crsctl stop crs
-bash-4.1# ./crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

Check new spfile location.

[grid@rac1 dbs]$ asmcmd spget
+VOTE_TMP/rac/asmparameterfile/registry.253.854289061

Restart clusterware stack on second node:

[grid@rac1 ~]$ su -
Password: 
-bash-4.1# cd /u01/app/11.2.0/grid/bin/
-bash-4.1# ./crsctl stop crs
-bash-4.1# ./crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

Check new spfile on second node:

[grid@rac2 ~]$ asmcmd spget
+VOTE_TMP/rac/asmparameterfile/registry.253.854289061

Remove old spfile:

[grid@rac2 ~]$ asmcmd rm +vote/rac/asmparameterfile/REGISTRY.253.853798947

RAC 11.2 Migrating voting disks on different DG

This implementation was made using 2 KVM.



1. Check votedisk location

[grid@rac1 ~]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   26f8032a6f6a4f28bfe642ea5b99da9c (ORCL:DISK1) [DATA]
Located 1 voting disk(s).

2. Add disks to the cluster and format partitions
 
[root@ images]# cd /var/lib/libvirt/images
[root@ images]# qemu-img create -f raw Virtual_Client_for_Linux_RHEL_6_RAC_DISK6.img 1G
Formatting 'Virtual_Client_for_Linux_RHEL_6_RAC_DISK6.img', fmt=raw size=1073741824 
[root@ images]# qemu-img create -f raw Virtual_Client_for_Linux_RHEL_6_RAC_DISK7.img 1G
Formatting 'Virtual_Client_for_Linux_RHEL_6_RAC_DISK7.img', fmt=raw size=1073741824 
[root@ images]# qemu-img create -f raw Virtual_Client_for_Linux_RHEL_6_RAC_DISK8.img 1G
Formatting 'Virtual_Client_for_Linux_RHEL_6_RAC_DISK8.img', fmt=raw size=1073741824 
[root@ images]# chown qemu:qemu Virtual_Client_for_Linux_RHEL_6_RAC_DISK6.img
[root@ images]# chown qemu:qemu Virtual_Client_for_Linux_RHEL_6_RAC_DISK7.img
[root@ images]# chown qemu:qemu Virtual_Client_for_Linux_RHEL_6_RAC_DISK8.img
[root@ images]# virsh attach-disk Virtual_Client_for_Linux_RHEL_6_RAC1-KVM --persistent /var/lib/libvirt/images/Virtual_Client_for_Linux_RHEL_6_RAC_DISK6.img sdf
Disk attached successfully

[root@ images]# virsh attach-disk Virtual_Client_for_Linux_RHEL_6_RAC2-KVM --persistent /var/lib/libvirt/images/Virtual_Client_for_Linux_RHEL_6_RAC_DISK6.img sdf
Disk attached successfully

[root@ images]# virsh attach-disk Virtual_Client_for_Linux_RHEL_6_RAC2-KVM --persistent /var/lib/libvirt/images/Virtual_Client_for_Linux_RHEL_6_RAC_DISK7.img sdg
Disk attached successfully

[root@ images]# virsh attach-disk Virtual_Client_for_Linux_RHEL_6_RAC1-KVM --persistent /var/lib/libvirt/images/Virtual_Client_for_Linux_RHEL_6_RAC_DISK7.img sdg
Disk attached successfully

[root@ images]# virsh attach-disk Virtual_Client_for_Linux_RHEL_6_RAC1-KVM --persistent /var/lib/libvirt/images/Virtual_Client_for_Linux_RHEL_6_RAC_DISK8.img sdh
Disk attached successfully

[root@ images]# virsh attach-disk Virtual_Client_for_Linux_RHEL_6_RAC2-KVM --persistent /var/lib/libvirt/images/Virtual_Client_for_Linux_RHEL_6_RAC_DISK8.img sdh
Disk attached successfully



-bash-4.1# fdisk –l

Disk /dev/sdf: 1073 MB, 1073741824 bytes
34 heads, 61 sectors/track, 1011 cylinders
Units = cylinders of 2074 * 512 = 1061888 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000


Disk /dev/sdg: 1073 MB, 1073741824 bytes
34 heads, 61 sectors/track, 1011 cylinders
Units = cylinders of 2074 * 512 = 1061888 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000


Disk /dev/sdh: 1073 MB, 1073741824 bytes
34 heads, 61 sectors/track, 1011 cylinders
Units = cylinders of 2074 * 512 = 1061888 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

-bash-4.1# fdisk /dev/sdf
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x1214349e.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1011, default 1): 
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-1011, default 1011): 
Using default value 1011

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
-bash-4.1# fdisk /dev/sdg
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x268cbd64.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1011, default 1): 
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-1011, default 1011): 
Using default value 1011

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
-bash-4.1# fdisk /dev/sdh
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x621c212d.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1011, default 1): 
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-1011, default 1011): 
Using default value 1011

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.



3. Create ORACLEASM disks

-bash-4.1# oracleasm createdisk DISK6 /dev/sdf
Device "/dev/sdf" is not a partition
-bash-4.1# oracleasm createdisk DISK6 /dev/sdf1
Writing disk header: done
Instantiating disk: done
-bash-4.1# oracleasm createdisk DISK7 /dev/sdg1
Writing disk header: done
Instantiating disk: done
-bash-4.1# oracleasm createdisk DISK8 /dev/sdh1
Writing disk header: done
Instantiating disk: done
-bash-4.1# oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
DISK6
DISK7
DISK8
-bash-4.1#


-bash-4.1# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "DISK6"
Instantiating disk "DISK7"
Instantiating disk "DISK8"
-bash-4.1# oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
DISK6
DISK7
DISK8

4. Create ASM diskgroup


SQL> create diskgroup VOTE_TMP normal redundancy
failgroup DISK6 disk
'ORCL:DISK6' name DISK6
failgroup DISK7 disk
'ORCL:DISK7' name DISK7
failgroup DISK8 disk
'ORCL:DISK8' name DISK8
attribute
'au_size'='1M',
'compatible.asm'='11.2',
'compatible.rdbms'='10.1';    

Diskgroup created.

SQL> select group_number, name from v$asm_diskgroup;

GROUP_NUMBER NAME
------------ ------------------------------
       1 DATA
       2 FRA
       3 VOTE
       4 VOTE_TMP

SQL>

SQL> exit



5. Move votedisk to new diskgroup, that will have 3 copy of voting, because thiis disk group was created with normal redundancy on 3 disks.

[grid@rac1 ~]$ crsctl replace votedisk +VOTE_TMP
Successful addition of voting disk 01c0cd36cdff4faabfc4d59d7fda6599.
Successful addition of voting disk 819d4681343a4f56bf44f76f812000ba.
Successful addition of voting disk ab1b6ee43be14fe2bf91798868124614.
Successful deletion of voting disk 26f8032a6f6a4f28bfe642ea5b99da9c.
Successfully replaced voting disk group with +VOTE_TMP.
CRS-4266: Voting file(s) successfully replaced
[grid@rac1 ~]$
[grid@rac1 ~]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   01c0cd36cdff4faabfc4d59d7fda6599 (ORCL:DISK6) [VOTE_TMP]
 2. ONLINE   819d4681343a4f56bf44f76f812000ba (ORCL:DISK7) [VOTE_TMP]
 3. ONLINE   ab1b6ee43be14fe2bf91798868124614 (ORCL:DISK8) [VOTE_TMP]
Located 3 voting disk(s).

emdb.nohup: XML-20108: (Fatal Error) Start of root element expected ement expected

oracle@dbt1.ro:/home/oracle>emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://dbt1.ro:5501/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ............................................................................................. failed.

oracle@dbt1.sniep.ro:/oracle/app/db/product/11.2.0/dbt1_repoimt/sysman/log>tail -f emdb.nohup
14/07/28 17:51:28 Fatal error: server exiting
----- Mon Jul 28 17:51:30 2014::Checking status of DBConsole : 42074202 -----
----- Mon Jul 28 17:51:30 2014::DBConsole exited at Mon Jul 28 17:51:30 2014 with return value 1. -----
----- Mon Jul 28 17:51:30 2014::Restarting DBConsole. -----
----- Mon Jul 28 17:51:30 2014::Console Launched with PID 42074206 at time Mon Jul 28 17:51:30 2014 -----
----- Mon Jul 28 17:51:30 2014::osname is aix -----
----- Mon Jul 28 17:51:31 2014::heap size used is 384M -----
----- Mon Jul 28 17:51:31 2014::perm size used is -1 -----
----- Mon Jul 28 17:51:31 2014::joptions used are -Xmx384M -----
WARNING: Ignoring zero-length code source: /oracle/app/db/product/11.2.0/jdk/jre/lib/ext/ldapsec.jar(from system property java.ext.dirs)
WARNING: Unable to access loaded classes.
14/07/28 17:51:33 Error initializing server: /oracle/app/db/product/11.2.0/oc4j/j2ee/OC4J_DBConsole_dbt1_repoimt/config/server.xml, Fatal error at line 1 offset1 in file:/oracle/app/db/product/11.2.0/oc4j/j2ee/OC4J_DBConsole_dbt1_repoimt/config/server.xml: .<Line 1, Column 1>: XML-20108: (Fatal Error) Start of root element expected.

14/07/28 17:51:33 Fatal error: server exiting

This can happen after one of the following:

- DB was upgraded and a patchset was applied.
- The server experienced a disk full issue

Option 1

Restore the file from a binary backup or another similar installation (same OS and DB version) or from a new similar fresh installation.

This option worked for me:

oracle@dbt1.ro:/home/oracle>cp /oracle/app/db/product/11.2.0/oc4j/j2ee/OC4J_DBConsole_dbt1_repoimt/config/server.xml /oracle/app/db/product/11.2.0/oc4j/j2ee/OC4J_DBConsole_dbt1_repbit/config/server.xml

oracle@dbt1.ro:/home/oracle>more /oracle/app/db/product/11.2.0/oc4j/j2ee/OC4J_DBConsole_dbt1_repbit/config/server.xml 

oracle@dbt1.ro:/home/oracle>emctl start dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0

Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.

https://dbt1.ro:5502/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ............................ started.

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

Logs are generated in directory /oracle/app/db/product/11.2.0/dbt1_repbit/sysman/log

There is also the second option with reconfiguration of EM dbconsole

Rebuild the configuration files for the DB Console:

- Stop DB Console processes: emctl stop dbconsole
- deconfig the DB Console: emca -deconfig dbcontrol db
- reconfig the DB Console: emca -config dbcontrol db

RAC 11.2 enable archivelog

[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> show parameter recovery;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string
db_recovery_file_dest_size         big integer 0
recovery_parallelism             integer     0

SQL> alter system set db_recovery_file_dest_size=20G scope=both sid='*';
SQL> alter system set db_recovery_file_dest='+FRA' scope=both sid='*';
SQL> archive log list;
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Current log sequence           5
SQL> alter system set cluster_database=false scope=spfile sid='racdb1';
SQL> exit
[oracle@rac1 ~]$ srvctl stop database -d racdb
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> startup mount
SQL> alter database archivelog;
SQL> alter system set cluster_database=true scope=spfile sid='racdb1';
SQL> shutdown immediate
SQL> exit
[oracle@rac1 ~]$ srvctl start database -d racdb
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   5
Current log sequence           5

RAC 11.2 Multiplex Oracle Database control files on ASM

Multiplex oracle rac control files on ASM

[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> show parameter control_file

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time         integer     7
control_files                 string     +DATA/racdb/controlfile/curren
                         t.260.853897145

SQL> alter system set control_files='+DATA/racdb/controlfile/current.260.853897145','+FRA' scope=spfile;

System altered.

SQL> exit

[oracle@rac1 ~]$ srvctl stop database -d racdb
[oracle@rac1 ~]$ srvctl start database -d racdb -o nomount

[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> show parameter control_files;

control_files                 string     +DATA/racdb/controlfile/curren
                         t.260.853897145, +FRA
SQL> exit

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jul 26 21:21:19 2014

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

connected to target database: RACDB (not mounted)

RMAN> restore controlfile from '+DATA/racdb/controlfile/current.260.853897145';

Starting restore at 26-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 instance=racdb1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/racdb/controlfile/current.260.853897145
output file name=+FRA/racdb/controlfile/current.256.853968129
Finished restore at 26-JUL-14

RMAN> exit

[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> alter system set control_files='+DATA/racdb/controlfile/current.260.853897145','+FRA/racdb/controlfile/current.256.853968129' scope=spfile;

System altered.

SQL> exit

[oracle@rac1 ~]$ srvctl stop database -d racdb
[oracle@rac1 ~]$ srvctl start database -d racdb

[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> show parameter control_files;

control_files                 string     +DATA/racdb/controlfile/curren
                         t.260.853897145, +FRA/racdb/co
                         ntrolfile/current.256.85396812
                         9
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/racdb/controlfile/current.260.853897145
+FRA/racdb/controlfile/current.256.853968129

SQL> exit
[oracle@rac1 ~]$

Increase SHM memory to acomodate MEMORY_TARGET

When setting memory_target for oracle you can get errors

-bash-4.1# umount tmpfs
umount: /dev/shm: device is busy.
        (In some cases useful info about processes that use
         the device is found by lsof(8) or fuser(1))

-bash-4.1# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/vda1              28G   19G  7.5G  72% /
tmpfs                 939M  200M  740M  22% /dev/shm

-bash-4.1# umount tmpfs
umount: /dev/shm: device is busy.
        (In some cases useful info about processes that use
         the device is found by lsof(8) or fuser(1))

-bash-4.1# umount -l tmpfs
-bash-4.1# mount -t tmpfs shmfs -o size=2048m /dev/shm
-bash-4.1# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/vda1              28G   19G  7.5G  72% /
shmfs                 2.0G     0  2.0G   0% /dev/shm
-bash-4.1# vi /etc/fstab
tmpfs                   /dev/shm                tmpfs   size=2048m        0 0

Configure user equivalence for user oracle RAC

[grid@rac1 ~]$ su - oracle
Password:
[oracle@rac1 ~]$ mkdir .ssh
[oracle@rac1 ~]$ ssh-keygen -t rsa
[oracle@rac1 ~]$ cd .ssh
[oracle@rac1 .ssh]$ ls
id_rsa  id_rsa.pub
[oracle@rac1 .ssh]$ cat id_rsa.pub >> authorized_keys
[oracle@rac1 .ssh]$ scp authorized_keys oracle@rac2:/home/oracle/.ssh
oracle@rac2's password:
authorized_keys                               100%  405     0.4KB/s   00:00   



On second node run the same commands to generate the keys, update authorized_keys file and send it to the other server. Then test the connection between nodes.

[oracle@rac1 .ssh]$ ssh rac1 date
[oracle@rac1 .ssh]$ ssh rac2 date

CRS-2800: Cannot start resource 'ora.asm' as it is already in the INTERMEDIATE state on server

In my test environment with 2 VM I had a situation when the Oracle cluster was unable to start:

-bash-4.1# ./crsctl stop cluster
CRS-2796: The command may not proceed when Cluster Ready Services is not running
CRS-4000: Command Stop failed, or completed with errors.
-bash-4.1# ./crsctl start cluster
CRS-2800: Cannot start resource 'ora.asm' as it is already in the INTERMEDIATE state on server 'rac1'
CRS-4000: Command Start failed, or completed with errors.
-bash-4.1# ./crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.

Looking in ASM at the state of diskgroup (only the one with OCR and voting disks), it was dismounted and the cluster needed it in order to start.

[grid@rac1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 25 22:42:22 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select NAME , STATE FROM V$ASM_DISKGROUP;

NAME                   STATE
------------------------------ -----------
VOTE                   DISMOUNTED

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
[grid@rac1 ~]$ asmcmd mount VOTE
[grid@rac1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 25 22:43:46 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL>                
SQL> select NAME , STATE FROM V$ASM_DISKGROUP;

NAME                   STATE
------------------------------ -----------
VOTE                   MOUNTED

SQL> exit

Then as root, try to start the cluster and check its status.

-bash-4.1# ./crsctl start cluster
CRS-4691: Oracle Clusterware is already running
CRS-4000: Command Start failed, or completed with errors.
-bash-4.1# ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1                                        
               ONLINE  ONLINE       rac2                                        
ora.VOTE.dg
               ONLINE  ONLINE       rac1                                        
               ONLINE  ONLINE       rac2                                        
ora.asm
               ONLINE  ONLINE       rac1                     Started            
               ONLINE  ONLINE       rac2                     Started            
ora.gsd
               OFFLINE OFFLINE      rac1                                        
               OFFLINE OFFLINE      rac2                                        
ora.net1.network
               ONLINE  ONLINE       rac1                                        
               ONLINE  ONLINE       rac2                                        
ora.ons
               ONLINE  ONLINE       rac1                                        
               ONLINE  ONLINE       rac2                                        
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac1                                        
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       rac2                                        
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       rac2                                        
ora.cvu
      1        ONLINE  ONLINE       rac2                                        
ora.oc4j
      1        ONLINE  ONLINE       rac2                                        
ora.rac1.vip
      1        ONLINE  ONLINE       rac1                                        
ora.rac2.vip
      1        ONLINE  ONLINE       rac2                                        
ora.scan1.vip
      1        ONLINE  ONLINE       rac1                                        
ora.scan2.vip
      1        ONLINE  ONLINE       rac2                                        
ora.scan3.vip
      1        ONLINE  ONLINE       rac2                                        

compat-libstdc++ not installed when installing Oracle 11.2.0.3 on RHEL6

When installing 11.2.0.3 on RHEL6, to ease the installation, you can install

-bash-4.1# yum install oracle-rdbms-server-11gR2-preinstall

This RPM still don't install 2 required packages: compat-libstdc++ and sysstat

The interesting part is that the package name of compat-libstdc++ is compat-libstdc++-33.

-bash-4.1# yum install compat-libstdc++
Setting up Install Process
No package compat-libstdc++ available.
Error: Nothing to do

-bash-4.1# yum install compat-libstdc++-33
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package compat-libstdc++-33.x86_64 0:3.2.3-69.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================================================================
 Package                                                         Arch                                               Version                                                    Repository                                               Size
=============================================================================================================================================================================================================================================
Installing:
 compat-libstdc++-33                                             x86_64                                             3.2.3-69.el6                                               ol6_ga_base                                             183 k

Transaction Summary
=============================================================================================================================================================================================================================================
Install       1 Package(s)

Total download size: 183 k
Installed size: 806 k
Is this ok [y/N]: y
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 183 k
compat-libstdc++-33-3.2.3-69.el6.x86_64.rpm                                                                                                                                                                           | 183 kB     00:01    
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : compat-libstdc++-33-3.2.3-69.el6.x86_64                                                                                                                                                                                   1/1
  Verifying  : compat-libstdc++-33-3.2.3-69.el6.x86_64                                                                                                                                                                                   1/1

Installed:
  compat-libstdc++-33.x86_64 0:3.2.3-69.el6                                                                                                                                                                                                 

Complete!

Change Oracle DB Console logout timeout

For DB Control, you should set it in:

$ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/em/em/WEB-INF/web.xml
and set the following parameter to -1 (no limit):   <session-config>       <session-timeout>-1</session-timeout>  </session-config> 

or if you like, you can specify a positive number to indicate the number of minutes before timeout.

ORA-01111: name for data file 6 is unknown - on standby after adding datafile to primary

alert.log file on standby:

Managed Standby Recovery starting Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file E:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_pr00_3232.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00006'
Managed Standby Recovery not using Real Time Apply
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

Check for the files taht need recover on standby:

SQL> select * from v$recover_file where error like '%FILE%';

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
         6 ONLINE  ONLINE
FILE MISSING                                                               0



SQL>

Get the details on missing datafile from the primary:

SQL> select file#, name from v$datafile;

     FILE#
----------
NAME
--------------------------------------------------------------------------------

         1
E:\APP\ORACLE\ORADATA\STANDBY1\SYSTEM01.DBF

         2
E:\APP\ORACLE\ORADATA\STANDBY1\SYSAUX01.DBF

         3
E:\APP\ORACLE\ORADATA\STANDBY1\UNDOTBS01.DBF


     FILE#
----------
NAME
--------------------------------------------------------------------------------

         4
E:\APP\ORACLE\ORADATA\STANDBY1\USERS01.DBF

         5
E:\APP\ORACLE\ORADATA\STANDBY1\EXAMPLE01.DBF

         6
E:\APP\ORACLE\ORADATA\STANDBY1\AUD_AUX01.DBF


6 rows selected.


Check MRP is not running on standby:

SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

no rows selected



SQL> alter database create datafile 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATAB
ASE\UNNAMED00006' as 'E:\APP\ORACLE\ORADATA\ORCL\AUD_AUX01.DBF';
alter database create datafile 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\U
NNAMED00006' as 'E:\APP\ORACLE\ORADATA\ORCL\AUD_AUX01.DBF'
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management
is automatic.





SQL> alter system set standby_file_management=MANUAL scope=both;

System altered.

SQL> alter database create datafile 'E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATAB
ASE\UNNAMED00006' as 'E:\APP\ORACLE\ORADATA\ORCL\AUD_AUX01.DBF';

Database altered.


Check recovery process on standby:


SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

no rows selected

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCO
NNECT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             285213864 bytes
Database Buffers          541065216 bytes
Redo Buffers                6565888 bytes
Database mounted.
SQL>
SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

no rows selected

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME                                                          PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------

   1518564                     4 YES    681574400
13-JAN-14 02.08.19.000000000 PM
                                                                            YES
SWITCHOVER_START_GRP


SQL> drop restore point switchover_start_grp;

Restore point dropped.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCO
NNECT;

Database altered.

SQL> SELECT PROCESS, DELAY_MINS FROM V$MANAGED_STANDBY WHERE PROCESS like 'MRP%'
;

PROCESS   DELAY_MINS
--------- ----------
MRP0               0

SQL>

ORA-38881: Cannot drop tablespace

SQL> drop tablespace aud_aux including contents and datafiles;
drop tablespace aud_aux including contents and datafiles
*
ERROR at line 1:
ORA-38881: Cannot drop tablespace AUD_AUX on primary database due to guaranteed
restore points.


SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,        GUARANTEE_FLASHBACK_D
ATABASE,STORAGE_SIZE        FROM V$RESTORE_POINT;

NAME
--------------------------------------------------------------------------------

       SCN
----------
TIME
---------------------------------------------------------------------------
DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------------- --- ------------
SWITCHOVER_START_GRP
   1518415
13-JAN-14 02.07.10.000000000 PM
                    4 YES    629145600


SQL> drop restore point switchover_start_grp;

Restore point dropped.

SQL> drop tablespace aud_aux including contents and datafiles;

Tablespace dropped.

SQL>

Restart Data Guard physical standby database

On primary:

# Defer the standby archive log destination

SQL> alter system set log_archive_dest_state_2=defer scope=both;

System altered.

SQL> alter system switch logfile;

System altered.

Shutdown and startup mount the standby database:

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             285213864 bytes
Database Buffers          541065216 bytes
Redo Buffers                6565888 bytes
Database mounted.
SQL>

In case Data Guard Broker is configured, no other steps are required after restart of standby database.

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account ORA-00849: SGA_TARGET 838860800 cannot be set to more than MEMORY_MAX_TARGET 0.

I encountered this error when switching from AMM to ASMM.

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 908M
memory_target                        big integer 908M
shared_memory_address                integer     0
SQL> alter system set sga_target=800M scope=spfile;

System altered.

SQL> alter system set sga_max_size=800M scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=200M scope=spfile;

System altered.

SQL> alter system set memory_max_target=0 scope=spfile;

System altered.

SQL> alter system set memory_target=0 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 838860800 cannot be set to more than MEMORY_MAX_TARGET 0.
SQL> alter system reset memory_max_target scope=spfile;
alter system reset memory_max_target scope=spfile
*

Workaround: Create pfile from spfile, edit pfile and remove lines with memory_target and memory_max_target, and then recreate spfile from pfile

SQL> create pfile from spfile;

File created.

SQL> startup pfile=e:\app\oracle\product\11.2.0\dbhome_1\database\INITorcl.ORA
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             230687912 bytes
Database Buffers          595591168 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> create spfile from pfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             230687912 bytes
Database Buffers          595591168 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 800M
sga_target                           big integer 800M
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 200M
SQL>

Correct implementation:

SQL> show parameter memory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 908M
memory_target                        big integer 908M
shared_memory_address                integer     0
SQL>
SQL>
SQL>
SQL> alter system set sga_target=800M scope=spfile;

System altered.

SQL> alter system set sga_max_size=800M scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=200M scope=spfile;

System altered.

SQL> alter system reset memory_target scope=spfile;

System altered.


SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 908M
memory_target                        big integer 908M
shared_memory_address                integer     0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2259800 bytes
Variable Size             171967656 bytes
Database Buffers          654311424 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SQL>