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
Sunday, November 14, 2021
script to find postgres db size:
script to find postgres db size:
SELECT
pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;
postgres=# \l+ postgres
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
----------+----------+-----------+---------+-------+------------------------+---------+------------+--------------------------------------------
postgres | postgres | SQL_ASCII | C | C | =Tc/postgres +| 7941 kB | pg_default | default administrative connection database
| | | | | postgres=CTc/postgres +| | |
| | | | | kmonitor00=c/postgres +| | |
| | | | | testmonitor=c/postgres | | |
(1 row)
select t1.datname AS db_name,
pg_size_pretty(pg_database_size(t1.datname)) as db_size
from pg_database t1
order by pg_database_size(t1.datname) desc;
datname | size
----------------------+---------
testdbTest | 7941 kB
template1 | 7941 kB
template0 | 7801 kB
testdba | 7941 kB
script to check what are the tables available on specific tablespace:
script to check what are the tables available on specific tablespace:
\db+ ts_primary
postgres=# \db+ test_data
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
-----------+------------+---------------------------------------+-------------------+---------+------------+-------------
test_data | test_admin | /var/opt/pgsql12/tablespace/test_data | | | 4096 bytes |
(1 row)
query/script to list tablespace in PostgreSQL:
query to list tablespace in PostgreSQL:
SELECT spcname FROM pg_tablespace;
to list all Tablespace in
postgres=# \db
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+------------+-----------------------------------+-------------------+---------+--------+-------------
test_data | test_admin | /opt/pgsql12/tablespace/test_data | | | 59 GB |
pg_default | postgres | | | | 35 MB |
pg_global | postgres | | | | 399 kB |
query to find out memory values of postgresql memory:
query the list of parameter
select name,setting from pg_settings;
query the list of parameter requires restart
select name,setting from pg_settings where context='postmaster';
show command used to display the vaule of parameter
query to find out memory values of postgresql memory:
select name,setting from pg_settings where name LIKE '%shared_buffer%';
name | setting
----------------+---------
shared_buffers | 16384
postgres=# show shared_buffers;
shared_buffers
----------------
128MB
(1 row)
How to backup database object definitions in PostgreSQL database?
Sometimes, you want to backup only database object definitions, not the data This is helpful in the testing phase, which you do not want to move test data to the live system.
To back up objects in all databases, including roles, tablespaces, databases, schemas, tables, indexes, triggers, functions, constraints, views, ownerships, and privileges, you use the following command:
pg_dumpall --schema-only > c:\pgdump\definitiononly.sql
Code language: CSS (css)
If you want to back up role definition only, use the following command:
pg_dumpall --roles-only > c:\pgdump\allroles.sql
Code language: CSS (css)
If you want to backup tablespaces definition, use the following command:
pg_dumpall --tablespaces-only > c:\pgdump\allroles.sql
Code language: CSS (css)
Further Reading
script to find PostgreSQL configuration file location
command/script to find postgreSQL configuration file location
*****************************************************
postgres=# SHOW config_file;
config_file
---------------------------------------
/var/opt/pgsql12/data/postgresql.conf
script check the all active connections to the db database by using the following query:
script check the all active connections to the db database by using the following query:
select usename,datname FROM pg_stat_activity;
SELECT *
FROM pg_stat_activity
WHERE datname = 'db';
terminate all the connections to the db database by using the following statement:
SELECT
pg_terminate_backend (pid)
FROM
pg_stat_activity
WHERE
datname = 'db';
Tuesday, November 2, 2021
script to check failed logins in aws redshift database?
script to check failed logins in aws redshift database
SELECT *
FROM stl_connection_log
WHERE event='authentication failure'
ORDER BY recordtime;
script to Showing successfully authenticated users with the number of successful authentications in aws redshift Database?
script to Showing successfully authenticated users with the number of successful authentications:
SELECT username, event, COUNT(*)
FROM stl_connection_log
WHERE event = 'authenticated'
GROUP BY 1, 2
ORDER BY 3 DESC;
username | event | count
----------------------------------------------------+----------------------------------------------------+-------
rdsdb | authenticated | 4927
IAM:test2@us.domain.com | authenticated | 1833
test3 | authenticated | 720
IAMA:test1@us.domain.com | authenticated | 344
IAMA:xyz.123@domain.com | authenticated | 65
script to create database user in aws Redshift database with IAM authentication
script to create database user in aws Redshift database with IAM authentication
authentication will be done using the Single sign on thru IAM and DB access will be done through group .
create user "ABC.123@domain.com" password disable valid until '31-Aug-2022';
alter group redshift_readwrite_dev add user "ABC.123@domain.com" ;
scripts to find view list from the schema in aws redshift database?
scripts to find view list from the schema
select table_schema as schema_name,
table_name as view_name
from information_schema.views
where table_schema in ('app_username')
order by schema_name,
view_name;
script to check tables under which schema in aws redshift database?
script to check tables under which schema in aws redshift database
SELECT
TRIM(nspname) AS schema_name,
TRIM(relname) AS table_name,
relcreationtime AS creation_time
FROM pg_class_info
LEFT JOIN pg_namespace ON pg_class_info.relnamespace = pg_namespace.oid
WHERE reltype != 0
AND TRIM(nspname) = 'app_username';
script to find the user part of which group in aws redshift database?
script to find the user part of which group in aws redshift database
SELECT pg_group.groname
,pg_group.grosysid
,pg_user.*
FROM pg_group, pg_user
WHERE pg_user.usesysid = ANY(pg_group.grolist)
AND pg_user.usename='redshiftuser1'
ORDER BY 1,2 ;
groname | grosysid | usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig
---------------------------+----------+------------+----------+-------------+----------+-----------+----------+----------+-----------
redshift_readonly_sa | 104 | TESTDB_user| 145 | f | f | f | ******** | |
redshift_readwrite_sa | 105 | TESTDB_user| 145 | f | f | f | ******** | |
script to find Db owner details in aws Redshift DB?
script to find Db owner details in aws Redshift DB
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
WHERE d.datname = 'redshiftDB'
ORDER BY 1;
Name | Owner
-------------+------------------------------
redshiftDB | DB_owner_username
(1 row)
script to find table creation time stamp or date in aws redshift database?
script to find table creation time stamp or date in aws redshift database:
provide the table_name and schema name and get the table created time from Redshift DB
SELECT
nspname AS schema_name,
relname AS table_name,
relcreationtime AS creation_time
FROM pg_class_info c, pg_namespace n
WHERE c.relnamespace=n.oid
AND reltype != 0
AND nspname='<schema_name>'
AND relname='<table_name>';
How to provide Read only and Read write access to Redshift Database users?
Syntax For read only access –
grant usage on schema <schemaname> to redshift_readonly_dev;
grant select on ALL tables in schema <schemaname> to redshift_readonly_dev;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT select ON TABLES TO group redshift_readonly_dev;
Syntax for Read write access –
grant usage on schema <schemaname> to nga_redshift_readwrite_dev;
grant select,insert,update,delete on ALL tables in schema <schemaname> to redshift_readwrite_dev;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT select,insert,update,delete ON TABLES TO group redshift_readwrite_dev;
add the users to respective groups and they will inherit the privileges.
alter group redshift_readwrite_dev add user <userid>;
Subscribe to:
Posts (Atom)