db cloning hist

Server Configuartion:

　

HRIS DB:

　

EBS DB:

　

　

fixed line number:

　

　

DB cloning:

otjtghrp->testhris

　

--------------------------------

HRIS PROD:

TJTGHRP (DBID=134672065)

　

SQL> show parameter spfile;

/TJTGHRP/app/otjtghrp/product/11.2.0/dbhome_1/dbs/spfileTJTGHRP.ora

　

SQL> create pfile from spfile;

ls -lrt

-rw-r--r-- 1 otjtghrp dba 1203 Feb 26 11:22 initTJTGHRP.ora

　

SQL> archive log list;

Archive destination /TJTGHRP/app/otjtghrp/archivelogs (2.8gb)

Current log sequence 96425

　

/TJPSB004_BACKUP/TJTGHRP/DB/

user:hrist/hrist

/home/hrist/filehris

　

finished piece 1 at 26-FEB-2018 07:07:15 piece

handle=/TJPSB004_BACKUP/TJTGHRP/DB/TJTGHRP_Control_file969088033_16002_1 tag=CF1

comment=NONE channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 26-FEB-2018 07:07:15

　

----------------------------------------------------------------

BASH PROFILE SETUP IN HRIS:

[otjtghrp@tjpsb004 ~]$ cat .bash_profile

# .bash_profile

　

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

　

# User specific environment and startup programs

　

PATH=$PATH:$HOME/bin

　

export PATH

　

　

export ORACLE_HOME=/TJTGHRP/app/otjtghrp/product/11.2.0/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE=/TJTGHRP/app/otjtghrp

export ORACLE_SID=TJTGHRP

----------------------------------------------------------------

----------------------------------------------------------------

　

　

　

-rw-r----- 1 otjtghrp dba 1982464 Feb 26 07:00 TJTGHRP_969087606_15996_1

-rw-r----- 1 otjtghrp dba 98304 Feb 26 07:00 TJTGHRP_969087608_15997_1

-rw-r----- 1 otjtghrp dba 160784384 Feb 26 07:00 TJTGHRP_969087606_15995_1

-rw-r----- 1 otjtghrp dba 1240023040 Feb 26 07:05 TJTGHRP_969087606_15994_1

-rw-r----- 1 otjtghrp dba 1671372800 Feb 26 07:06 TJTGHRP_969087606_15993_1

-rw-r----- 1 otjtghrp dba 101066752 Feb 26 07:07 TJTGHRP_Arch_Log969088007_16001_1

-rw-r----- 1 otjtghrp dba 115075072 Feb 26 07:07 TJTGHRP_Arch_Log969088006_15999_1

-rw-r----- 1 otjtghrp dba 111539200 Feb 26 07:07 TJTGHRP_Arch_Log969088006_15998_1

-rw-r----- 1 otjtghrp dba 139741696 Feb 26 07:07 TJTGHRP_Arch_Log969088007_16000_1

-rw-r----- 1 otjtghrp dba 17006592 Feb 26 07:07 TJTGHRP_Control_file969088033_16002_1

　

scp -r TJTGHRP_Control_file969088033_16002_1 hrist@172.16.2.65:/home/hrist/filehris/

　

[otjtghrp@tjpsb004 DB]$ scp -r TJTGHRP_Arch_Log969088007_16000_1 TJTGHRP_Arch_Log969088006_15998_1 TJTGHRP_Arch_Log969088006_15999_1 TJTGHRP_Arch_Log969088007_16001_1 TJTGHRP_969087606_15993_1 TJTGHRP_969087606_15994_1 TJTGHRP_969087606_15995_1 TJTGHRP_969087608_15997_1 TJTGHRP_969087606_15996_1 hrist@172.16.2.65:/home/hrist/filehris/

hrist@172.16.2.65's password:

TJTGHRP_Arch_Log969088007_16000_1 100% 133MB 133.3MB/s 00:01

TJTGHRP_Arch_Log969088006_15998_1 100% 106MB 106.4MB/s 00:01

TJTGHRP_Arch_Log969088006_15999_1 100% 110MB 109.7MB/s 00:01

TJTGHRP_Arch_Log969088007_16001_1 100% 96MB 96.4MB/s 00:01

TJTGHRP_969087606_15993_1 100% 1594MB 113.9MB/s 00:14

TJTGHRP_969087606_15994_1 100% 1183MB 107.5MB/s 00:11

TJTGHRP_969087606_15995_1 100% 153MB 153.3MB/s 00:01

TJTGHRP_969087608_15997_1 100% 96KB 96.0KB/s 00:00

TJTGHRP_969087606_15996_1

　

　

　

FILES NEED TO MOVE:

1.PASSWORD FILE

2.PFILE

3.ALL ARCHIVE FILE

4.RMAN FILE

　

　

================

SETUP NI HRIST;

================

--------------------------------------------------------------------------

BASH PROFILE SETUP IN HRIST:

export PATH

export ORACLE_HOME=/TJTGHRP/app/otjtghrp/product/11.2.0/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE=/TJTGHRP/app/otjtghrp

export ORACLE_SID=HRIST

　

--------------------------------------------------------------------------

　

FROM PROD PARAMETER FILE;

　

TJTGHRP.__db_cache_size=910612736

TJTGHRP.__java_pool_size=16777216

TJTGHRP.__large_pool_size=33554432

TJTGHRP.__oracle_base='/TJTGHRP/app/otjtghrp'#ORACLE_BASE set from environment

TJTGHRP.__pga_aggregate_target=2294967296

TJTGHRP.__sga_target=2294967296

TJTGHRP.__shared_io_pool_size=0

TJTGHRP.__shared_pool_size=1566914048

TJTGHRP.__streams_pool_size=33554432

*.audit_file_dest='/TJTGHRP/app/otjtghrp/admin/TJTGHRP/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/TJTGHRP/app/otjtghrp/oradata/TJTGHRP/control01.ctl','/TJTGHRP/app/otjtghrp/fast_recovery_area/TJTGHRP/control02.ctl'

*.cpu_count=4

*.db_block_size=8192

*.db_domain=''

*.db_name='TJTGHRP'

*.db_recovery_file_dest='/TJTGHRP/app/otjtghrp/fast_recovery_area'

*.db_recovery_file_dest_size=10737418240

*.diagnostic_dest='/TJTGHRP/app/otjtghrp'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=TJTGHRPXDB)'

*.local_listener='LISTENER_TJTGHRP'

*.log_archive_dest=''

*.log_archive_dest_1='LOCATION=/TJTGHRP/app/otjtghrp/archivelogs'

*.open_cursors=300

*.pga_aggregate_target=2294967296

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.resource_manager_plan='DEFAULT_PLAN'

*.sga_target=2294967296

*.undo_tablespace='UNDOTBS1'

　

TO HRIST PARAMETER FILE;

　

TJTGHRP.__db_cache_size=910612736

TJTGHRP.__java_pool_size=16777216

TJTGHRP.__large_pool_size=33554432

TJTGHRP.__oracle_base='/GSTDB/app/hrist/'#ORACLE_BASE set from environment

TJTGHRP.__pga_aggregate_target=2294967296

TJTGHRP.__sga_target=2294967296

TJTGHRP.__shared_io_pool_size=0

TJTGHRP.__shared_pool_size=1566914048

TJTGHRP.__streams_pool_size=33554432

*.audit_file_dest='/GSTDB/app/hrist/admin/TJTGHRP/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/GSTDB/app/hrist/oradata/TJTGHRP/control01.ctl','/GSTDB/app/hrist/fast_recovery_area/TJTGHRP/control02.ctl'

*.cpu_count=4

*.db_block_size=8192

*.db_domain=''

*.db_name='HRIST'

*.db_recovery_file_dest='/GSTDB/app/hrist/fast_recovery_area'

*.db_recovery_file_dest_size=10737418240

*.diagnostic_dest='/GSTDB/app/hrist/'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=HRISTXDB)'

*.local_listener='LISTENER_HRIST'

*.log_archive_dest=''

*.log_archive_dest_1='LOCATION=/GSTDB/app/hrist/archivelogs'

*.open_cursors=300

*.pga_aggregate_target=2294967296

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.resource_manager_plan='DEFAULT_PLAN'

*.sga_target=2294967296

*.undo_tablespace='UNDOTBS1'

　

　

　

1610612736 byte

1610612.736 kb

1610.612736 mb

　

export PATH

. /TESTDB/app/oradev/db/11.2.0/DEV_tjpsb007.env

/TESTDB/app/oradev/db/11.2.0

　

ORAINST.LOC IS :$ORACLE_HOME

Orainventory locatioin under it.

　

inventory_loc=/TESTDB/app/oradev/db/11.2.0/admin/oui/DEV_tjpsb007/oraInventory

　

　

vnc password:

vncpasswd

redhat



　

dump :

/GSTDB/app/hrist/diag/rdbms/hrist/HRIST/trace

　

set dbid 134672065;

restore controlfile from '/u01/backup/CT_4bkisua9_1_1';

　

restore controlfile from '/home/hrist/filehris/rmanbkp/TJTGHRP_Control_file969088033_16002_1';

same sessioin:

sql "alter database mount";

　

TJTGHRP_FULL_BACKUP

　

list backup TAG='TJTGHRP_FULL_BACKUP';

crosscheck backup TAG='TJTGHRP_FULL_BACKUP';

restore database from TAG='TJTGHRP_FULL_BACKUP';

　

RMAN-06023: no backup or copy of datafile 5 found to restore

list backup of datafile 5,6,7,8 summary;

　

restore database until time "TO_DATE('2018-FEB-25 19:54:30','YYYY-MON-DD HH24:MI:SS')" preview summary;

Recovery must be done beyond SCN 625787 to clear datafile fuzziness

　

　

　

　

catalog start with '/home/hrist/filehris/rmanbkp/';

YES

list backup of database;

　

　

RMAN> run

2> {

3> allocate channel c1 device type disk;

4> SET NEWNAME FOR DATAFILE 1 TO '/oracle/u02/oradata/system/otp/system01.dbf';

5> SET NEWNAME FOR DATAFILE 2 TO '/oracle/u02/oradata/data02/otp/users02.dbf';

6> SET NEWNAME FOR DATAFILE 3 TO '/oracle/u02/oradata/system/otp/sysaux01.dbf';

7> SET NEWNAME FOR DATAFILE 4 TO '/oracle/u02/oradata/data02/otp/users01.dbf';

8> SET NEWNAME FOR DATAFILE 5 TO '/oracle/u02/oradata/index/otp/tools01.dbf';

9> SET NEWNAME FOR DATAFILE 6 TO '/oracle/u02/oradata/data02/otp/users03.dbf';

10> SET NEWNAME FOR DATAFILE 7 TO '/oracle/u02/oradata/system/otp/undotbs01.dbf';

11> SET NEWNAME FOR DATAFILE 8 TO '/oracle/u02/oradata/system/otp/cluser_tbs01.dbf';

12>

13> RESTORE DATABASE;

14> SWITCH DATAFILE ALL;

15> RECOVER DATABASE;

16> }

　

=======================================

RMAN>

run

{

allocate channel c1 device type disk;

SET NEWNAME FOR DATAFILE 1 TO '/GSTDB/app/hrist/oradata/TJTGHRP/system01.dbf';

SET NEWNAME FOR DATAFILE 2 TO '/GSTDB/app/hrist/oradata/TJTGHRP/sysaux01.dbf';

SET NEWNAME FOR DATAFILE 3 TO '/GSTDB/app/hrist/oradata/TJTGHRP/undotbs01.dbf';

SET NEWNAME FOR DATAFILE 4 TO '/GSTDB/app/hrist/oradata/TJTGHRP/users01.dbf';

SET NEWNAME FOR DATAFILE 5 TO '/GSTDB/app/hrist/oradata/TJTGHRP/tjtghrp_data01.dbf';

　

RESTORE DATABASE;

SWITCH DATAFILE ALL;

RECOVER DATABASE;

}

　

　

　

scp -r TJTGHRP_Control_file969088033_16002_1 hrist@172.16.2.65:/home/hrist/filehris/

/GSTDB/app/hrist/archivelogs/1_96394_906911169.dbf thread=1 sequence=96394

　

scp -r * hrist@172.16.2.65:/GSTDB/app/hrist/archivelogs/

11gb

　

　

run{

ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';

restore archivelog from logseq 665 until logseq 671;

release channel ch00;

}

　

　

run{

allocate channel c1 device type disk;

restore archivelog from logseq 96664 until logseq 671;

release channel c1;

}

　

　

list backupset;

sequence 96664

　

If I want to restore 59593 alone then is this format correct ..

--------------------------------------------------------------------

run {

　

allocate channel t1 type 'sbt_tape';

set archivelog destination to '/disk1/oracle/temp_restore';

restore archivelog logseq 59593 ;

release channel t1;

}

　

　

　

If I want to restore 59593 alone then is this format correct ..

　

run {

　

allocate channel c1 device type disk;

set archivelog destination to '/GSTDB/app/hrist/archivelogs/';

restore archivelog logseq 96664;

release channel c1;

}

　

RMAN-03002: failure of restore command at 02/27/2018 09:17:53

RMAN-20242: specification does not match any archived log in the repository

　

　

　

restore archivelog from logseq = 96664 until logseq = 96664;

　

　

　

ERROR;

TNS-01189: The listener could not authenticate the user

att_tbltransactionsummary