Tuesday 4 November 2014

Predefined Roles for Oracle 12C


When we run standard scripts that are part of database creation, Oracle 12C automatically defines few roles which are listed below

Predefined Roles for Oracle 12C

ADM_PARALLEL_EXECUTE_TASK
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
AUDIT_ADMIN
AUDIT_VIEWER
AUTHENTICATEDUSER
CAPTURE_ADMIN
CDB_DBA, CONNECT
CSW_USR_ROLE
CTXAPP
CWM_USER
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
DBA
DBFS_ROLE
DELETE_CATALOG_ROLE
EJBCLIENT
EM_EXPRESS_ALL
EM_EXRESS_BASIC
EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
GLOBAL_AQ_USER_ROLE
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_ROLE
HS_ADMIN_SELECT_ROLE
IMP_FULL_DATABASE
JAVADEBUGPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVAUSERPRIV
JAVA_ADMIN
JAVA_DEPLOY
JMXSERVER
LBAC_DBA
LOGSTDBY_ADMINISTRATOR
OEM_ADVISOR
OEM_MONITOR
OLAP_DBA
OLAP_USER
OLAP_XS_ADMIN
OPTIMIZER_PROCESSING_RATE
ORDADMIN
PDB_DBA
PROVISIONER
RECOVERY_CATALOG_OWNER
RESOURCE
SCHEDULER_ADMIN
SELECT_CATALOG_ROLE
SPATIAL_CSW_ADMIN
SPATIAL_WFS_ADMIN
WFS_USR_ROLE
WM_ADMIN_ROLE
XDBADMIN
XDB_SET_INVOKER
XDB_WEBSERVICES
XDB_WEBSERVICES_OVER_HTTP
XDB_WEBSERVICES_WITH_PUBLIC
XS_CACHE_ADMIN
XS_NSATTR_ADMIN
XS_RESOURCE
XS_SESSION_ADMIN

SYS user table USER$ in Oracle Database

SYS.USER$ Internal table in Oracle Database

# PTIME provides the date the password was last changed.
# LCOUNT provides the number of failed logins.
# CTIME provides the date the user was created.
# LTIME provides the date the user was last locked.

In the case of LTIME column the value will not be set to null if the user will be unlocked.

Below is an example of how to query user$ table :-

select ctime,ltime,ptime from user$ where name = 'SCOTT';

In Oracle database 12C the SELECT ANY DICTIONARY Privilege does not allows users to access some SYS Data Dictionary Tables like for example the sys schema tables USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS, DEFAULT_PWD$, ENC$, LINK$, and USER$. So thats a new enhancement for 12C security, there are lots of them and i will be publishing them too soon on this blog on Label Oracle Security.

Wednesday 15 October 2014

Tracing in Oracle Data Guard


Sometime to do additional analysis and troubleshooting in oracle databases or even to provide additional information to Oracle Support we need to enable tracing, and when it comes to oracle data guard we have several tracing levels as well, like other oracle events.

Gathering trace data for database workload there are lot of ways which is also shown in my another article,  however today we will see special type of tracing which is for log archive and log apply services. To gather and generate trace data for archiving you can enable tracing on the Primary and Standby Databases. Archive Tracing can be enabled via the Initialization Parameter ‘log_archive_trace’.

There are several Levels available. To enable different levels of tracing for oracle data guard archiving set the ‘log_archive_trace’ to the Sum of the desired Levels. Remember that setting this parameter back to ‘0’ disables the Tracing. Relevant and common Levels for Log Transport Services are:


For the Primary Database:

1:            Tracks archiving of log files
2:            Tracks archive status by archive log file destination
64:         Tracks ARCn process state activity
128:       Tracks FAL server process activity
512:       Tracks LGWR redo shipping network activity
2048:     Tracks RFS/ARCn ping heartbeat

For the Standby Database:

1:            Tracks archiving of log files
2:            Tracks archive status by archive log file destination
64:          Tracks ARCn process state activity
256:       Tracks RFS Logical Client
1024:     Tracks RFS physical client
2048:     Tracks RFS/ARCn ping heartbeat

There are some more Levels which are concerning local Archiving and Log Apply Services which can be set as well if required. See below

4096 Tracks real-time apply activity

8192 Tracks Redo Apply activity (media recovery or physical standby)

16384 Tracks archive I/O buffers

32768 Tracks LogMiner dictionary archiving

You can combine tracing levels by setting the value of the LOG_ARCHIVE_TRACE parameter to the sum of the individual levels. For example, setting the parameter to 6 generates level 2 and level 4 trace output.

Note: The level numbers do not appear in the actual trace output; they are shown here for clarification only.

You can read oracle documentation for further information from following link of doc

Reference :- http://docs.oracle.com/cd/E11882_01/server.112/e41134/toc.htm

Switchover not happening in Oracle Data guard

Usually when we are trying to switchover, problems come when sessions are active, that is why session shutdown option is there in oracle, so in case some sessions are

active, make sure you wait for them to end or kill them, also do the following workaround in order to make sure the a clean switchover happens.


So like i said When sessions are active we should not try to switchover, even if we do an attempt to switch over fails with the following error message:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY * 
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
Action: Query the V$SESSION view to determine which processes are causing the error. For example:

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);

SID        PROCESS   PROGRAM
---------  --------  ------------------------------------------------
        7      3537  oracle@nhclone2 (CJQ0)
       10
       14
       16
       19
       21

6 rows selected.

In the above output and example, the JOB_QUEUE_PROCESSES parameter corresponds to the CJQ0 process entry.JOB_QUEUE_PROCESSES parameter is used for lot of things in

oracle like for example refresh support for materialized views or may be advanced queus using job queues for message propagation. Oracle says the job queue process is

a user process, it is counted as a SQL session that prevents switchover from taking place. The entries with no process or program information are threads started by

the job queue controller.

JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per

instance (J000, ... J999).


Verify the JOB_QUEUE_PROCESSES parameter is set using the following SQL statement:

SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES;
NAME                           TYPE      VALUE
------------------------------ -------   --------------------
job_queue_processes            integer   5

Then, set the parameter to 0. For example:

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;


Some more sessions can also be active and below processes are very critical to stop in order for a clean switchover to take place.

## Change the AQ_TM_PROCESSES dynamic parameter to the value 0. The change will take effect immediately without having to restart the instance.

## Issue the emctl stop agent command from the operating system prompt.

Friday 3 October 2014

Mastering 12C Data Guard BROKER


In this article we will see how to master 12C Oracle Data Guard Broker, we will see how to setup a broker, troubleshoot the broker, problems faced with 12C Broker, Switchover and failover with 12C Broker and also Fast Start failover with 12C Broker.

In this article we have a data guard setup with primary as PRD and standby as DRS.

[oracle@node4 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /   
Connected as SYSDG.

DGMGRL> create configuration myconfig1 as primary database is 'prd'  connect identifier is prd ;
Error: 
ORA-16525: the Data Guard broker is not yet available

## So in this case we will enable the initialization parameter dg_broker_start

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRD       READ WRITE

SQL> alter system set dg_broker_start=true;

System altered.

## Lets go to standby database

[oracle@node4 ~]$ sqlplus sys@drs as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 8 15:09:22 2014

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

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL>  alter system set dg_broker_start=true;

System altered.

[oracle@node4 ~]$ dgmgrl

DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@prd
Password:
Connected as SYSDBA.

DGMGRL>  create configuration myconfig1 as primary database is 'prd'  connect identifier is prd ;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Failed.

## In this case we should disable the following initialization parameter

SQL>   ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=" ";

System altered.


DGMGRL>  create configuration myconfig1 as primary database is 'prd'  connect identifier is prd ;

Configuration "myconfig1" created with primary database "prd"

DGMGRL> add database drs as  connect identifier is drs ;

Database "drs" added
DGMGRL> 
DGMGRL> 

DGMGRL> enable configuration ;
Enabled.

DGMGRL> show configuration ;

Configuration - myconfig1

  Protection Mode: MaxPerformance
  Databases:
  prd - Primary database
    Warning: ORA-16789: standby redo logs not configured

    drs - Physical standby database 
      Error: ORA-16525: the Data Guard broker is not yet available

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

## So we will configure standby redo logs now for primary

SQL> alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m;
Enter value for a: 4
Enter value for a: red04.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m
new   1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/prd/red04.log') size 50m

Database altered.

SQL> /
Enter value for a: 5
Enter value for a: red05.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m
new   1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/prd/red05.log') size 50m

Database altered.

SQL> /
Enter value for a: 6
Enter value for a: red06.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m
new   1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/prd/red06.log') size 50m

Database altered.

SQL> /
Enter value for a: 7
Enter value for a: red07.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m
new   1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/prd/red07.log') size 50m

Database altered.


DGMGRL> show database drs

Database - drs

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     46 seconds (computed 17 seconds ago)
  Apply Lag:         2 minutes 36 seconds (computed 17 seconds ago)
  Apply Rate:        (unknown)
  Real Time Query:   OFF
  Instance(s):
    drs

  Database Warning(s):
    ORA-16789: standby redo logs not configured

Database Status:
WARNING

## Standby logs also not configured for standby database

SQL> alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m;
Enter value for a: 4
Enter value for a: red04.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m
new   1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/drs/red04.log') size 50m

Database altered.

SQL> /
Enter value for a: 5
Enter value for a: red05.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m
new   1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/drs/red05.log') size 50m

Database altered.

SQL> /
Enter value for a: 6
Enter value for a: red06.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m
new   1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/drs/red06.log') size 50m

Database altered.

SQL> /
Enter value for a: 7
Enter value for a: red07.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m
new   1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/drs/red07.log') size 50m

Database altered.


DGMGRL>  show database drs

Database - drs

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     4 minutes 45 seconds (computed 6 seconds ago)
  Apply Lag:         4 minutes 45 seconds (computed 6 seconds ago)
  Apply Rate:        (unknown)
  Real Time Query:   OFF
  Instance(s):
    drs

  Database Error(s):
    ORA-16766: Redo Apply is stopped

Database Status:
ERROR

## Redo apply is stopped now because we had to add standby log and for that we have to disable MRP Process

SQL> alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m;
Enter value for a: 4
Enter value for a: red04.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m
new   1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/prd/red04.log') size 50m
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/prd/red04.log') size 50m
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files


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

## Now i was able to add standby redo log files


DGMGRL> show database drs

Database - drs

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     6 minutes 47 seconds (computed 28 seconds ago)
  Apply Lag:         6 minutes 47 seconds (computed 28 seconds ago)
  Apply Rate:        (unknown)
  Real Time Query:   OFF
  Instance(s):
    drs

Database Status:
SUCCESS


DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance
  Databases:
  prd - Primary database
    drs - Physical standby database 
      Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> disable database drs ;
Disabled.
DGMGRL> enable database drs ;
Enabled.
DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance
  Databases:
  prd - Primary database
    drs - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

## Lets do a switchover and a failover

DGMGRL> edit database prd set property FastStartFailoverTarget = 'drs' ;
Property "faststartfailovertarget" updated

DGMGRL> edit database drs set property FastStartFailoverTarget = 'prd' ;
Property "faststartfailovertarget" updated

DGMGRL> enable fast_start failover ;
Enabled.

DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance
  Databases:
  prd - Primary database
    Warning: ORA-16819: fast-start failover observer not started

    drs - (*) Physical standby database 
      Warning: ORA-16819: fast-start failover observer not started

Fast-Start Failover: ENABLED

Configuration Status:
WARNING

DGMGRL> start observer
Observer started

## While doing switchover and failover i kept on investigating for a long time why invalid username password error is coming even though the password and password file is same for primary and standby, i later realized that the password was not complex because it was simple 3 letter password. and that was the reason i was getting this error. Let us see now for switchover and failover example.

DGMGRL> switchover to prd ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prd" on database "prd"
Connecting to instance "prd"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

        connect to instance "prd" of database "prd"

DGMGRL> switchover to prd ;
not logged on
DGMGRL> connect sys@drs
Password:
ORA-01017: invalid username/password; logon denied

## Now i made sure my password is complex

DGMGRL> connect sys@drs
Password:
Connected as SYSDBA.
DGMGRL> switchover to prd ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prd" on database "prd"
Connecting to instance "prd"...
Connected as SYSDBA.
New primary database "prd" is opening...
Operation requires startup of instance "drs" on database "drs"
Starting instance "drs"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prd"
DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance
  Databases:
  prd - Primary database
    drs - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>  switchover to drs ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "drs" on database "drs"
Connecting to instance "drs"...
Connected as SYSDBA.
New primary database "drs" is opening...
Operation requires startup of instance "prd" on database "prd"
Starting instance "prd"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "drs"


## It is really important to connect to the target


DGMGRL> connect sys@drs
Password:
Connected as SYSDBA.
DGMGRL> failover to prd ;
Performing failover NOW, please wait...
Error: ORA-16600: not connected to target standby database for failover

Failed.
Unable to failover

DGMGRL> connect sys@prd 
Password:
Connected as SYSDBA.
DGMGRL> failover to prd ;
Performing failover NOW, please wait...
Failover succeeded, new primary is "prd"

## Reinstating the standby database DRS

DGMGRL> reinstate database drs ;
Reinstating database "drs", please wait...
Operation requires shutdown of instance "drs" on database "drs"
Shutting down instance "drs"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "drs" on database "drs"
Starting instance "drs"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "drs" ...
Reinstatement of database "drs" succeeded

____________

DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:          30 seconds
  Target:             (none)
  Observer:           node4.oracle.com
  Lag Limit:          30 seconds
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)

DGMGRL> enable fast_start failover condition "1031" ;
Succeeded.

DGMGRL> enable fast_start failover condition "1578" ;
Succeeded.
DGMGRL>  show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:          30 seconds
  Target:             (none)
  Observer:           node4.oracle.com
  Lag Limit:          30 seconds
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    ORA-01031: insufficient privileges
    ORA-01578: ORACLE data block corrupted (file # %s, block # %s)

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = "15" ;
Property "faststartfailoverthreshold" updated

DGMGRL> enable fast_start failover ;
Enabled.

SQL> conn scott 
Enter password: 
Connected.
SQL> alter system set log_checkpoints_to_alert=true;
alter system set log_checkpoints_to_alert=true
*
ERROR at line 1:
ORA-01031: insufficient privileges

### Broker Log is given below

7:48:32.60  Monday, September 08, 2014
Initiating Fast-Start Failover to database "drs"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "drs"
17:48:36.72  Monday, September 08, 2014

FAR SYNC in Oracle 12C Database

In this article we will see  setting up a far sync configuration in your Dr Environment, We will also see how to integrate it with the broker but before the practical setup we will understand the concept of FAR SYNC INSTANCE.

In previous releases before 12C, the problem in sync configuration of primary to standby was that if geographical distance was very huge commit latency was affected for the primary because sync means transaction is not complete till the time standby acknowledges the redo change vector. Wouldnt it be great if primary database ships the redo to a dummy instance which is close to it geographically which means it can very well work in sync mode with it with a very good commit latency and later that dummy instance would transfer the redo changes to other standby databases in ASYNC mode without affecting the transaction commit latency for the primary database. Well yes it happens in Oracle Database 12C with a thing known as FAR SYNC INSTANCE.


FAR SYNC is a new enhancement in Oracle 12C Data guard, A Data Guard far sync instance is a destination geographically close to the primary which will accept redo from primary database and then ship it to the other standby terminals. This far sync instance is basically a light weight instance which just has a controlfile , standby redo logs and it archives the standby redo logfiles to local archive redo logs. This type of instance does not have any user datafiles so it means it cannot be opened for read access and it cannot run redo apply and also it can never function in primary role and also it cannot be converted to any type of standby. Its function is to receive redo quickly from primary in sync mode so that transaction on primary commits quickly and later if geographical distance is big between far sync and standby terminals it can keep up with those standby terminals later easily in asynch mode without affecting primary database commit latency.


Setting up a far sync configuration in your Dr Environment, We will also see how to integrate it with the broker.

## In our configuration we have a primary database (PROD1) and 1 physical standby (DR1) and we will see how to add a far sync PRODFS in this configuraton

## In the previous article on this blog of mine i have shown the setup of Data guard for primary PROD1 and standby DR1

## lets create a far sync instance named as prodfs

## create far sync controlfile from primary database prod1

SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/u01/app/oracle/oradata/prodfs/control01.ctl';

## Making changes in spfile of Primary database PROD1

Make the following changes in spfile

LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod1,prodfs,dr1)'

LOG_ARCHIVE_DEST_2='SERVICE=prodfs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prodfs'

## Making changes in spfile of FAR SYNC (PRODFS)

Make the following changes in spfile

DB_UNIQUE_NAME=prodfs

CONTROL_FILES='/u01/app/oracle/oradata/prodfs/control01.ctl'

DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod1','/u01/app/oracle/oradata/prodfs'

LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod1','/u01/app/oracle/oradata/prodfs'

FAL_SERVER=prod1

LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod1,prodfs,dr1)'

LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prodfs'

LOG_ARCHIVE_DEST_2='SERVICE=dr1 ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=dr1'

## Making changes in spfile of PHYSICAL STANDBY (DR1)

Make the following changes in spfile

## Below are the commands shown practically this time

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod1,prodfs,dr1)' ;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=prod1 SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod1' ;

System altered.

SQL>  alter system set fal_server = 'prodfs','prod' ;

System altered.


## Lets login to far sync instance

[oracle@node4 ~]$ export ORACLE_SID=prodfs
[oracle@node4 ~]$
[oracle@node4 ~]$
[oracle@node4 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Oct 2 17:25:55 2014

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

Connected to an idle instance.


SQL> create spfile from pfile='/u01/initprodfs.ora' ;

File created.

SQL> startup mount
ORACLE instance started.

Total System Global Area  617975808 bytes
Fixed Size                  2291376 bytes
Variable Size             276826448 bytes
Database Buffers          335544320 bytes
Redo Buffers                3313664 bytes
Database mounted.

## We are almost done

_____________________________________________________________

Broker setup for far sync standby

export ORACLE_SID=prod1

[oracle@node4 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected as SYSDG.
DGMGRL>
DGMGRL> create configuration conf1 as primary database is prod1 connect identifier is prod1 ;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Failed.

Fixing it on primary database

SQL> alter system set log_archive_dest_2='' scope=both;

System altered.


DGMGRL> create configuration conf1 as primary database is 'prod1' connect identifier is prod1 ;
Configuration "conf1" created with primary database "prod1"
DGMGRL>
DGMGRL>


DGMGRL> add database 'dr1' as connect identifier is dr1  maintained as physical ;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Failed.


## Fixing this on our standby terminal

SQL>  alter system set log_archive_dest_2='' scope=both;

System altered.


DGMGRL>  add database 'dr1' as connect identifier is dr1  maintained as physical ;
Database "dr1" added

DGMGRL> enable configuration ;
Enabled.
DGMGRL>
DGMGRL> ADD FAR_SYNC 'prodfs' AS CONNECT IDENTIFIER IS prodfs;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Failed.

## fixing this on far sync instance

SQL>  alter system set log_archive_dest_2='' scope=both;

System altered.


DGMGRL> ADD FAR_SYNC 'prodfs' AS CONNECT IDENTIFIER IS prodfs;
far sync instance "prodfs" added

DGMGRL> ENABLE FAR_SYNC 'prodfs';
Enabled.

DGMGRL> SHOW CONFIGURATION;

Configuration - conf1

  Protection Mode: MaxPerformance
  Databases:
  prod1  - Primary database
    dr1    - Physical standby database
    prodfs - Far Sync (inactive)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

## Far sync still shows inactive, to enable this we need to do the following

DGMGRL> EDIT DATABASE 'prod1' SET PROPERTY RedoRoutes='(LOCAL : PRODFS SYNC)';
Property "redoroutes" updated

DGMGRL>  EDIT FAR_SYNC 'prodfs' SET PROPERTY RedoRoutes='(PROD1 : DR1)' ;
Property "redoroutes" updated
DGMGRL> SHOW CONFIGURATION;

Configuration - conf1

  Protection Mode: MaxPerformance
  Databases:
  prod1  - Primary database
    prodfs - Far Sync
      dr1    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL>  show configuration

Configuration - conf1

  Protection Mode: MaxAvailability
  Databases:
  prod1  - Primary database
    prodfs - Far Sync
      dr1    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

______________________________________________



Setting up a physical standby in Oracle 12C


Setting up a physical standby in Oracle 12C

In our configuration we will have primary database as PROD1 and physical standby as DR1

## Making changes to the primary environment

SQL> alter system set log_archive_config='dg_config=(prod1,dr1)' ;

System altered.

SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_roles,all_logfiles) db_unique_name=prod1' ;

System altered.

SQL> alter system set log_archive_dest_2='service=dr1 valid_for=(primary_roles,online_logfiles) db_unique_name=dr1' ;

System altered.

SQL> alter system set fal_client=prod1;

System altered.

SQL> alter system set fal_server=dr1 ;

System altered.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/dr1','/u01/app/oracle/oradata/prod1' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/dr1','/u01/app/oracle/oradata/prod1' scope=spfile;

System altered.

SQL> alter system set standby_file_management=auto ;

System altered.

## Making changes to the standby environment

SQL> alter system set log_archive_config='dg_config=(prod1,dr1)' ;

System altered.

SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_roles,all_logfiles) db_unique_name=dr1' ;

System altered.

SQL> alter system set log_archive_dest_2='service=prod1 valid_for=(primary_roles,online_logfiles) db_unique_name=prod1' ;

System altered.

SQL> alter system set fal_client=dr1;

System altered.

SQL> alter system set fal_server=prod1 ;

System altered.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/prod1','/u01/app/oracle/oradata/dr1' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/prod1','/u01/app/oracle/oradata/dr1' scope=spfile;

System altered.

SQL> alter system set standby_file_management=auto ;

System altered.

__________________

## Below are tns entries

PROD1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname of primary)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod1)
    )
  )


DR1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname of standby)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dr1)
    )
  )

## Appropriate directories and Password files are also in place

[oracle@node4 ~]$ ls -ld /u01/app/oracle/oradata/dr1
drwxr-xr-x 2 oracle oinstall 4096 Oct  2 16:49 /u01/app/oracle/oradata/dr1


[oracle@node4 ~]$ ls -ltr /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwdr1
-rw-r----- 1 oracle oinstall 7680 Oct  2 16:45 /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwdr1

[oracle@node4 ~]$ ls -ltr /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwprod1
-rw-r----- 1 oracle oinstall 7680 Oct  2 16:19 /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwprod1


[oracle@node4 ~]$ rman target/

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Oct 2 16:44:16 2014

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

connected to target database: PROD1 (DBID=2081254351)

RMAN> connect auxiliary sys@dr1

auxiliary database Password:
connected to auxiliary database: PROD1 (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 02-OCT-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/02/2014 16:44:47
RMAN-05501: aborting duplication of target database
RMAN-05609: Must specify a username for target connection when using active duplicate

RMAN> exit


Recovery Manager complete.
[oracle@node4 ~]$ rman target sys@prod1 auxiliary sys@dr1

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Oct 2 16:44:59 2014

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

target database Password:
connected to target database: PROD1 (DBID=2081254351)
auxiliary database Password:
connected to auxiliary database: PROD1 (not mounted)

RMAN> duplicate target database for standby from active database;

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

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwprod1' auxiliary format
 '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwdr1'   ;
}
executing Memory Script

Starting backup at 02-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
Finished backup at 02-OCT-14

contents of Memory Script:
{
   restore clone from service  'prod1' standby controlfile;
}
executing Memory Script

Starting restore at 02-OCT-14
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prod1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/dr1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/dr1/control02.ctl
Finished restore at 02-OCT-14

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/dr1/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/dr1/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/dr1/example01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/dr1/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/dr1/undotbs01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/dr1/users01.dbf";
   restore
   from service  'prod1'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/dr1/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

Starting restore at 02-OCT-14
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prod1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dr1/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prod1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/dr1/example01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prod1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/dr1/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prod1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dr1/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prod1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/dr1/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 02-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=1 STAMP=859913214 file name=/u01/app/oracle/oradata/dr1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=859913214 file name=/u01/app/oracle/oradata/dr1/example01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=859913214 file name=/u01/app/oracle/oradata/dr1/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=859913214 file name=/u01/app/oracle/oradata/dr1/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=859913214 file name=/u01/app/oracle/oradata/dr1/users01.dbf
Finished Duplicate Db at 02-OCT-14

RMAN> exit


Recovery Manager complete.

## Login to the standby database

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

SQL> @arch

 SEQUENCE# ARCHIVED APPLIED   COMPLETION_TIME
---------- ------------ --------------------
         7 YES YES       02-oct-2014 16:50:14
         8 YES YES       02-oct-2014 16:50:17