script to check who is the owner of the schema in Redshift
select n.nspname, u.usename as owner from pg_catalog.pg_namespace n, pg_user u where n.nspname='schema4' and n.nspowner=u.usesysid;
Tuesday, November 30, 2021
script to check default privilege's Applied to schema in Redshift database?
when we provide access to Redshift schemas objects, we need to consider providing the access to future objects as well,so we need to grant "alter default privilege" for considering for future object.
Here is the script will check if the schema/user granted default privilege's for future object :
select pg_get_userbyid(d.defacluser) as user,
n.nspname as schema,
case d.defaclobjtype when 'r' then 'tables' when 'f' then 'functions' end
as object_type,
array_to_string(d.defaclacl, ' + ') as default_privileges
from pg_catalog.pg_default_acl d
left join pg_catalog.pg_namespace n on n.oid = d.defaclnamespace;
|user |schema |object_type|default_privileges |
|----------------------------|------------|-----------|-------------------------------------------------------------------|
|dbadmin_service12@domain.com|Appln_schema|tables |group OLAP1_redshift_readwrite_qa=arwd/"dbadmin_service@domain.com"|==> which means this user has update,select,insert,Delete privileges for future creating objects/tables
Monday, November 29, 2021
Steps to Migrate oracle 8i Database to AWS service
All,
we got requirement to migrate our legacy oracle 8i servers to AWS server,but AWS will not support for oracle 8i,so after analyzing we have got solution from a service provided called " Stromasys".
The concept is ,Stromasys will provide emulator for all system.we used old HP-unix on top of AWS Ec2 instance and problem solved and migrating the DB to AWS now simple.
Here are the list of steps that we followed
**********************************
Here is the high level steps for this DB migration
1 Prepare the target host with requested FS and Soft link
2 Ask backup team to do restore the specific DB
3 Crosscheck & compare the number of files with Prod & Target DB on target server.
4 Modify & create the directory according to DB structure
5 Create the ctl file trace on target server
6 Modify the pfile,config,DMplus files
7 Recreate the ctl file
8 Recover the DB using until cancel and do the cancel/scn based recovery
9 Open the DB with resetlogs
11 Do the sanity check and bring down the DB
12 Create the same FS and soft link on AWS server and verify
13 Start the file copy from target server to AWS server using SFTP (includes C,R,D ,Pfile,config,DMSQL files)
14 verify the no# of CRD files on AWS server
15 Modify & create the directory according to DB structure
16 Bring up the DB on AWS server
17 Validate object count and setup any cron job required on AWS server side
Detailed steps :
*************
ora8iDB
************
oracle 8i DB migration to AWS cloud
******************************************
Stromasys legacy server expert
User:oracle On <source_server_name>
SID:ora8iDB
/opt/oracle/product/8.1.7> sqlplus "/ as sysdba"
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Aug 4 05:50:12 2021
(c) Copyright 2000 Oracle Corporation. All rights reserved.
<Source_server>
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
ora8iDB READ WRITE
1.check the count CRD files count
select count(*) from dba_data_files;
data file --196
select count(member) from v$logfile;
log file--12
select count(name) from v$controlfile;
ctl file-3
Total files ==> 211
2.check the archivelog location & log seque
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/data/ora8iDB/arch/
Oldest online log sequence 281856
Next log sequence to archive 281861
Current log sequence 281861
SQL>
background_dump_dest string /opt/oracle/admin/ora8iDB/bdump
collect the CRD file detail
select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
/oracle/data/ora8iDB/data/disk01/ora8iDB_system_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_rbs01_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_temp_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_tools_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_users_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_trslus_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_trsd01_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_trsd02_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_trsd03_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_trsd04_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_trsx01_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_trsx02_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_trsx03_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_trsx04_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_testata_other_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_testata_other_ind_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_testata_eo_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_testata_eo_ind_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_testata_evt_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_testata_evt_ind_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_testata_ifd_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_testata_ifd_ind_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_testef_eo_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_testef_eo_ind_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_testef_evt_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_testef_evt_ind_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_testef_ifd_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_testef_ifd_ind_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_testef_other_ind_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_testef_other_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_slotd01_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_slotx01_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_gend01_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_gend02_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_genx01_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_genx02_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_rbs02_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_rbs03_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_rbs04_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_bakd01_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_bakd02_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_bakd03_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_bakd04_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_bakx01_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_bakx02_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_bakx03_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_bakx04_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_inv_act_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_inv_act_ind_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_ship_hist_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_ship_hist_ind_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_ord_act_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_ord_act_ind_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_lpn_dtl_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_lpn_dtl_ind_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_lpn_loc_01.dbf
/oracle/data/ora8iDB/data/disk02/ora8iDB_lpn_loc_ind_01.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_testata_ifd_02.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_inv_act_ind_02.dbf
/oracle/data/ora8iDB/data/disk01/ora8iDB_inv_act_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_ship_hist_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_ship_hist_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_bakx02_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_bakd02_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_system_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_temp_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_bakx03_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_bakx04_01.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_eo_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_rbs01_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_rbs02_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_trsx03_03
/oracle/data/ora8iDB/data/disk03/ora8iDB_trslus_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_bakd01_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_bakx01_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_other_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_genx01_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_other_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_bakd04_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_evt_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_tools_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_users_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_trsd01_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_trsd02_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_trsd03_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_trsd04_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_trsx01_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_trsx02_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_trsx04_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_eo_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_evt_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_ifd_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_ifd_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_eo_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_eo_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_evt_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_evt_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_ifd_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_ifd_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_other_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_other_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_slotd01_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_slotx01_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_gend01_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_gend02_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_genx02_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_rbs03_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_rbs04_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_bakd03_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_ord_act_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_ord_act_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_dtl_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_dtl_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_loc_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_loc_ind_02.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_ifd_03.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_ind_03.dbf
/oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_trslus_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_trsd01_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_trsd02_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_trsd03_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_trsd04_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_trsx01_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_trsx02_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_trsx03_04.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_trsx04_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_gend01_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_gend02_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_genx01_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_genx02_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_bakd01_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_bakd02_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_bakd03_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_bakd04_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_bakx01_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_bakx02_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_bakx03_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_bakx04_02.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_inv_act_05.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_inv_act_ind_05.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_dtl_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_dtl_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_loc_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_loc_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_ord_act_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_ord_act_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_rbs01_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_rbs02_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_rbs03_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_rbs04_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_ship_hist_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_ship_hist_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_slotd01_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_slotx01_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_eo_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_eo_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_evt_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_evt_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_ifd_04.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_ifd_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_other_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_other_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_eo_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_eo_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_evt_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_evt_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_ifd_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_ifd_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_other_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_other_ind_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_system_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_temp_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_tools_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_users_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_slot_data_ts_01.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_slot_index_ts_01.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_01.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_04.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_06.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_02.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_03.dbf
/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_05.dbf
/ORACLE/ora8iDB/data5/ora8iDB_system_04.dbf
/oracle/data/ora8iDB/data/disk05/ora8iDB_testata_other_ind_04.dbf
/oracle/data/ora8iDB/data/disk05/ora8iDB_inv_act_ind_06.dbf
/oracle/data/ora8iDB/data/disk05/ora8iDB_ord_act_ind_04.dbf
/oracle/data/ora8iDB/data/disk05/ora8iDB_slot_data_ts_02.dbf
/oracle/data/ora8iDB/data/disk05/ora8iDB_bakx04_03.dbf
/oracle/data/ora8iDB/data/disk05/ora8iDB_bakd03_04.dbf
/ORACLE/ora8iDB/data5/perfstat.dbf
/oracle/data/ora8iDB/data/disk05/ora8iDB_system_05.dbf
/oracle/data/ora8iDB/data/disk06/audit_mgmt_1.dbf
/ORACLE/ora8iDB/data5/perfstat_02.dbf
196 rows selected.
select member from v$logfile;
/oracle/data/ora8iDB/redoA/ora8iDB_redo01_A.log
/oracle/data/ora8iDB/redoB/ora8iDB_redo01_B.log
/oracle/data/ora8iDB/redoB/ora8iDB_redo02_B.log
/oracle/data/ora8iDB/redoA/ora8iDB_redo02_A.log
/oracle/data/ora8iDB/redoA/ora8iDB_redo03_A.log
/oracle/data/ora8iDB/redoB/ora8iDB_redo03_B.log
/oracle/data/ora8iDB/redoB/ora8iDB_redo04_B.log
/oracle/data/ora8iDB/redoA/ora8iDB_redo04_A.log
/oracle/data/ora8iDB/redoA/ora8iDB_redo05_A.log
/oracle/data/ora8iDB/redoB/ora8iDB_redo05_B.log
/oracle/data/ora8iDB/redoB/ora8iDB_redo06_B.log
/oracle/data/ora8iDB/redoA/ora8iDB_redo06_A.log
12 rows selected.
select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
/oracle/data/ora8iDB/ctrl/disk01/ora8iDB_ctrl_01.ctl
/oracle/data/ora8iDB/ctrl/disk02/ora8iDB_ctrl_02.ctl
/oracle/data/ora8iDB/ctrl/disk01/ora8iDB_ctrl_03.ctl
3.add ls -ltr prefix the script
==> this used to validate the files on target server after copying from the source using the script.
*******************************
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_system_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_rbs01_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_temp_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_tools_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_users_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_trslus_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_trsd01_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_trsd02_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_trsd03_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_trsd04_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_trsx01_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_trsx02_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_trsx03_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_trsx04_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_testata_other_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_testata_other_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_testata_eo_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_testata_eo_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_testata_evt_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_testata_evt_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_testata_ifd_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_testata_ifd_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_testef_eo_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_testef_eo_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_testef_evt_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_testef_evt_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_testef_ifd_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_testef_ifd_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_testef_other_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_testef_other_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_slotd01_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_slotx01_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_gend01_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_gend02_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_genx01_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_genx02_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_rbs02_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_rbs03_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_rbs04_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_bakd01_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_bakd02_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_bakd03_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_bakd04_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_bakx01_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_bakx02_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_bakx03_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_bakx04_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_inv_act_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_inv_act_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_ship_hist_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_ship_hist_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_ord_act_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_ord_act_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_lpn_dtl_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_lpn_dtl_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_lpn_loc_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk02/ora8iDB_lpn_loc_ind_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_testata_ifd_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_inv_act_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk01/ora8iDB_inv_act_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_ship_hist_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_ship_hist_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_bakx02_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_bakd02_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_system_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_temp_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_bakx03_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_bakx04_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testata_eo_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_rbs01_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_rbs02_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_trsx03_03
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_trslus_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_bakd01_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_bakx01_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testata_other_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_genx01_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testata_other_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_bakd04_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testata_evt_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_tools_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_users_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_trsd01_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_trsd02_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_trsd03_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_trsd04_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_trsx01_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_trsx02_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_trsx04_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testata_eo_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testata_evt_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testata_ifd_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testata_ifd_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testef_eo_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testef_eo_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testef_evt_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testef_evt_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testef_ifd_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testef_ifd_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testef_other_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testef_other_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_slotd01_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_slotx01_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_gend01_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_gend02_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_genx02_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_rbs03_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_rbs04_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_bakd03_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_ord_act_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_ord_act_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_dtl_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_dtl_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_loc_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_loc_ind_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_testata_ifd_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_trslus_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_trsd01_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_trsd02_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_trsd03_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_trsd04_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_trsx01_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_trsx02_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_trsx03_04.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_trsx04_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_gend01_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_gend02_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_genx01_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_genx02_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_bakd01_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_bakd02_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_bakd03_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_bakd04_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_bakx01_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_bakx02_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_bakx03_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_bakx04_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_inv_act_05.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_inv_act_ind_05.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_dtl_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_dtl_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_loc_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_loc_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_ord_act_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_ord_act_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_rbs01_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_rbs02_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_rbs03_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_rbs04_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_ship_hist_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_ship_hist_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_slotd01_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_slotx01_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testata_eo_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testata_eo_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testata_evt_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testata_evt_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testata_ifd_04.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testata_ifd_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testata_other_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testata_other_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testef_eo_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testef_eo_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testef_evt_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testef_evt_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testef_ifd_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testef_ifd_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testef_other_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_testef_other_ind_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_system_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_temp_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_tools_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_users_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_slot_data_ts_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_slot_index_ts_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_01.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_04.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_06.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_05.dbf
ls -ltr /ORACLE/ora8iDB/data5/ora8iDB_system_04.dbf
ls -ltr /oracle/data/ora8iDB/data/disk05/ora8iDB_testata_other_ind_04.dbf
ls -ltr /oracle/data/ora8iDB/data/disk05/ora8iDB_inv_act_ind_06.dbf
ls -ltr /oracle/data/ora8iDB/data/disk05/ora8iDB_ord_act_ind_04.dbf
ls -ltr /oracle/data/ora8iDB/data/disk05/ora8iDB_slot_data_ts_02.dbf
ls -ltr /oracle/data/ora8iDB/data/disk05/ora8iDB_bakx04_03.dbf
ls -ltr /oracle/data/ora8iDB/data/disk05/ora8iDB_bakd03_04.dbf
ls -ltr /ORACLE/ora8iDB/data5/perfstat.dbf
ls -ltr /oracle/data/ora8iDB/data/disk05/ora8iDB_system_05.dbf
ls -ltr /oracle/data/ora8iDB/data/disk06/audit_mgmt_1.dbf
ls -ltr /ORACLE/ora8iDB/data5/perfstat_02.dbf
ls -ltr /oracle/data/ora8iDB/redoA/ora8iDB_redo01_A.log
ls -ltr /oracle/data/ora8iDB/redoB/ora8iDB_redo01_B.log
ls -ltr /oracle/data/ora8iDB/redoB/ora8iDB_redo02_B.log
ls -ltr /oracle/data/ora8iDB/redoA/ora8iDB_redo02_A.log
ls -ltr /oracle/data/ora8iDB/redoA/ora8iDB_redo03_A.log
ls -ltr /oracle/data/ora8iDB/redoB/ora8iDB_redo03_B.log
ls -ltr /oracle/data/ora8iDB/redoB/ora8iDB_redo04_B.log
ls -ltr /oracle/data/ora8iDB/redoA/ora8iDB_redo04_A.log
ls -ltr /oracle/data/ora8iDB/redoA/ora8iDB_redo05_A.log
ls -ltr /oracle/data/ora8iDB/redoB/ora8iDB_redo05_B.log
ls -ltr /oracle/data/ora8iDB/redoB/ora8iDB_redo06_B.log
ls -ltr /oracle/data/ora8iDB/redoA/ora8iDB_redo06_A.log
ls -ltr /oracle/data/ora8iDB/ctrl/disk01/ora8iDB_ctrl_01.ctl
ls -ltr /oracle/data/ora8iDB/ctrl/disk02/ora8iDB_ctrl_02.ctl
ls -ltr /oracle/data/ora8iDB/ctrl/disk01/ora8iDB_ctrl_03.ctl
4.modify the init.ora file and iffile and configtrslus.ora and config.ora create the respective directory on target server
User:oracle On <source_server_name>
SID:ora8iDB
/opt/oracle/admin/ora8iDB/pfile> cat initora8iDB.ora
5.edit the pfile and other 2 files according to old db structre
ifile = /opt/oracle/admin/ora8iDB/pfile/configora8iDB.ora
##############################################################################
# The following are set for DM Plus #
##############################################################################
ifile = /opt/oracle/admin/ora8iDB/pfile/configDmPlus.ora
**************************************
/opt/oracle/admin/ora8iDB/pfile> cat /opt/oracle/admin/ora8iDB/pfile/configora8iDB.ora
****************************************
6.startup the DB using the pfile with nomount and recreate the ctl file
"
7.take the ctl file trc from source and copy to <Target_server_name> server
ALTER DATABASE BACKUP CONTROLFILE TO TRACE-- will go to udump
8.edit the trc file and set the DB name and remove the logfile & ctl file on target location
create the controlfile trace:
********************************
9.create the directory and copy the files from source to target
mkdir -p /opt/oracle/admin/ora8iDB/pfile
mkdir -p /opt/oracle/admin/ora8iDB/bdump
mkdir -p /opt/oracle/admin/ora8iDB/udump
mkdir -p /opt/oracle/admin/ora8iDB/cdump
10.copy all CRD files from source to target server and verify if all file exist on target server and ake sure all the CRD files copied to target server.
/opt/oracle/admin/ora8iDB/pfile> wc -l files_from_prod.kshoutput.log files_from_prod.ksh
211 files_from_prod.kshoutput.log
211 files_from_prod.ksh
422 total
11.remove the ctl & redo log files from the target server before recreating the controlfile:
remove_ctl_redo_ora8iDB.ksh
CREATE CONTROLFILE SET DATABASE "ora8iDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 20
MAXLOGMEMBERS 2
MAXDATAFILES 1022
MAXINSTANCES 1
MAXLOGHISTORY 907
LOGFILE
GROUP 1 (
'/oracle/data/ora8iDB/redoA/ora8iDB_redo01_A.log',
'/oracle/data/ora8iDB/redoB/ora8iDB_redo01_B.log'
) SIZE 60M,
GROUP 2 (
'/oracle/data/ora8iDB/redoB/ora8iDB_redo02_B.log',
'/oracle/data/ora8iDB/redoA/ora8iDB_redo02_A.log'
) SIZE 60M,
GROUP 3 (
'/oracle/data/ora8iDB/redoA/ora8iDB_redo03_A.log',
'/oracle/data/ora8iDB/redoB/ora8iDB_redo03_B.log'
) SIZE 60M,
GROUP 4 (
'/oracle/data/ora8iDB/redoB/ora8iDB_redo04_B.log',
'/oracle/data/ora8iDB/redoA/ora8iDB_redo04_A.log'
) SIZE 60M,
GROUP 5 (
'/oracle/data/ora8iDB/redoA/ora8iDB_redo05_A.log',
'/oracle/data/ora8iDB/redoB/ora8iDB_redo05_B.log'
) SIZE 60M,
GROUP 6 (
'/oracle/data/ora8iDB/redoB/ora8iDB_redo06_B.log',
'/oracle/data/ora8iDB/redoA/ora8iDB_redo06_A.log'
) SIZE 60M
DATAFILE
'/oracle/data/ora8iDB/data/disk01/ora8iDB_system_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_rbs01_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_temp_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_tools_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_users_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_trslus_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_trsd01_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_trsd02_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_trsd03_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_trsd04_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_trsx01_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_trsx02_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_trsx03_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_trsx04_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_testata_other_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_testata_other_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_testata_eo_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_testata_eo_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_testata_evt_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_testata_evt_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_testata_ifd_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_testata_ifd_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_testef_eo_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_testef_eo_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_testef_evt_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_testef_evt_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_testef_ifd_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_testef_ifd_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_testef_other_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_testef_other_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_slotd01_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_slotx01_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_gend01_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_gend02_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_genx01_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_genx02_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_rbs02_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_rbs03_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_rbs04_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_bakd01_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_bakd02_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_bakd03_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_bakd04_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_bakx01_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_bakx02_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_bakx03_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_bakx04_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_inv_act_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_inv_act_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_ship_hist_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_ship_hist_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_ord_act_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_ord_act_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_lpn_dtl_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_lpn_dtl_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_lpn_loc_01.dbf',
'/oracle/data/ora8iDB/data/disk02/ora8iDB_lpn_loc_ind_01.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_testata_ifd_02.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_inv_act_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk01/ora8iDB_inv_act_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_ship_hist_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_ship_hist_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_bakx02_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_bakd02_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_system_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_temp_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_bakx03_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_bakx04_01.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_eo_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_rbs01_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_rbs02_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_trsx03_03',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_trslus_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_bakd01_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_bakx01_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_other_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_genx01_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_other_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_bakd04_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_evt_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_tools_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_users_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_trsd01_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_trsd02_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_trsd03_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_trsd04_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_trsx01_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_trsx02_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_trsx04_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_eo_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_evt_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_ifd_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_ifd_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_eo_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_eo_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_evt_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_evt_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_ifd_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_ifd_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_other_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testef_other_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_slotd01_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_slotx01_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_gend01_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_gend02_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_genx02_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_rbs03_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_rbs04_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_bakd03_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_ord_act_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_ord_act_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_dtl_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_dtl_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_loc_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_lpn_loc_ind_02.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_testata_ifd_03.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk03/ora8iDB_inv_act_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_trslus_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_trsd01_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_trsd02_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_trsd03_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_trsd04_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_trsx01_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_trsx02_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_trsx03_04.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_trsx04_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_gend01_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_gend02_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_genx01_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_genx02_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_bakd01_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_bakd02_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_bakd03_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_bakd04_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_bakx01_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_bakx02_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_bakx03_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_bakx04_02.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_inv_act_05.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_inv_act_ind_05.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_dtl_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_dtl_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_loc_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_lpn_loc_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_ord_act_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_ord_act_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_rbs01_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_rbs02_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_rbs03_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_rbs04_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_ship_hist_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_ship_hist_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_slotd01_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_slotx01_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_eo_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_eo_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_evt_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_evt_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_ifd_04.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_ifd_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_other_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testata_other_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_eo_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_eo_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_evt_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_evt_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_ifd_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_ifd_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_other_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_testef_other_ind_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_system_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_temp_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_tools_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_users_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_slot_data_ts_01.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_slot_index_ts_01.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_01.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_04.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_06.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_02.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_03.dbf',
'/oracle/data/ora8iDB/data/disk04/ora8iDB_rbsbig_05.dbf',
'/ORACLE/ora8iDB/data5/ora8iDB_system_04.dbf',
'/oracle/data/ora8iDB/data/disk05/ora8iDB_testata_other_ind_04.dbf',
'/oracle/data/ora8iDB/data/disk05/ora8iDB_inv_act_ind_06.dbf',
'/oracle/data/ora8iDB/data/disk05/ora8iDB_ord_act_ind_04.dbf',
'/oracle/data/ora8iDB/data/disk05/ora8iDB_slot_data_ts_02.dbf',
'/oracle/data/ora8iDB/data/disk05/ora8iDB_bakx04_03.dbf',
'/oracle/data/ora8iDB/data/disk05/ora8iDB_bakd03_04.dbf',
'/ORACLE/ora8iDB/data5/perfstat.dbf',
'/oracle/data/ora8iDB/data/disk05/ora8iDB_system_05.dbf',
'/oracle/data/ora8iDB/data/disk06/audit_mgmt_1.dbf',
'/ORACLE/ora8iDB/data5/perfstat_02.dbf'
CHARACTER SET WE8ISO8859P1
;
recover database using backup controlfile until cancel;
remove the redo log file and recreate the DB using controlfile
/oracle/data/ora8iDB/arch/
Target server:
startup pfile='/opt/oracle/admin/ora8iDB/pfile/initora8iDB.ora' nomount;
!ls -ltr /opt/oracle/admin/ora8iDB/pfile/*
/oracle/data/ora8iDB/arch> sqlplus ' / as sysdba'
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Aug 2 08:00:33 2021
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/opt/oracle/admin/ora8iDB/pfile/initora8iDB.ora' nomount;
ORACLE instance started.
Total System Global Area 275151892 bytes
Fixed Size 76820 bytes
Variable Size 151138304 bytes
Database Buffers 122880000 bytes
Redo Buffers 1056768 bytes
SQL> !ls -ltr /opt/oracle/admin/ora8iDB/pfile/*
-rw-rw-r-- 1 oracle dba 4839 Aug 2 07:57 /opt/oracle/admin/ora8iDB/pfile/ora8iDB_ctl.sql
12.ctl file recreation
************************
SQL> @/opt/oracle/admin/ora8iDB/pfile/ora8iDB_ctl.sql;
Control file created.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
ora8iDB MOUNTED
SQL> select count(*) from v$backup;
SQL> select count(*) from v$recover_file;
13.Recover the DB until the last change on source Db and using the log files accordingally
*******************************************************************************************
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1940592856157 generated at 07/12/2021 09:26:21 needed for
thread 1
ORA-00289: suggestion : /oracle/data/ora8iDB/arch/arch_ora8iDB_1_281025.arc
ORA-00280: change 1940592856157 for thread 1 is in sequence #281025
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281025.arc
ORA-00279: change 1940592860269 generated at 07/12/2021 09:42:52 needed for
thread 1
ORA-00289: suggestion : /oracle/data/ora8iDB/arch/arch_ora8iDB_1_281026.arc
ORA-00280: change 1940592860269 for thread 1 is in sequence #281026
ORA-00278: log file '/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281025.arc' no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281026.arc
ORA-00279: change 1940592879366 generated at 07/12/2021 10:16:44 needed for
thread 1
ORA-00289: suggestion : /oracle/data/ora8iDB/arch/arch_ora8iDB_1_281027.arc
ORA-00280: change 1940592879366 for thread 1 is in sequence #281027
ORA-00278: log file '/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281026.arc' no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281027.arc
ORA-00279: change 1940592898375 generated at 07/12/2021 10:58:37 needed for
thread 1
ORA-00289: suggestion : /oracle/data/ora8iDB/arch/arch_ora8iDB_1_281028.arc
ORA-00280: change 1940592898375 for thread 1 is in sequence #281028
ORA-00278: log file '/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281027.arc' no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281028.arc
ORA-00279: change 1940592910483 generated at 07/12/2021 11:19:13 needed for
thread 1
ORA-00289: suggestion : /oracle/data/ora8iDB/arch/arch_ora8iDB_1_281029.arc
ORA-00280: change 1940592910483 for thread 1 is in sequence #281029
ORA-00278: log file '/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281028.arc' no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281029.arc
ORA-00279: change 1940592923596 generated at 07/12/2021 11:44:37 needed for
thread 1
ORA-00289: suggestion : /oracle/data/ora8iDB/arch/arch_ora8iDB_1_281030.arc
ORA-00280: change 1940592923596 for thread 1 is in sequence #281030
ORA-00278: log file '/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281029.arc' no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281030.arc
ORA-00279: change 1940592944358 generated at 07/12/2021 12:25:35 needed for
thread 1
ORA-00289: suggestion : /oracle/data/ora8iDB/arch/arch_ora8iDB_1_281031.arc
ORA-00280: change 1940592944358 for thread 1 is in sequence #281031
ORA-00278: log file '/oracle/data/ora8iDB/arch/arch_ora8iDB_1_281030.arc' no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
14.once media recovery completed ,open the DB with resetlog mode
******************************************************************
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
ora8iDB READ WRITE
SQL> select count(*) from v$recover_file;
COUNT(*)
----------
0
15.finally add the DB name in oratab, this will help us to choose the SID while logging
Step by step to create tablespace in PostgreSQL RDS instance
Step by step to create tablespace in PostgreSQL RDS instance
when you create tablespace in RDS postgreSQL instance ,you can give the location as any string.
Tablespace location will be "PREFIXED " by /rdsdbdata/db/base/tablespace
CREATE TABLESPACE testTBSspace LOCATION '/test_data';
postgreSQL_dev=> \db+ testtbsspace
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
--------------+----------+-----------------------------------------+-------------------+---------+---------+-------------
testtbsspace | db_admin | /rdsdbdata/db/base/tablespace/test_data | | | 0 bytes |
(1 row)
drop TABLESPACE testTBSspace;
postgreSQL_dev=> drop TABLESPACE testTBSspace;
DROP TABLESPACE
postgreSQL_dev=> \db+ testtbsspace
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------+-------+----------+-------------------+---------+------+-------------
(0 rows)
Friday, November 26, 2021
what are the directories available for SAP oracle database on linux system?
*******directories for SAP DB related files********
/oracle/<SID>/sapcheck/ ==>it has log files ends with .sta (tells about stats collection) & .chk (tells about brconnect error)
/oracle/<SID>/sapreorg ==>it has log files ends with *.tse (tells about tablespace extention)
/oracle/<SID>/saptrace ==>it has log filesusertrace,diag,Audit
/oracle/<SID>/sapbackup ==>it has log files for backup related
/oracle/<SID>/sapprof
/oracle/<SID>/sapdata1==>it has log datafile for all tablespace
/oracle/<SID>/saparch
executable location:
usaws6970:oraSAP_DB1 58>pwd
/sapmnt/SAP_DB1/exe/uc/linuxx86_64
usaws6970:oraSAP_DB1 58> ls -ltr br*
-rwxr-xr-x 1 SAP_DB1adm sapsys 7578817 Mar 10 2020 brtools
-rwsrwsr-- 1 oracle oinstall 6852897 Mar 10 2020 brrestore
-rwsrwsr-- 1 oracle oinstall 14311924 Mar 10 2020 brspace
-rwsrwsr-- 1 oracle oinstall 11963744 Mar 10 2020 brrecover
-rwsrwsr-- 1 oracle oinstall 13719374 Mar 10 2020 brconnect
-rwsrwsr-- 1 oracle oinstall 11436039 Mar 10 2020 brbackup
-rwsrwsr-- 1 oracle oinstall 11350641 Mar 10 2020 brarchive
How does the SAP user gets authenticated to oracle SAP database?
For the database, the SAP system is a single user, SAPR3 / SAP<SAPSID>, whose password is stored in the table SAPUSER. Therefore, to access the database, the SAP System uses a mechanism called the OPS$ mechanism, which works as follows:
When the system accesses the database, it first logs on to the database as the user OPS$<operating_system_user>, for example, OPS$<SAPSID>adm.
(The OPS$ user that corresponds to the operating system user must be defined in the database and identified as externally.
It retrieves the password for SAPR3 from the SAPUSER table.
It logs on to the database as the user SAPR3 or /SAPR3DB.
OPS$ORACLE 10-JAN-21
OPS$ORA<SID> 10-JAN-21
OPS$<SID>ADM 10-JAN-21
Script to flush the online redologs in oracle database
Script to flush the online redo logs in oracle database:
we have come across some requirement to flush the online redo log and generate the archive forcifully,so created this script.
When you run this script just pass the <SID> and script will create the archives.
flush_oracle_8i_redo.ksh
#!/bin/ksh
#######################################################################
# Purpose :This script force to generate archive
# Developed by Bala nov-17-2021
# need to pass the SID
#######################################################################
wdir=`pwd`
hostname=`hostname`
date=`date +"%b %d, %Y %T"`
export wdir
cd $wdir
ORACLE_SID=$1
export ORACLE_SID=$1
export ORACLE_HOME=/opt/oracle/product/8.1.7
export PATH=$PATH:/opt/oracle/product/8.1.7/bin
export NLS_LANG=AMERICAN_AMERICA.UTF8
export SHLIB_PATH=$ORACLE_HOME/lib
########################################################################
# Check DB status and flush archive #
########################################################################
sqlplus -s /nolog << EOF
connect / as sysdba
set echo on
set verify on
set feedback on
set heading off;
col open_mode for a10
spool /home/sqlutils/${ORACLE_SID}_db_archive_flush.log
select name,open_mode from v\$database;
ALTER SYSTEM ARCHIVE LOG CURRENT;
spool off
EOF
**********************************
Step by step Table migration from AWS RDS postgreSQL to other PostgreSQL DB instance
Step by step Table migration from AWS RDS postgreSQL to other PostgreSQL DB instance:
steps:
1.use the pg_dump to take the backup to plain file
2.restore on target using psql import.
pg_dump --username=db_admin --host=<host_name_aws endpoint> --port=5700 --format=plain --file=backup.sql --dbname=postgrsql_db_dev --table=test_table1
-rw-r--r-- 1 postgres postgres 2294 Nov 26 08:30 backup.sql
<server_name>:~/aws> cat backup.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.5
-- Dumped by pg_dump version 12.1
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
--
-- Name: test_table1; Type: TABLE; Schema: public; Owner: app456_admin
--
CREATE TABLE public.test_table1 (
id integer NOT NULL,
app_id integer NOT NULL,
variant_id integer NOT NULL,
principal_id integer NOT NULL,
include boolean NOT NULL
);
ALTER TABLE public.test_table1 OWNER TO app456_admin;
--
-- Name: test_table1_id_seq; Type: SEQUENCE; Schema: public; Owner: app456_admin
--
CREATE SEQUENCE public.test_table1_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.test_table1_id_seq OWNER TO app456_admin;
--
-- Name: test_table1_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: app456_admin
--
ALTER SEQUENCE public.test_table1_id_seq OWNED BY public.test_table1.id;
--
-- Name: test_table1 id; Type: DEFAULT; Schema: public; Owner: app456_admin
--
ALTER TABLE ONLY public.test_table1 ALTER COLUMN id SET DEFAULT nextval('public.test_table1_id_seq'::regclass);
--
-- Data for Name: test_table1; Type: TABLE DATA; Schema: public; Owner: app456_admin
--
COPY public.test_table1 (id, app_id, variant_id, principal_id, include) FROM stdin;
4 80 43 51 t
5 89 52 51 t
\.
--
-- Name: test_table1_id_seq; Type: SEQUENCE SET; Schema: public; Owner: app456_admin
--
SELECT pg_catalog.setval('public.test_table1_id_seq', 5, true);
--
-- Name: test_table1 test_table1_pkey; Type: CONSTRAINT; Schema: public; Owner: app456_admin
--
ALTER TABLE ONLY public.test_table1
ADD CONSTRAINT test_table1_pkey PRIMARY KEY (id);
--
-- Name: test_table1_by_app_id_variant_id; Type: INDEX; Schema: public; Owner: app456_admin
--
CREATE INDEX test_table1_by_app_id_variant_id ON public.test_table1 USING btree (app_id, variant_id);
--
-- PostgreSQL database dump complete
--
***************************restore using import**************************************
psql --username=testuser --host=<host_name> --port=5432 --file=backup.sql --dbname=testdb --table=test_table1
<server_name>:~/aws> psql --username=testuser --host=<host_name> --port=5432 --file=backup.sql --dbname=testdb --table=test_table1
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 2
setval
--------
5
(1 row)
ALTER TABLE
CREATE INDEX
<server_name>:~/aws> psql
psql (12.1, server 11.6)
Type "help" for help.
postgres=# \c testdb
psql (12.1, server 11.6)
You are now connected to database "testdb" as user "postgres".
after Data migration to new PostgreSQL instance
testdb=# select * from test_table1;
id1 | app_id | variant_id | principal_id | include
----+--------+------------+--------------+---------
14 | 480 | 543 | 751 | t
45 | 489 | 552 | 751 | t
(2 rows)
Thursday, November 25, 2021
script to get the tablespace location and owner of the tablespace in PostgreSQL
script to get the tablespace location in PostgreSQL
Use pg_tablespace_location(tablespace_oid)(PostgreSQL 9.2+) to get the path in the file system where the tablespace is located.
You'll get oid of tablespace from pg_tablespace, so the query should be
select spcname
,pg_tablespace_location(oid)
from pg_tablespace;
spcname | pg_tablespace_location
--------------+-------------------------------------------------------------------------
pg_default |
pg_global |
testdb2_data | /rdsdbdata/db/base/tablespace/rdsdbdata/db/base/tablespace/testdb2_data
(3 rows)
******************create DB to specific tablespace in postgres SQL****************
postgres=# create database TEST tablespace TBSspace;
CREATE DATABASE
*****************************
testdb2_connect_prod=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)
postgres_db_dev=> \du db_admin
List of roles
Role name | Attributes | Member of
-----------+-------------------------------+-----------------
db_admin | Create role, Create DB +| {rds_superuser}
| Password valid until infinity |
postgres_db_dev=> SELECT spcname,spcowner FROM pg_tablespace;
spcname | spcowner
--------------+----------
pg_default | 10
pg_global | 10
testdb2_data | 16410
(3 rows)
postgres_db_dev=> select usename from pg_user where usesysid='16410';
usename
---------------
testdb2_admin
(1 row)
testdb2_workbench_dev=> select current_database();
current_database
-----------------------
testdb2_workbench_dev
(1 row)
postgres_db_dev=> SELECT current_user;
current_user
---------------
testdb2_admin
(1 row)
Error while creating database in PostgreSQL
issue:
when I tried to create new DB in postgreSQL got the below error and analyzed the issue and found the solution.
postgres_DEV=> create database postgres_db_dev1;
ERROR: source database "template1" is being accessed by other users
DETAIL: There is 1 other session using the database.
check which user/process holding the template DB
postgres_DEV=> select datname,pid,usename,application_name,client_hostname,client_port from pg_stat_activity where datname='template1';
datname | pid | usename | application_name | client_hostname | client_port
-----------+------+----------+--------------------------+-----------------+-------------
template1 | 1871 | DB_admin | pgAdmin 4 - DB:template1 | | 51681
(1 row)
Solution:
Kill a template1 session on PostgreSQL database:
select pg_terminate_backend(pid)
from pg_stat_activity
where pid = '1871';
postgres_DEV=> select pg_terminate_backend(pid)
postgres_DEV-> from pg_stat_activity
postgres_DEV-> where pid = '1871';
pg_terminate_backend
----------------------
t
(1 row)
postgres_DEV=> CREATE DATABASE test2 TEMPLATE template1;
CREATE DATABASE
Subscribe to:
Posts (Atom)