PRVF-5507 : NTP daemon or service in not running -Rac Error

December 1, 2016

PRVF-5507 : NTP daemon or service in not running

[root@tilak1/]# cat /etc/sysconfig/ntpd
# Drop root to id ‘ntp:ntp’ by default.
OPTIONS=”-u ntp:ntp -p /var/run/ntpd.pid”

# Set to ‘yes’ to sync hw clock after successful ntpdate
SYNC_HWCLOCK=no

# Additional options for ntpdate
NTPDATE_OPTIONS=””
OPTIONS=”-u ntp:ntp -p /var/run/ntpd.pid -x”

[root@tilak1/]# service ntpd stop
[root@tilak1/]# service ntpd start

[root@tilak1/]# chkconfig ntpd off

=========================
Adding daemon to inittab
CRS-4124: Oracle High Availability Services startup failed.
CRS-4000: Command Start failed, or completed with errors.
ohasd failed to start: Inappropriate ioctl for device
ohasd failed to start at /grid/app/11.2.0/grid/crs/install/rootcrs.pl line 443.
After googling, i found that 11.2.0.1 does not support RHL 6.0.

Here is workaround to fix the issue.

Step 1
Login to racnode1 and open the file $GRID_HOME/crs/install/s_crsconfig_lib.pm
Add the following command before # Start OHASD

my $UPSTART_OHASD_SERVICE = “oracle-ohasd”;
my $INITCTL = “/sbin/initctl”;

($status, @output) = system_cmd_capture (“$INITCTL start $UPSTART_OHASD_SERVICE”);
if (0 != $status)
{
error (“Failed to start $UPSTART_OHASD_SERVICE, error: $!”);
return $FAILED;
}

Step 2
Create a file /etc/init/oracle-ohasd.conf with below content.

# Oracle OHASD startup
start on runlevel [35]
stop on runlevel [!35]
respawn
exec /etc/init.d/init.ohasd run >/dev/null 2>&1

Step 3
Rollback the root.sh changes on racnode1.

cd $GRID_HOME/crs/install

# ./roothas.pl -deconfig -force -verbose

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

Issue –
[root@rac2 ~]# /u01/app/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /u01/app/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …

Creating /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
Installing Trace File Analyzer
OLR initialization – successful
Adding Clusterware entries to upstart
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node rac1, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
Start of resource “ora.ctssd” failed
CRS-2672: Attempting to start ‘ora.ctssd’ on ‘rac2’
CRS-2674: Start of ‘ora.ctssd’ on ‘rac2’ failed
CRS-4000: Command Start failed, or completed with errors.
Failed to start Oracle Grid Infrastructure stack

Failed to start Cluster Time Synchronisation Service – CTSS at /u01/app/11.2.0/grid/crs/install/crsconfig_lib.pm line 1288.

Solution
Run as Root:
$ ./crsctl start res ora.crsd –init

ORA-15260: permission denied on ASM disk group

December 1, 2016

By postgresdba

ORA-15260: permission denied on ASM disk group

SQL> alter diskgroup DG1 rebalance power 11;
alter diskgroup DG1 rebalance power 11
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group

Solution:
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Automatic Storage Management option

[grid@raj bin]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 16 14:41:21 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Automatic Storage Management option

SQL> alter system set asm_power_limit=11 scope=spfile;
System altered.

SQL> alter diskgroup DG1 rebalance power 11;
Diskgroup altered.

spfile and pfile errors

November 27, 2016

By postgresdba

SQL> create pfile=’/home/oracle/karan/admin/pfile/initclone.ora’ from spfile;

create pfile=’/home/oracle/karan/admin/pfile/initclone.ora’ from spfile

ERROR at line 1:

ORA-01565: error in identifying file ‘?/dbs/[email protected]’

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> show parameter spfile;

NAME TYPE VALUE

———————————— ———– ——————————

spfile string

SQL> create spfile=

SQL> create spfile=’/home/oracle/karan/admin/pfile/initclone.ora’ from pfile;

create spfile=’/home/oracle/karan/admin/pfile/initclone.ora’ from pfile

ERROR at line 1:

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file

‘/oraeng/app/oracle/product/11.2.0/dbs/initkiruba.ora’

SQL> create spfile from pfile;

create spfile from pfile

ERROR at line 1:

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file

‘/oraeng/app/oracle/product/11.2.0/dbs/initkiruba.ora’

SQL> create spfile from pfile=’/home/oracle/karan/admin/pfile/initclone.ora;

create spfile from pfile=’/home/oracle/karan/admin/pfile/initclone.ora

ERROR at line 1:

ORA-01756: quoted string not properly terminated

SQL> create spfile from pfile=’/home/oracle/karan/admin/pfile/initclone.ora’;

create spfile from pfile=’/home/oracle/karan/admin/pfile/initclone.ora’

ERROR at line 1:

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file

‘/home/oracle/karan/admin/pfile/initclone.ora’

SQL> create spfile from pfile;

create spfile from pfile

ERROR at line 1:

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file

‘/oraeng/app/oracle/product/11.2.0/dbs/initkiruba.ora’

SQL> crecreate spfile from pfile;

SP2-0734: unknown command beginning “crecreate …” – rest of line ignored.

SQL> create spfile from pfile=’/home/oracle/karan/admin/pfile/initclone.ora’;

create spfile from pfile=’/home/oracle/karan/admin/pfile/initclone.ora’

ERROR at line 1:

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file

‘/home/oracle/karan/admin/pfile/initclone.ora’

SQL>

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file ‘/oraeng/app/oracle/product/11.2.0/dbs/initkiruba.ora’

SQL> startup pfile=’/home/oracle/kiruba/admin/pfile/initkiruba.ora’;

ORA-01081: cannot start already-running ORACLE – shut it down first

SQL> /home/oracle/kiruba/admin/pfile/initkiruba.ora

SP2-0734: unknown command beginning “/home/orac…” – rest of line ignored.

SQL> startup pfile=’/home/oracle/kiruba/admin/pfile/initkiruba.ora force;

LRM-00109: could not open parameter file ‘/home/oracle/kiruba/admin/pfile/initkiruba.ora force’

ORA-01078: failure in processing system parameters

SQL> startup pfile=’/home/oracle/kiruba/admin/pfile/initkiruba.ora’force;

LRM-00109: could not open parameter file ‘/home/oracle/kiruba/admin/pfile/initkiruba.oraforce’

ORA-01078: failure in processing system parameters

SQL> startup pfile=’/home/oracle/kiruba/admin/pfile/initkiruba.ora’ force;;

SP2-0714: invalid combination of STARTUP options

solution:-

SQL> !

[oracle@localhost ~]$ locate initkiruba.or

/home/oracle/kiruba/admin/pfile/initkiruba.ora

[oracle@localhost ~]$ exit

exit

SQL> startup pfile=’/home/oracle/kiruba/admin/pfile/initkiruba.ora’ force;

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1340440 bytes

Variable Size 549456872 bytes

Database Buffers 385875968 bytes

Redo Buffers 4927488 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile=’/home/oracle/kiruba/admin/pfile/initkiruba.ora’;

File created.

moving and renaming datafiles –oracle

November 25, 2016

By postgresdba

Moving datafiles of a database: The datafiles reside under /home/oracle/OraHome1/databases/ora9 and have go to /home/oracle/databases/ora9.

SQL> select tablespace_name, substr(file_name,1,70) from dba_data_files;
TABLESPACE_NAME SUBSTR(FILE_NAME,1,70)
—————————— ———————————————————————-
SYSTEM /home/oracle/OraHome1/databases/ora9/system.dbf
UNDO /home/oracle/OraHome1/databases/ora9/undo.dbf
DATA /home/oracle/OraHome1/databases/ora9/data.dbf

SQL> select member from v$logfile;
MEMBER
——————————————————————————–
/home/oracle/OraHome1/databases/ora9/redo1.ora
/home/oracle/OraHome1/databases/ora9/redo2.ora
/home/oracle/OraHome1/databases/ora9/redo3.ora

SQL> select name from v$controlfile;
NAME
——————————————————————————–
/home/oracle/OraHome1/databases/ora9/ctl_1.ora
/home/oracle/OraHome1/databases/ora9/ctl_2.ora
/home/oracle/OraHome1/databases/ora9/ctl_3.ora

Now, as the files to be moved are known, the database can be shut down:
SQL> shutdown

The files can be copied to their destination:
$ cp /home/oracle/OraHome1/databases/ora9/system.dbf /home/oracle/databases/ora9/system.dbf
$ cp /home/oracle/OraHome1/databases/ora9/undo.dbf /home/oracle/databases/ora9/undo.dbf
$ cp /home/oracle/OraHome1/databases/ora9/data.dbf /home/oracle/databases/ora9/data.dbf
$
$ cp /home/oracle/OraHome1/databases/ora9/redo1.ora /home/oracle/databases/ora9/redo1.ora
$ cp /home/oracle/OraHome1/databases/ora9/redo2.ora /home/oracle/databases/ora9/redo2.ora
$ cp /home/oracle/OraHome1/databases/ora9/redo3.ora /home/oracle/databases/ora9/redo3.ora
$
$ cp /home/oracle/OraHome1/databases/ora9/ctl_1.ora /home/oracle/databases/ora9/ctl_1.ora
$ cp /home/oracle/OraHome1/databases/ora9/ctl_2.ora /home/oracle/databases/ora9/ctl_2.ora
$ cp /home/oracle/OraHome1/databases/ora9/ctl_3.ora /home/oracle/databases/ora9/ctl_3.ora

The init.ora file is also copied because it references the control files. I name the copied file just init.ora because it is not in a standard place anymore and it will have to be named explicitely anyway when the database is started up.
$ cp /home/oracle/OraHome1/dbs/initORA9.ora /home/oracle/databases/ora9/init.ora

The new location for the control files must be written into the (copied) init.ora file:
/home/oracle/databases/ora9/init.ora
control_files = (/home/oracle/databases/ora9/ctl_1.ora,
/home/oracle/databases/ora9/ctl_2.ora,
/home/oracle/databases/ora9/ctl_3.ora)

$ sqlplus “/ as sysdba”
SQL> startup exclusive mount pfile=/home/oracle/databases/ora9/init.ora
SQL> alter database rename file ‘/home/oracle/OraHome1/databases/ora9/system.dbf’ to ‘/home/oracle/databases/ora9/system.dbf’;
SQL> alter database rename file ‘/home/oracle/OraHome1/databases/ora9/undo.dbf’ to ‘/home/oracle/databases/ora9/undo.dbf’;
SQL> alter database rename file ‘/home/oracle/OraHome1/databases/ora9/data.dbf’ to ‘/home/oracle/databases/ora9/data.dbf’;

SQL> alter database rename file ‘/home/oracle/OraHome1/databases/ora9/redo1.ora’ to ‘/home/oracle/databases/ora9/redo1.ora’;
SQL> alter database rename file ‘/home/oracle/OraHome1/databases/ora9/redo2.ora’ to ‘/home/oracle/databases/ora9/redo2.ora’;
SQL> alter database rename file ‘/home/oracle/OraHome1/databases/ora9/redo3.ora’ to ‘/home/oracle/databases/ora9/redo3.ora’;

SQL> shutdown

SQL> startup pfile=/home/oracle/databases/ora9/init.ora

Moving Datafiles with/without shutting down the database –oracle

November 25, 2016

By postgresdba

Moving Datafiles with shutting down the database
$ sqlplus “/ as sysdba”
SQL> shutdown immediate
SQL> !mv /u03/oradata/PROD/devl_PROD_01.dbf /u04/oradata/PROD
SQL> startup mount
SQL> alter database rename file ‘/u03/oradata/PROD/devl_PROD_01.dbf’
to ‘/u04/oradata/PROD/devl_PROD_01.dbf’;
SQL> alter database open;
SQL> select * from v$datafile

Moving Datafiles without shutting down the databa
$ sqlplus “/ as sysdba”
SQL> alter tablespace development offline;
SQL> !mv /u03/oradata/PROD/devl_PROD_01.dbf /u04/oradata/PROD
SQL> alter database rename file ‘/u03/oradata/PROD/devl_PROD_01.dbf’
to ‘/u04/oradata/PROD/devl_PROD_01.dbf’;
SQL> alter tablespace development online;
SQL> select * from v$datafile;

Control File Multiplexing Using Pfile –oracle

November 25, 2016

By postgresdba

Control file is head of the every oracle database. If control files lost or corrupt for any kind of physical damage of storage then the database must be shutdown. So every database mast have more than one control files needed on different physical storage. Control files can be multiplexed up to eight times. There are different ways to multiplexing the control file.

Step-1: Shutdown the database as normal
SQL> shutdown normal;

Step-2: Using OS command copy the existing control file to a new name and location
$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl

Step-3: Add the new control file name to PFILE
CONTROL_FILES = (/u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/oradata/orcl/control02.ctl)

Step-4: Start the database
SQL> startup;

Multiplexing Control File Using Spfile -Oracle

November 25, 2016

By postgresdba

Oracle consist of three major physical files, they are :
Controlfiles
Datafiles
Online Redo log files

Among them control files are the most impotent one. Controlfile contains Database name, database creation date, Tablespace names, Physical location of datafiles and Recovery information.
With default installation, Oracle has 3 control files placed in same physical location. According to database availability, It is safe to place the 3 controlfiles in disk.

To see the current physical location of control file
SQL> select name from v$controlfile;
NAME
———————————————————
/ua1/control01.ctl
/ua1/control02.ctl
/ua1/control03.ctl

Suppose we have two HD and those two are mount as /ua1 and /ua2. So we need to move at list one controlfile in /ua1 and we move the 3rd contronlife. there are several way to do the Control File Multiplexing:

1. Using SPFILE:
The steps to multiplex control files using an SPFILE are describe bellow:

Login as SYSDBA
1. Alter the SPFILE: Using the ALTER SYSTEM SET command, alter the SPFILE to include a list of all control files to be used.
SQL> ALTER SYSTEM SET control_files=’/ua1/control01.ctl’
,’/ua1/control01.ctl’, ‘/ua1/control01.ctl’ scope=spfile;

2. Shut down the database: Shut down the database in order to create the additional/ relocate control files on the operating system.
SQL> SHUTDOWN IMMEDIATE;

3. Create additional control files: Using the operating system copy command, create/move the additional control files as required and verify that the files have been created in the appropriate directories.
mv /ua1/control01.ct /ua2

4. Start the database: When the database is started the SPFILE will be read and the Oracle server will maintain all the control files listed in the CONTROL_FILES parameter.
SQL>STARTUP;

To see the changed physical location of control file
SQL> select name from v$controlfile;
NAME
———————————————————
/ua1/control01.ctl
/ua1/control02.ctl
/ua2/control03.ctl

11gr2 software installation errors and solutions

November 23, 2016

By postgresdba

“error code 35” : The machine name in the “/etc/hosts” file is not correct. It needs an entry for the loopback adapter
(localhost) and the machine name. If you are using DNS for name resolution,
you still need the loopback adapter reference in this file.

“error code 37” : The DNS not working properly. You may also get this error is the
“/etc/hosts” file is not configured correctly.

“sqlplus: error while loading shared libraries: libclntsh.so.11.1” : The prerequisites
have not been met. Work through them again. Specifically, make sure the “gcc” package
has been installed.

Listener fails to start – Typically this is due to incorrect name resolution.
Make sure the “/etc/hosts” and/or DNS is configured correctly.

Linking errors – Almost always due to missing prerequisites. Review the setup sections.

Creating Oracle 11g Database manually

November 22, 2016

By postgresdba

Creating Database in 11g is very simple.In 10g you need to create additional
directories bdump,cdump,udump instead of diagnostic dump directory.Below are the
steps:

Step 1:Create Directory structure with Oracle ownership and permission as below:

[oracle@localhost ~]$ cd /u03

[oracle@localhost u03]$ mkdir testdb

[oracle@localhost u03]$ chmod -R 777 /u03/testdb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/testdb/*

[oracle@localhost testdb]$ mkdir adump diag flash_recovery_area

[oracle@localhost u03]$ chmod -R 777 /u03/testdb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/testdb/*

Step 2:Create Parameter file in $ORACLE_HOME/dbs location:

[oracle@localhost testdb]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ vi init_testdb.ora

db_name=’testdb’
memory_target=1G
processes = 150
audit_file_dest=’/u03/testdb/adump’
audit_trail =’db’
db_block_size=8192
db_domain=”
db_recovery_file_dest=’/u03/testdb/flash_recovery_area’
db_recovery_file_dest_size=2G
diagnostic_dest=’/u03/testdb/diag’
dispatchers='(PROTOCOL=TCP) (SERVICE=testdb)’
open_cursors=300
remote_login_passwordfile=’EXCLUSIVE’
undo_tablespace=’UNDOTBS1′
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u03/testdb/ora_control1.ctl,/u03/testdb/ora_control2.ctl)
compatible =’11.2.0′

Step 3:Prepare Create Database script :

[oracle@localhost u03]$ cd /u03/testdb/

[oracle@localhost testdb]$ vi createdb_shaik.sql

CREATE DATABASE testdb
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 ‘/u03/testdb/redo1.log’ SIZE 10M,
GROUP 2 ‘/u03/testdb/redo2.log’ SIZE 10M,
GROUP 3 ‘/u03/testdb/redo3.log’ SIZE 10M
DATAFILE
‘/u03/testdb/system.dbf’ size 200M REUSE
sysaux datafile ‘/u03/testdb/sysaux.dbf’ size 100m
undo tablespace UNDOTBS1
datafile ‘/u03/testdb/undo1.dbf’ size 100m
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE ‘/u03/testdb/temp01.dbf’
SIZE 100M REUSE
CHARACTER SET AL32UTF8
;
:wq

Step 4:Set the Oracle ENVIRONMENT and SID of Database in the Operating System:

[root@localhost testdb]# su – oracle

[oracle@localhost ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=orcl
export TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

:wq
[oracle@localhost ~]$ . .bash_profile
This will set the Oracle Environment variables in Unix-based operating system.

[oracle@localhost ~]$export ORACLE_SID=testdb
This will set the SID of the current Database in Unix-based operating system.

Step 5:Create the Password file.

[oracle@localhost ~]$orapwd file=$ORACLE_HOME/dbs/orapwtestdb
password=Oracle entries=10

Step 6:Create server parameter file.

[oracle@localhost dbs]$ sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 28 14:08:02 2012

Connected to an idle instance.

SQL>create spfile from pfile=’$ORACLE_HOME/dbs/init_testdb.ora’;

step 7:Start the Database in nomount State.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 620759168 bytes
Database Buffers 444596224 bytes
Redo Buffers 4636672 bytes
SQL> select status from v$instance;

STATUS
————
STARTED

Note:Common issue memory_target not supported,refer the below link for resolving:

http://rafioracledba.blogspot.in/2011/06/ora-00845-memorytarget-not-supported-on.html

Step 8:Execute Create Database script created in Step 3

SQL> @/u03/testdb/createdb_shaik.sql

Database created

Step 9:Execute the catalog.sql,catproc.sql and pupbld.sql scripts:
a)catalog.sql =>Creates dictionary tables and views
b)catproc.sql =>Creates PL/SQL procedures,functions and packages necessary.
c)pupbld.sql =>Creates user profiles.

So our database is created. Now just run the catalog.sql,catproc.sql and pupbld.sql
scripts.
WE will find catalog.sql and catproc.sql in $ORACLE_HOME/rdbms/admin path and
pupbld.sql in $ORACLE_HOME/sqlplus/admin path.

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
conn system/manager

SQL>@$ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL> alter user system identified by manager;

User altered.

SQL> conn system
Enter password:
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist

DATE_VALUE FROM PRODUCT_USER_PROFILE
*
ERROR at line 3:
ORA-00942: table or view does not exist

DROP TABLE PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist

ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

DROP TABLE PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist

DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist

View created.

Grant succeeded.

DROP PUBLIC SYNONYM PRODUCT_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist

Synonym created.

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Step 10:Verify the Dictionary views created.

SQL> select name from v$database;

Step 11:Change the Database mode from noarchive log to archive log mode
Changing to archive log mode:

——————————–

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 3
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 620759168 bytes
Database Buffers 444596224 bytes
Redo Buffers 4636672 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> select status from v$instance;

STATUS
————
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
————
OPEN

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3

Export/Import – Data Pump interviews questions and answwer– 1 in oracle

November 19, 2016

By postgresdba

1) What is use of CONSISTENT option in exp?

Cross-table consistency. Implements SET TRANSACTION READ ONLY. Default value N.

2) What is use of DIRECT=Y option in exp?

Setting direct=yes, to extract data by reading the data directly, bypasses the SGA,
bypassing the SQL command-processing layer (evaluating buffer), so it should be faster. Default value N.

3) What is use of COMPRESS option in exp?

Imports into one extent. Specifies how export will manage the initial extent for the table data.
This parameter is helpful during database re-organization.
Export the objects (especially tables and indexes) with COMPRESS=Y.
If table was spawning 20 Extents of 1M each (which is not desirable, taking into account performance), if you export the table with COMPRESS=Y, the DDL generated will have initial of 20M. Later on when importing the extents will be coalesced.
Sometime it is found desirable to export with COMPRESS=N, in situations where you do not have contiguous space on disk (tablespace), and do not want imports to fail.

4) How to improve exp performance?

ANS:

a). Set the BUFFER parameter to a high value. Default is 256KB.
b). Stop unnecessary applications to free the resources.
c). If you are running multiple sessions, make sure they write to different disks.
d). Do not export to NFS (Network File Share). Exporting to disk is faster.
e). Set the RECORDLENGTH parameter to a high value.
f). Use DIRECT=yes (direct mode export).

5) How to improve imp performance?

ANS:

a). Place the file to be imported in separate disk from datafiles.
b). Increase the DB_CACHE_SIZE.
c). Set LOG_BUFFER to big size.
d). Stop redolog archiving, if possible.
e). Use COMMIT=n, if possible.
f). Set the BUFFER parameter to a high value. Default is 256KB.
g). It’s advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import.
Indexes can easily be recreated after the data was successfully imported.
h). Use STATISTICS=NONE
i). Disable the INSERT triggers, as they fire during import.
j). Set Parameter COMMIT_WRITE=NOWAIT(in Oracle 10g) or COMMIT_WAIT=NOWAIT (in Oracle 11g) during import.

6) What is use of INDEXFILE option in imp?

ANS:

Will write DDLs of the objects in the dumpfile into the specified file.

7) What is use of IGNORE option in imp?

ANS:

Will ignore the errors during import and will continue the import.

8) What are the differences between expdp and exp (Data Pump or normal exp/imp)?

ANS:

Data Pump is server centric (files will be at server).
Data Pump has APIs, from procedures we can run Data Pump jobs.
In Data Pump, we can stop and restart the jobs.
Data Pump will do parallel execution.
Tapes & pipes are not supported in Data Pump.
Data Pump consumes more undo tablespace.
Data Pump import will create the user, if user doesn’t exist.

9) Why expdp is faster than exp (or) why Data Pump is faster than conventional export/import?

Data Pump is block mode, exp is byte mode.
Data Pump will do parallel execution.
Data Pump uses direct path API.

10) How to improve expdp performance?

ANS:

Using parallel option which increases worker threads. This should be set based on the number of cpus.

11) How to improve impdp performance?

ANS:

Using parallel option which increases worker threads. This should be set based on the number of cpus.

12) In Data Pump, where the jobs info will be stored (or) if you restart a job in Data Pump, how it will know from where to resume?

Whenever Data Pump export or import is running, Oracle will create a table with the JOB_NAME and will be deleted once the job is done. From this table, Oracle will find out how much job has completed and from where to continue etc.

Default export job name will be SYS_EXPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.
Default import job name will be SYS_IMPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.

13) What is the order of importing objects in impdp?

Tablespaces
Users
Roles
Database links
Sequences
Directories
Synonyms
Types
Tables/Partitions
Views
Comments
Packages/Procedures/Functions
Materialized views

14) How to import only metadata?

ANS:

CONTENT= METADATA_ONLY

15) How to import into different user/tablespace/datafile/table?

ANS:

REMAP_SCHEMA
REMAP_TABLESPACE
REMAP_DATAFILE
REMAP_TABLE
REMAP_DATA

16) Using Data Pump, how to export in higher version (11g) and import into lower version (10g), can we import to 9i?

ANS:

Import data pump can always read export datapump dumpfile sets created by older versions of database. In your case it works, normal expdp on 10g and impdp on 11g
VERSION parameter in datapump is for other way around, if you want to import data taken from 11g into 10g database you need
to specify VERSION while taking backup.

17) How to do transport tablespaces (and across platforms) using exp/imp or expdp/impdp?

ANS: [http://satya-dba.blogspot.in/2010/01/oracle-transportable-tablespaces-tts.html ]

We can use the transportable tablespaces feature to copy/move subset of data (set of user tablespaces), from an Oracle database and plug it in to another Oracle database. The tablespaces being transported can be either dictionary managed or locally managed.

With Oracle 8i, Oracle introduced transportable tablespace (TTS) technology that moves tablespaces between databases. Oracle 8i supports tablespace transportation between databases that run on same OS platforms and use the same database block size.

With Oracle 9i, TTS (Transportable Tablespaces) technology was enhanced to support tablespace transportation between databases on platforms of the same type, but using different block sizes.

With Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats. Oracle Database 10g Release 1 introduced cross platform transportable tablespaces (XTTS), which allows data files to be moved between platforms of different endian format. XTTS is an enhancement to the transportable tablespace (TTS). If ENDIAN formats are different we have to use RMAN (e.g. Windows to Solaris, Tru64 to AIX).

select * from v$transportable_platform order by platform_id;

18)
How to determine the Schemas inside an Oracle Data Pump Export file ?

strings dumpfile.dmp | grep SCHEMA_LIST
(or)
$ strings myfile.dmp|more