Copy files from ASM diskgroup to remote ASM diskgroup.

ASMCMD> cp  +DATA_XDB4/SRCDB/PARAMETERFILE/spfile.3151.869812217  sys@10.112.39.14.+ASM2:+RECO_XDB5/TGTDB/test/spfile_sk.ora  --port 2483
Enter password: *******

10.112.39.14=remote host IP make sure this is the IP where +ASM2 listener listening to.
+ASM2= remote ASM instance

--port 2483 = port number




To debug
export DBI_TRACE=1

then

ASMCMD> cp  +DATA_XDB4/SRCDB/PARAMETERFILE/spfile.3151.869812217  sys@10.112.39.14.+ASM2:+RECO_XDB5/TGTDB/test/spfile_sk.ora  --port 2483
Enter password: *******

Shell script to read list of databases from a file.

while read line;
do
DBNAME=`echo $line |awk {'print $1'}`
TBS_THRESHOLD=`echo $line |awk {'print $2'}`
DBA=`echo $line |awk {'print $3'}`
RMANCAT=`echo $line |awk {'print $4'}`
INSTANCES=`echo $line |awk {'print $5'}`
. /orahome/work/sk/dbmon.sh $DBNAME
. /orahome/work/sk/tbsmon.sh $DBNAME $TBS_THRESHOLD
. /orahome/work/sk/dbimon.sh $DBNAME
done < /orahome/work/sk/Listofdatabases.txt

Above script will execute following for every db listed in /orahome/work/sk/Listofdatabases.txt

. /orahome/work/sk/dbmon.sh $DBNAME
. /orahome/work/sk/tbsmon.sh $DBNAME $TBS_THRESHOLD
. /orahome/work/sk/dbimon.sh $DBNAME

 <@dbax1:/orahome/work/sk>cat Listofdatabases.txt
db1 15 SK rmandbq 2
db2 15 SK rmandbq 2
db3 15 SK rmandbq 2
db4 15 SK rmandbq 2

db1,db2..are database names in tnsnames.ora file.

RMAN restore of few tablespaces in DEV from backup of Prod.

Create a dummy temporary instance DEVDB and restore the required tablespaces skipping all other tablespaces not required(DO NOT skip system,SYSAUX,TEMP,UNDO) during restore it will check dependencies of tablespace.

copy init parameter from existing DEVDB1 and create DEVDB dummy instance and change parameters accordingly to the required locations.

*.DB_FILE_NAME_CONVERT='/oradata/PRODDB/','/orabackup/DEVDB'
*.LOG_FILE_NAME_CONVERT='/oralogs/PRODDB/','/oralogs/DEVDB/'
*.db_create_file_dest='/orabackup/DEVDB/'
*.DB_CREATE_ONLINE_LOG_DEST_1='/orabackup/DEVDB/'
*.DB_CREATE_ONLINE_LOG_DEST_2='/orabackup/DEVDB/'
 *.control_files='/orabackup/DEVDB/control01.ctl','/orabackup/DEVDB/control02.ctl'
*.db_name='DEVDB'

Add tns entries in dev server for  rmancatalogdb,DEVDB,productionDB
Add listener.ora entries for dummy DB and reload.

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (GLOBAL_DBNAME = DEVDB)
      (ORACLE_HOME = /orahome/oracle/product/11.2.0.3/dbhome_1)
      (SID_NAME = DEVDB)
    )
  )


This restore is from BACKUP_HOST=DDBOOSTSERVER



DEVDB is a dummy temp instance

$sqlplus "/ as sysdba"
SQL> startup nomount
  
$rman log=rmanout.log

connect catalog rman@rmancatalogdb
connect auxiliary sys@DEVDB
connect target sys@productionDB
run
    {
    set UNTIL TIME "to_date('02/06/2015 06:18:00','MM/DD/YYYY HH24:MI:SS')";
    allocate AUXILIARY CHANNEL c1 DEVICE TYPE 'SBT_TAPE' connect sys@DEVDB  PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/orahome/oracle/product/11.2.0.3/dbhome_1/lib/libddobk.so,ENV=(STORAGE_UNIT=Oracle_Production,BACKUP_HOST=DDBOOSTSERVER,ORACLE_HOME=/orahome/oracle/product/11.2.0.3/dbhome_1)';
    allocate AUXILIARY CHANNEL c2 DEVICE TYPE 'SBT_TAPE' connect sys@DEVDB  PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/orahome/oracle/product/11.2.0.3/dbhome_1/lib/libddobk.so,ENV=(STORAGE_UNIT=Oracle_Production,BACKUP_HOST=DDBOOSTSERVER,ORACLE_HOME=/orahome/oracle/product/11.2.0.3/dbhome_1)';
    allocate AUXILIARY CHANNEL c3 DEVICE TYPE 'SBT_TAPE' connect sys@DEVDB  PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/orahome/oracle/product/11.2.0.3/dbhome_1/lib/libddobk.so,ENV=(STORAGE_UNIT=Oracle_Production,BACKUP_HOST=DDBOOSTSERVER,ORACLE_HOME=/orahome/oracle/product/11.2.0.3/dbhome_1)';
    allocate AUXILIARY CHANNEL c4 DEVICE TYPE 'SBT_TAPE' connect sys@DEVDB  PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/orahome/oracle/product/11.2.0.3/dbhome_1/lib/libddobk.so,ENV=(STORAGE_UNIT=Oracle_Production,BACKUP_HOST=DDBOOSTSERVER,ORACLE_HOME=/orahome/oracle/product/11.2.0.3/dbhome_1)';
DUPLICATE TARGET DATABASE TO DEVDB skip tablespace     GSGCAPPBKP_DATA,GSGCAPPBKP_INDX,GSGCPUB_DATA,GSGCPUB_INDX,GSIRAPP_DATA,GSIRAPP_INDX,USERS;
}


If this is from DISK then use following.


connect catalog rman@rmancatalogdb
connect auxiliary sys@DEVDB
connect target sys@productionDB
run
    {
    set UNTIL TIME "to_date('02/06/2015 06:18:00','MM/DD/YYYY HH24:MI:SS')";
    allocate AUXILIARY CHANNEL c1 DEVICE TYPE DISK;
    allocate AUXILIARY CHANNEL c2 DEVICE TYPE DISK;
    allocate AUXILIARY CHANNEL c3 DEVICE TYPE DISK;
    allocate AUXILIARY CHANNEL c4 DEVICE TYPE DISK;
DUPLICATE TARGET DATABASE TO DEVDB skip tablespace     GSGCAPPBKP_DATA,GSGCAPPBKP_INDX,GSGCPUB_DATA,GSGCPUB_INDX,GSIRAPP_DATA,GSIRAPP_INDX,USERS;
}


then export the schema from dummy temp DEVDB and import into DEVDB1

Key command:

DUPLICATE TARGET DATABASE TO DEVDB skip tablespace     GSGCAPPBKP_DATA,GSGCAPPBKP_INDX,GSGCPUB_DATA,GSGCPUB_INDX,GSIRAPP_DATA,GSIRAPP_INDX,USERS;

Oracle Database restore from backup using Duplicate RMAN.

Assumptions

There is a full backup level0 with control file and spfile in +DATA/ocdb/rmandata, it can be any location not necessarily ASM

Restoring backup onto database ocdb, db_name=ocdb
Backup belongs to OCDB database.
Incarnation is set to correct one.

STEP1

Startup new instance with following



db_name=ocdb
CONTROL_FILES='+DATA','+RECO'
db_create_file_dest='+DATA'
DB_CREATE_ONLINE_LOG_DEST_1='+RECO'
DB_CREATE_ONLINE_LOG_DEST_2='+RECO'
DB_RECOVERY_FILE_DEST='+RECO'


startup nomount pfile='/tmp/init.ora'




STEP2

Run following to restore database untill time from old backup.

connect auxiliary  /
run
 {
 ALLOCATE AUXILIARY CHANNEL ch1 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch2 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch3 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch4 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch5 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch6 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch7 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch8 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch9 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch10 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch11 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch12 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch13 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch14 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch15 DEVICE TYPE disk;
 ALLOCATE AUXILIARY CHANNEL ch16 DEVICE TYPE disk;
 DUPLICATE DATABASE TO ocdb
 BACKUP LOCATION '+DATA/ocdb/rmandata'
  until time "to_date ('15-10-2014 21:00:00', 'DD-MM-YYYY HH24:MI:SS')"
  NOFILENAMECHECK;
}

Active Duplicate using RMAN.

Two big disadvantages of the ACTIVE database duplication method are:

    Negative performance impact on the source database.
    High network traffic on the connection between the source and target databases.



STEP1

On TARGET database server.

change following parameters in init parameter file specific to target database

alter system set db_name=targetdb scope=spfile;
alter system set cluster_database=false scope=spfile;
alter system set db_create_file_dest='+DATA';
alter system set db_create_online_log_dest_1='+REDO';
alter system set db_create_online_log_dest_2='+REDO';


startup nomount pfile='/tmp/init.ora'

STEP2

On TARGET database server,change listener.ora make sure ORACLE_HOME is there in the file.

SID_LIST_LISTENER =
  (SID_LIST =
    )
    (SID_DESC =
      (SID_NAME = targetdb1)
      (ORACLE_HOME = /apps/oracle/product/11.2.0/db_1)
      (GLOBAL_DBNAME = targetdb)
    )
  )

and do lsnrctl status, lsnrctl reload


STEP3

Add following entries to both tnsnames.ora files on both SOURCE and TARGET

SOURCE =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sourceserv)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = source)
      )
    )
  
  target =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = targetserv)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = target)
      )
  )
 
   
 
STEP4

Create password file on both SOURCE and TARGET
If the password file already exists on source just copy it to TARGET $ORACLE_HOME/dbs


STEP5

Test connectivity to both SOURCE and TARGET from both servers using TNS entries created in STEP3 and
make sure it works


sqlplus sys/sys@target as sysdba
sqlplus sys/sys@source as sysdba



STEP6

On Auxillary host(TARGET) start RMAN and run DUPLICATE

Here very important auxillary connection is TARGET and connection target is SOURCE



$export ORACLE_SID=HRPRD1

$rman target sys/sys@SOURCE auxiliary sys/sys@TARGET
RMAN>run{
         DUPLICATE target DATABASE TO TARGET------> (the first target is the connection target and the second is db name of target database.)
         FROM ACTIVE DATABASE;
        }

View data in a datafile.

SQL> create tablespace testts datafile '/tmp/testts_01.dbf' size 1M;

SQL> create table mythbuster1 (col1 varchar2(200)) tablespace testts;

Insert a row:

SQL> insert into mythbuster1 values (‘ORIGINAL_VALUE’);
SQL> Commit; 

$ strings /tmp/testts_01.dbf
}|{z
-N?pD112D2
TESTTS
 1j)
 w>!
ORIGINAL_VALUE
 

Wait event.

The server process then identifies the block the row exists in. After 
the database instance just came up the buffer cache is empty and the 
block will not be found. Therefore the server process issues a read call
 from the datafile for that specific block. The block is read from the 
disk to the buffer cache.

Until the loading of the block from the disk 
to the buffer cache is complete, 

the session waits with the event – db file scattered read. 

Two Task Architecture 

 There are two tasks – the 
user task that a regular user has written and the server task that 
performs the database operations. This is an important concept 
established during the early foundations of the Oracle database to 
protect the database from errant code in the user task introduced either
 maliciously or inadvertently.
 

usertask=user processes=Java program, a Pro*C code, SQL*Plus process

server task=server processes=oracleinstance 

Oracle online redologs resize with Oracle Dataguard in place.

Step1

ON PRIMARY

check primary

select status,instance_name,database_role from v$database,v$instance;  

check current size of online redo

select THREAD# ,group#,bytes/1024/1024 from v$log ;

check current size of standby redo log

select THREAD# ,group#,bytes/1024/1024 from v$standby_log ;

Step2

ON STANDBY


check standby


select status,instance_name,database_role from v$database,v$Instance;


check current size of online redo logs


select THREAD# ,group#,bytes/1024/1024 from v$log ;

check standby redlo log size.

select THREAD# ,group#,bytes/1024/1024 from v$standby_log  ;


Step3
on standby

SET standby_file_management  to manual

show parameter standby_file_management


alter system set standby_file_management=manual sid='*' scope=both;


show parameter standby_file_management



Step4

ON PRIMARY


On the primary database, check the status of the Online Redo Logs and resize them by dropping the INACTIVE redo logs and re-creating them with the new size.

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 50 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 51 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 52 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 53 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;


ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 54 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 55 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 56 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 57 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;


ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 58 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 59 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 60 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 61 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

alter database drop logfile group 4;

alter database drop logfile group 5;

alter database drop logfile group 6;

alter database drop logfile group 7;


Step5

ON STANDBY

cancel recovery


set dg broker =flase before cancel recovery

alter database recover managed standby database cancel;



select THREAD# ,group#,bytes/1024/1024 from v$log ;



ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 50 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 51 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 52 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 53 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;




ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 54 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 55 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 56 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 57 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;


ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 58 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 59 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 60 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 61 ('+DATA1(ONLINELOG)', '+FRA(ONLINELOG)') SIZE 500M;


alter database drop logfile group 1;


Step6

Verify online redolog size on primary and dr then do following on DR

alter system set standby_file_management=auto sid='*' scope=both;




ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


set dg broker =true