Standalone Standby Database Creation for 2 node RAC in Oracle 11g

Environment.

Primary 2 Node Database RAC
1. Instance rac1 host rac1
2. Instance rac2 host rac2

Standby Database STBY
Instance stby1 host stby


At Standby, I have 3 disks avaiable, so I created the new Disk group FRA as identical to Primary database.

select a.name,a.state,b.name,b.label,b.path
from v$asm_disk b, v$asm_diskgroup a
where a.GROUP_NUMBER=b.GROUP_NUMBER;


NAME       STATE       NAME       LABEL      PATH
---------- ----------- ---------- ---------- ---------------
DATA       MOUNTED     DISK1      DISK1      ORCL:DISK1
DATA       MOUNTED     DISK2      DISK2      ORCL:DISK2
DATA       MOUNTED     DISK3      DISK3      ORCL:DISK3



SQL> select label from v$asm_disk;

LABEL
----------
DISK4
DISK5
DISK6
DISK1
DISK2
DISK3



SELECT group_number, name, compatibility, database_compatibility, ALLOCATION_UNIT_SIZE/1024/1024 "AU_Size in MB" FROM v$asm_diskgroup;

GROUP_NUMBER NAME       COMPATIBILITY        DATABASE_COMPATIBILI AU_Size in MB
------------ ---------- -------------------- -------------------- -------------
           1 DATA       11.2.0.0.0           10.1.0.0.0                       1


create diskgroup FRA EXTERNAL REDUNDANCY
DISK
'ORCL:DISK4',
'ORCL:DISK5',
'ORCL:DISK6'
 ATTRIBUTE
'au_size'='1M',
'compatible.asm'='11.2.0.0.0',
'compatible.rdbms'='10.1.0.0.0'
/

Diskgroup created.


select a.name,a.state,b.name,b.label,b.path
from v$asm_disk b, v$asm_diskgroup a
where a.GROUP_NUMBER=b.GROUP_NUMBER;

NAME       STATE       NAME       LABEL                           PATH
---------- ----------- ---------- ------------------------------- --------------------
DATA       MOUNTED     DISK1      DISK1                           ORCL:DISK1
DATA       MOUNTED     DISK2      DISK2                           ORCL:DISK2
DATA       MOUNTED     DISK3      DISK3                           ORCL:DISK3
FRA        MOUNTED     DISK4      DISK4                           ORCL:DISK4
FRA        MOUNTED     DISK5      DISK5                           ORCL:DISK5
FRA        MOUNTED     DISK6      DISK6                           ORCL:DISK6

6 rows selected.



At Standby Database server create a temporary listener, listening to standby instance stby1.

[root@stby bin]# ./srvctl add listener -l listener_standby -p "TCP:1522" -o /u01/app/oracle/product/11.2.0/dbhome_1/

Make Static entry for Standby database.

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_STANDBY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_STANDBY))))            # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_STANDBY=ON              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

LISTENER_STANDBY =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = stby.xxx.com)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = stby1)
      (GLOBAL_DBNAME = stby)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
    )
  )



[root@stby bin]# ./srvctl start listener -l listener_standby

[oracle@stby dbs]lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-OCT-2014 16:13:09

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                03-OCT-2014 14:03:18
Uptime                    0 days 2 hr. 9 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/stby/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.46)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.3)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.46)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.27.31.3)(PORT=1522)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "stby" has 1 instance(s).
  Instance "stby1", status UNKNOWN, has 1 handler(s) for this service...
Service "stby.xxx.com" has 1 instance(s).
  Instance "stby1", status READY, has 1 handler(s) for this service...
The command completed successfully



TNSNAMES.ORA file entry at both server i.e. Primary and standby.

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

########################################################################################################

                      #       Primary Server Entry      #

########################################################################################################

RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac.xxx.com)
    )
  )

RAC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac.xxx.com)
      (INSTANCE_NAME = rac1)
    )
  )

RAC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac.xxx.com)
      (INSTANCE_NAME = rac2)
    )
  )



########################################################################################################


                        #       Standby Server Entry      #

########################################################################################################

STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stby-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stby.xxx.com)
      (UR = A )
      (INSTANCE_NAME= stby1)
    )
  )





-bash-3.2$ ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       stby
ora.FRA.dg
               ONLINE  ONLINE       stby
ora.LISTENER.lsnr
               ONLINE  ONLINE       stby
ora.LISTENER_STANDBY.lsnr
               ONLINE  ONLINE       stby
ora.asm
               ONLINE  ONLINE       stby                     Started
ora.gsd
               OFFLINE OFFLINE      stby
ora.net1.network
               ONLINE  ONLINE       stby
ora.ons
               ONLINE  ONLINE       stby
ora.registry.acfs
               ONLINE  ONLINE       stby
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       stby
ora.cvu
      1        ONLINE  ONLINE       stby
ora.oc4j
      1        ONLINE  ONLINE       stby
ora.scan1.vip
      1        ONLINE  ONLINE       stby
ora.stby.vip
      1        ONLINE  ONLINE       stby



-- > Copy the password file at standby location and rename with the Standby database name.

--> Modify the parameter file as below.

[oracle@stby dbs]vim initstby1.ora
stby1.__db_cache_size=117440512
stby1.__java_pool_size=4194304
stby1.__large_pool_size=4194304
stby1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stby1.__pga_aggregate_target=209715200
stby1.__sga_target=314572800
stby1.__shared_io_pool_size=0
stby1.__shared_pool_size=180355072
stby1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stby/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='xxx.com'
*.db_name='rac'
*.db_unique_name='stby'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)'
*.fal_client='STBY'
*.fal_server='RAC'
stby1.instance_number=1
*.log_archive_config='DG_CONFIG=(rac,stby)'
*.log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'
*.log_archive_dest_2='SERVICE=rac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE DB_UNIQUE_NAME=rac'
*.log_archive_dest_state_1='ENABLE'
#*.log_archive_dest_state_2='DEFER'
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.resumable_timeout=0
stby1.thread=1
stby1.undo_tablespace='UNDOTBS1'
~


--> Startup standby database stby1 in nomount mode using parameter file.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstby1.ora';
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1346052 bytes
Variable Size             398460412 bytes
Database Buffers          117440512 bytes

Redo Buffers                5861376 bytes

--> Connecting Standby Database from Primary Database. 

[oracle@rac1 ~]rman target / auxiliary sys/Oracle12@stby

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 3 15:29:31 2014

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

connected to target database: RAC (DBID=2444882112)
connected to auxiliary database: RAC (not mounted)

RMAN> run
2>
3> {

4> 5> duplicate target database for standby from active database

spfile
6> 7> 8>
parameter_value_convert 'rac','stby'
9> 10>
11> set cluster_database='FALSE'

12> 13> set db_file_name_convert='+DATA/rac','+DATA/stby','/u01/','+DATA/stby'

14> 15> set log_file_name_convert='+DATA/rac','+DATA/stby'
16>
set control_files='+DATA/'
17> 18>
set db_unique_name='stby'

set fal_client='stby'

19> 20> 21> 22> 23> set fal_server='rac'

24> 25> set standby_file_management='AUTO'
26>
27> set audit_file_dest='/u01/app/oracle'

28> 29> set diagnostic_dest='/u01/app/oracle'
30>
;
31> 32>
}33>

Starting Duplicate Db at 03-OCT-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=140 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwrac1' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1//dbs/orapwstby1'   targetfile
 '/u01/app/oracle/product/11.2.0/db_1/dbs/spfilerac1.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora''";
}
executing Memory Script

Starting backup at 03-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=161 instance=rac1 device type=DISK
Finished backup at 03-OCT-14

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestby1.ora''

contents of Memory Script:
{
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  remote_listener =
 ''stby-scan:1521'' comment=
 '''' scope=spfile";
   sql clone "alter system set  cluster_database =
 FALSE comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''+DATA/rac'', ''+DATA/stby'', ''/u01/'', ''+DATA/stby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''+DATA/rac'', ''+DATA/stby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''+DATA/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''stby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client =
 ''stby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server =
 ''rac'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  audit_file_dest =
 ''/u01/app/oracle'' comment=
 '''' scope=spfile";
   sql clone "alter system set  diagnostic_dest =
 ''/u01/app/oracle'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stby'' comment= '''' scope=spfile

sql statement: alter system set  remote_listener =  ''stby-scan:1521'' comment= '''' scope=spfile

sql statement: alter system set  cluster_database =  FALSE comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''+DATA/rac'', ''+DATA/stby'', ''/u01/'', ''+DATA/stby'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''+DATA/rac'', ''+DATA/stby'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''+DATA/'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''stby'' comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''stby'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''rac'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle'' comment= '''' scope=spfile

sql statement: alter system set  diagnostic_dest =  ''/u01/app/oracle'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     523108352 bytes

Fixed Size                     1346052 bytes
Variable Size                314574332 bytes
Database Buffers             201326592 bytes
Redo Buffers                   5861376 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/stby/controlfile/current.262.859994701'' comment=
 ''Set by RMAN'' scope=spfile";
   backup as copy current controlfile for standby auxiliary format  '+DATA/stby/controlfile/current.263.859994701';
   sql clone "alter system set  control_files =
  ''+DATA/stby/controlfile/current.263.859994701'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/stby/controlfile/current.262.859994701'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 03-OCT-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_rac1.f tag=TAG20141003T153009 RECID=7 STAMP=859995012
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 03-OCT-14

sql statement: alter system set  control_files =   ''+DATA/stby/controlfile/current.263.859994701'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     523108352 bytes

Fixed Size                     1346052 bytes
Variable Size                314574332 bytes
Database Buffers             201326592 bytes
Redo Buffers                   5861376 bytes

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to
 "+data";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+data";
   set newname for datafile  2 to
 "+data";
   set newname for datafile  3 to
 "+data";
   set newname for datafile  4 to
 "+data";
   set newname for datafile  5 to
 "+data";
   set newname for datafile  6 to
 "+data";
   set newname for datafile  7 to
 "+DATA/stbydemo01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "+data"   datafile
 2 auxiliary format
 "+data"   datafile
 3 auxiliary format
 "+data"   datafile
 4 auxiliary format
 "+data"   datafile
 5 auxiliary format
 "+data"   datafile
 6 auxiliary format
 "+data"   datafile
 7 auxiliary format
 "+DATA/stbydemo01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 03-OCT-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/rac/datafile/undotbs1.258.856899977
output file name=+DATA/stby/datafile/undotbs1.264.859994751 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/rac/datafile/sysaux.257.856899977
output file name=+DATA/stby/datafile/sysaux.265.859995107 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/rac/datafile/system.256.856899975
output file name=+DATA/stby/datafile/system.266.859995223 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/rac/datafile/example.264.856900139
output file name=+DATA/stby/datafile/example.267.859995297 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/rac/datafile/undotbs2.265.856900533
output file name=+DATA/stby/datafile/undotbs2.268.859995333 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/rac/datafile/users.259.856899977
output file name=+DATA/stby/datafile/users.269.859995369 tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/demo01.dbf
output file name=+DATA/stbydemo01.dbf tag=TAG20141003T153057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-OCT-14

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=859995378 file name=+DATA/stby/datafile/system.266.859995223
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=859995378 file name=+DATA/stby/datafile/sysaux.265.859995107
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=859995379 file name=+DATA/stby/datafile/undotbs1.264.859994751
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=859995379 file name=+DATA/stby/datafile/users.269.859995369
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=859995379 file name=+DATA/stby/datafile/example.267.859995297
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=859995380 file name=+DATA/stby/datafile/undotbs2.268.859995333
datafile 7 switched to datafile copy
input datafile copy RECID=13 STAMP=859995380 file name=+DATA/stbydemo01.dbf
Finished Duplicate Db at 03-OCT-14

RMAN>





SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
RAC       READ ONLY            PHYSICAL STANDBY

SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE
RFS       IDLE
RFS       IDLE


SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE
RFS       IDLE
RFS       IDLE
MRP0      WAIT_FOR_LOG

9 rows selected.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
RAC       READ ONLY WITH APPLY PHYSICAL STANDBY

'ORA-01031: insufficient privileges' , While Connecting as sysdba.

While connecting to database as sysdba, I was getting yhe following oracle error.
[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 2 09:09:06 2014

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

ERROR:
ORA-01031: insufficient privileges

Debugging.

1. Checked the ORACLE_SID and ORACLE_HOME by issue the 'env', which were Okay.

2. I checked the listener status which was fine.

3. Checked the Listener.ora,Tnsnames.ora and Sqlnet.ora file.

Entries of Sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

#SQLNET.AUTHENTICATION_SERVICES = (NONE)

AUTOMATIC_IPC = OFF

ADR_BASE = /u01/app/oracle

Entries of Listener file.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = db1)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

    )

  )

Entries of Tnsnames.ora file.

DB1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = db1)

    )

  )

The Entries for those above files look okay.

4. Tnsping the Database , which was pinging.

5. Created the Oracle database password file.

orapwd file=orapwdb1 password=xxxx entries=3 force=y

But still  couldn't able to login into the database.

Solution.

1. Go under the folder. $ORACLE_HOME/rdbms/lib.

cd /u01/app/oracle/product/11.2.0/db_1/rdbms/lib

2. Make the backup of file 'config.c' and 'config.o'.

[oracle@test lib]$ ls -l config.*

-rwxrwxr-x 1 oracle oinstall 454 Aug  2 21:11 config.c

-rwxr-xr-x 1 oracle oinstall 611 Aug  2 21:11 config.c.bkp

-rw-r--r-- 1 oracle oinstall 828 Aug  2 21:11 config.o

-rwxr-xr-x 1 oracle oinstall 828 Aug  2 09:53 config.o.bkp

3. [oracle@test lib]$ cat config.c

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */

/*  Refer to the Installation and User's Guide for further information.  */

/* IMPORTANT: this file needs to be in sync with

              rdbms/src/server/osds/config.c, specifically regarding the

              number of elements in the ss_dba_grp array.

 */

#define SS_DBA_GRP "dba"

#define SS_OPER_GRP "dba"

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};   

4. Need to crosscheck if the dba and oper group are correct or not and then accordinly take the action.

[oracle@test lib]$ id

uid=500(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper),504(asmadmin)

So as can be see that the Group user is 'onstall' but the entries stored in the 'config.c' is 'dba'. So we need to change that.

oracle@test lib]$ cat config.c

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */

/*  Refer to the Installation and User's Guide for further information.  */

/* IMPORTANT: this file needs to be in sync with

              rdbms/src/server/osds/config.c, specifically regarding the

              number of elements in the ss_dba_grp array.

 */

#define SS_DBA_GRP "oinstall"

#define SS_OPER_GRP "oinstall"

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};   

5. Now need to recreate the file 'config.o' with the below command.

[oracle@test lib]$ make -f ins_rdbms.mk config.o

/usr/bin/gcc -m32   -O2   -I/u01/app/oracle/product/11.2.0/db_1/rdbms/demo -I/u01/app/oracle/product/11.2.0/db_1/rdbms/public -I/u01/app/oracle/product/11.2.0/db_1/plsql/public -I/u01/app/oracle/product/11.2.0/db_1/network/public -DLINUX -D_GNU_SOURCE -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -DSLTS_ENABLE -DSLMXMX_ENABLE -D_REENTRANT -DNS_THREADS -DLDAP_CM       -c -o config.o config.c

6. Relink the Oracle Binaries.

[oracle@test lib]$ relink all

writing relink log to: /u01/app/oracle/product/11.2.0/db_1/install/relink.log

After doing the above steps , I was then able to log into the database.

[oracle@test lib]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 2 21:18:54 2014

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  464519168 bytes

Fixed Size                  1345660 bytes

Variable Size             146802564 bytes

Database Buffers          310378496 bytes

Redo Buffers                5992448 bytes

Database mounted.

Database opened.

Cheers. HTH. :)

Roll Forward in ASM Standby with incremental backup of the NON ASM Primary Database

In the post I'll show you to deal with the issue when the archive log physical removed from the Primary Database server before to apply it over the Standby Database.

Here:

Database_Type     Database_Unique_Name    File_Management

Primary                  DB1              NON-ASM
Standby                  SDB1             ASM


first I'll create the scenario where :


1. I'll disable the second archive destination state over the Primary Database which is pointing for Standby Database service.
2. Once it is disable , I'll create drop and create a table under user 'SCOTT'.
3. The generated archive logs will move to the another directory.
4. Then will start the secondary archive log destination so it will start pointing to the standby database server.
5. The Standby recovery will identify the missing archive logs and will throw the message into the alert log file for missing archive logs.


After that I'll start the recovery of the missing archive logs on the standby database.

Before starting lets have a look on the Primary and Standby Database status.


At Primary


SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ WRITE PRIMARY

SQL> archive log list
 
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/fast_recovery_area/db1/archivelog/
Oldest online log sequence 108
Next log sequence to archive 110
Current log sequence 110

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE

At Standby Database.

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA/
Oldest online log sequence 108
Next log sequence to archive 0
Current log sequence 110

At Primary Site.

Lets Disable the secondary archive log destination and create some table under 'Scott' schema.
And after that move the generated archives into another directory.
 
SQL> alter system set log_archive_dest_state_2='DEFER' scope=both;
System altered.

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

log_archive_dest_state_2 string DEFER

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEST TABLE

SQL> drop table test purge;

Table dropped.

SQL> create table test
2 as
3 select * from emp;

Table created.

SQL> select count(*) from test;
COUNT(*)
----------
14

SQL> commit;

Commit complete.


[oracle@node1 archivelog]$ ls -l
total 376384
-rw-r----- 1 oracle oinstall 868864 Jun 26 18:14 1_100_850229880.dbf
-rw-r----- 1 oracle oinstall 421376 Jun 26 18:27 1_101_850229880.dbf
-rw-r----- 1 oracle oinstall 42317824 Jun 27 01:30 1_102_850229880.dbf
-rw-r----- 1 oracle oinstall 33522688 Jun 27 11:26 1_103_850229880.dbf
-rw-r----- 1 oracle oinstall 6123520 Jun 27 13:25 1_104_850229880.dbf
-rw-r----- 1 oracle oinstall 86016 Jun 27 13:28 1_105_850229880.dbf
-rw-r----- 1 oracle oinstall 1191424 Jun 27 13:30 1_106_850229880.dbf
-rw-r----- 1 oracle oinstall 102912 Jun 27 13:33 1_107_850229880.dbf
-rw-r----- 1 oracle oinstall 932352 Jun 27 14:00 1_108_850229880.dbf
-rw-r----- 1 oracle oinstall 2560 Jun 27 14:01 1_109_850229880.dbf
-rw-r----- 1 oracle oinstall 515584 Jun 27 14:14 1_110_850229880.dbf
-rw-r----- 1 oracle oinstall 1024 Jun 27 14:14 1_111_850229880.dbf
-rw-r----- 1 oracle oinstall 3584 Jun 27 14:15 1_112_850229880.dbf
....

[oracle@node1 archivelog]$ mkdir -p bkp/
[oracle@node1 archivelog]$ mv 1_1* bkp/

Enable the secondary archive destination state.

SQL> alter system set log_archive_dest_state_2='ENABLE' scope=both;
System altered.

SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE




At Secondary Site.

SQL> select * from v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 110 112

SQL> select VALUE from v$dataguard_stats
2 where name='transport lag';

VALUE
----------------------------------------------------------------
+00 00:31:22
 Excerpts from alert log

*****************************************************************************************

ARC2 started with pid=30, OS id=12037

Media Recovery Waiting for thread 1 sequence 110

Fetching gap sequence in thread 1, gap sequence 110-112

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the heartbeat ARCH

ARC1: Becoming the active heartbeat ARCH

Fri Jun 27 14:26:24 2014

ARC3 started with pid=31, OS id=12039

Fri Jun 27 14:26:25 2014

Using STANDBY_ARCHIVE_DEST parameter default value as +DATA

************************************************************************************

Check the current scn number of the standby database.

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1327971
 SQL> conn scott/tiger

Connected.

SQL> select created from user_objects where object_name ='TEST';

CREATED
---------
26-JUN-14

The Test Table creation date is showing the earlier time which we had deleted on the Production database but not showing the current one.
Take the incremental backup from the database current scn over the Primary Database.

At Primary Site.

RMAN> backup incremental from scn 1327971 database format '/u01/bkp/inc_stby_%U.bkp';
Starting backup at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
backup will be obsolete on date 04-JUL-14
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 27-JUN-14
channel ORA_DISK_1: finished piece 1 at 27-JUN-14
piece handle=/u01/bkp/inc_stby_33pbt6b9_1_1.bkp tag=TAG20140627T143832 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
using channel ORA_DISK_1
backup will be obsolete on date 04-JUL-14
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 27-JUN-14
channel ORA_DISK_1: finished piece 1 at 27-JUN-14
piece handle=/u01/bkp/inc_stby_34pbt6cc_1_1.bkp tag=TAG20140627T143832 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 27-JUN-14
RMAN> exit

[oracle@node1 bkp]$ ls -l
total 13444
-rw-r----- 1 oracle oinstall 3588096 Jun 27 14:39 inc_stby_33pbt6b9_1_1.bkp
-rw-r----- 1 oracle oinstall 10158080 Jun 27 14:39 inc_stby_34pbt6cc_1_1.bkp

Copy the backup to the Standby Host.

[oracle@node1 bkp]$ scp orapwdb1 node2:/u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@node1 bkp]$ scp -r * node2:/u01/bkp/
oracle@node2's password:
inc_stby_33pbt6b9_1_1.bkp 100% 3504KB 3.4MB/s 00:01
inc_stby_34pbt6cc_1_1.bkp 100% 9920KB 9.7MB/s 00:00

At Standby Site.
 
Shut down the Standby Database, catalog the incremental backup and perform the recovery.
 
 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 1033670656 bytes
Fixed Size 1349476 bytes
Variable Size 595593372 bytes
Database Buffers 432013312 bytes
Redo Buffers 4714496 bytes
Database mounted.

[oracle@node2 ~]$ rman target /
connected to target database: DB1 (DBID=1444093493, not open)
RMAN> catalog start with '/u01/bkp';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/bkp
List of Files Unknown to the Database
=====================================
File Name: /u01/bkp/inc_stby_2rpbqsba_1_1.bkp
File Name: /u01/bkp/inc_stby_2qpbqsa7_1_1.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/bkp/inc_stby_2rpbqsba_1_1.bkp
File Name: /u01/bkp/inc_stby_2qpbqsa7_1_1.bkp
 
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name SDB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 710 SYSTEM *** +DATA/sdb1/system01.dbf
2 570 SYSAUX *** +DATA/sdb1/sysaux01.dbf
3 30 UNDOTBS1 *** +DATA/sdb1/undotbs01.dbf
4 5 USERS *** +DATA/sdb1/users01.dbf
5 500 TEST *** +DATA/sdb1/test01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/sdb1/temp01.dbf

RMAN> recover database noredo;
Starting recover at 27-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/sdb1/system01.dbf
destination for restore of datafile 00002: +DATA/sdb1/sysaux01.dbf
destination for restore of datafile 00003: +DATA/sdb1/undotbs01.dbf
destination for restore of datafile 00004: +DATA/sdb1/users01.dbf
destination for restore of datafile 00005: +DATA/sdb1/test01.dbf
channel ORA_DISK_1: reading from backup piece /u01/bkp/inc_stby_33pbt6b9_1_1.bkp
channel ORA_DISK_1: piece handle=/u01/bkp/inc_stby_33pbt6b9_1_1.bkp tag=TAG20140627T143832
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 27-JUN-14
RMAN>
　
Create the Standby Controlfile from the Production server and ship that on the standby site.

At Primary Site.

RMAN> backup current controlfile for standby format '/u01/bkp/stby_control.ctl';
Starting backup at 26-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 26-JUN-14
channel ORA_DISK_1: finished piece 1 at 26-JUN-14
piece handle=/u01/bkp/stby_control.ctl tag=TAG20140626T175537 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-JUN-14

[oracle@node1 bkp]$ scp stby_control.ctl node2:/u01/bkp/
oracle@node2's password:
stby_control.ctl 100% 9856KB 9.6MB/s 00:00
[oracle@node1 bkp]$
　
At Standby Site.

Bounce the Standby Database in nomount mode , restore the controlfile from the controlfile backup.

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
 
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1033670656 bytes
Fixed Size 1349476 bytes
Variable Size 595593372 bytes
Database Buffers 432013312 bytes
Redo Buffers 4714496 bytes
 
 [oracle@node2 bkp]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 27 14:51:29 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB1 (not mounted)

RMAN> restore standby controlfile from '/u01/bkp/stby_control.ctl';
Starting restore at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/sdb1/controlfile/current.275.851347679
Finished restore at 27-JUN-14

RMAN>

　

SQL> alter database mount;
Database altered.
connected to target database: DB1 (DBID=1444093493, not open)

RMAN> report schema;
Starting implicit crosscheck backup at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
Crosschecked 19 objects
Finished implicit crosscheck backup at 27-JUN-14
Starting implicit crosscheck copy at 27-JUN-14
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 27-JUN-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================

File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_107.284.851350363

File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_108.263.851350365

File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_109.259.851350365

File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_113.262.851350651

File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_114.277.851351187

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name SDB1

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------
1 710 SYSTEM *** +DATA/sdb1/system01.dbf
2 570 SYSAUX *** +DATA/sdb1/sysaux01.dbf
3 30 UNDOTBS1 *** +DATA/sdb1/undotbs01.dbf
4 5 USERS *** +DATA/sdb1/users01.dbf
5 500 TEST *** +DATA/sdb1/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/sdb1/temp01.dbf


Start the Recovery and check the status of the table created under SCOTT schema and the Database Status.

At Standby Site.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
　
SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY WITH APPLY PHYSICAL STANDBY

　
SQL> conn scott/tiger

Connected.

SQL> select created from user_objects where object_name ='TEST';
CREATED
--------
27-JUN-14

SQL> conn /as sysdba
Connected.

SQL> select current_scn from v$database;
CURRENT_SCN
----------
1331998

SQL> select VALUE from v$dataguard_stats
2 where name='transport lag';

VALUE
----------------------------------------------------------------
+00 00:00:00
　References.

http://saruamit4.wordpress.com/2014/05/03/recovering-standby-database-using-scn-based-backup/#more-441