How to import an Oracle database schema to another database with a different schema name

During import, whenever your source schema name is different from target schema name, use REMAP_SCHEMA parameter:

impdp system/pwd dumpfile=sourceschema.dmp directory=DATA_PUMP_DIR schemas=sourceschema logfile=sourceschema_imp.log remap_schema=sourceschema:targetschema

How to connect to an Oracle database hosted on a different server from ksh script

This can be done two ways, by providing tns string inside the script or by placing it into tnsnames.ora file:

FILE1=output.log
sql="INSERT INTO TABLE1(...)VALUES(...);"
sqlplus 'username/pwd@@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHostname)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=myDB)))' << EOF > $FILE1
$sql
commit;
exit;
EOF

FILE1=output.log
sql="INSERT INTO TABLE1(...)VALUES(...);"
sqlplus 'username/pwd@DB_ALIAS' << EOF > $FILE1
$sql
commit;
exit;
EOF

How to store sqlplus output into a single or multiple variables via ksh script

db_status=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       select status from v\$instance;
       exit
EOF`
echo $db_status

The above works for when you want to retrieve a single value. If you want to retrieve multiple values, use:

output=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       select host_name, instance_name, status from v\\$instance;
       exit
EOF`

var1=`echo $output | awk '{print $1}'`
var2=`echo $output | awk '{print $2}'`
var3=`echo $output | awk '{print $3}'`
echo $var1
echo $var2
echo $var3

You can also put your sql statement into a file with .sql extension. Something like that:

set head off
set verify off
set feedback off
set pages 0

SELECT field1, field2, field3 
FROM Table1;

exit;

And then read the script values into variables:

sqlplus -s "/ as sysdba" @script2run.sql.sql | read var1 var2 var3

Fixing warnings and errors after running impdp

First check the registry to see if the list of invalid objects by running the query below and see which objects have status set to INVALID:

SELECT comp_id, version, status FROM dba_registry;

or simply do:

SELECT COUNT(*) FROM dba_registry WHERE status='INVALID';

If there are any invalid objects, run utlrp script located in which is located in $ORACLE_HOME/rdbms/admin/utlrp.sql. This scrip will recompile inalid objects.

@?/rdbms/admin/utlrp.sql

The line above will run the script.

Next step is to check dba_registry again to see if all invalid objects have been fixed.

How to use grep command to exclude the lines beginning with a pattern

For instance when reading from a file you want to exclude all the comments i.e. lines beginning with hash sign (#), then use -v switch for grep command:

grep -v "^\#" $FILENAME

SCN Explained

How Oracle transaction data change is written to a datafile

1. Transaction starts
2. Required data blocks are found in the buffer cache. If not found in the buffer cache, then found in the datafile
3. Transaction modifies the data blocks in buffer cache, identified as "dirty data" and is written to the log buffer
4. Transaction is committed and LGWR process writes the dirty data in the log buffer to the redo log file
5. CKPT process updates the headers of all the datafiles and DBWn process writes the dirty data from Buffer Cache to the datafile

SCN - system change number. the SCN is an internal number maintained by the database to log the changes made. it is sort of an Oracle clock - every time transaction commits, it increments. There are a total of 4 SCN : System checkpoint SCN, the datafile checkpoint SCN, Start SCN and Stop SCN.

System checkpoint SCN - when a checkpoint operation is cimpleted, the Oracle stores the system checkpoint SCN in the control file

SELECT checkpoint_change# FROM V$DATABASE

It is for the entire database.

Datafile checkpoint SCN - when checkpoint operation is completed, Oracle stores it separately in the control file

SELECT name, checkpoint_change# FROM V$DATAFILE

Start SCN - checkpoint SCN stored in the file header of each datfile. it is called start SCN because it is used to start the database instance

SELECT name, checkpoint_change# FROM V$DATAFILE_HEADER

Stop SCN - SCN termination of each datafile header are stored in the control file

SELECT name, last_change# FROM V$DATAFILE

During the normal operation, stop SCN (last_change# field) is an infinite number or is NULL.

Current SCN is retrieved using:

select dbms_flashback.get_system_change_number from dual;

Whenever a transaction is comitted, the log writer process (LGWR) writes the records from the redo log buffers in SGA to the online redo logs on disk. LGWR also writes the SCN for the transaction to that online redo log file.

When all modified database buffers are written out to datafiles byt the database writer (DBWn) process, a checkpoint occurs. Checkpoint (CKPT) updates all database files and control files with the SCN at the time of the checkpoint. A successful checkpoint guarantees that all database changes up to that SCN have been recorded in the datafiles. As a result, only those changes that occurred afetr the checkpoint, need to be applied during recovery.

Checkpoint typically occurs at intervals specified in LOG_CHECKPOINT_INTERVAl parameter or whenever redo log switch takes place. Oracle stores SCN associated with that checkpoint in 4 places: 3 in control files and 1 in the datafile header for each datafile.

How to determine whether clean shutdown has occurred?

Run

select name,checkpoint_change# from v$datafile_header where name like '%datafilenamehere%';

If a clean shutdown has occurred, the CHECKPOINT_CHANGE# and LAST_CHANGE# will match for each datafile. if an instance crashes at shutdown, the numbers will be different and instance crash recovery will be required at the next instance start up.

Upon the next instance startup Oracle will detect that the stop SCN for datafiles was not set in the control file during startup. So during crash recovery oracle will apply all the redo log records from online redo logs in a process of rolling forward - to ensure that all transactions committed before the crash are applied to the datafiles. Also active transactions that did not commit before the crash will be rolled back.

Instance recovery happens automatically at startup without DBA's intervention.

While the database is up and open for use, the system checkpoint in the control file, the datafile checkpoint SCN in the control file, and the start SCN in each datafile header all match.

During a clean shutdown, a checkpoint is performed and the stop SCN for each datafile is set to the start SCN from the datafile header. Upon startup, Oracle checks the start SCN in the file header with the datafile checkpoint SCN. If they match, Oracle checks the start SCN in the datafile header with the datafile stop SCN in the control file. If they match, the database can be opened because all block changes have been applied, no changes were lost on shutdown, and therefore no recovery is required on startup. After the database is opened, the datafile stop SCN in the control file once again changes to NULL to indicate that the datafile is open for normal use.

System crash - For database after a system crash, upon startup, the stop SCN is not set (LAST_CHANGE#) and thus the Oracle will know the instance crashed because the checkpoint on a shutdown was not performed. If it had been performed, the CHECKPOINT_CHANGE# and LAST_CHANGE# values would match for each datafile as they do during clean shutdown.

During crash recovery, Oracle applies redo log records from the online redo logs in a process referred to as roll forward to ensure that all transactions committed before the crash are applied to the datafiles. Following roll forward, active transactions that did not commit are identified from the rollback segments and are undone before the blocks involved in the active transactions can be accessed. This process is referred to as roll back.

Media failure - In case of a media failure, the datafile checkpointSCN and start SCN in the datafile header won't match at instance startup.

How to retrieve the SQL used to create database links for Oracle database

The code used to cretae the database links can be retrieved using dbms_metadata.get_ddl:

SET serveroutput on
SET size 1000
SET long 1000

select dbms_metadata.get_ddl('DB_LINK',db_link,owner) from dba_db_links;