Create a physical standby database through RMAN duplicate active data files

I have a very small database ( ~ 5GB) called etsdb, which hosts an Oracle APEX application called DBAETS for DBA team internal use.

In the past, as a better-than-nothing backup and disaster recovery strategy, I export it every night and ship the export dump file to a remote server. Now I decide to create a physical standby database at the same remote server for it.

The transaction volumn of this application is typically very low as at any given time there could be just two or three DBAs logged into it to update some info. So it is ideal to use RMAN active database duplication technique to create the physical standby. In this method, RMAN duplicate process copies the active data files of the primary database directly to the remote host through network. RMAN supports two basic types of duplication: active database duplication and backup-based duplication. Oracle online documentation has a section about the considerations  to choose duplication technique.

I describe the steps involved in the following:

1. Prepare the production database to be the primary database

a. Ensure that the database is in archivelog mode

b. Enable force logging

    SQL> ALTER DATABASE FORCE LOGGING;

c. Create standby redologs

     SQL> alter database add standby logfile '/db2st2/u01/oradata/etsdb/stby_redo01.rdo' size 100M;

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

         
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(etsdb,ettdb)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/db2st2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=etsdalter system set LOG_ARCHIVE_DEST_2='SERVICE=ettdb LGWR ASYNC VALID_FOR=(online_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=ettdb';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set FAL_SERVER=ettdb;
alter system set FAL_CLIENT=etsdb;
alter system set DB_FILE_NAME_CONVERT='/db2st2/u01/oradata/etsdb','/db2/u01/oradata/ettdb' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/db2st2/u01/oradata/etsdb','/db2/u01/oradata/ettdb', '/db2st2/u02/oradata/etsdb','/db2/u02/oradata/ettdb' scope=spfile;

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

Configure the listener in standby and put the following tns entries in tnsnames.ora:

 ettdb =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1523))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = ettdb )
     )
   )
  
 etsdb =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = etsdb )
     )
   )

Confirm that tnsping ettdb and tnsping etsdb work on both primary and standby sites

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.

orapwetsdb ----> orapwettdb

b. Create a initialization parameter with only one parameter DB_NAME.

  
 -- initettdb.ora 
 DB_NAME=etsdb
 DB_UNIQUE_NAME=ettdb
 DB_BLOCK_SIZE=8192

c. Create the necessary directories in the standby location to place the datafiles and the trace files in the $ADR_HOME.

d. Set the environment variable ORACLE_SID to the standby service and start the standby-instance.

 % export ORACLE_SID=ettdb
 % sqlplus "/ as sysdba"
 SQL> startup nomount pfile=$ORACLE_HOME/dbs/initettdb.ora

e. Verify if the connection 'AS SYSDBA' is working
  sqlplus sys@ettdb as sysdba
  sqlplus sys@etsdb as sysdba

f. On the primary system invoke the RMAN executable and connect to the primary and the auxiliary database ( i.e., the standby)

$ rman target sys/xxx@etsdb auxiliary sys/xxx@ettdb cmdfile=dup.rmn

content of dup.rmn :

duplicate target database for standby from active database
spfile
parameter_value_convert 'etsdb','ettdb'
set db_unique_name='ettdb'
set db_file_name_convert='/db2st2/u01/oradata/etsdb','/db2/u01/oradata/ettdb','/db2st2','/db2'
set log_file_name_convert='/db2st2/u01/oradata/etsdb','/db2/u01/oradata/ettdb', '/db2st2/u02/oradata/etsdb','/db2/u02/oradata/ettdb'
set control_files='/db2/u01/oradata/ettdb/control01.ctl'
set log_archive_dest_1 =  'LOCATION=/db2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ettdb' 
set log_archive_max_processes='5'
set fal_client='ettdb'
set fal_server='etsdb'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(etsdb,ettdb)'
set log_archive_dest_2='service=etsdb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=etsdb'
;

4. Connect to standby using SQL*Plus and start the MRP ( Managed Recovery Process)

-- mount db first if required

alter database recover managed standby database disconnect from session;

5. If licensed and want to use Active Dataguard (ADG), than open the Standby Database in READ ONLY and start the revoer

Enable Active Data Guard using SQL Plus :

SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> alter database recover managed standby database disconnect using current logfile;

-- verify
SQL> select name, db_unique_name, database_role, open_mode from v$database;
 
NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ETSDB     ettdb                          PHYSICAL STANDBY READ ONLY WITH APPLY

testing to confirm that standby db can be read and meanwhile redo logs are being applied:

in standby

create table dbaets.t as select * from all_objects
                                       *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
 


in primary

  1* select thread#, max(sequence#) from v$log_history group by thread#
SQL> /
 
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1           1057
 
SQL> create table dbaets.t as select * from all_objects;
 
Table created.
 
SQL> alter system archive log current;
 
System altered.
 
SQL> select thread#, max(sequence#) from v$log_history group by thread#
  2  ;
 
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1           1058


in standby:



SQL> select thread#, max(sequence#) from v$log_history group by thread#;
 
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1           1058


SQL> select count(*) from dbaets.t;
 
  COUNT(*)
----------
     17855

References:

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

RMAN duplicate: http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV298

Installation of Oracle Big Data Lite Virtual Machine on My New Desktop PC

I bought a new desktop PC - Lenovo ThinkCentre M83 with 32 GB RAM and 64-bit Windows 7. The intention is to use it as a learning environment for something like Oracle database 12c as I know it requires 64-bit OS, which I did not have at home previously. While trying to find an Oracle 12c virtual machine to install, I came across the Oracle Big Data Lite Virtual Machine page. I am excited to see that this virtual machine really contains lots of good stuff I am interested in, including the Oracle 12c database. It could be a great learning tool. So I downloaded and installed the Oracle Virtual Box, and I downloaded the virtual machine files and imported it to the Virtual Box. However when powering on the machine, I got:
 

Failed to open a session for the virtual machine xxx

          VT-X is disabled in the BIOS
          (VERR_VMX_MSR_VMXON_DISABLED)

To fix this, I need to go to the vendor's website to download and install the latest BIOS driver and use a tool provided by the vendor called CFGWIN.exe to change Bios setting.
 

To flash the BIOS from operating system, I need to issue the following command:
 

c:\SWTOOLS\FLASH\FBJY74USA>flash.cmd

To capture the current BIOS settings into a file:

C:\SWTOOLS\FLASH\FBJW73USA>CFGWIN.exe /c /path:c:\settings.txt
File Path = "c:\settings.txt".
All items have been captured successfully!

To change a BIOS settings:

C:\SWTOOLS\FLASH\FBJW73USA>CFGWIN.exe /r /path:C:\Users\Denis\Documents\settings_vtx.txt

File Path = "C:\Users\Denis\Documents\settings_vtx.txt".
Restore all BIOS settings successfully!

The settings_vtx.txt contains one line:

Intel(R) Virtualization Technology,Enabled;[Optional:Disabled,Enabled]




Basically after change the bios parameter: Intel(R) Virtualization Technology to Enabled. I bypassed the first errror shown above. Now I got something different:

This kernal requires an x86-64 CPU, but only detected an i686 CPU.

To fix this, I change the OS version from 32 bit to 64 bit in the virtual machine General tab.

Finally, I have the environment. I call it a day, happy ending. I was a little worried that I might end up unable to run the virtual machine on this new PC. Nowdays, it is easy to set up learning environment with pre-built virtual machines, just  so little time and so much to learn!

Update selected rows of a large table through rowid

We have a requirement to update a date column of about 14M rows in a large table of billions of rows .  The challenge is we have limited downtime to perform such a task.
In a Sun SPARC T4-2 machine with 128 virtual CPUs, we tested two different join methods with parallel executions:

-- nested loop took about 8h30
UPDATE TTQ_DQG.DQG_PRODUCT b
    SET billing_end_date = SYSDATE
    where exists (select /*+ parallel (a 12) */ 1 from DBSCRUB.ISOC_MAPPING_5M a where b.bo_id=a.old_bo_id);

-- hash join took about 5h
UPDATE /*+ parallel (b 12) */TTQ_DQG.DQG_PRODUCT b
    SET billing_end_date = SYSDATE
    where exists (select 1 from DBSCRUB.ISOC_MAPPING_5M a where b.bo_id=a.old_bo_id)
   ;

The 5h execution time still does not meet our downtime requirement. I proposed to perform the task in two steps.

(1) create a rowid driver table outside downtime window
(2) update the table with rowid in the downtime window

The test in the same machine shows we can do it in 41 min!
SQL>
SQL> --Step 1. Create a rowid driver table  several hours before downtime
SQL>
SQL> CREATE table  dbscrub.dqg_product_5m_rowid nologging
  2              AS
  3              select /*+ full(a) parallel( a 10) */ a.rowid rid from  TTQ_DQG.dqg_product a
  4              where exists ( select 1 from dbscrub.ISOC_MAPPING_5M b where a.bo_id=b.old_bo_id);
Table created.
Elapsed: 00:22:01.71
SQL>
SQL>
SQL>
SQL>
SQL> --Step 2 update table with rowid access in the downtime window
SQL>
SQL>
SQL> UPDATE  TTQ_DQG.CPF_PRODUCT b
  2  SET billing_end_date = SYSDATE
  3  where b.rowid in  (select /*+ parallel (a, 12) */  rid from DBSCRUB.dqg_product_5m_rowid a)
  4  ;
14431117 rows updated.
Elapsed: 00:41:34.28
SQL>
SQL>
SQL> spool off

Is this approach safe, any concerns? You may check this AskTom thread:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53140678334596

Move ASM database files from one diskgroup to another

In the past week, I have been involved in a migration of a standalone database to RAC with ASM through datab pump export and import.  After the migration was done, one of the ASM diskgroup is more than 98% percent used, which triggerred alerts from BMC Patrol, which is a standard monitoring tool in my working environment. Searching My Oracle Support website, I found the following note:

How to move ASM database files from one diskgroup to another ? (Doc ID 330103.1)


The note is pretty straight forword, however, there was a one suprise when I tried to offline a datafile :

ORA-01145: offline immediate disallowed unless media recovery enabled

That was because I put the db in noarchivelog mode in order to speed up the data pump import jobs. After I configured the archivelog mode, thing went fine.

Here are the steps I followed for future reference

1) Identify the data file to be moved

+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_01.dbf            NON_TTQ_CLB_MSG
2)  Identify the diskgroup on to which the file has to be moved.

+DATA_09/roeprdsc/datafile

3) Take the file offline.

 ALTER DATABASE DATAFILE '+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_01.dbf' OFFLINE;

4. Copy the data file

I used DBMS_FILE_TRANSFER package. We can also use RMAN, detail see the note.
     
          
create or replace directory orcl1 as '+DATA_01/roeprdsc/datafile';
        
create or replace directory orcl2 as '+DATA_09/roeprdsc/datafile';
      
 Note, if need to create a directory in a diskgroup, syntax looks like

 ASM> Alter disgroup asmdsk2 add directory  '+asmdsk2/test';        
       
BEGIN
 DBMS_FILE_TRANSFER.COPY_FILE(
 source_directory_object => 'ORCL1',
 source_file_name => 'non_ttq_clb_msg_01.dbf',
 destination_directory_object => 'ORCL2',
 destination_file_name => 'non_ttq_clb_msg_01.dbf');
END;                          
/


5) Rename the file to point to new location.

ALTER DATABASE RENAME FILE '+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_01.dbf' TO
 '+DATA_09/roeprdsc/datafile/non_ttq_clb_msg_01.dbf' ;

6) Recover the file.

  RECOVER DATAFILE '+DATA_09/roeprdsc/datafile/non_ttq_clb_msg_01.dbf'

7) Bring the file online.

 ALTER DATABASE DATAFILE '+DATA_09/roeprdsc/datafile/non_ttq_clb_msg_01.dbf' online;


8) Verify the new file location.
         
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES where file_name like '%clb_msg%';

FILE_NAME
--------------------------------------------------------------------------------
...
+DATA_09/roeprdsc/datafile/non_ttq_clb_msg_01.dbf
+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_02.dbf
+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_03.dbf
+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_04.dbf
+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_05.dbf
..



9) Delete the file from its original location either per SQLPLUS or per ASMCMD:

Syntax examples:

SQL:ASM> ALTER DISKGROUP ASMDSK2 DROP FILE users.256.565313879;

or:   ASMCMD> rm -rf 

Note:
Most Automatic Storage Management files do not need to be manually deleted because, as Oracle managed files, they are removed automatically when they are no longer needed. ( To not to have the alert come again, I have to do this)

Reference: Clean up SharePlex Queues

We use SharePlex replication as our DR solution for couple of applicatins. Last Saturday night, we did a DR test for one of the very important applications, but we could not make replication working from DR site to other target. As we ran out of time in the window, we just brought up application back to the production site without fixing issues. Today I involved vendor support to test DR configuration (with a dummy configuration) again to make sure that if real DR situation happens replication will work. I have learned that the key is that we should clean up orphan or corrupted queues before activating a configuration in DR. ( note: when we issue deactivate config , suppose all associated queues with this particular conifg will be gone, if not, those left queues need to be cleaned up).


Below are the steps to clean up SharePlex queues for future reference:


1. shutdown or shutdown force at source and target
2. qview -i
3. qview> qsetup
4. qview> qstatus
5. qview> deleteq p   -- for post
   qview> deleteq x   -- for export
6. On target:  truncate splex.shareplex_trans;