Select Session Parameters using SYS_CONTEXT

Selecting all Session Parameters using Oracle SYS_CONTEXT function
==============================================================


select tbs.*
    from (
      select *
      from (
        select
          sys_context ('userenv','ACTION') ACTION,
          sys_context ('userenv','AUDITED_CURSORID') AUDITED_CURSORID,
          sys_context ('userenv','AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,
          sys_context ('userenv','AUTHENTICATION_DATA') AUTHENTICATION_DATA,
          sys_context ('userenv','AUTHENTICATION_METHOD') AUTHENTICATION_METHOD,
          sys_context ('userenv','BG_JOB_ID') BG_JOB_ID,
          sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
          sys_context ('userenv','CLIENT_INFO') CLIENT_INFO,
          sys_context ('userenv','CURRENT_BIND') CURRENT_BIND,
          sys_context ('userenv','CURRENT_EDITION_ID') CURRENT_EDITION_ID,
          sys_context ('userenv','CURRENT_EDITION_NAME') CURRENT_EDITION_NAME,
          sys_context ('userenv','CURRENT_SCHEMA') CURRENT_SCHEMA,
          sys_context ('userenv','CURRENT_SCHEMAID') CURRENT_SCHEMAID,
          sys_context ('userenv','CURRENT_SQL') CURRENT_SQL,
          sys_context ('userenv','CURRENT_SQLn') CURRENT_SQLn,
          sys_context ('userenv','CURRENT_SQL_LENGTH') CURRENT_SQL_LENGTH,
          sys_context ('userenv','CURRENT_USER') CURRENT_USER,
          sys_context ('userenv','CURRENT_USERID') CURRENT_USERID,
          sys_context ('userenv','DATABASE_ROLE') DATABASE_ROLE,
          sys_context ('userenv','DB_DOMAIN') DB_DOMAIN,
          sys_context ('userenv','DB_NAME') DB_NAME,
          sys_context ('userenv','DB_UNIQUE_NAME') DB_UNIQUE_NAME,
          sys_context ('userenv','DBLINK_INFO') DBLINK_INFO,
          sys_context ('userenv','ENTRYID') ENTRYID,
          sys_context ('userenv','ENTERPRISE_IDENTITY') ENTERPRISE_IDENTITY,
          sys_context ('userenv','FG_JOB_ID') FG_JOB_ID,
          sys_context ('userenv','GLOBAL_CONTEXT_MEMORY') GLOBAL_CONTEXT_MEMORY,
          sys_context ('userenv','GLOBAL_UID') GLOBAL_UID,
          sys_context ('userenv','HOST') HOST,
          sys_context ('userenv','IDENTIFICATION_TYPE') IDENTIFICATION_TYPE,
          sys_context ('userenv','INSTANCE') INSTANCE,
          sys_context ('userenv','INSTANCE_NAME') INSTANCE_NAME,
          sys_context ('userenv','IP_ADDRESS') IP_ADDRESS,
          sys_context ('userenv','ISDBA') ISDBA,
          sys_context ('userenv','LANG') LANG,
          sys_context ('userenv','LANGUAGE') LANGUAGE,
          sys_context ('userenv','MODULE') MODULE,
          sys_context ('userenv','NETWORK_PROTOCOL') NETWORK_PROTOCOL,
          sys_context ('userenv','NLS_CALENDAR') NLS_CALENDAR,
          sys_context ('userenv','NLS_CURRENCY') NLS_CURRENCY,
          sys_context ('userenv','NLS_DATE_FORMAT') NLS_DATE_FORMAT,
          sys_context ('userenv','NLS_DATE_LANGUAGE') NLS_DATE_LANGUAGE,
          sys_context ('userenv','NLS_SORT') NLS_SORT,
          sys_context ('userenv','NLS_TERRITORY') NLS_TERRITORY,
          sys_context ('userenv','OS_USER') OS_USER,
          sys_context ('userenv','POLICY_INVOKER') POLICY_INVOKER,
          sys_context ('userenv','PROXY_ENTERPRISE_IDENTITY') PROXY_ENTERPRISE_IDENTITY,
          sys_context ('userenv','PROXY_USER') PROXY_USER,
          sys_context ('userenv','PROXY_USERID') PROXY_USERID,
          sys_context ('userenv','SERVER_HOST') SERVER_HOST,
          sys_context ('userenv','SERVICE_NAME') SERVICE_NAME,
          sys_context ('userenv','SESSION_EDITION_ID') SESSION_EDITION_ID,
          sys_context ('userenv','SESSION_EDITION_NAME') SESSION_EDITION_NAME,
          sys_context ('userenv','SESSION_USER') SESSION_USER,
          sys_context ('userenv','SESSION_USERID') SESSION_USERID,
          sys_context ('userenv','SESSIONID') SESSIONID,
          sys_context ('userenv','SID') SID,
          sys_context ('userenv','STATEMENTID') STATEMENTID,
          sys_context ('userenv','TERMINAL') TERMINAL
        from dual
        -- where sys_context ('userenv','SESSIONID') NOT in ('SYS', 'XDB')    -- <<<<< filter by user
      )
      unpivot include nulls (
        val for name in (action, audited_cursorid, authenticated_identity, authentication_data, authentication_method, bg_job_id, client_identifier, client_info, current_bind, current_edition_id, current_edition_name, current_schema, current_schemaid, current_sql, current_sqln, current_sql_length, current_user, current_userid, database_role, db_domain, db_name, db_unique_name, dblink_info, entryid, enterprise_identity, fg_job_id, global_context_memory, global_uid, host, identification_type, instance, instance_name, ip_address, isdba, lang, language, module, network_protocol, nls_calendar, nls_currency, nls_date_format, nls_date_language, nls_sort, nls_territory, os_user, policy_invoker, proxy_enterprise_identity, proxy_user, proxy_userid, server_host, service_name, session_edition_id, session_edition_name, session_user, session_userid, sessionid, sid, statementid, terminal)
      )
    ) tbs;



http://psoug.org/reference/sys_context.html

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE FROM ACTIVE DATABASE

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE FROM ACTIVE DATABASE 
=======================================================================


Database Name :- hr
Primary db_unique_name :- hr
standby db_unique_name :- hrsty

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

Primary database information
Host: pri.localdomain
IP Address : 192.168.1.12
Database name  = hr


Standby database information
Host: stby.localdomain
IP Address : 192.168.1.13
Database name = hrsty


1. Make the necessary changes to the primary database.
    a. Enable force logging.
    b. Creating the password file if one does not exist.
    c. Create standby redologs.
    d. Modify the parameter file suitable for Dataguard.

2. Ensure that the sql*net connectivity is working fine.

3. Create the standby database over the network using the active(primary) database files.
    a. Create the password file
    b. Create the initialization parameter file for the standby database (auxiliary database)
    c. Create the necessary mount points or the folders for the database files
    d. Run the standby creation ON STANDBY by connecting to primary as target database.






1. Prepare the production database to be the primary database
 
a. Ensure that the database is in archivelog mode .

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>


b. Enable force logging

SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES


c. Create standby redologs (redologs+1)

SQL> select group#, thread#, bytes/1024/1024
    from v$log;  2

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1              50
         2          1              50
         3          1              50

SQL>

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         3         ONLINE  /u01/app/oracle/oradata/hr/redo03.log                                            NO
         2         ONLINE  /u01/app/oracle/oradata/hr/redo02.log                                            NO
         1         ONLINE  /u01/app/oracle/oradata/hr/redo01.log                                            NO


SQL> alter database add standby logfile '/u01/app/oracle/oradata/hr/stby_redo01.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/hr/stby_redo02.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/hr/stby_redo03.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/hr/stby_redo04.log' size 50M;

Database altered.

SQL>


SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         3         ONLINE  /u01/app/oracle/oradata/hr/redo03.log                                            NO
         2         ONLINE  /u01/app/oracle/oradata/hr/redo02.log                                            NO
         1         ONLINE  /u01/app/oracle/oradata/hr/redo01.log                                            NO
         4         STANDBY /u01/app/oracle/oradata/hr/stby_redo01.log                                       NO
         5         STANDBY /u01/app/oracle/oradata/hr/stby_redo02.log                                       NO
         6         STANDBY /u01/app/oracle/oradata/hr/stby_redo03.log                                       NO
         7         STANDBY /u01/app/oracle/oradata/hr/stby_redo04.log                                       NO



d. Modify the primary initialization parameter for dataguard on primary.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      hr
SQL>
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      hr
SQL>

The DB_NAME parameter will be hr for the both the primary database and the standby database.
The DB_UNIQUE_NAME will be hr for the primary database and hrsty for the standby.
The DB_UNIQUE_NAME will be used in the LOG_ARCHIVE_CONFIG parameter to enable the sending and receiving of redo logs to remote destinations.





SQL> alter system set log_archive_config='DG_CONFIG=(hr,hrsty)';

System altered.

SQL>

SQL> show parameter LOG_ARCHIVE_CONFIG

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(hr,hrsty)
SQL>






Set value for LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2


SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string




SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hr';

System altered.


SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST VALID_FOR=(ALL_LOGFILES,A
                                                 LL_ROLES) DB_UNIQUE_NAME=hr
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string


The redo transport mode is set in the parameter LOG_ARCHIVE_DEST_2. Below we configure redo transport mode for Maximum performance by setting ASYNC NOAFFIRM.
Instead of a location a service name is provided to send the archive logs.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
SQL>






SQL> alter system set log_archive_dest_2='SERVICE=hrsty LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hrsty';

System altered.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=hrsty LGWR ASYNC VALID
                                                 _FOR=(ONLINE_LOGFILES,PRIMARY_
                                                 ROLE) DB_UNIQUE_NAME=hrsty
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string


SQL> show parameter LOG_ARCHIVE_DEST_STATE

log_archive_dest_state_1             string      enable
log_archive_dest_state_2             string      enable



The FAL_SERVER specifies the fetch archive log (FAL) server for the standby database.
The FAL_CLIENT specifies the FAL client name. Both are used by the FAL service. The FAL_SERVER is the primary and FAL_CLIENT is the standby.

In Primary site:

FAL_SERVER=STANDBY
FAL_CLIENT=PRIMARY

In Standby site:

FAL_SERVER=PRIMARY
FAL_CLIENT=STANDB

SQL> alter system set FAL_SERVER=hrsty;

System altered.

SQL> alter system set FAL_CLIENT=hr;

System altered.

SQL>
SQL> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      HR
fal_server                           string      HRSTY
SQL>


Operating system file additions and deletions can be replicated to the physical standby database setting STANDBY_FILE_MANAGEMENT to auto.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL>
SQL>

SQL>
SQL> alter system set standby_file_management=auto;

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL>



SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL>



SQL>  select name from v$datafile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/hr/system01.dbf
/u01/app/oracle/oradata/hr/sysaux01.dbf
/u01/app/oracle/oradata/hr/undotbs01.dbf
/u01/app/oracle/oradata/hr/users01.dbf
/u01/app/oracle/oradata/hr/example01.dbf
/u01/app/oracle/oradata/hr/test01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/hr/redo03.log
/u01/app/oracle/oradata/hr/redo02.log
/u01/app/oracle/oradata/hr/redo01.log
/u01/app/oracle/oradata/hr/stby_redo01.log
/u01/app/oracle/oradata/hr/stby_redo02.log
/u01/app/oracle/oradata/hr/stby_redo03.log
/u01/app/oracle/oradata/hr/stby_redo04.log

primary files location :

db files: /u01/app/oracle/oradata/hr/
redo log files : /u01/app/oracle/oradata/hr/

standby files locations :

db files: /u01/app/oracle/oradata/hrsty/
redo log files : /u01/app/oracle/oradata/hrsty/

SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/hr/','/u01/app/oracle/oradata/hrsty/' scope=spfile;
System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/hr/','/u01/app/oracle/oradata/hrsty/' scope=spfile;
System altered.

SQL> show parameter conve

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
log_file_name_convert                string
SQL>

SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/hr/','/u01/app/oracle/oradata/hrsty/' scope=spfile;

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/hr/','/u01/app/oracle/oradata/hrsty/' scope=spfile;

System altered.



(Need restart database for reflect parameter)


SQL> show parameter conve

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /u01/app/oracle/oradata/hr/, /
                                                 u01/app/oracle/oradata/hrsty/
log_file_name_convert                string      /u01/app/oracle/oradata/hr/, /
                                                 u01/app/oracle/oradata/hrsty/
SQL>




2. Ensure that the sql*net connectivity is working fine.
===============================================================================


Insert a static entry for hrsty in the listener.ora file of the standby system.
NOTE : For non default port set the REMOTE_LISTENER

primary network files


[oracle@pri admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-AUG-2017 11:13:24

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

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/pri/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pri)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pri)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                27-AUG-2017 11:13:24
Uptime                    0 days 0 hr. 0 min. 0 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/pri/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pri)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "hr" has 1 instance(s).
  Instance "hr", status UNKNOWN, has 1 handler(s) for this service...
Service "hrsty" has 1 instance(s).
  Instance "hrsty", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@pri admin]$
[oracle@pri admin]$
[oracle@pri admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pri)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME =hr )
    )
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME =hrsty )
    )
  )



ADR_BASE_LISTENER = /u01/app/oracle

[oracle@pri admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

HR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hr)
    )
  )

HRSTY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hrsty)
    )
  )

[oracle@pri admin]$





standby network files
-----------------------------------


[oracle@stby admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@stby admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
HR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hr)
    )
  )


  HRSTY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hrsty)
    )
  )
[oracle@stby admin]$
[oracle@stby admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-AUG-2017 11:13:38

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

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/stby/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.13)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.13)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                27-AUG-2017 11:13:38
Uptime                    0 days 0 hr. 0 min. 0 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=tcp)(HOST=192.168.1.13)(PORT=1521)))
Services Summary...
Service "hr" has 1 instance(s).
  Instance "hr", status UNKNOWN, has 1 handler(s) for this service...
Service "hrsty" has 1 instance(s).
  Instance "hrsty", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@stby admin]$





Check with the SQL*Net configuration using the following commands on the Primary AND Standby

 tnsping hr
 tnsping hrsty

from primary
---------------------

 [oracle@pri admin]$ tnsping hr

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-AUG-2017 11:15:33

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = hr)))
OK (0 msec)
[oracle@pri admin]$ tnsping hrsty

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-AUG-2017 11:15:39

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = hrsty)))
OK (0 msec)
[oracle@pri admin]$

From standby
----------------------------
[oracle@stby admin]$ tnsping hr

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-AUG-2017 11:18:05

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = hr)))
OK (10 msec)
[oracle@stby admin]$

[oracle@stby admin]$ tnsping hrsty

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-AUG-2017 11:18:08

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = hrsty)))
OK (0 msec)
[oracle@stby admin]$



3. Create the standby database
=========================================================

a. Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.

The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The best practice for this is to copy the passwordfile as suggested.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.
b. Create a initialization parameter with only one parameter DB_NAME.

DB_NAME=hr
DB_UNIQUE_NAME=hrsty
DB_BLOCK_SIZE=<same as primary>


mkdir oradata
mkdir oradata/hrsty
[oracle@stby admin]$ pwd
/u01/app/oracle/admin
mkdir /u01/app/oracle/admin/hrsty
mkdir /u01/app/oracle/admin/hrsty/adump
mkdir /u01/app/oracle/admin/hrsty/bdump
mkdir /u01/app/oracle/admin/hrsty/dpdump
mkdir /u01/app/oracle/admin/hrsty/pfile

[oracle@stby hrsty]$ pwd
/u01/app/oracle/admin/hrsty
[oracle@stby hrsty]$ ls -lrt
total 16
drwxr-xr-x. 2 oracle oinstall 4096 Aug 27 11:24 adump
drwxr-xr-x. 2 oracle oinstall 4096 Aug 27 11:24 bdump
drwxr-xr-x. 2 oracle oinstall 4096 Aug 27 11:24 dpdump
drwxr-xr-x. 2 oracle oinstall 4096 Aug 27 11:24 pfile
[oracle@stby hrsty]
[oracle@stby hrsty]$ pwd
/u01/app/oracle/fast_recovery_area
[oracle@stby hrsty]$mkdir hrsty
[oracle@stby fast_recovery_area]$ ls -lrt
total 4
drwxr-xr-x. 2 oracle oinstall 4096 Aug 27 11:51 hrsty
[oracle@stby fast_recovery_area]$
oracle@stby oradata]$ mkdir hrsty
[oracle@stby oradata]$ ls -lrt
total 4
drwxr-xr-x. 2 oracle oinstall 4096 Aug 27 11:53 hrsty
[oracle@stby oradata]$ pwd
/u01/app/oracle/oradata
[oracle@stby oradata]$




Add the following line to /etc/oratab on the standby server.

hrsty:/u01/app/oracle/product/11.2.0/dbhome_1:N


[oracle@stby hrsty]$ cat /etc/oratab
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
#
hrsty:/u01/app/oracle/product/11.2.0/dbhome_1:N
[oracle@stby hrsty]$


Create a basic PFILE in ORACLE_HOME/dbs with the following parameters.

u01/app/oracle/product/11.2.0/dbhome_1/dbs/oracle $ orapwd file=orapwhr password=oracle entries=5


orapwd file=orapwhr password=oracle entries=5

scp orapwhr stby:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwhrsty


[oracle@pri dbs]$ scp orapwhr stby:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwhrsty
The authenticity of host 'stby (192.168.1.13)' can't be established.
RSA key fingerprint is f5:e6:6d:1a:54:0a:22:3f:64:df:ff:66:d8:b2:df:03.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'stby' (RSA) to the list of known hosts.
oracle@stby's password:
orapwhr                                                                                                                               100% 1536     1.5KB/s   00:00
[oracle@pri dbs]$




Create a basic PFILE in ORACLE_HOME/dbs with the following parameters.
DB_NAME=hr
DB_UNIQUE_NAME=hrsty


[oracle@stby dbs]$ cat inithrsty.ora
DB_NAME=hr
DB_UNIQUE_NAME=hrsty
[oracle@stby dbs]$


[oracle@stby dbs]$ . oraenv
ORACLE_SID = [oracle] ? hrsty
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@stby dbs]$


[oracle@stby dbs]$ echo $ORACLE_SID
hrsty
[oracle@stby dbs]$


[oracle@stby dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 27 11:49:25 2017

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2251816 bytes
Variable Size             159384536 bytes
Database Buffers           50331648 bytes
Redo Buffers                5189632 bytes
SQL>
SQL> show parameter db_na

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      hr
SQL> show parameter db_uni

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      hrsty
SQL>





Below is the RMAN run command to perform the database duplication from and active database along with brief explanations for the options used in the duplicate.

FOR STANDBY – the duplicate is for use as a standby so a DBID change will not be forced.
FROM ACTIVE DATABASE – instructs RMAN to use the active target database instead of disk based backups.
DORECOVER – do recovery bringing the standby database up to the current point in time.
SPFILE – values for parameters specific to the auxiliary instance can be set here.
NOFILENAMECHECK – this option is added because the duplicate database files uses the same name as the source database.



Primary parameter

SQL> col value for a90
col name for a50

select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
               'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
               'log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert',
                     'log_file_name_convert', 'standby_file_management','control_files');

NAME                                               VALUE
-------------------------------------------------- ------------------------------------------------------------------------------------------
control_files                                      /u01/app/oracle/oradata/hr/control01.ctl, /u01/app/oracle/fast_recovery_area/hr/control02.ctl
db_file_name_convert                               /u01/app/oracle/oradata/hr/, /u01/app/oracle/oradata/hrsty/
log_file_name_convert                              /u01/app/oracle/oradata/hr/, /u01/app/oracle/oradata/hrsty/
log_archive_dest_1                                 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hr
log_archive_dest_2                                 SERVICE=hrsty LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hrsty
log_archive_dest_state_1                           enable
log_archive_dest_state_2                           enable
fal_client                                         HR
fal_server                                         HRSTY
log_archive_config                                 DG_CONFIG=(hr,hrsty)
log_archive_format                                 %t_%s_%r.arc
log_archive_max_processes                          4
standby_file_management                            AUTO
remote_login_passwordfile                          EXCLUSIVE
db_name                                            hr
db_unique_name                                     hr

16 rows selected.

SQL>




On primary side

rman target /

connect auxiliary sys/oracle@hrsty


orapwd file=orapwhr password=oracle entries=5 ignorecase=Y

There are no spaces permitted around the equal-to (=).


[oracle@pri dbs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Aug 27 12:19:01 2017

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

connected to target database: HR (DBID=3602606687)

RMAN> connect auxiliary sys/oracle@hrsty

connected to auxiliary database: HR (not mounted)

RMAN>



run {
 allocate channel pri1 type disk;
 allocate channel pri2 type disk;
 allocate channel prmy4 type disk;
 allocate auxiliary channel stby type disk;
 duplicate target database for standby from active database
  spfile
  parameter_value_convert 'hr','hrsty'
    set db_unique_name='hrsty'
    set control_files='/u01/app/oracle/oradata/hrsty/control01.ctl','/u01/app/oracle/fast_recovery_area/hrsty/control02.ctl'
    set fal_client='hrsty'
    set fal_server='hr'
set log_archive_max_processes='5'
    set standby_file_management='AUTO'
    set log_archive_config='dg_config=(hr,hrsty)'
set db_file_name_convert='/u01/app/oracle/oradata/hr/','/u01/app/oracle/oradata/hrsty/'
    set log_file_name_convert='/u01/app/oracle/oradata/hr/','/u01/app/oracle/oradata/hrsty/'
    set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hrsty'
    set log_archive_dest_2='service=hr ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=hr'
;
  }
 
       

on standby site
------------------------

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HR        MOUNTED              PHYSICAL STANDBY

SQL> alter database open;

Database altered.

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

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

SQL>

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     21
Next log sequence to archive   0
Current log sequence           23
SQL>
SQL> alter database recover managed standby database disconnect from session;

Database altered.

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

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

SQL>

SQL> select protection_mode,protection_level,database_role,name from v$database;

PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    NAME
-------------------- -------------------- ---------------- ---------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY HR



we can validate that that logs are shipping and applying. On the standby issue the following query.
===========================================================================================================
On standby

SQL> select sequence#, first_time, next_time, applied
    from v$archived_log
    order by sequence#;   2    3

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
        19 27-AUG-17 27-AUG-17 YES
        20 27-AUG-17 27-AUG-17 YES
        21 27-AUG-17 27-AUG-17 YES
        22 27-AUG-17 27-AUG-17 YES
        23 27-AUG-17 27-AUG-17 YES
        24 27-AUG-17 27-AUG-17 YES
        25 27-AUG-17 27-AUG-17 YES
        26 27-AUG-17 27-AUG-17 YES
        27 27-AUG-17 27-AUG-17 YES
        28 27-AUG-17 27-AUG-17 YES
        29 27-AUG-17 27-AUG-17 YES
        30 27-AUG-17 27-AUG-17 YES
        31 27-AUG-17 27-AUG-17 YES


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     24
Next log sequence to archive   0
Current log sequence           26
SQL>


SQL> SELECT 'Last Applied : ' Logs,
  2  TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES'
)
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log );

LOGS             TIME                           THREAD#  SEQUENCE#
---------------- --------------------------- ---------- ----------
Last Applied :   27-AUG-17:12:52:32                   1         31
Last Received :  27-AUG-17:12:52:32                   1         31

SQL>



On primary
============================


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     24
Next log sequence to archive   26
Current log sequence           26
SQL>








SQL> SELECT
  2   (SELECT name FROM V$DATABASE
 ) name,
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
 ) Current_primary_seq,
 (SELECT MAX (sequence#)
 FROM v$archived_log
 WHERE TRUNC(next_time) > SYSDATE - 1
 AND dest_id = 2
 ) max_stby,
 (SELECT NVL (
 (SELECT MAX (sequence#) - MIN (sequence#)
 FROM v$archived_log
 WHERE TRUNC(next_time) > SYSDATE - 1
 AND dest_id = 2
 AND applied = 'NO'
 ), 0)
 FROM DUAL
 ) "To be applied",
 (
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
 ) -
 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 2
 )) "To be Shipped"
FROM DUAL;  

NAME      CURRENT_PRIMARY_SEQ   MAX_STBY To be applied To be Shipped
--------- ------------------- ---------- ------------- -------------
HR                         31         31             0             0

SQL>





 logs
 ===============
 [oracle@pri dbs]$
[oracle@pri dbs]$
[oracle@pri dbs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Aug 27 12:19:01 2017

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

connected to target database: HR (DBID=3602606687)

RMAN> connect auxiliary sys/oracle@hrsty

connected to auxiliary database: HR (not mounted)

RMAN> run {
 allocate channel pri1 type disk;
 allocate channel pri2 type disk;
 allocate channel prmy4 type disk;
 allocate auxiliary channel stby type disk;
 duplicate target database for standby from active database
  spfile
  parameter_value_convert 'hr','hrsty'
    set db_unique_name='hrsty'
    set control_files='/u01/app/oracle/oradata/hrsty/control01.ctl','/u01/app/oracle/fast_recovery_area/hrsty/control02.ctl'
2> 3> 4> 5> 6> 7> 8> 9> 10> 11>     set fal_client='hrsty'
    set fal_server='hr'
        set log_archive_max_processes='5'
    set standby_file_management='AUTO'
    set log_archive_config='dg_config=(hr,hrsty)'
        set db_file_name_convert='/u01/app/oracle/oradata/hr/','/u01/app/oracle/oradata/hrsty/'
    set log_file_name_convert='/u01/app/oracle/oradata/hr/','/u01/app/oracle/oradata/hrsty/'
    set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hrsty'
    set log_archive_dest_2='service=hr ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=hr'
        ;
  }12> 13> 14> 15> 16> 17> 18> 19> 20> 21>

using target database control file instead of recovery catalog
allocated channel: pri1
channel pri1: SID=41 device type=DISK

allocated channel: pri2
channel pri2: SID=42 device type=DISK

allocated channel: prmy4
channel prmy4: SID=40 device type=DISK

allocated channel: stby
channel stby: SID=19 device type=DISK

Starting Duplicate Db at 27-AUG-17

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwhr' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwhrsty'   targetfile
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilehr.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilehrsty.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilehrsty.ora''";
}
executing Memory Script

Starting backup at 27-AUG-17
Finished backup at 27-AUG-17

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

contents of Memory Script:
{
   sql clone "alter system set  audit_file_dest =
 ''/u01/app/oracle/admin/hrsty/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=hrstyXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''hrsty'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''/u01/app/oracle/oradata/hrsty/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/hrsty/control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client =
 ''hrsty'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server =
 ''hr'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes =
 5 comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config =
 ''dg_config=(hr,hrsty)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/u01/app/oracle/oradata/hr/'', ''/u01/app/oracle/oradata/hrsty/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/u01/app/oracle/oradata/hr/'', ''/u01/app/oracle/oradata/hrsty/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hrsty'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_2 =
 ''service=hr ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=hr'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

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

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

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

sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/hrsty/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/hrsty/control02.ctl'' comment= '''' scope=spfile

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

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

sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile

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

sql statement: alter system set  log_archive_config =  ''dg_config=(hr,hrsty)'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/u01/app/oracle/oradata/hr/'', ''/u01/app/oracle/oradata/hrsty/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/u01/app/oracle/oradata/hr/'', ''/u01/app/oracle/oradata/hrsty/'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hrsty'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_2 =  ''service=hr ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=hr'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1252663296 bytes

Fixed Size                     2252824 bytes
Variable Size                788533224 bytes
Database Buffers             452984832 bytes
Redo Buffers                   8892416 bytes
allocated channel: stby
channel stby: SID=18 device type=DISK

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/hrsty/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/hrsty/control02.ctl' from
 '/u01/app/oracle/oradata/hrsty/control01.ctl';
}
executing Memory Script

Starting backup at 27-AUG-17
channel pri1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_hr.f tag=TAG20170827T122351 RECID=2 STAMP=953123032
channel pri1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-AUG-17

Starting restore at 27-AUG-17

channel stby: copied control file copy
Finished restore at 27-AUG-17

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

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/hrsty/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/hrsty/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/hrsty/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/hrsty/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/hrsty/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/hrsty/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/hrsty/test01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/app/oracle/oradata/hrsty/system01.dbf"   datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/hrsty/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/hrsty/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/hrsty/users01.dbf"   datafile
 5 auxiliary format
 "/u01/app/oracle/oradata/hrsty/example01.dbf"   datafile
 6 auxiliary format
 "/u01/app/oracle/oradata/hrsty/test01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/hrsty/temp01.dbf 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

Starting backup at 27-AUG-17
channel pri1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/hr/test01.dbf
channel pri2: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/hr/system01.dbf
channel prmy4: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/hr/sysaux01.dbf
output file name=/u01/app/oracle/oradata/hrsty/sysaux01.dbf tag=TAG20170827T122402
channel prmy4: datafile copy complete, elapsed time: 00:08:58
channel prmy4: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/hr/example01.dbf
output file name=/u01/app/oracle/oradata/hrsty/system01.dbf tag=TAG20170827T122402
channel pri2: datafile copy complete, elapsed time: 00:09:34
channel pri2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/hr/undotbs01.dbf
output file name=/u01/app/oracle/oradata/hrsty/undotbs01.dbf tag=TAG20170827T122402
channel pri2: datafile copy complete, elapsed time: 00:00:25
channel pri2: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/hr/users01.dbf
output file name=/u01/app/oracle/oradata/hrsty/users01.dbf tag=TAG20170827T122402
channel pri2: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/hrsty/test01.dbf tag=TAG20170827T122402
channel pri1: datafile copy complete, elapsed time: 00:10:25
output file name=/u01/app/oracle/oradata/hrsty/example01.dbf tag=TAG20170827T122402
channel prmy4: datafile copy complete, elapsed time: 00:01:26
Finished backup at 27-AUG-17

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=2 STAMP=953123668 file name=/u01/app/oracle/oradata/hrsty/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=953123668 file name=/u01/app/oracle/oradata/hrsty/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=953123668 file name=/u01/app/oracle/oradata/hrsty/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=953123668 file name=/u01/app/oracle/oradata/hrsty/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=953123668 file name=/u01/app/oracle/oradata/hrsty/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=953123668 file name=/u01/app/oracle/oradata/hrsty/test01.dbf
Finished Duplicate Db at 27-AUG-17
released channel: pri1
released channel: pri2
released channel: prmy4
released channel: stby

RMAN>

RMAN>

RMAN>

RMAN>


Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)

Session Tracing in Oracle : trace a session in Oracle SQL trace, 10046 event

Enable before start session trace in oracle
=============================================

ALTER SESSION SET TRACEFILE_IDENTIFIER = "USER_TRACE" ';
alter session set timed_statistics = true';
alter session set statistics_level=ALL';
alter session set max_dump_file_size=UNLIMITED';


Enable trace for user
=================================

CREATE OR REPLACE TRIGGER USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'HR'
  THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = "USER_TRACE" ';
    EXECUTE IMMEDIATE 'alter session set timed_statistics = true';
    EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
    EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

* Drop trigger for disable


Enable trace for session
=======================================

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';


Disabling trace
==============================================

alter system set events '10046 trace name context off';

Find its OS pid from the following
================================================
select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &SESSION_ID;

SQL> select sid,serial# from v$session where username='HR';

       SID    SERIAL#
---------- ----------
        42        293


Trace session useing pl/SQL package(DBMS_MONITOR)
==================================================================

Enable tracing
------------------------------------
EXEC DBMS_MONITOR.session_trace_enable(session_id =>42, serial_num=>293, waits=>TRUE, binds=>TRUE);

Disable Tracing
-------------------------------
EXEC DBMS_MONITOR.session_trace_disable(session_id=>42, serial_num=>293);



Identify trace file using  SID
==========================================

SELECT p.tracefile
FROM   v$session s
       JOIN v$process p ON s.paddr = p.addr
WHERE  s.sid = 42;


Example
----------------

SQL> SELECT p.tracefile
FROM   v$session s
       JOIN v$process p ON s.paddr = p.addr
WHERE  s.sid = 42;  2    3    4

TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/hr/hr/trace/hr_ora_10369.trc

SQL>



check own SID and trace file
=================================

SET LINESIZE 100
COLUMN trace_file FORMAT A60

SELECT s.sid,
       s.serial#,
       pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||  
       '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
       v$process p,
       v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

=====================================================================
Oracle provides event 10046 that can also aid us in collecting extended SQL trace data.

There are four levels available when setting up a trace with Event 10046:

Level 1: this cause tracing of sql activities and is similar to 'alter session set sql_trace=true'
Level 4: provides level 1 tracing + displays the values for all bind variables. It is equivalent to dbms_support.start_trace(waits=>false,binds=>true);

Level 8: provides level 1 tracing and displays a list of all database wait events. It is equivalent to dbms_support.start_trace(waits=>true,binds=>false);

Level 12 provides level 1 tracing in addition to both bind variable substitution and database wait events. It is equivalent to dbms_support.start_trace(waits=>true,binds=>true);

Note that Level 0 disables tracing.

1 is same as a regular trace.
4 means also dump bind variables
8 means also dump wait information
12 means dump both bind and wait information

Tracing Enhancements Using DBMS_MONITOR (In 10g, 11g and Above) (Doc ID 293661.1)

#########################################
Fron oracle Support
##########################################
DBMS_MONITOR.session_trace_enable in SQL*Plus Current Session or Profile Option Method

It is not recommended to use DBMS_MONITOR.session_trace_enable for tracing the current session in SQL*Plus or in the Profile Option “Initialization SQL Statement - Custom”.

The APPS user is used in both of these scenarios and it may not have the correct privilege to run DBMS_MONITOR.

2.19 Obtaining trace for another database session prior to 10g

Prior to 10g the DBMS_MONITOR package is not available. In this case the DBMS_SYSTEM.set_sql_trace_in_session or
DBMS_SUPPORT.start_trace_in_session procedures can be used to trace another session.

The DBMS_SUPPORT and DBMS_SYSTEM packages are not documented So, they should be avoided by customers, unless specifically
instructed by either Oracle Support or Oracle Development.

The DBMS_SUPPORT package is not installed by default. The package is supplied in files dbmssupp.sql and prvtsupp.plb (these are in $ORACLE_HOME/rdbms/admin).

The DBMS_SYSTEM package is usually already installed (but it can be installed using catproc.sql).

Public synonyms are not created (by default) for either package and no privileges are granted. So use SYS to run these procedures.

See My Oracle Support documents:

The DBMS_SUPPORT Package (Document 62294.1)

For both of these methods the Session ID (SID) and Serial Number will be needed. These can be obtained by querying the v$session and v$process views.

DBMS_SYSTEM

To enable SQL Trace run the following:

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=><sid>,
                                          serial#=><serial#>,
                                          sql_trace=>TRUE);
To disable SQL Trace run the following:

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=><sid>,
                                          serial#=><serial#>,
                                          sql_trace=>FALSE);
DBMS_SUPPORT

To enable SQL Trace run the following :

EXEC DBMS_SUPPORT.start_trace_in_session(sid=><sid>,
                                         serial=><serial#>,
                                         waits=>true,
                                         binds=>false);
Note that with waits, but not binds is recommended (and the default in this case). But other options can be chosen.

To disable SQL Trace run the following :

EXEC DBMS_SUPPORT.stop_trace_in_session(sid=><sid>,
                                        serial=><serial#>);
Serial Number can be set to NULL (or 0) to trace the current session with the supplied SID.

SERIAL only needs to be specified to ensure that a specific session is traced.
If the session ends and the SID is reused before the START_TRACE_IN_SESSION call is made then including
the SERIAL will cause an error to be raised (rather than the trace being started against a different session to the one intended).

Script to Show Objects That are Missing Statistics

Script to Show Objects That are Missing Statistics:-
================================================


SELECT 'TABLE' object_type,owner, table_name object_name, last_analyzed, stattype_locked, stale_stats
FROM all_tab_statistics
WHERE (last_analyzed IS NULL OR stale_stats = 'YES') and stattype_locked IS NULL
and owner NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM' )
AND owner NOT LIKE 'FLOW%'
UNION ALL
SELECT 'INDEX' object_type,owner, index_name object_name,  last_analyzed, stattype_locked, stale_stats
FROM all_ind_statistics
WHERE (last_analyzed IS NULL OR stale_stats = 'YES') and stattype_locked IS NULL
AND owner NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'SYS' )
AND owner NOT LIKE 'FLOW%'
ORDER BY object_type desc, owner, object_name
/


Script to Show Objects That are Missing Statistics (Doc ID 957993.1)

ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot DROP object used in Oracle GoldenGate replication while trigger is enabled

ORA-00604: error occurred at recursive SQL level 2
ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot
DROP object used in Oracle GoldenGate replication while trigger is enabled.
Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate Technical
Support if you wish to do so., error stack: ORA-06512: at line 310
ORA-06512: at line 1272

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         2 UNDOTBS1                       YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 EXAMPLE                        YES NO  YES
         7 TEST                           YES NO  YES
         8 GG                             YES NO  YES

SQL> drop tablespace gg including contents and datafiles;
drop tablespace gg including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot
DROP object used in Oracle GoldenGate replication while trigger is enabled.
Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate Technical
Support if you wish to do so., error stack: ORA-06512: at line 310
ORA-06512: at line 1272

[oracle@pri ~]$ sqlp

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 15 10:09:43 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop trigger GGS_DDL_TRIGGER_BEFORE;

Trigger dropped.

SQL> drop tablespace gg including contents and datafiles;

Tablespace dropped.

SQL>