step by step Reading MSSQL DB data from Oracle 19c database using DB link & ODBC Drivers :

 Reading MSSQL DB data from Oracle 19c  database using DB link & ODBC Drivers  :

****************************************************************************

List of steps:

**************

1. Root - Install Microsoft SQL server ODBC drivers.

  sudo ACCEPT_EULA=Y yum install -y msodbcsql17

2. Root - Create ODBC entry in /etc/odbc.ini file pointing to remote.

3. Oracle - create init parameter file initora_19c_DB_MSSQL. Ora

4. Oracle - register SID in listener.ora.

5. Oracle- Create entry in tnsnames.ora pointing to SID.

6. Oracle - create DB link in oracle database.

7. Oracle- Optionally - Create synonym.

8. Install Microsoft SQL server ODBC drivers

How to install the Microsoft ODBC Driver for SQL Server on Linux. It also includes instructions for the optional command-line tools for SQL Server and the unixODBC development headers.

Here we are taking up the ODBC 17 drivers. Please go to the below link and ask to unix team to install the driver on the server.

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver17&tabs=redhat18-install%2Credhat17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline

Once installed the package they need to run the ODBC driver using below command.

  sudo ACCEPT_EULA=Y yum install -y msodbcsql17

9. Create ODBC entry in /etc/odbc.ini file pointing to remote

To create an ODBC entry in /etc/odbc.ini that points to a remote database, you need to define a Data Source Name (DSN) within the file. This DSN will contain the necessary parameters to connect to your remote database.

Here is an example of an odbc.ini entry for a remote database.

[DS_MSSQL]

Description = MSSQL

Driver = /usr/lib64/libmsodbcsql-17.so

Server = 20.20.30.30.\MSSQL_instance1

Port = 57500

Database = MSSQL_DB

UID = user_ora2mssql

Explanation of the parameters:

• [DS_MSSQL]: This is the DSN name. You can choose any descriptive name for your connection.

• Description: A brief description of the data source.

• Driver: The name of the ODBC driver you have installed for your specific database SQL Server. Ensure the corresponding driver is correctly installed and configured in /etc/odbcinst.ini.

• Server : The hostname or IP address of your remote database server.

• Port: The port number on which the database server is listening for  SQL Server.

• Database: The name of the specific database you want to connect to on the remote server.

• UID: The username for authenticating with the database.

10. Create init parameter file initora_19c_DB_MSSQL. Ora

To create a Heterogeneous Services (HS) initialization parameter file for connecting to a SQL Server database named ora_19c_DB, you would create a text file named initora_19c_DB_MSSQL.ora and place it in the $ORACLE_HOME/hs/admin directory. This file contains parameters specific to the gateway, not the main Oracle database.

The file should be located in the $ORACLE_HOME/hs/admin directory.

Below is a sample configuration. The specific parameters required will depend on your environment and the specific Oracle Database Gateway version you are using.

vi $ORACLE_HOME/hs/admin/initora_19c_DB_MSSQL.ora

# needed for the database gateway for odbc

# HS init parameters

HS_FDS_CONNECT_INFO = DS_MSSQL

HS_FDS_SHAREABLE_NAME = /usr/lib64/libmsodbcsql-17.so

HS_LANGUAGE= AL16UTF16

HS_FDS_REMOTE_DB_CHARSET=AMERICAN_AMERICA.AL16UTF16

HS_NLS_NCHAR=UCS2

#

# ODBS SPECIFIC ENVIRONMEANT VARIABLES

#

set ODBCINI=/etc/odbc.ini

11. register SID in listener.ora. 

Add the HS SID listener in listener file To register an Oracle System Identifier (SID) for a database instance or a Heterogeneous Services (HS) agent in the listener.ora file, you need to perform static registration. This involves manually adding a SID_DESC entry to the SID_LIST section of the file. 

The listener. Ora file is typically located in the $ORACLE_HOME/network/admin directory

Add a SID_DESC entry:-

Within the SID_LIST_<listener_name> section (by default SID_LIST_LISTENER), add a new SID_DESC block. This block specifies the details for the service you want to register.For an Oracle Database Instance

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

LISTENER_ora_19c_DB =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 30.30.30.50)(PORT = 1587))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1587))

    )

  )

 

SID_LIST_LISTENER_ora_19c_DB =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = ora_19c_DB)

      (ORACLE_HOME = /oracle/ora_19c_DB/12201)

    )

    (SID_DESC =

      (SID_NAME = ora_19c_DB_MSSQL)

      (ORACLE_HOME = /oracle/ora_19c_DB/12201)

      (PROGRAM = /oracle/ora_19c_DB/12201/bin/dg4odbc)

    )

  )

12. Create entry in tnsnames.ora pointing to SID.

To create an entry in the tnsnames.ora file pointing to an Oracle SID, follow these steps:

• Locate the tnsnames.ora file: This file is typically found in the $ORACLE_HOME/network/admin directory. If the TNS_ADMIN environment variable is set, it will point to the directory containing the tnsnames.ora file.

• Open the tnsnames.ora file: Use a text editor to open the tnsnames.ora file.

• To create an entry in the tnsnames.ora file for an Oracle Heterogeneous Services (HS) SID, you need to manually edit the file to include a specific connect descriptor. The HS=OK parameter is crucial in the CONNECT_DATA section to direct Oracle Net to use Heterogeneous Services.

TNS_MSSQL.WORLD =

(DESCRIPTION =

    (SDU = 32768)

    (ADDRESS_LIST =

        (ADDRESS =

          (PROTOCOL = TCP)

          (HOST = 30.30.30.50)

          (PORT = 1587)

        )

    )

    (CONNECT_DATA =

       (SID =ora_19c_DB_MSSQL)

         )

        (HS=OK)

  )

• (HS = OK): This mandatory parameter tells Oracle Net to connect to a non-Oracle system using Heterogeneous Services.

13. Oracle - create DB link in oracle database.

• Permissions: 

Ensure the user creating the database link has the necessary CREATE DATABASE LINK privilege. The remote_user in the CONNECT TO clause must exist in the remote database and have privileges to access the objects you intend to use via the link.

• Public vs. Private: 

Database links can be PUBLIC (accessible by all users) or private (owned by the user who created it). To create a public link, add the PUBLIC keyword after CREATE.

• GLOBAL_NAMES parameter: 

The GLOBAL_NAMES initialization parameter can impact database link behavior. If set to TRUE, the database link name must match the global name of the remote database. Consider setting it to FALSE if strict name matching is not desired.

Note :- Before you creating the DB link they need to be create the SQL server authentication user in SQL server then only you make connection between from Oracle to MSSQL.

Create SQL Server Authentication User:- 

• In SQL Server Management Studio (SSMS): 

o Go to Security → Logins → New Login.

o Choose SQL Server Authentication (not Windows Authentication).

o Set a strong password and ensure Enforce password policy is configured as needed.

o Map the login to the required database and assign appropriate roles (e.g., db_datareader.

Syntax :-         CREATE DATABASE LINK my_remote_link     CONNECT TO remote_user IDENTIFIED     BY remote_password    USING 'REMOTE_DB_ALIAS';

Example :- create public database link  mssql_dblink6 connect to "user_ora2mssql" identified by "" using 'TNS_MSSQL';

Select  count(*)  from  "view_test"@mssql_dblink6;

 

14. Optionally - Create synonym.

To create a synonym for a remote object accessed through a database link in Oracle.

Once the database link is established, you can create a synonym for a specific object (table, view, sequence, etc.) in the remote database.

CREATE SYNONYM synonym_name FOR remote_table_name@db_link_n

Example :- Create public synonym mssqlview   For view_test@mssql_dblink6;

After creating the synonym, you can query the remote table using the synonym name as if it were a local object:

Select count(*) from mssqlview.

This will read the Data from MSSQL from Oracle DB

 

 

step by step to read oracle DB data from MSSQL server using linked server in MSSQL database

 Linked Server in MS SQL Server

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

Source : MS SQL 2019 EE on Windows 22

Target : Oracle 19c R2 EE on Linux 8 - IP - 192.168.56.74 Port- 1583 ServiceName- ORA_DB_19c

Pre-Req:

-----------

a. You must have Oracle client installed on MSSQL server.

b. Working Network Connection between both Servers.

   (If required create exception in Firewall.)

c. Remote (Oracle) DB credentials.

d. Proper TNS entry for remote (Oracle) DB.

Steps:

---------

1. Create User in Remote DB Server

Create user oracle identified by oracle123;

grant create session to oracle;

2. Provide Required Permission to User as per your need

grant select any table to oracle;

grant select any dictionary to oracle;

3. Create TNS Entry in Oracle Client TNSNAMES.ora in Source Server (MS SQL Server)

ORA_DB_19c=(DESCRIPTION=

(CONNECT_DATA=

(SERVICE_NAME=ORA_DB_19c))

(ADDRESS=

(PROTOCOL=TCP)(HOST=12.16.53.12)(PORT=1583)

)

)

4. Create Linked Server Using SSMS

You Need 3 Values : 

a. LINKED SERVER NAME -- Any Name you want to use , Just Like any DBLink. --ORACLE_DB

b. Product Name -- Oracle

c. Data Source -- TNS Service Name of Oracle DB

d. Fill Credentials of Remote DB in Security Section --oracle DB user name & password

5. Enable AllowInProcess in SQL Server

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1

6. Test Linked Server

7. Fetch records from Oracle DB using Linked Server in MS SQL Server

ORACLE_DB---Linked server name

SELECT * FROM OPENQUERY([ORACLE_DB] , 'select name,open_mode,host_name from v$database,v$instance') ;

we got Oracle database error ORA-38774: cannot disable media recovery - flashback database is enabled

 Issue:

we got Oracle database error  ORA-38774: cannot disable media recovery - flashback database is enabled when tried to turn off the archive log mode .

Root cause:

oracle DB will not allow  to turn off the archive log mode ,until you disable the Flashback.

Solution:

Total System Global Area 8.7242E+10 bytes

Fixed Size                  5295200 bytes

Variable Size            9663677344 bytes

Database Buffers         7.7309E+10 bytes

Redo Buffers              263139328 bytes

Database mounted.

SQL> alter database noarchivelog;

alter database noarchivelog

*

ERROR at line 1:

ORA-38774: cannot disable media recovery - flashback database is enabled

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON

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

DB1       YES

SQL> alter database flashback off;

Database altered.

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON

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

DB1       NO

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

DB1       MOUNTED

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /oracle/DB1/oraarch/DB1arch

Oldest online log sequence     361611

Current log sequence           361616

SQL>  select name,open_mode from v$database;

NAME      OPEN_MODE

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

DB1       READ WRITE

shell script for defragmentation in oracle Database Tables

 Here is the shell script to do de-fragmentation activities for a table in Oracle database. 

You can feed the table name and schema name and run this script in back ground 

#!/bin/ksh

export ORACLE_HOME=/oracle/TESTDB5/1924

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=TESTDB5

TMP_LOG=/oracle/scripts/logs/trans_table_redef.log

echo > $TMP_LOG

sqlplus -s /nolog <<EOF >> $TMP_LOG

whenever sqlerror exit 1

conn / as sysdba

SET SERVEROUTPUT ON;

ALTER SESSION FORCE PARALLEL DML PARALLEL 2;

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;

exec DBMS_REDEFINITION.CAN_REDEF_TABLE('<schema_name>','<Table_name>', DBMS_REDEFINITION.CONS_USE_ROWID);

exit

EOF

# Check if SQL execution was successful

if [ $? -eq 0 ]; then

    SUBJECT="Table Redefinition Completed Successfully"

else

    SUBJECT="Table Redefinition Failed"

fi

# Send the log file via email

mailx -s "$SUBJECT" Balamani@abc.com < $TMP_LOG

shell script to build oracle database with easy interactive mode

 Here is the full  script which will get few input and build the oracle database and configure the listener,tnsnames.ora.pfile/spfile and convert the database to archive log 

#########################################################################################################

#purpose       : Script used to build oracle DB,pass requested info                                     #

#Input needed  :DB_NAME,ORACLE_HOME,PORT#,DB CHARSET,sys,system,PDB pws,Redlog size in MB               #

#script will do: create DB,convert archive mode,setup listener& tns and start,add /etc/oratab,BCT       #

#Developed by  :Bala P  3March2025  v2                                                                  #

#########################################################################################################

#!/bin/sh

# Prompt for database name and convert to uppercase

echo "Enter the database name:"

read DB_NAME

DB_NAME=$(echo $DB_NAME | tr '[:lower:]' '[:upper:]')

# Prompt the user for   port number

read -p "Enter the port number: " PORT_NUMBER

# Prompt the user for   Redolog size in MB

read -p "Enter the Redolog size in MB: " RLOG

# Prompt for Oracle DB Home

echo "Enter the Oracle DB Home full Path:"

read  ORACLE_HOME

# Check if sqlplus /oracle_home status

if [ ! -x "$ORACLE_HOME/bin/sqlplus" ]; then

echo " sqlplus not found at $ORACLE_HOME/bin. Please check ORACLE_HOME."

exit 1

else

  echo "Oracle_Home FOUND ,proceeding with next steps"

fi

echo -e "\n \n"

#Prompt for  DB CHARACTER SET

echo "Enter the DB  CHARACTER SET"

read charset

# Prompt for passwords

echo "Enter new password for SYS:"

read -s sysPassword

echo "Enter new password for SYSTEM:"

read -s systemPassword

echo "Enter new password for PDBADMIN:"

read -s pdbAdminPassword

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

# Create necessary directories

OLD_UMASK=`umask`

umask 0027

mkdir -p /oracle/${DB_NAME}

mkdir -p $ORACLE_HOME/dbs

mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/adump

mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/dpdump

mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/pfile

mkdir -p /oracle/${DB_NAME}/audit

mkdir -p /oracle/${DB_NAME}/cfgtoollogs/dbca/${DB_NAME}

mkdir -p /oracle/${DB_NAME}/control1

mkdir -p /oracle/${DB_NAME}/control2

mkdir -p /oracle/${DB_NAME}/control3

mkdir -p /oracle/${DB_NAME}/mirrlogA

mkdir -p /oracle/${DB_NAME}/mirrlogB

mkdir -p /oracle/${DB_NAME}/origlogA

mkdir -p /oracle/${DB_NAME}/origlogB

mkdir -p /oracle/${DB_NAME}/data1

mkdir -p /oracle/${DB_NAME}/SystemTBS

mkdir -p /oracle/${DB_NAME}/UndoTBS

mkdir -p /oracle/${DB_NAME}/tempTBS

mkdir -p /oracle/${DB_NAME}/admin/scripts

umask ${OLD_UMASK}

# Create init<DB_NAME>.ora file

rm -f /oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora

cat <<EOL > /oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora

${DB_NAME}.__oracle_base='/oracle/${DB_NAME}'#ORACLE_BASE set from environment

${DB_NAME}.__db_cache_size=1451700

${DB_NAME}.__java_pool_size=16686

${DB_NAME}.__large_pool_size=16686

${DB_NAME}.__sga_target=1067918334

${DB_NAME}.__shared_io_pool_size=216920

${DB_NAME}.__shared_pool_size=2552992

${DB_NAME}.__data_transfer_cache_size=0

${DB_NAME}.__streams_pool_size=0

*.audit_file_dest='/oracle/${DB_NAME}/admin/${DB_NAME}/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_file_record_keep_time=30

*.control_files='/oracle/${DB_NAME}/control1/control01.ctl','/oracle/${DB_NAME}/control2/control02.ctl','/oracle/${DB_NAME}/control3/control03.ctl'

*.db_block_size=8192

*.db_domain='abc.domain.com'

*.db_files=200

*.db_name='${DB_NAME}'

*.diagnostic_dest='/oracle/${DB_NAME}'

*.filesystemio_options='SETALL'

*.log_archive_dest='/oracle/${DB_NAME}/oraarch/${DB_NAME}arch'

*.log_archive_format='%t_%s_%r.arc'

*.log_checkpoints_to_alert=TRUE

*.max_dump_file_size='20000'

*.open_cursors=3000

*.parallel_execution_message_size=16384

*.parallel_max_servers=20

*.parallel_threads_per_cpu=1

*.pga_aggregate_target=675m

*.processes=500

*.replication_dependency_tracking=FALSE

*.sessions=1000

*.sga_target=1g

*.undo_management='AUTO'

*.undo_retention=21600

*.undo_tablespace='UNDOTBS'

_disable_directory_link_check=TRUE

CONTROL_MANAGEMENT_PACK_ACCESS = "NONE"

EOL

# Create Oracle password file

rm -f $ORACLE_HOME/dbs/orapw${DB_NAME}

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw${DB_NAME} force=y format=12

# Run SQL scripts for DB creation and configuration

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

echo -e "\n \n creating the DB...."

sqlplus /nolog <<EOF

CONNECT SYS/${sysPassword} AS SYSDBA

conn / as sysdba

SET VERIFY OFF

SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDB.log

STARTUP NOMOUNT PFILE='/oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora';

CREATE DATABASE "${DB_NAME}"

MAXINSTANCES 8

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1000

DATAFILE '/oracle/${DB_NAME}/SystemTBS/system01.dbf' SIZE 2072M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/oracle/${DB_NAME}/SystemTBS/sysaux01.dbf' SIZE 2192M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oracle/${DB_NAME}/tempTBS/temp01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

SMALLFILE UNDO TABLESPACE "UNDOTBS" DATAFILE  '/oracle/${DB_NAME}/UndoTBS/undotbs01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

CHARACTER SET "${charset}"

NATIONAL CHARACTER SET AL16UTF16

LOGFILE

GROUP 1(

    '/oracle/${DB_NAME}/origlogA/redo_g1m1.log',

    '/oracle/${DB_NAME}/mirrlogA/redo_g1m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512,

  GROUP 2 (

    '/oracle/${DB_NAME}/origlogB/redo_g2m1.log',

    '/oracle/${DB_NAME}/mirrlogB/redo_g2m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512,

  GROUP 3 (

    '/oracle/${DB_NAME}/origlogB/redo_g3m1.log',

    '/oracle/${DB_NAME}/mirrlogB/redo_g3m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512,

  GROUP 4 (

    '/oracle/${DB_NAME}/origlogA/redo_g4m1.log',

    '/oracle/${DB_NAME}/mirrlogA/redo_g4m2.log'

  ) SIZE ${RLOG}M BLOCKSIZE 512

USER SYS IDENTIFIED BY "${sysPassword}" USER SYSTEM IDENTIFIED BY "${systemPassword}";

SPOOL OFF

----creating the needed tablespaces--------

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDBFiles.log

CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/oracle/${DB_NAME}/data1/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO;

ALTER DATABASE DEFAULT TABLESPACE "USERS";

SPOOL OFF

------loading oracle binaries--------------

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDBCatalog.log

@$ORACLE_HOME/rdbms/admin/catalog.sql;

@$ORACLE_HOME/rdbms/admin/catproc.sql;

@$ORACLE_HOME/rdbms/admin/catoctk.sql;

@$ORACLE_HOME/rdbms/admin/owminst.plb;

CONNECT SYSTEM/${systemPassword}

@$ORACLE_HOME/sqlplus/admin/pupbld.sql;

CONNECT SYS/${sysPassword} AS SYSDBA

@$ORACLE_HOME/sqlplus/admin/pupdel.sql;

CONNECT SYSTEM/${systemPassword};

SPOOL /oracle/${DB_NAME}/admin/scripts/sqlPlusHelp.log

@$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/JServer.log

@$ORACLE_HOME/javavm/install/initjvm.sql;

@$ORACLE_HOME/xdk/admin/initxml.sql;

@$ORACLE_HOME/xdk/admin/xmlja.sql;

@$ORACLE_HOME/rdbms/admin/catjava.sql;

@$ORACLE_HOME/rdbms/admin/catxdbj.sql;

SPOOL OFF

---checked above  this

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/context.log

@$ORACLE_HOME/ctx/admin/catctx.sql Xbkfsdcdf1ggh_123 SYSAUX TEMP LOCK; --no path

ALTER USER CTXSYS ACCOUNT UNLOCK IDENTIFIED BY "CTXSYS";

CONNECT CTXSYS/CTXSYS

@$ORACLE_HOME/ctx/admin/defaults/dr0defin.sql "AMERICAN";

CONNECT SYS/${sysPassword} AS SYSDBA

conn / as sysdba

ALTER USER CTXSYS PASSWORD EXPIRE ACCOUNT LOCK;

@$ORACLE_HOME/rdbms/admin/dbmsxdbt.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/ordinst.log

@$ORACLE_HOME/ord/admin/ordinst.sql SYSAUX SYSAUX;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/interMedia.log

@$ORACLE_HOME/ord/im/admin/iminst.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/cwmlite.log

@$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/spatial.log

@$ORACLE_HOME/md/admin/mdinst.sql;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/apex.log APPEND

@$ORACLE_HOME/apex/catapx.sql Xbkfsdcdf1ggh_123 SYSAUX SYSAUX TEMP /i/ NONE;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/postDBCreation.log

host $ORACLE_HOME/OPatch/datapatch -skip_upgrade_check -db ${DB_NAME};

CREATE SPFILE='$ORACLE_HOME/dbs/spfile${DB_NAME}.ora' FROM PFILE='/oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora';

SELECT 'utlrp_begin: ' || TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;

@$ORACLE_HOME/rdbms/admin/utlrp.sql;

SELECT 'utlrp_end: ' || TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;

SELECT comp_id, status FROM dba_registry;

SHUTDOWN IMMEDIATE;

STARTUP;

SPOOL OFF

conn / as sysdba

SPOOL /oracle/${DB_NAME}/admin/scripts/lockAccount.log

BEGIN

FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (

'SYS','SYSTEM') )

LOOP

IF item.AUTHENTICATION_TYPE='PASSWORD' THEN

  DBMS_OUTPUT.PUT_LINE('Locking and Expiring: ' || item.USERNAME);

  EXECUTE IMMEDIATE 'ALTER USER ' ||

         SYS.DBMS_ASSERT.ENQUOTE_NAME(

         SYS.DBMS_ASSERT.SCHEMA_NAME(

         item.USERNAME),FALSE) || ' PASSWORD EXPIRE ACCOUNT LOCK' ;

ELSE

  DBMS_OUTPUT.PUT_LINE('Locking: ' || item.USERNAME);

  EXECUTE IMMEDIATE 'ALTER USER ' ||

         SYS.DBMS_ASSERT.ENQUOTE_NAME(

         SYS.DBMS_ASSERT.SCHEMA_NAME(

         item.USERNAME),FALSE) || ' ACCOUNT LOCK' ;

END IF;

END LOOP;

END;

/

SPOOL OFF

EOF

# Set ORACLE_HOME

export ORACLE_HOME=$ORACLE_HOME

# Get the current hostname

SERVER_NAME=$(hostname)

# Define paths

TNS_PATH="$ORACLE_HOME/network/admin/tnsnames.ora"

LISTENER_PATH="$ORACLE_HOME/network/admin/listener.ora"

TNS_BACKUP_PATH="${TNS_PATH}_backup_$(date +%Y%m%d_%H%M%S)"

LISTENER_BACKUP_PATH="${LISTENER_PATH}_backup_$(date +%Y%m%d_%H%M%S)"

# Backup tnsnames.ora

if [ -f "$TNS_PATH" ]; then

  cp "$TNS_PATH" "$TNS_BACKUP_PATH"

  echo "Backup of tnsnames.ora created at $TNS_BACKUP_PATH"

fi

# Append to tnsnames.ora

cat <<EOF >> "$TNS_PATH"

${DB_NAME}.WORLD=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS =

        (PROTOCOL = TCP)

        (HOST = ${SERVER_NAME})

        (PORT = ${PORT_NUMBER})

      )

    )

    (CONNECT_DATA =

      (SID = ${DB_NAME})

      (GLOBAL_NAME = ${DB_NAME}.WORLD)

    )

  )

#for PROD

RMANCATPRD.WORLD=

  (DESCRIPTION =

    (SDU = 32768)

    (ADDRESS_LIST =

        (ADDRESS =

          (PROTOCOL = TCP)

          (HOST = <catlog_server>)

          (PORT = 1591)

        )

    )

    (CONNECT_DATA =

       (SID = KRMANCATPRD)

       (GLOBAL_NAME = RMANCATPRD.WORLD)

    )

  )

#prod end

EOF

echo "New TNS entry for ${DB_NAME} added to $TNS_PATH"

# Backup listener.ora

if [ -f "$LISTENER_PATH" ]; then

  cp "$LISTENER_PATH" "$LISTENER_BACKUP_PATH"

  echo "Backup of listener.ora created at $LISTENER_BACKUP_PATH"

fi

# Create listener.ora

cat <<EOF > "$LISTENER_PATH"

LISTENER_${DB_NAME} =

  (ADDRESS_LIST =

    (ADDRESS =

      (PROTOCOL = IPC)

      (KEY = ${DB_NAME}.WORLD)

    )

    (ADDRESS =

      (PROTOCOL = IPC)

      (KEY = ${DB_NAME})

    )

    (ADDRESS =

      (PROTOCOL = TCP)

      (HOST = ${SERVER_NAME})

      (PORT = ${PORT_NUMBER})

    )

  )

STARTUP_WAIT_TIME_LISTENER_${DB_NAME} = 0

CONNECT_TIMEOUT_LISTENER_${DB_NAME} = 10

TRACE_LEVEL_LISTENER_${DB_NAME} = OFF

SID_LIST_LISTENER_${DB_NAME} =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = ${DB_NAME})

      (ORACLE_HOME = $ORACLE_HOME)

    )

  )

EOF

echo "New listener entry for ${DB_NAME} added to $LISTENER_PATH"

# Define paths for sqlnet.ora

SQLNET_PATH="$ORACLE_HOME/network/admin/sqlnet.ora"

SQLNET_BACKUP_PATH="${SQLNET_PATH}_backup_$(date +%Y%m%d_%H%M%S)"

# Backup sqlnet.ora

if [ -f "$SQLNET_PATH" ]; then

  cp "$SQLNET_PATH" "$SQLNET_BACKUP_PATH"

  echo "Backup of sqlnet.ora created at $SQLNET_BACKUP_PATH"

fi

# Append to sqlnet.ora

cat <<EOF >> "$SQLNET_PATH"

AUTOMATIC_IPC = ON

TRACE_LEVEL_CLIENT = OFF

NAMES.DEFAULT_DOMAIN = WORLD

NAME.DEFAULT_ZONE = WORLD

SQLNET.EXPIRE_TIME = 0

TCP.NODELAY=YES

EOF

#start the listener

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

lsnrctl start LISTENER_${DB_NAME}

TNSPING_LOG="tnsping_${DB_NAME}.log"

# Run tnsping and log the output

tnsping ${DB_NAME}.WORLD > "$TNSPING_LOG" 2>&1

# Check tnsping result

if grep -q "OK" "$TNSPING_LOG"; then

  echo "TNSPING successful: This is good"

else

  echo "TNSPING failed: Please check the configuration"

fi

# Check logs for errors or warnings

LOG_FILES="/oracle/${DB_NAME}/admin/scripts/*.log"

OUTPUT_FILE="/oracle/${DB_NAME}/admin/scripts/error_check.log"

ERRORS_FOUND=false

> "$OUTPUT_FILE"  # Clear previous output

for file in $LOG_FILES

do

  if grep -iq "ORA-" "$file" ; then

    ERRORS_FOUND=true

    ERR_FILE="${file}_err"

    # Extract relevant lines and save to .log_err file

    grep -iE "ORA-" "$file" > "$ERR_FILE"

    echo "Issues found in: $(basename "$file") $(basename "$ERR_FILE")" >> "$OUTPUT_FILE"

  fi

done

if [ "$ERRORS_FOUND" = false ]; then

  echo "Everything is good with Db creation." > "$OUTPUT_FILE"

fi

#Backup /etc/oratab with timestamp

timestamp=$(date +%Y%m%d_%H%M%S)

cp /etc/oratab /oracle/$DB_NAME/admin/scripts/oratab_bak_$timestamp

# Append entry to /etc/oratab

echo "${DB_NAME}:${ORACLE_HOME}:Y" >> /etc/oratab

echo "Entry added to /etc/oratab and backup created as /oracle/$DB_NAME/admin/scripts/oratab_bak_$timestamp"

# Set environment variables arch mode conversion

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

# Check if DB is already in ARCHIVELOG mode

archive_mode=$(sqlplus -s / as sysdba <<'EOF'

SET HEADING OFF

SET FEEDBACK OFF

SET PAGESIZE 0

SELECT log_mode FROM v$database;

EXIT

EOF

)

archive_mode=$(echo "$archive_mode" | xargs)  # Trim whitespace

if [ "$archive_mode" == "ARCHIVELOG" ]; then

  echo "Database is already in ARCHIVELOG mode. Skipping conversion."

else

  echo "Converting database to ARCHIVELOG mode..."

  sqlplus / as sysdba <<EOF

  SPOOL convert_db_archive_mode_bct.log

  SHOW PARAMETER spfile;

  CREATE PFILE='$ORACLE_HOME/pfile_before_arch.ora' FROM SPFILE;

  SHUTDOWN IMMEDIATE;

  STARTUP MOUNT;

  ALTER DATABASE ARCHIVELOG;

  ALTER DATABASE OPEN;

  ARCHIVE LOG LIST;

  alter database enable block change tracking using file '/oracle/${DB_NAME}/SystemTBS/${DB_NAME}_block_change.dbf';

  SPOOL OFF

  EXIT

EOF

fi

echo "exporting Db variables final check"

export ORACLE_HOME=$ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=$DB_NAME

EMAIL="BALAmani@abc.com"

# Run SQL to get DB name and open mode

DB_STATUS=$(sqlplus -s / as sysdba <<'EOF'

SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF

SELECT open_mode FROM v$database;

EXIT;

EOF

)

# Check if SQL*Plus ran successfully

if [[ $? -ne 0 ]]; then

    echo "SQL*Plus execution failed."

    exit 1

fi

# Trim whitespace

DB_STATUS=$(echo "$DB_STATUS" | xargs)

# Log the status

echo "Database status returned: $DB_STATUS"

# Check if status is "READ WRITE"

if [[ "$DB_STATUS" == "READ WRITE" ]]; then

    echo "DB creation completed successfully." | mailx -s "DB $DB_NAME build completed, proceed with next steps" "$EMAIL"

else

    echo "Database is not in READ WRITE mode. Current status: $DB_STATUS"

    exit 1

fi

exit 0

script to list the oracle database ORA- errors from oracle Database views

 

script to list the oracle database ORA- errors

COLUMN ORIGINATING_TIMESTAMP FORMAT A40

COLUMN message_text FORMAT A100

set linesize 300

set pages 300

SELECT ORIGINATING_TIMESTAMP , message_text

FROM X$DBGALERTEXT

WHERE originating_timestamp > (SYSDATE - 5)

AND message_text LIKE '%ORA-%'

ORDER BY originating_timestamp;

ORIGINATING_TIMESTAMP                    MESSAGE_TEXT

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

14-JUL-25 11.25.14.115 AM +01:00         ORA-1100 signalled during: alter database mount...

15-JUL-25 01.52.59.280 PM +01:00         Patch Description: ORA-00800  SOFT EXTERNAL ERROR, ARGUMENTS  [SET PRIORITY FAILED], [VKTM] , DISM(1

                                         6)

ORA-800 [Set Priority Failed] [VKTM] [Check traces and OS configuration] [Check Oracle 19.24.0.0

Issue:

we had ORA-800 popping up after the DB creation  and we tried different approaches ,but the error is not going away on oracle database 19.24.0.0.

Error:

ORA-800 [Set Priority Failed] [VKTM] [Check traces and OS configuration] [Check Oracle document and MOS notes] [] [] [] [] [] [] [] []

[00]: dbgexExplicitEndInc [diag_dde]

[01]: dbgeEndDDEInvocationImpl [diag_dde]

[02]: ksbsethighpri_int [background_proc]<-- Signaling

[03]: ksbrdp [background_proc]

[04]: opirip [OPI]

solution :

we had ORA-800 popping up after the DB creation  and we tried different approaches ,but the error is not going away on oracle database 19.24.0.0.Then we applied the below Patch and  issue resolved.

p34672698_1924000DBRU_Linux-x86-64.zip

no more ORA-800 on the alert log.

Step -by Step Applying the Patch:

**********************bug patch apply on standalone server************************

(I) Prerequisites

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

Before you install or deinstall the patch, ensure that you meet the following requirements:

1. Ensure that the Oracle home on which you are installing the patch or from which you are rolling back the patch is Oracle Database 19 Release 19.24.0.0.240716DBRU.

2. Ensure that 19 Release 19.24.0.0.240716DBRU Patch Set Update (PSU) 36582781 is already applied on the Oracle Database.

3. Oracle recommends you to use the latest version available for 19 Release 19.24.0.0.240716DBRU. If you do not have OPatch 19 Release 19.24.0.0.240716DBRU, then download it from patch# 6880880 for 19.24.0.0.240716DBRU release.

4. Ensure environment variable ORACLE_HOME is set correctly.

5. Ensure that the $PATH definition has the following executables: make, ar, ld and nm. The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin.

6. Before beginning patch application, check the consistency of inventory information for GI home and each database home to be patched. Run the following command as respective Oracle home owner to check the consistency.

    % $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME

    Note:

    - If this command succeeds, it lists the Oracle components that are installed in the home.

    - Save the output so you have the status prior to the patch apply.

    - If this command fails, contact Oracle Support Services for assistance.

7. (Only for Installation) Maintain a location for storing the contents of the patch ZIP file. In the rest of the document, this location (absolute path) is referred to as <PATCH_TOP_DIR>. Extract the contents of the patch ZIP file to the location (PATCH_TOP_DIR) you have created above. To do so, run the following command:

    $ unzip -d <PATCH_TOP_DIR> p34672698_1924000DBRU_Linux-x86-64.zip

8. (Only for Installation) Determine whether any currently installed interim patches conflict with this patch 34672698 as shown below:

    $ cd <PATCH_TOP_DIR>/34672698

    $ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

    The report will indicate the patches that conflict with this patch and the patches for which the current 34672698 is a superset.

    Note:

    Upon execution of the Opatch command, it validates the patch and ensures that there are no conflicts with the software already installed in the ORACLE_HOME. OPatch categorizes conflicts into the following types:

    - Conflicts with a patch already applied to the ORACLE_HOME that is a subset of the patch you are trying to apply  - In this case, continue with the patch installation because the new patch contains all the fixes from the existing patch in the ORACLE_HOME. The subset patch will automatically be rolled back prior to the installation of the new patch.

    - Conflicts with a patch already applied to the ORACLE_HOME - In this case, stop the patch installation and contact Oracle Support Services.

(II) Installation

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

To install the patch, follow these steps:

     As root user, change oradism permissions

        Pre-install    //these make the existing oradism owned by grid home owner and replaceable

        a.ls -ld $GI_HOME/bin/oradism

          i.-rwsr-x--- 1 root  dba  <GI_HOME>/bin/oradism

2. Apply Patch to DB home(s) as DB home owner :

       Set your current directory to the directory where the patch is located

       and then run the OPatch utility by entering the following commands:

       As root user, change oradism permissions:

       $ cd <PATCH_TOP_DIR>/34672698

         Pre-install    //these make the existing oradism owned by "oracle" user and replaceable

         a.ls -ld $ORACLE_HOME/bin/oradism

           i.-rwsr-x--- 1 root  dba  <ORACLE_HOME>/bin/oradism

         b.change ownership to the DB home owner (i.e oracle)

           i.chown oracle $ORACLE_HOME/bin/oradism

           ii.chmod 0750 $ORACLE_HOME/bin/oradism

         c.ls -ld $ORACLE_HOME/bin/oradism

       As DB home owner, apply the patch:

make sure NO DB & listener process running

  ---> /oracle/TESTDB78/19.0.0/OPatch/opatch apply -oh /oracle/TESTDB78/19.0.0 -local /oracle/TESTDB78/19.0.0/34672698

<ORACLE_HOME>/OPatch/opatch apply -oh <ORACLE_HOME> -local <PATCH_TOP_DIR>/34672698

3. Verify whether the patch has been successfully installed by running the following command:

   As  the Oracle Database home owner, run the following command:

   $ opatch lsinventory -oh <ORACLE_HOME>

3b. Post-install

    These make the new oradism binary owned by root with setuid, and executable by "oracle" user

3b.2 - For DB home(s) as root user

        a.ls -ld $ORACLE_HOME/bin/oradism

        b.change owner to 'root' and enable setuid bit

          i.chown root $ORACLE_HOME/bin/oradism

          ii.chmod 4750 $ORACLE_HOME/bin/oradism

        c.ls -ld $ORACLE_HOME/bin/oradism

----when I delete the patch ,it generate the error : when I apply the patch ,it stops the error

Starting background process VKTM

2025-07-03T11:25:14.278411+01:00

Errors in file /oracle/TESTDB78/diag/rdbms/TESTDB78/TESTDB78/trace/TESTDB78_vktm_276905.trc  (incident=28045):

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []

Incident details in: /oracle/TESTDB78/diag/rdbms/TESTDB78/TESTDB78/incident/incdir_28045/TESTDB78_vktm_276905_i28045.trc

2025-07-03T11:25:14.279793+01:00

----Stop the DB services

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

----pre-checks

<server_name>:/oracle/TESTDB78/19.0.0/34672698> /oracle/TESTDB78/19.0.0/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.46

Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

   from           : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version    : 12.2.0.1.46

OUI version       : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-15_13-28-23PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Version 19.24.0.0.0

[oracle@<server_name> 34672698]$ ls -ld $ORACLE_HOME/bin/oradism

-rwxr-x--- 1 oracle dba 147848 Jul  3 11:14 /oracle/TESTDB78/19.0.0/bin/oradism

[oracle@<server_name> 34672698]$ /oracle/TESTDB78/19.0.0/OPatch/opatch apply -oh /oracle/TESTDB78/19.0.0 -local /oracle/TESTDB78/19.0.0/34672698

Oracle Interim Patch Installer version 12.2.0.1.46

Copyright (c) 2025, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

   from           : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version    : 12.2.0.1.46

OUI version       : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-42-39AM_1.log

Verifying environment and performing prerequisite checks...

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

Start OOP by Prereq process.

Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.46

Copyright (c) 2025, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

   from           : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version    : 12.2.0.1.46

OUI version       : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-43-30AM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   34672698

Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/oracle/TESTDB78/19.0.0')

Is the local system ready for patching? [y|n]

y

User Responded with: Y

Backing up files...

Applying interim patch '34672698' to OH '/oracle/TESTDB78/19.0.0'

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patch 34672698 successfully applied.

Log file location: /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-43-30AM_1.log

OPatch succeeded.

How to disable automatic restart of the cluster services in oracle RAC

 

login as root user in linux server  and check the status of cluster resources

1)# ./crsctl status resource -t

stop all the services

2)[root@<server_name> bin]# ./crsctl stop cluster -all

3)disable the automatic restart of the cluster

[root@<server_name> bin]# ./crsctl disable has

***************************************************

[root@<server_name> bin]# ./crsctl status resource -t

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

Name           Target  State        Server                   State details

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

Local Resources

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

ora.DATA.SHARED.advm

               ONLINE  ONLINE       <server_name>                 Volume device /dev/a

                                                             sm/shared-500 is onl

                                                             ine,STABLE

ora.DATA.dg

               ONLINE  ONLINE       <server_name>                 STABLE

ora.FRA.dg

               ONLINE  ONLINE       <server_name>                 STABLE

ora.LISTENER.lsnr

               ONLINE  ONLINE       <server_name>                 STABLE

ora.asm

               ONLINE  ONLINE       <server_name>                 Started,STABLE

ora.data.shared.acfs

               ONLINE  ONLINE       <server_name>                 mounted on /shared,S

                                                             TABLE

ora.net1.network

               ONLINE  ONLINE       <server_name>                 STABLE

ora.ons

               ONLINE  ONLINE       <server_name>                 STABLE

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       <server_name>                 STABLE

ora.LISTENER_SCAN2.lsnr

      1        ONLINE  ONLINE       <server_name>                 STABLE

ora.LISTENER_SCAN3.lsnr

      1        ONLINE  ONLINE       <server_name>                 STABLE

ora.cdb1.db

      1        OFFLINE OFFLINE                               Instance Shutdown,ST

                                                             ABLE

ora.<server_name>.vip

      1        ONLINE  ONLINE       <server_name>                 STABLE

ora.collabn2.vip

      1        ONLINE  INTERMEDIATE <server_name>                 FAILED OVER,STABLE

ora.cvu

      1        ONLINE  ONLINE       <server_name>                 STABLE

ora.oc4j

      1        OFFLINE OFFLINE                               STABLE

ora.rac.db

      1        OFFLINE OFFLINE                               Instance Shutdown,ST

                                                             ABLE

      2        OFFLINE OFFLINE                               STABLE

ora.scan1.vip

      1        ONLINE  ONLINE       <server_name>                 STABLE

ora.scan2.vip

      1        ONLINE  ONLINE       <server_name>                 STABLE

ora.scan3.vip

      1        ONLINE  ONLINE       <server_name>                 STABLE

ora.target.db

      1        OFFLINE OFFLINE                               STABLE

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

[root@<server_name> bin]# ./crsctl stop cluster -all

CRS-2673: Attempting to stop 'ora.crsd' on '<server_name>'

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on '<server_name>'

CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on '<server_name>'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on '<server_name>'

CRS-2673: Attempting to stop 'ora.FRA.dg' on '<server_name>'

CRS-2673: Attempting to stop 'ora.data.shared.acfs' on '<server_name>'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on '<server_name>'

CRS-2673: Attempting to stop 'ora.<server_name>.vip' on '<server_name>'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on '<server_name>'

CRS-2673: Attempting to stop 'ora.cvu' on '<server_name>'

CRS-2677: Stop of 'ora.LISTENER.lsnr' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.scan3.vip' on '<server_name>'

CRS-2673: Attempting to stop 'ora.scan2.vip' on '<server_name>'

CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.scan1.vip' on '<server_name>'

CRS-2677: Stop of 'ora.cvu' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.<server_name>.vip' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.collabn2.vip' on '<server_name>'

CRS-2677: Stop of 'ora.data.shared.acfs' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.DATA.SHARED.advm' on '<server_name>'

CRS-2677: Stop of 'ora.DATA.SHARED.advm' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.DATA.dg' on '<server_name>'

CRS-2677: Stop of 'ora.FRA.dg' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.scan1.vip' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.scan2.vip' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.scan3.vip' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.collabn2.vip' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.DATA.dg' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.asm' on '<server_name>'

CRS-2677: Stop of 'ora.asm' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.ons' on '<server_name>'

CRS-2677: Stop of 'ora.ons' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.net1.network' on '<server_name>'

CRS-2677: Stop of 'ora.net1.network' on '<server_name>' succeeded

CRS-2792: Shutdown of Cluster Ready Services-managed resources on '<server_name>' has completed

CRS-2677: Stop of 'ora.crsd' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.ctssd' on '<server_name>'

CRS-2673: Attempting to stop 'ora.evmd' on '<server_name>'

CRS-2673: Attempting to stop 'ora.storage' on '<server_name>'

CRS-2677: Stop of 'ora.storage' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.asm' on '<server_name>'

CRS-2677: Stop of 'ora.evmd' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.ctssd' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.asm' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on '<server_name>'

CRS-2677: Stop of 'ora.cluster_interconnect.haip' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on '<server_name>'

CRS-2677: Stop of 'ora.cssd' on '<server_name>' succeeded

[root@<server_name> bin]#

[root@<server_name> bin]# ./crsctl disable has

CRS-4621: Oracle High Availability Services autostart is disabled.

Pl/Sql script to generate n number of datafile in oracle database.

 we have scenario ,used to add 100 plus datafiles to a tablespace  and we dont need to create it manually,I have created pl/sql script to generate n number of datafiles for this table space.

This is really helped us.

DECLARE

    v_tablespace_name VARCHAR2(30) := 'USER'; -- Specify your tablespace name

    v_datafile_prefix VARCHAR2(50) := '/path/to/datafile'; -- Specify the base path for datafiles

    v_datafile_size_mb NUMBER := 100; -- Specify the size of each datafile in MB

    v_num_files NUMBER := 100; -- Specify the number of datafiles to add

    

    v_sql VARCHAR2(1000);

BEGIN

    FOR i IN 1..v_num_files LOOP

        -- Generate SQL to add datafile

        v_sql := 'ALTER TABLESPACE ' || v_tablespace_name ||

                 ' ADD DATAFILE ''' || v_datafile_prefix || i || '.dbf''' ||

                 ' SIZE ' || v_datafile_size_mb || 'M AUTOEXTEND OFF NEXT 100M MAXSIZE UNLIMITED;';

        

        -- Output SQL statement

        DBMS_OUTPUT.PUT_LINE(v_sql);

    END LOOP;

END;

/

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration],

 issue:

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], [] error reported on oracle database 64 bit 19.16.0.0

Root cause:

looks like oradism  (file located under cd $ORACLE_HOME/bin) permission issue.

Solution:

1) check if the below parameter set to _highest_priority_processes=VKTM in DB level ,if not set

alter system set _highest_priority_processes=VKTM scope=spfile;

2) check cd $ORACLE_HOME/bin oradism  and make sure it has Sticky bit set,if not set the sticky bit and reboot the DB.

cd $ORACLE_HOME/bin ls -lrt oradism (see the sticky bit)

-rwxr-x--- 1 oracle oinstall 147848 Apr 17  2019 oradism  >>>>>>>>>>>>>>>>>> wrong one

-rwsr-x--- 1 root oinstall 147848 Apr 17 2019 oradism >>>>>>>>>>>>>>>>>>>> Correct one

ORA-00800 and ORA-700 in oracle database 19.16.0.0

 Issue:

we have recently build new oracle database with version 19.16.0.0 and alert log reported the below 2 errors.

Errors in file /oracle/DB_name/diag/rdbms/DB_name/DB_name/trace/DB_name_vktm_60713.trc  (incident=54058):

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], [] (Doc ID 2718971.1)

Incident details in: /oracle/DB_name/diag/rdbms/DB_name/DB_name/incident/incdir_54058/DB_name_vktm_60713_i54058.trc

2022-11-09T12:21:15.626799+00:00

Error attempting to elevate VKTM's priority: no further priority changes will be attempted for this process

VKTM started with pid=5, OS id=60713

Root cause and solution:

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM] (Doc ID 2718971.1)I

In the DB_name DB system, it seems "$ORACLE_HOME/bin/oradism" file permissions and ownership are not as per the oracle recommendation. It should be owned by root:oinstall and setups bit set. Will inform to System admin to set the ownership and permission.

$ ls -latr $ORACLE_HOME/bin/oradism

-rwsr-x--- 1 root oinstall 147848 Apr 17 2019 oradism >>>>>>>>>>>>>>>>>>>>>>>> Correct one

Need run from root user:

# chmod u+s oradism

************************************************************

Issue:

PGA_AGGREGATE_TARGET specified is high

Errors in file /oracle/DB_name/diag/rdbms/DB_name/DB_name/trace/DB_name_ora_60307.trc  (incident=48015):

ORA-00700: soft internal error, arguments: [pga physmem limit], [3221225472], [3033848422], [], [], [], [], [], [], [], [], []

Incident details in: /oracle/DB_name/diag/rdbms/DB_name/DB_name/incident/incdir_48015/DB_name_ora_60307_i48015.trc

LICENSE_MAX_SESSION = 0

Oracle Recommendation :

ORA-700 [sms physmem limit] / [pga physmem limit] Errors while Starting the Database (Doc ID 2610312.1)

The issue is caused by overall memory configuration, i.e. the SGA TARGET + PGA LIMIT total is greater than system's memory limit. Here SGA TARGET + PGA memory allocated  is around 92 % of total physical memory. We have changed “pga_aggregate_target” memory value as per recommendation. Please see below details for reference. We will check the alert log after a DB bounce to confirm the alerts.

/oracle/DB_name/diag/rdbms/DB_name/DB_name/incident/incdir_48015/DB_name_ora_60307_i48015.trc

----- Beginning of Customized Incident Dump(s) -----

WARNING: PGA_AGGREGATE_TARGET (3072 MB) is too high for

         the amount of physical memory (15531 MB) and

         SGA size (8192 MB) - it should be less than 2893 MB.

Thumb rule to setup PGA_AGGREGATE_TARGET:

(Doc ID 2610312.1)

Total memory = 100%  

Reserved for OS = 20% 

Out of the remaining 80% 

PGA_AGGREGATE_TARGET = 50% of 80%   

SGA_TARGET = 50% of 80% of total memory

***********************************************************

PGA_AGGREGATE_TARGET = 25% of 85% total memory 3

PGA_AGGREGATE_LIMIT = 50% of 85% total memory 6

SGA_TARGET = 50% of 85% total memory

***********************************************************

How to change the time zone of time in oracle database

 we have requirement to change the time zone format of the data (UTC to CST)  from the column and oracle provided solution to convert the time zones.

Here is the function that you should use to convert the timezone:

to_char(NEW_TIME(SCHEDULED_TIME,'GMT','CDT'), 'dd-mm-yy hh:mi:ss')

old time zone data:

WORK_NAME JOB_NAME  NEWTIME_TEST

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

servername124        FINAL            06-09-22 03:29:00 PM

new  time zone data:

WORK_NAME JOB_NAME  NEWTIME_TEST

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

servername124         FINAL            07-09-22 10:29:00 AM

ORA-39181: Only partial table data may be exported due to fine grain access control on

 

Issue:

I got the below error while doing the export of few tables.

. exported "admin"."REG_CONFIG"                          6.507 KB       1 rows

. . exported "admin"."TEST_TO_UPLOAD"                    5.156 KB       3 rows

ORA-39181: Only partial table data may be exported due to fine grain access control on "admin"."fiel_TAB"

Root cause:

Looks like fine grained access policy has been enabled on those tables and need to granted the access .

Solution:

select * from dba_policies where OBJECT_OWNER='ADMIN' AND OBEJCT_NAME='FIEL_TAB';

grant EXEMPT ACCESS POLICY to  ADMIN;

ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found

 issue:

when I tried to register the oracle DB with catalog DB during registration process got the below error.

RMAN> REGISTER DATABASE;

database registered in recovery catalog

starting full resync of recovery catalog

got ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found

ORA-06512: at "RMAN.DBMS_RCVCAT", line 7261 during resync

retrying with snapshot controlfile

Creating and using snapshot control file for resync

starting full resync of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03008: error while performing automatic resync of recovery catalog

ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found

Cause:

could be the same Db already registered and not properly unregistred ,so that we  got the above error.

Solution:

connect to RMAN catalog DB using Catalog user and  execute the below package.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

4RMANCAT  READ WRITE

SQL> select 'EXEC DBMS_RCVCAT.UNREGISTERDATABASE('||DB_KEY||','||DBID||');'

  command, resetlogs_time

 from rc_database

 where name = '&db_name';  2    3    4

Enter value for db_name: TESTDB

old   4:  where name = '&db_name'

new   4:  where name = 'TESTDB'

 from rc_database

      *

ERROR at line 3:

ORA-00942: table or view does not exist

SQL> conn rman

Enter password:

Connected.

SQL> select 'EXEC DBMS_RCVCAT.UNREGISTERDATABASE('||DB_KEY||','||DBID||');'

  command, resetlogs_time

 from rc_database

 where name = '&db_name';  

Enter value for db_name: TESTDB

old   4:  where name = '&db_name'

new   4:  where name = 'TESTDB'

COMMAND

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

RESETLOGS

---------

EXEC DBMS_RCVCAT.UNREGISTERDATABASE(85399,987618234);

28-JAN-21

SQL> EXEC DBMS_RCVCAT.UNREGISTERDATABASE(85399,987618234);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

> rman target / catalog rman@RCATDB

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 12 06:11:50 2022

Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=987618234)

recovery catalog database Password:

connected to recovery catalog database

recovery catalog schema version 19.11.00.00. is newer than RMAN version

RMAN> REGISTER DATABASE ;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN> list db_unique_name all;

List of Databases

DB Key  DB Name  DB ID            Database Role    Db_unique_name

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

86302   TESTDB    987618234        PRIMARY          TESTDB

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