RMAN

Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.

From sqlplus:

shutdown immediate
startup mount
exit
From unix:

nid target=/
NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:

shutdown immediate
startup mount
alter database open resetlogs
/


Script to check the satus of Rman backup and recovery
set lines 130
set pages 60
column handle format a65
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK

select s.sid,s.serial#, p.spid ,module,s.status from v$session s,v$process p where s.paddr=p.addr and s.sid=&sid;



query for DR recovery
select THREAD#, SEQUENCE#, to_char (COMPLETION_TIME,'DD-MON-YYYY HH: MI: SS') from v$archived_log where 2615561921 between FIRST_CHANGE# and NEXT_CHANGE#;
also one can check to apply nearest firstchange# which matches scn number from command prompt using below query
SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP#


To find SCN
select COMPLETION_TIME,THREAD#,SEQUENCE#,first_change# first,next_change# next from rc_backup_redolog where trunc(completion_time) >= trunc(sysdate-1) order by 3,4,2;
15-OCT-06 1 27046 10064111842 10064523210
15-OCT-06 2 21661 10064505076 10064523222
15-OCT-06 3 22644 10064453457 10064523109


select DB_NAME,to_char(COMPLETION_TIME,'MM/DD/YYYY HH24:MI:SS'),THREAD#,SEQUENCE#,first_change# first,next_change# next from rc_backup_redolog where trunc(completion_time) >= trunc(sysdate-1) and DB_NAME='VIS' order by 3,4,2;


Rman recover command

rman catalog rmanprod/xxxxxi@rman target sys/xxx@prod auxiliary /
cmdfile='/porabackup/vis/scripts/duplicate_vis.rman' log='/porabackup/vis
/scripts/rman_duplicate.log'

Target tns entry eg
RMAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = demo.abc.local)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RMAN)
)
)
RMAN listener is running in source catalog db

run {

allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;

allocate AUXILIARY CHANNEL a1 type disk;
allocate AUXILIARY CHANNEL a2 type disk;
allocate AUXILIARY CHANNEL a3 type disk;
allocate AUXILIARY CHANNEL a4 type disk;
allocate AUXILIARY CHANNEL a5 type disk;
allocate AUXILIARY CHANNEL a6 type disk;
allocate AUXILIARY CHANNEL a7 type disk;
allocate AUXILIARY CHANNEL a8 type disk;

set newname for datafile 1 to '/pdata02/vis/system01.dbf';
set newname for datafile 2 to '/pdata02/vis/system02.dbf';
.
.
.
.set newname for tempfile 5 to '/pdata02/vis/temp05.dbf';
set newname for tempfile 6 to '/pdata02/vis/temp06.dbf';
set until scn 1141383124;
duplicate target database to 'vis'
LOGFILE
GROUP 1 ('/pdata03/vis/log1a.dbf',
'/pdata03/vis/log1b.dbf') SIZE 100M,
GROUP 2 ('/pdata03/vis/log2a.dbf',
'/pdata03/vis/log2b.dbf') SIZE 100M,
GROUP 3 ('/pdata03/vis/log3a.dbf',
'/pdata03/vis/log3b.dbf') SIZE 100M;
}



To add thread 2
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 4 (
'/pdata03/vis/log04a.dbf',
'/pdata03/vis/log04b.dbf'
) SIZE 100M REUSE,
GROUP 5 (
'/pdata03/vis/log05a.dbf',
'/pdata03/vis/log05b.dbf'
) SIZE 100M REUSE,
GROUP 6 (
'/pdata03/vis/log06a.dbf',
'/pdata03/vis/log06b.dbf'
) SIZE 100M REUSE;

alter database enable public thread 2;


An eg of RMAN script screation

To set new name for datafiles
set heading off
set pagesize 9999
spool ptch
select 'set newname for datafile ' || file# || ' to "' || name || '" ;' from v$datafile order by file#;
spool off


Execute the following script from target server

rman TARGET sys/xxx@PROD CATALOG rman/xxxxx@RMAN auxiliary / cmdfile=/mcdev1/app/orvis/rman_duplicate_vis.rman trace=/mcdev1/app/orvis/rman_duplicate_vis.log

PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = lenovo.abc.org)(PORT = 1541))
(CONNECT_DATA =
(SID = PROD)
)
)

RMAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host = demo.abc.org)(Port = 1541))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RMAN)
)
)

Make sure that date format is correct if you are using set until time clause. Export the NLS_DATE_FORMAT in the target server:

export
NLS_DATE_FORMAT=’DD-MON-YY:HH24:MI:SS’

echo $NLS_DATE_FORMAT

rman_duplicate_vis.rman
run{
allocate AUXILIARY CHANNEL c1 type disk;
allocate AUXILIARY CHANNEL c2 type disk;
allocate AUXILIARY CHANNEL c3 type disk;
allocate AUXILIARY CHANNEL c4 type disk;

set newname for datafile 1 to '/mcdev0/app/visoradata/data04/system01.dbf';
set newname for datafile 2 to '/mcdev0/app/visoradata/data04/system02.dbf';
set newname for datafile 3 to '/mcdev0/app/visoradata/data04/system03.dbf';
set newname for datafile 4 to '/mcdev0/app/visoradata/data04/system04.dbf';
set newname for datafile 5 to '/mcdev0/app/visoradata/data04/system05.dbf';
set newname for datafile 6 to '/mcdev0/app/visoradata/data05/ctxd01.dbf';
set newname for datafile 7 to '/mcdev0/app/visoradata/data05/owad01.dbf';
set newname for datafile 8 to '/mcdev0/app/visoradata/data05/abmd01.dbf';
set newname for datafile 9 to '/mcdev0/app/visoradata/data03/abmx01.dbf';
set newname for datafile 10 to '/mcdev0/app/visoradata/data05/akd01.dbf';
.
.
.
set newname for datafile 491 to '/mcdev0/app/visoradata/data05/apps_undots13.dbf';
set until scn 5968861770423;
duplicate target database to "vis"
logfile
group 1 ('/mcdev0/app/visoradata/data06/log1_1.dbf','/mcdev0/app/visoradata/data06/log2_1.dbf') size 500m,
group 2 ('/mcdev0/app/visoradata/data07/log1_2.dbf','/mcdev0/app/visoradata/data07/log2_2.dbf') size 500m;
}


To create catalog:
SQL> create user rmanprod identified by xxxxx default tablespace RMAN temporary tablespace TEMP
2 /

User created

SQL> grant connect, resource to RMANprod
2 /

Grant succeeded.

SQL> grant recovery_catalog_owner to RMANprod
2 /

Grant succeeded.

$ rman catalog rmanprod/xxxxxx

Recovery Manager: Release 10.2.0.3.0 - Production on Fri Oct 5 02:35:09 2007

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

connected to recovery catalog database


RMAN> create catalog

recovery catalog created


register the prod database by connecting as RMANprod. The command will be:
register database;


To find number of archives generated on each day
select 'Number of Archives generated on xxxxxx server: ' || to_char(COMPLETION_TIME,'DD-MON-YY'), count(*) from v$archived_log where to_char(COMPLETION_TIME,'DD-MON-YY')>=(sysdate-5) group by to_char(COMPLETION_TIME,'DD-MON-YY') order by 1;


To find archive timing
select trunc(to_date(COMPLETION_TIME),'DAY'),count(*) from v$archived_log where trunc(to_date(COMPLETION_TIME),'DAY')>=(sysdate-1) group by trunc(to_date(COMPLETION_TIME),'DAY') order by 1;
select count(*) from v$archived_log where COMPLETION_TIME>=(sysdate-1);

Use the below query to know the closest edologfile.
SELECT * FROM gv$archived_log ORDER BY completion_time;