ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump job.

Alert log File information:

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.

Errors in file /u01/app/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_j000_22426.trc:

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump job.

Trace File Information:

*** 2015-03-26 08:30:18.342

DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"OPS$ORACLE"','"ET$016FA3770001"','""', ...)

DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump job.

Cause:

The primary cause of this issue is that an external table existed at some point in time but does not now. However, the database still believes the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there.

There are many reasons that an external table may not exist including:

1. Temporary Data pump external tables have not been cleaned up properly. The dictionary information should have been dropped when the DataPump jobs completed.

2. An External table has been removed without clearing up the corresponding data dictionary information. For example: Oracle Demo Schema Tables such as the external table “SALES_TRANSACTIONS_EXT” may have been removed but the dictionary has not been updated to reflect this. The "SALES_TRANSACTIONS_EXT" table is an external table in the "SH" schema which is one of Demo Schema provided by Oracle.

Note:Our issue is due to point one. External tables are not cleaned properly.

Solutions:  To clean up the Orphaned datapump jobs.

Check and cleanup orphaned datapump jobs:

SELECT owner_name, job_name, operation, job_mode,state, attached_sessions FROM dba_datapump_jobs

WHERE job_name NOT LIKE 'BIN$%'

ORDER BY 1,2;

To identify the external tables.

SQL> conn / as sysdba

Connected.

SQL>

set linesize 200 trimspool on

set pagesize 2000

col owner form a30

col created form a25

col last_ddl_time form a25

col object_name form a30

col object_type form a25

select OWNER,OBJECT_NAME,OBJECT_TYPE, status,

to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created

,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time

from dba_objects

where object_name like 'ET$%'

/

OWNER                          OBJECT_NAME                    OBJECT_TYPE               STATUS  CREATED                   LAST_DDL_TIME

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

OPS$ORACLE                     ET$000E4FF90001                TABLE                     VALID   16-oct-2012 13:10:15      16-oct-2012 13:10:15

OPS$ORACLE                     ET$007360190001                TABLE                     VALID   18-sep-2012 23:17:32      18-sep-2012 23:17:32

OPS$ORACLE                     ET$00F39F430001                TABLE                     VALID   16-oct-2012 13:33:10      16-oct-2012 13:33:10

OPS$ORACLE                     ET$016FA3770001                TABLE                     VALID   16-oct-2012 13:57:36      16-oct-2012 13:57:36

8 rows selected.

SQL> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE

from dba_external_tables order by 1,2

 /

OWNER                          TABLE_NAME                     DEFAULT_DIRECTORY_NAME         ACCESS_

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

OPS$ORACLE                     ET$000E4FF90001                PRODUCT_REFRESH_DIR            CLOB

OPS$ORACLE                     ET$007360190001                PRODUCT_REFRESH_DIR            CLOB

OPS$ORACLE                     ET$00F39F430001                PRODUCT_REFRESH_DIR            CLOB

OPS$ORACLE                     ET$016FA3770001                PRODUCT_REFRESH_DIR            CLOB

To Drop the external temporary datapump tables.

SQL> drop table OPS$ORACLE.ET$000E4FF90001;

SQL> drop table OPS$ORACLE.ET$007360190001;

SQL> drop table OPS$ORACLE.ET$00F39F430001;

SQL> drop table OPS$ORACLE.ET$016FA3770001;

5.To ensure there is no datapump temporary tables.

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE, status,

to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created

,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time

from dba_objects where object_name like 'ET$%'

/

no rows selected

SQL> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE from dba_external_tables order by 1,2

 /

Reference doc   1274653.1  

De-install RAC cluster

How to de-install RAC cluster?

 cd /u01/11.2.0.4/grid/deinstall

[oracle@node-1 deinstall]$ ./deinstall

Checking for required files and bootstrapping ...

Please wait ...

Location of logs /u01/app/oraInventory/logs/

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################### CHECK OPERATION START #########################

## [START] Install check configuration ##

Checking for existence of the Oracle home location /u01/11.2.0.4/grid

Oracle Home type selected for deinstall is: Oracle Grid Infrastructure for a Cluster

Oracle Base selected for deinstall is: /u01/app/oracle

Checking for existence of central inventory location /u01/app/oraInventory

Checking for existence of the Oracle Grid Infrastructure home /u01/11.2.0.4/grid

The following nodes are part of this cluster: node-1, node-2

Checking for sufficient temp space availability on node(s): 'node-1, node-2'

## [END] Install check configuration ##

Traces log file: /u01/app/oraInventory/logs//crsdc.log

Enter an address or the name of the virtual IP used on node "node-1"[node-1-vip]

 >

node-1-vip

The following information can be collected by running "/sbin/ifconfig -a" on node "node-1"

Enter the IP netmask of Virtual IP "xx.xxx.xx.xxx" on node "node-1"[255.xxx.xxx.x]

 >255.xxx.xxx.x

Enter the network interface name on which the virtual IP address "xx.xxx.xx.xxx" is active

 >xx.xxx.xx.xxx

Enter an address or the name of the virtual IP used on node "node-2"[node-2-vip]

 > node-2-vip

The following information can be collected by running "/sbin/ifconfig -a" on node "node-2"

Enter the IP netmask of Virtual IP "xx.xxx.xx.xxx" on node "node-2"[255.xxx.xxx.x]

 >255.xxx.xxx.x

Enter the network interface name on which the virtual IP address "xx.xxx.xx.xxx" is active

 >

Enter an address or the name of the virtual IP[]

 >

Network Configuration check config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check2014-02-04_03-38-34-AM.log

Specify all RAC listeners (do not include SCAN listener) that are to be de-configured [LISTENER,LISTENER_SCAN1]:LISTENER,LISTENER_SCAN1

Network Configuration check config END

Asm Check Configuration START

ASM de-configuration trace file location: /u01/app/oraInventory/logs/asmcadc_check2014-02-04_03-38-42-AM.log

ASM configuration was not detected in this Oracle home. Was ASM configured in this Oracle home (y|n) [n]: y

Specify the ASM Diagnostic Destination [ ]:

Specify the diskstring []:

Specify the diskgroups that are managed by this ASM instance []:

######################### CHECK OPERATION END #########################

####################### CHECK OPERATION SUMMARY #######################

Oracle Grid Infrastructure Home is: /u01/11.2.0.4/grid

The cluster node(s) on which the Oracle home deinstallation will be performed are:node-1,node-2

Oracle Home selected for deinstall is: /u01/11.2.0.4/grid

Inventory Location where the Oracle home registered is: /u01/app/oraInventory

Following RAC listener(s) will be de-configured: LISTENER,LISTENER_SCAN1

ASM instance will be de-configured from this Oracle home

Do you want to continue (y - yes, n - no)? [n]: y

A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2014-02-04_03-36-32-AM.out'

Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2014-02-04_03-36-32-AM.err'

######################## CLEAN OPERATION START ########################

ASM de-configuration trace file location: /u01/app/oraInventory/logs/asmcadc_clean2014-02-04_03-39-03-AM.log

ASM Clean Configuration START

ASM Clean Configuration END

Network Configuration clean config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_clean2014-02-04_03-39-04-AM.log

De-configuring RAC listener(s): LISTENER,LISTENER_SCAN1

De-configuring listener: LISTENER

    Stopping listener: LISTENER

    Warning: Failed to stop listener. Listener may not be running.

Listener de-configured successfully.

De-configuring listener: LISTENER_SCAN1

    Stopping listener: LISTENER_SCAN1

    Warning: Failed to stop listener. Listener may not be running.

Listener de-configured successfully.

De-configuring Naming Methods configuration file on all nodes...

Naming Methods configuration file de-configured successfully.

De-configuring Local Net Service Names configuration file on all nodes...

Local Net Service Names configuration file de-configured successfully.

De-configuring Directory Usage configuration file on all nodes...

Directory Usage configuration file de-configured successfully.

De-configuring backup files on all nodes...

Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

---------------------------------------->

The deconfig command below can be executed in parallel on all the remote nodes. Execute the command on  the local node after the execution completes on all the remote nodes.

Run the following command as the root user or the administrator on node "node-2".

/tmp/deinstall2014-02-04_03-34-41AM/perl/bin/perl -I/tmp/deinstall2014-02-04_03-34-41AM/perl/lib -I/tmp/deinstall2014-02-04_03-34-41AM/crs/install /tmp/deinstall2014-02-04_03-34-41AM/crs/install/rootcrs.pl -force  -deconfig -paramfile "/tmp/deinstall2014-02-04_03-34-41AM/response/deinstall_Ora11g_gridinfrahome2.rsp"

[root@node-02 ~]# /tmp/deinstall2015-03-04_03-34-41AM/perl/bin/perl -I/tmp/deinstall2015-03-04_03-34-41AM/perl/lib -I/tmp/deinstall2015-03-04_03-34-41AM/crs/install /tmp/deinstall2015-03-04_03-34-41AM/crs/install/rootcrs.pl -force  -deconfig -paramfile "/tmp/deinstall2015-03-04_03-34-41AM/response/deinstall_Ora11g_gridinfrahome2.rsp" -lastnode Using configuration parameter file: /tmp/deinstall2015-03-04_03-34-41AM/response/deinstall_Ora11g_gridinfrahome2.rsp CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node-02' CRS-2676: Start of 'ora.cssdmonitor' on 'node-02' succeeded CRS-2672: Attempting to start 'ora.cssd' on 'node-02' CRS-2672: Attempting to start 'ora.diskmon' on 'node-02' CRS-2676: Start of 'ora.diskmon' on 'node-02' succeeded CRS-2676: Start of 'ora.cssd' on 'node-02' succeeded PRCR-1068 : Failed to query resources Cannot communicate with crsd PRCR-1068 : Failed to query resources Cannot communicate with crsd PRCR-1068 : Failed to query resources Cannot communicate with crsd PRCR-1068 : Failed to query resources Cannot communicate with crsd PRCR-1119 : Failed to look up CRS resources of ora.cluster_vip_net1.type type PRCR-1068 : Failed to query resources Cannot communicate with crsd PRCR-1070 : Failed to check if resource ora.gsd is registered Cannot communicate with crsd PRCR-1070 : Failed to check if resource ora.ons is registered Cannot communicate with crsd  CRS-4535: Cannot communicate with Cluster Ready Services CRS-4000: Command Stop failed, or completed with errors. CRS-4535: Cannot communicate with Cluster Ready Services CRS-4000: Command Delete failed, or completed with errors. CRS-4689: Oracle Clusterware is already stopped CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node-02' CRS-2676: Start of 'ora.cssdmonitor' on 'node-02' succeeded CRS-2672: Attempting to start 'ora.cssd' on 'node-02' CRS-2672: Attempting to start 'ora.diskmon' on 'node-02' CRS-2676: Start of 'ora.diskmon' on 'node-02' succeeded CRS-2676: Start of 'ora.cssd' on 'node-02' succeeded CRS-4611: Successful deletion of voting disk +OCR_VOTE. ASM de-configuration trace file location: /u01/app/oraInventory/logs/asmcadc_clean2015-03-04_03-51-20-AM.log ASM Clean Configuration START ASM Clean Configuration END ASM with SID +ASM1 deleted successfully. Check /u01/app/oraInventory/logs/asmcadc_clean2015-03-04_03-51-20-AM.log for details. CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node-02' CRS-2673: Attempting to stop 'ora.ctssd' on 'node-02' CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'node-02' CRS-2673: Attempting to stop 'ora.mdnsd' on 'node-02' CRS-2677: Stop of 'ora.mdnsd' on 'node-02' succeeded CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'node-02' succeeded CRS-2677: Stop of 'ora.ctssd' on 'node-02' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'node-02' CRS-2677: Stop of 'ora.cssd' on 'node-02' succeeded CRS-2673: Attempting to stop 'ora.crf' on 'node-02' CRS-2677: Stop of 'ora.crf' on 'node-02' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'node-02' CRS-2677: Stop of 'ora.gipcd' on 'node-02' succeeded CRS-2673: Attempting to stop 'ora.gpnpd' on 'node-02' CRS-2677: Stop of 'ora.gpnpd' on 'node-02' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node-02' has completed CRS-4133: Oracle High Availability Services has been stopped. Removing Trace File Analyzer Successfully deconfigured Oracle clusterware stack on this node

Run the following command as the root user or the administrator on node "node-1".

/tmp/deinstall2014-02-04_03-34-41AM/perl/bin/perl -I/tmp/deinstall2014-02-04_03-34-41AM/perl/lib -I/tmp/deinstall2014-02-04_03-34-41AM/crs/install /tmp/deinstall2014-02-04_03-34-41AM/crs/install/rootcrs.pl -force  -deconfig -paramfile "/tmp/deinstall2014-02-04_03-34-41AM/response/deinstall_Ora11g_gridinfrahome2.rsp" -lastnode

[root@node-01 ~]# /tmp/deinstall2015-03-04_03-34-41AM/perl/bin/perl -I/tmp/deinstall2015-03-04_03-34-41AM/perl/lib -I/tmp/deinstall2015-03-04_03-34-41AM/crs/install /tmp/deinstall2015-03-04_03-34-41AM/crs/install/rootcrs.pl -force  -deconfig -paramfile "/tmp/deinstall2015-03-04_03-34-41AM/response/deinstall_Ora11g_gridinfrahome2.rsp" -lastnode Using configuration parameter file: /tmp/deinstall2015-03-04_03-34-41AM/response/deinstall_Ora11g_gridinfrahome2.rsp CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node-01' CRS-2676: Start of 'ora.cssdmonitor' on 'node-01' succeeded CRS-2672: Attempting to start 'ora.cssd' on 'node-01' CRS-2672: Attempting to start 'ora.diskmon' on 'node-01' CRS-2676: Start of 'ora.diskmon' on 'node-01' succeeded CRS-2676: Start of 'ora.cssd' on 'node-01' succeeded PRCR-1068 : Failed to query resources Cannot communicate with crsd PRCR-1068 : Failed to query resources Cannot communicate with crsd PRCR-1068 : Failed to query resources Cannot communicate with crsd PRCR-1068 : Failed to query resources Cannot communicate with crsd PRCR-1119 : Failed to look up CRS resources of ora.cluster_vip_net1.type type PRCR-1068 : Failed to query resources Cannot communicate with crsd PRCR-1070 : Failed to check if resource ora.gsd is registered Cannot communicate with crsd PRCR-1070 : Failed to check if resource ora.ons is registered Cannot communicate with crsd CRS-4535: Cannot communicate with Cluster Ready Services CRS-4000: Command Stop failed, or completed with errors. CRS-4535: Cannot communicate with Cluster Ready Services CRS-4000: Command Delete failed, or completed with errors. CRS-4689: Oracle Clusterware is already stopped CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node-01' CRS-2676: Start of 'ora.cssdmonitor' on 'node-01' succeeded CRS-2672: Attempting to start 'ora.cssd' on 'node-01' CRS-2672: Attempting to start 'ora.diskmon' on 'node-01' CRS-2676: Start of 'ora.diskmon' on 'node-01' succeeded CRS-2676: Start of 'ora.cssd' on 'node-01' succeeded CRS-4611: Successful deletion of voting disk +OCR_VOTE. ASM de-configuration trace file location: /u01/app/oraInventory/logs/asmcadc_clean2015-03-04_03-51-20-AM.log ASM Clean Configuration START ASM Clean Configuration END ASM with SID +ASM1 deleted successfully. Check /u01/app/oraInventory/logs/asmcadc_clean2015-03-04_03-51-20-AM.log for details. CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node-01' CRS-2673: Attempting to stop 'ora.ctssd' on 'node-01' CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'node-01' CRS-2673: Attempting to stop 'ora.mdnsd' on 'node-01' CRS-2677: Stop of 'ora.mdnsd' on 'node-01' succeeded CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'node-01' succeeded CRS-2677: Stop of 'ora.ctssd' on 'node-01' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'node-01' CRS-2677: Stop of 'ora.cssd' on 'node-01' succeeded CRS-2673: Attempting to stop 'ora.crf' on 'node-01' CRS-2677: Stop of 'ora.crf' on 'node-01' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'node-01' CRS-2677: Stop of 'ora.gipcd' on 'node-01' succeeded CRS-2673: Attempting to stop 'ora.gpnpd' on 'node-01' CRS-2677: Stop of 'ora.gpnpd' on 'node-01' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node-01' has completed CRS-4133: Oracle High Availability Services has been stopped. Removing Trace File Analyzer Successfully deconfigured Oracle clusterware stack on this node

Setting the force flag to false

Setting the force flag to cleanup the Oracle Base

Oracle Universal Installer clean START

Detach Oracle home '/u01/11.2.0.4/grid' from the central inventory on the local node : Done

Failed to delete the directory '/u01/11.2.0.4/grid'. The directory is in use.

Delete directory '/u01/11.2.0.4/grid' on the local node : Failed <<<<

The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/11.2.0/grid'.

Detach Oracle home '/u01/11.2.0.4/grid' from the central inventory on the remote nodes 'node-2' : Done

Delete directory '/u01/11.2.0.4/grid' on the remote nodes 'node-2' : Failed <<<<

Could not remove listed directories based on '/tmp/OraInstall2014-02-04_03-54-46-AM/installRemoveDirFile.lst' from nodes 'node-2'. [PRKC-1083 : Failed to remove listed directory in "/tmp/OraInstall2014-02-04_03-54-46-AM/installRemoveDirFile.lst" to any of the given nodes "node-2 ".

Error on node node-2:/bin/rm: cannot remove directory `/u01/11.2.0.4/grid/': Permission denied]

The Oracle Base directory '/u01/app/oracle' will not be removed on node 'node-2'. The directory is in use by Oracle Home '/u01/app/oracle/product/11.2.0/db_1'.

Oracle Universal Installer cleanup completed with errors.

Oracle Universal Installer clean END

## [START] Oracle install clean ##

Clean install operation removing temporary directory '/tmp/deinstall2014-02-04_03-34-41AM' on node 'node-1'

Clean install operation removing temporary directory '/tmp/deinstall2014-02-04_03-34-41AM' on node 'node-2'

## [END] Oracle install clean ##

######################### CLEAN OPERATION END #########################

####################### CLEAN OPERATION SUMMARY #######################

ASM instance was de-configured successfully from the Oracle home

Following RAC listener(s) were de-configured successfully: LISTENER,LISTENER_SCAN1

Oracle Clusterware is stopped and successfully de-configured on node "node-2"

Oracle Clusterware is stopped and successfully de-configured on node "node-1"

Oracle Clusterware is stopped and de-configured successfully.

Successfully detached Oracle home '/u01/11.2.0.4/grid' from the central inventory on the local node.

Failed to delete directory '/u01/11.2.0.4/grid' on the local node.

Successfully detached Oracle home '/u01/11.2.0.4/grid' from the central inventory on the remote nodes 'node-2'.

Failed to delete directory '/u01/11.2.0.4/grid' on the remote nodes 'node-2'.

Oracle Universal Installer cleanup completed with errors.

For complete clean up of Oracle Clusterware software from the system, deinstall the following old clusterware home(s). Refer to Clusterware Install guide of respective old release for details.

    /u01/11.2.0/grid on nodes : node-1

Oracle deinstall tool successfully cleaned up temporary directories.

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

############# ORACLE DEINSTALL & DECONFIG TOOL END #############

Stetps to create physical standby database using RMAN backup

How to  create physical standby database using RMAN backup?

Primary Database (PRIME):

Checks:

•          Archive log mode must be enabled on primary database.

sql>Archive log list

 (or)

sql>SELECT LOG_MODE FROM V$DATABASE;

If the DB is in "NOARCHIVELOG" mode, please perform the following to make it ARCHIVELOG mode:

http://orcl11gdba.blogspot.in/2014/03/enabling-archive-log-mode.html

•          Primary database must be forced logging.

SELECT FORCE_LOGGING FROM V$DATABASE;

ALTER DATABASE FORCE LOGGING;

•          Make sure the DB Name and DB Unique Name are set.

SQL> SHOW PARAMETER DB_NAME

NAME                                 TYPE        VALUE

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

db_name                              string      PRIME

SQL>SHOW PARAMETER DB_UNIQUE_NAME

NAME                                 TYPE        VALUE

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

db_unique_name                       string      PRIME

•          set the parameter Use LOG_ARCHIVE_CONFIG using DB_UNIQUE_NAME

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIME,STAND)' scope=both;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STAND LGWR ASYNC max_failure=10 max_connections=5 reopen=180   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAND' scope=both;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

•          Set FAL_SERVER and FAL_CLIENT parameters

ALTER SYSTEM SET FAL_SERVER=STAND scope=both;

ALTER SYSTEM SET FAL_CLIENT=PRIME scope=both;

•          Set the below parameters

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/oradata/log','/u04/oradata/logs' scope=spfile;

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/oradata/dbf','/u01/oradata/data' scope=spfile;

•          Add stand by redo log files

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/oradata/log/styredo_05a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/oradata/log/styredo_06a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/oradata/log/styredo_07a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u01/oradata/log/styredo_08a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/u01/oradata/log/styredo_09a.dbf') SIZE 100M BLOCKSIZE 1024 REUSE;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

RMAN>backup current controlfile for standby;

Take full backup of database  and send the dump to standby site:

http://orcl11gdba.blogspot.in/2014/05/taking-database-backup-using-rman-method.html

Make sure the authentication is exclusive and password file is created.

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys/admin123 entries=10 force=y

•          Prepare a TNSnames.ora file

PRIME=

      (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)

    (HOST = bgl-db01.ttd.com)

        (PORT = 1521)))

    (CONNECT_DATA =

    (SID = PRIME)))

STAND=

      (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)

      (HOST = bgl-dr01.dot.com)

        (PORT = 1521)))

      (CONNECT_DATA =

      (SID = PRIME)))

•          Start the listener listener_PRIME

lsnrctl start listener_PRIME

•          Create pfile:

Create pfile from spfile;

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

Standby database (STAND):

scp the pfile and the password files to $ORACLE_HOME/dbs destination.

Copy the tnsnames.ora file from primary to standby in location to $ORACLE_HOME/network/admin

Modify the parameters in pfile such as diagnostic_dump,adump,controlfile path, log_archive_dest_1, log_archive_dest_2, fal_client, fal_server, db_unique_name.

e.g.

log_archive_config='DG_CONFIG=(STAND,PRIME)' scope=both

log_archive_dest_1=/u04/oradata/logs

db_unique_name=STAND

instance_name=PRIME

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_2='SERVICE=PRIME  LGWR ASYNC max_failure=10 max_connections=5 reopen=180   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=PRIME

*.fal_client='STAND'

*.fal_server='PRIME'

sql>startup nomount pfile=initSTAND.ora

sql > show parameter db_unique

RMAN> set dbid=110374; ? DBID of PRIME database

RMAN> restore standby controlfile from ‘/u01/rman_bkp/’;

RMAN> sql 'alter database mount standby database';

RMAN> crosscheck backup;

RMAN> delete noprompt expired backup;

RMAN> list backup summary;

Follow the below step to register the backup dump to our instance

rman> catalog start with '/u01/rman_bkp/';

RMAN> restore database;

RMAN> list backup of archivelog all;

RMAN> recover database until sequence 1135 à  sequence number till what we have backed up archivelog files

SQL> alter database recover managed standby database disconnect from session;

Database altered.

CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

or 

How to switch from NTP to CTSS?

It is recommended to use CTSS for a cluster to synchronize the time between cluster nodes instead of NTP.

We will find the CTSS status in observer mode when ntp.conf file is in /etc/.

1. Check if ctssd is active or not

cd /u01/app/11.2.0.4/grid/bin

[oracle@test_node01 ~]$ ./crsctl check ctss

CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

[oracle@test_node02 ~]$ ./crsctl check ctss

CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

We can see that CTSSD is in observer mode

2. Stop  NTP and CRS

now we are going to disable the ntp service and enable CTSS service

#crsctl stop crs

#/sbin/service ntpd stop

#mv /etc/ntp.conf /etc/ntp.conf.original

3. Start the cluster on all nodes

#crsctl start crs

4. Check that ctssd automatically starts in active mode as shown below

[root@test_node02 etc]# crsctl check ctss

CRS-4701: The Cluster Time Synchronization Service is in Active mode.

CRS-4702: Offset (in msec): 0

[root@test_node01 etc]# crsctl check ctss

CRS-4701: The Cluster Time Synchronization Service is in Active mode.

CRS-4702: Offset (in msec): 0

./cluvfy comp clocksync

Verifying Clock Synchronization across the cluster nodes

Checking if Clusterware is installed on all nodes...

Check of Clusterware install passed

Checking if CTSS Resource is running on all nodes...

CTSS resource check passed

Querying CTSS for time offset on all nodes...

Query of CTSS for time offset passed

Check CTSS state started...

CTSS is in Active state. Proceeding with check of clock time offsets on all nodes...

Check of clock time offsets passed

Oracle Cluster Time Synchronization Services check passed

Verification of Clock Synchronization across the cluster nodes was successful.

[cssd(10157)]CRS-1714: Unable to discover any voting files, retrying discovery in 15 seconds

[cssd(10157)]CRS-1714: Unable to discover any voting files, retrying discovery in 15 seconds

Cause:

The above error occurred in my case after reboot of RAC nodes and then we tried to bring up the cluster . We found this in alert log of node.

Issue is during the start-up of cluster, the previously created partitions on disks are not recognized by nodes.

[root@test_node02 ]# /usr/sbin/oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Scanning system for ASM disks...

[root@test_node02 ]# /usr/sbin/oracleasm listdisks

[root@test_node02 ]#

Solution:

Try to recognize the disks manually as below

/etc/init.d/oracleasm stop

/etc/init.d/oracleasm start

/usr/sbin/oracleasm scandisks /dev/sdd*

[root@test_node02 ]# /usr/sbin/oracleasm listdisks

ASM_FRA03

ASM_FRA04

ASM_DATA01

ASM_DATA02

OCR01

this is one of the solution to above error.there may be other reasons also that could cause this.for my case issue is resolved after scanning the disks manually.

Cheers...!!!!

OracleHomeInventory was not able to create a lock file, probably due to a failed OPatch Session. The loaded inventory might not show correctly what you have in the Oracle Home.

OracleHomeInventory was not able to create a lock file, probably due to a failed OPatch Session. The loaded inventory might not show correctly what you have in the Oracle Home.

Error:
 OUI-67076:OracleHomeInventory was not able to create a lock file, probably due to a failed OPatch Session.
 The loaded inventory might not show correctly what you have in the Oracle Home

Cause
A previous "opatch apply" session failed and so a lock still exists on the local inventory.

Solution
1. Take a backup of $ORACLE_HOME/.patch_storage directory
$ cp -R $ORACLE_HOME/.patch_storage $ORACLE_HOME/.patch_storage_bkp

2. Remove $ORACLE_HOME/.patch_storage/patch_locked
$ rm $ORACLE_HOME/.patch_storage/patch_locked

3. Create empty file $ORACLE_HOME/.patch_storage/patch_free
$ touch $ORACLE_HOME/.patch_storage/patch_free

4. Verify that "opatch lsinventory" no longer reports the error
$ opatch lsinventory -detail