cluster - ORACLEAGENT BLOG

How to access the Hana table in the oracle database?

How does it work?

SQL commands are fired from an oracle database (sqlplus), which will reach out to Oracle Gateway via DB LINK > tnsnames.ora

Oracle Gateway will have a parameter in its init<sid>.ora file and load unixODBC libraries from its LD_LIBRARY_PATH.

unixODBC will load HANA odbc drivers and goes through DSN setting to read data from the HANA database.

Meaning Oracle DB > DBLINK > tnsnames.ora > Oracle Gateway > unixODBC drivers > Data source DSN (odbc.ini) > HANA odbc drivers > HANA DB

If you notice above Oracle Gateway and HANA odbc do not talk to each other directly. Instead, talk through unixODBC drivers.

Step 1 –

The first step is to make unixODBC working

Installed unixODBC RPMs (both 32 and 64-bit) on the RHEL machine where you will run Oracle Gateway.

unixODBC-2.2.14-11.el6.x86_64

unixODBC-devel-2.2.14-11.el6.i686

unixODBC-devel-2.2.14-11.el6.x86_64

unixODBC-2.2.14-11.el6.i686

Step 2 –

Install SAP HANA client (64bit) on the RHEL machine where you will run Oracle Gateway. Please refer to the HANA client installation guide.

Step 3 –

Create /etc/odbc.ini contents look like below

[H1X]

Driver=/usr/sap/hdbclient/libodbcHDB.so

ServerNode=serverhana:30015

Step 4 –

Install Oracle Gateway software. You may please google to find step-by-step on how to install using Oracle Universal Installer.

Step 5 –

Set the environment variable of the user running the oracle gateway. In my case it is

LD_LIBRARY_PATH=/usr/lib64:/usr/sap/hdbclient:/oracle/BW1/112_64/lib

It is very important to have 64bit unixODBC libraries path (/usr/lib64/libodbc.so) in LD_LIBRARY_PATH because we run RHEL x64 and Oracle 64-bit software.

Step 6 –

Create init<sid>.ora. In my case, I will call this dg4odbc (initdg4odbc.ora). The content should be like below.

HS_DB_NAME = H1X

HS_FDS_CONNECT_INFO = H1X <===== This is the DSN name that comes from step 2 /etc/odbc.ini

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_LANGUAGE=AMERICAN_AMERICA.UTF8

HS_NLS_NCHAR=UCS2

HS_FDS_TRANSACTION_MODEL=READ_ONLY

set ODBCINI=/etc/odbc.ini

Step 7 –

Create listener.ora

LISTENER =

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=oragwhost)(PORT=1551))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=dg4odbc)

(ORACLE_HOME=/oracle/BW1/112_64)

(PROGRAM=dg4odbc)

(ENV=”LD_LIBRARY_PATH=/usr/lib64:/oracle/BW1/112_64/lib:”)

)

)

Step 8 –

Start listener

lsnrctl start

Step 9 –

Let’s first test if unixODBC is working

Log in as the user which will run oracle gateway and check LD_LIBRARY_PATH (refer to step 4 above) and use the below commands.

isql -v <DSN name from step 2 /etc/odbc.ini> <hana user name> <password>

For example isql -v H1X SYSTEM password

+—————————————+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+—————————————+

SQL> select * from dummy

+——+

| DUMMY|

+——+

| X |

+——+

SQLRowCount returns 1

1 rows fetched

If you see these outputs you are halfway through. unixODBC is working.

Now it’s time to work on the oracle database from where data will be read with SELECT statements.

Step 10 –

Add entries in tnsnames.ora In my case it will look like the below.

dg4odbc =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=oragwhost)(PORT=1551))

(CONNECT_DATA=(SID=dg4odbc))

(HS=OK)

)

And test it with tnsping

Step 11 –

Create a DB link in the oracle database and run a SELECT command. Commands look like this.

CREATE PUBLIC DATABASE LINK H1X CONNECT TO

“SYSTEM” IDENTIFIED BY “password” USING ‘dg4odbc’;

SQL> select * from dummy@H1X;

DUMMY
——–
X

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Resolving common Oracle Wait Events using the Wait Interface

Wait Event:

log file parallel write

Possible Causes:

LGWR waits while writing the contents of the redo log buffer cache to the online log files on the disk
I/O wait on subsystem holding the online redo log files

ACTIONS:

Reduce the amount of redo being generated
Do not leave tablespaces in hot backup mode for longer than
necessary
Do not use RAID 5 to redo log files
Use faster disks to redo log files
Ensure that the disks holding the archived redo log files and the online redo log files are separate so as to avoid contention
Consider using NOLOGGING or UNRECOVERABLE options in SQL statements.

REMARKS:

Reference Note# 34583.1

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Resolving common Oracle Wait Events using the Wait Interface

Wait Event:

DB file scattered reads

Possible Causes:

The Oracle session has requested and is waiting for multiple contiguous database blocks (up to DB_FILE_MULTIBLOCK_READ_COUNT) to be read into the SGA from the disk.
Full Table scans
Fast Full Index Scans

ACTIONS:

Optimize multi-block I/O by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT
Partition pruning to reduce the number of blocks visited
Consider the usage of multiple buffers pools and caches of frequently used indexes/tables in the KEEP pool
Optimize the SQL statement that initiated most of the waits. The goal is to minimize the number of physical and logical reads.
Should the statement access the data by a full table scan or index FFS? Would an index range or unique scan be more efficient?
Does the query use the right driving table?
Are the SQL predicates appropriate for hash or merge join?
If full scans are appropriate, can parallel query improve the response time?
The objective is to reduce the demands for both the logical and
physical I/Os, and this is best achieved through SQL and application tuning.
Make sure all statistics are representative of the actual data. Check the LAST_ANALYZED date.

REMARKS:

If an application that has been running fine for a while suddenly clocks a lot of time on the DB file scattered read event and there hasn’t been a code change, you might want to check to see if one or more indexes have been dropped or become unusable.
DB file scattered read Reference Note# 34558.1

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Resolving common Oracle Wait Events using the Wait Interface

Wait Event:

DB file sequential reads

Possible Causes:

Use of an unselective index
Fragmented Indexes
High I/O on a particular disk or mount point
Bad application design
Index reads performance can be affected by slow I/O subsystem and/or poor database files layout, which results in a higher average
wait time

Actions:

Check indexes on the table to ensure that the right index is being used
Check the column order of the index with the WHERE clause of the Top SQL statements
Rebuild indexes with a high clustering factor
Use partitioning to reduce the number of blocks being visited
Make sure optimizer statistics are up to date
Relocate ‘hot’ datafiles
Consider the usage of multiple buffers pools and cache frequently used indexes/tables in the KEEP pool
Inspect the execution plans of the SQL statements that access data
through indexes
Is it appropriate for the SQL statements to access data through
index lookups?
Is the application an online transaction processing (OLTP) or decision support system (DSS)?
Would full table scans be more efficient?
Do the statements use the right driving table?
The optimization goal is to minimize both the number of logical and physical I/Os.

Remarks:

The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from the disk.
Significant DB file sequential read wait time is most likely an application issue.
If the DBA_INDEXES.CLUSTERING_FACTOR of the index approaches the number of blocks in the table, then most of the rows in the table are ordered. This is desirable.
However, if the clustering factor approaches the number of rows in the table, it means the rows in the table are randomly ordered and thus it requires more I/Os to complete the operation. You can improve the index’s clustering factor by rebuilding the table so that rows are ordered according to the index key and rebuilding the index thereafter.
The OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING initialization parameters can influence the optimizer to favor the operation of the nested loop and choose an index access path over a full table scan.
Tuning I/O-related waits for Note id 223117.1
DB file sequential read Reference Note# 34559.1

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Datapump Utilities Import Parameters

[oratest@oracle ~]$ impdp help=y

Import: Release 19.0.0.0.0 - Production on Mon Aug 9 22:52:15 2021
Version 19.3.0.0.0

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


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

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

The available keywords and their descriptions follow. Default values are listed 
within square brackets.

ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object's process order number in the master table.

ACCESS_METHOD
Instructs Import to use a particular method to load data.
Valid keyword values are: [AUTOMATIC], CONVENTIONAL, DIRECT_PATH,
EXTERNAL_TABLE, and INSERT_AS_SELECT.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].

CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keywords are: DISABLE_APPEND_HINT, ENABLE_NETWORK_COMPRESSION,
REJECT_ROWS_WITH_REPL_CHAR, SKIP_CONSTRAINT_ERRORS, CONTINUE_LOAD_ON_FORMAT_ERROR,
TRUST_EXISTING_TABLE_PARTITIONS and VALIDATE_TABLE_DATA.

DIRECTORY
Directory object to be used for dump, log and SQL files.

DUMPFILE
List of dump files to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION_PASSWORD
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.

ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo is suppressed while standard input is read.

ESTIMATE
Calculate network job estimates.
Valid keywords are: [BLOCKS] and STATISTICS.

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Import everything from source [YES].

HELP
Display help messages [NO].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of import job to create.

KEEP_MASTER
Retain the master table after an import job that completes successfully [NO].

LOGFILE
Log file name [import.log].

LOGTIME
Specifies that messages displayed during import operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.

MASTER_ONLY
Import just the master table and then stop the job [NO].

METRICS
Report additional job information to the import log file [NO].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [NO].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file.

PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].

QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_DATAFILE
Redefine data file references in all DDL statements.

REMAP_SCHEMA
Objects from one schema are loaded into another schema.

REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.

REMAP_TABLESPACE
Tablespace objects are remapped to another tablespace.

REUSE_DATAFILES
Tablespace will be initialized if it already exists [NO].

SCHEMAS
List of schemas to import.

SERVICE_NAME
Name of an active service and associated resource group to constrain 
Oracle RAC resources.

SKIP_UNUSABLE_INDEXES
Skip indexes that were set to the Index Unusable state.

SOURCE_EDITION
Edition to be used for extracting metadata.

SQLFILE
Write all the SQL DDL to a specified file.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STREAMS_CONFIGURATION
Enable the loading of Streams metadata [YES].

TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to import.

TARGET_EDITION
Edition to be used for loading metadata.

TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE,
LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, SEGMENT_CREATION,
STORAGE, and TABLE_COMPRESSION_CLAUSE.

TRANSPORTABLE
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.

TRANSPORT_DATAFILES
List of data files to be imported by transportable mode.

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].
Only valid in NETWORK_LINK mode import operations.

TRANSPORT_TABLESPACES
List of tablespaces from which metadata is loaded.
Only valid in NETWORK_LINK mode import operations.

VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

VIEWS_AS_TABLES
Identifies one or more views to be imported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.
Note that in network import mode, a table name is appended
to the view name.

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

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

START_JOB
Start or resume current job.
Valid keywords are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keywords are: IMMEDIATE.

STOP_WORKER
Stops a hung or stuck worker.

TRACE
Set trace/debug flags for the current job.

Include and Exclude

Include

impdp testdb/testdb schemas=apple include=TABLE:"IN ('test', 'emp')" 
directory=dumpdir  dumpfile=include.dmp logfile=include.log

Exclude

impdp testdb/testdb schemas=apple  exclude=TABLE:"= 'employee'" 
directory=dumpdir dumpfile=exclude.dmp logfile=exclude_im.log

Content {Meta_data_only / data_only}

Meta_data only

impdp system/password schemas=apple  directory=dump_dir  dumpfile=apple_meta.dmp 
logfile=apple_meta01.log content=METADATA_ONLY

Data_only

impdp system/password schemas=apple  directory=dump_dir  dumpfile=apple_data.dmp 
logfile=apple_data.log content=DATA_ONLY

Query

impdp system/password full=Y directory=dump_dir dumpfile=full.dmp logfile=full.log 
query='apple.EMP:"WHERE no=1",orange.DEPT:"WHERE no=2"'

Flashback_Scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3795360

impdp system/password directory=dumpdir dumpfile=flash.dmp logfile=flash.log
flashback_scn=3795360

Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_IMPORT_SCHEMA_01 is:
  /home/oracle/backup/flash.dmp
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri Aug 6 00:40:13 2021
elapsed 0 00:00:31

Flashback_Time

impdp directory=dumpdir dumpfile=flash_time.dmp logfile=flash_time.log’ 
tables=india.tamil 
flashback_time=\”to_timestamp(\’08-06-2021 00:48:00\’, \’DD-MM-YYYY HH24:MI:SS\’)\”

PARFILE

[oratest@oracle ~]$ cat > schema_par
directory=dump dumpfile=schema_par.dmp
logfile=schema.log
schemas=india
tables=apple

[oratest@oracle ~]$ impdp system/oracle PARFILE=schema_par

TABLES

SQL> create or replace directory dump_dir as '/home/oracle/backup';

Directory created.

[oracle@oracle ~]$ impdp  directory=dump_dir dumpfile=india.dmp logfile=india.log 
tables=india.apple
Export: Release 19.0.0.0.0 - Production on Wed Aug 4 00:03:28 2021
Version 19.3.0.0.0

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

Username: test
Password:

Tablespace

impdp system/password 
directory=dump_dir 
dumpfile=tablespace.dmp 
logfile=tablespace.log 
tablespaces=system,india

Attach

The ATTACH parameter is used to attach the  interrupted job and status of data pump 
operation.

[oracle@oracle ~]$ impdp system/oracle attach=SYS_EXPORT_SCHEMA_01

Export: Release 19.0.0.0.0 - Production on Mon Aug 20 22:19:02 2018
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Job: SYS_EXPORT_SCHEMA_01
Owner: SYSTEM

Remap Table

[oracle@oracle ~]$ impdp DIRECTORY=dumpdir DUMPFILE=india_table.dmp 
logfile=india_table1.log TABLES=india.apple REMAP_TABLE=india.apple:mango

Username: system
Password:

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "INDIA"."MANGO"                             5.609 KB       8 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "INDIA"."SYS_IMPORT_TABLE_01" successfully completed at Wed Aug 4 21:49:48 2021 
elapsed 0 00:00:19

Remap_schema

[oracle@oracle ~]$ impdp directory=dumpdir dumpfile=india_owner.dmp 
logfile=india_owner1.log remap_schema=tamil:english

Username: system
Password:

. . imported "ENGLISH"."T1"                              5.539 KB       2 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 4 22:24:10 2021 
elapsed 0 00:00:17

Remap_Tablespace

[oracle@oracle backup]$ impdp  directory=dumpdir dumpfile=test_tablespace.dmp 
logfile=test_tablespace1.log remap_tablespace=test:orcl

Username: system
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dumpdir 
dumpfile=test_tablespace.dmp logfile=test_tablespace1.log remap_tablespace=test:orcl
Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 4 22:56:25 2021 
elapsed 0 00:00:02

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Datapump Utilities Export Parameters

expdp help =y

[oracle@sample ~]$ expdp help=y

Export: Release 19.0.0.0.0 - Production on Thu Aug 5 21:33:52 2021
Version 19.3.0.0.0

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


The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

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

The available keywords and their descriptions follow. Default values are listed
 within square brackets.

ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object's process order number in the master table.

ACCESS_METHOD
Instructs Export to use a particular method to unload data.
Valid keyword values are: [AUTOMATIC], DIRECT_PATH and EXTERNAL_TABLE.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

COMPRESSION_ALGORITHM
Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.

CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keyword values are: GROUP_PARTITION_TABLE_DATA, VERIFY_STREAM_FORMAT and 
XML_CLOBS.

DIRECTORY
Directory object to be used for dump and log files.

DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY 
and NONE.

ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.

ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].

ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.

ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.

ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.

ESTIMATE_ONLY
Calculate job estimates without performing the export [NO].

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FILESIZE
Specify the size of each dump file in units of bytes.

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Export entire database [NO].

HELP
Display Help messages [NO].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of export job to create.

KEEP_MASTER
Retain the master table after an export job that completes successfully [NO].

LOGFILE
Specify log file name [export.log].

LOGTIME
Specifies that messages displayed during export operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.

METRICS
Report additional job information to the export log file [NO].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [NO].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file name.

QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].

SAMPLE
Percentage of data to be exported. 

SCHEMAS
List of schemas to export [login schema].

SERVICE_NAME
Name of an active Service and associated resource group to constrain 
Oracle RAC resources.

SOURCE_EDITION
Edition to be used for extracting metadata.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to export.

TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.

VERSION
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.

VIEWS_AS_TABLES
Identifies one or more views to be exported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.

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

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO]. 

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.

STOP_WORKER
Stops a hung or stuck worker.

TRACE
Set trace/debug flags for the current job.

Full Database Export

[oracle@oracle backup]$  expdp system/password directory=dumpdir dumpfile=full.dmp 
logfile=full.log full=y



Master table "INDIA"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for INDIA.SYS_EXPORT_FULL_01 is:
  /home/oracle/backup/full.dmp
Job "INDIA"."SYS_EXPORT_FULL_01" successfully completed at Fri Aug 6 00:19:43 2021 
elapsed 0 00:02:41

Include and Exclude

Include

expdp testdb/testdb schemas=apple include=TABLE:"IN ('test', 'emp')" 
directory=dumpdir dumpfile=include.dmp logfile=include.log

Exclude

expdp testdb/testdb schemas=apple  exclude=TABLE:"= 'employee'" 
directory=dumpdir dumpfile=exclude.dmp logfile=exclude.log

Content {Meta_data_only / data_only}

Meta_data only

expdp system/password schemas=apple  directory=dump_dir  dumpfile=apple_meta.dmp 
logfile=apple_meta.log content=METADATA_ONLY

Data_only

expdp system/password schemas=apple  directory=dump_dir  dumpfile=apple_data.dmp 
logfile=apple_data.log content=DATA_ONLY

Query

expdp system/password full=Y directory=dump_dir dumpfile=full.dmp logfile=full.log 
query='apple.EMP:"WHERE no=1",orange.DEPT:"WHERE no=2"'

Flashback_Scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3795360


expdp system/password directory=dumpdir dumpfile=flash.dmp logfile=flash.log 
flashback_scn=3795360

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/backup/flash.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 6 00:40:13 2021 
elapsed 0 00:00:31

Flashback_Time

expdp directory=dumpdir 
dumpfile=flash_time.dmp 
logfile=flash_time.log' 
tables=india.tamil 
flashback_time=\"to_timestamp(\'08-06-2021 00:48:00\', \'DD-MM-YYYY HH24:MI:SS\')\"

PARFILE

[oratest@oracle ~]$ cat > schema_par
directory=dump dumpfile=schema_par.dmp
logfile=schema.log
schemas=india
tables=apple

[oratest@oracle ~]$ expdp system/oracle PARFILE=schema_par

TABLES

SQL> create or replace directory dump_dir as '/home/oracle/backup';

Directory created.

[oracle@oracle ~]$ expdp  directory=dump_dir dumpfile=india.dmp logfile=india.log 
tables=india.apple

Export: Release 19.0.0.0.0 - Production on Wed Aug 4 00:03:28 2021
Version 19.3.0.0.0

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

Username: test
Password:

Tablespace

exp system/password directory=dump_dir dumpfile=tablespace.dmp logfile=tablespace.log 
tablespaces=system,india

Attach

The ATTACH parameter is used to attach the  interrupted job and 
status of data pump operation.

[oracle@oracle ~]$ expdp system/oracle attach=SYS_EXPORT_SCHEMA_01

Export: Release 19.0.0.0.0 - Production on Mon Aug 6 22:19:02 2021
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Job: SYS_EXPORT_SCHEMA_01
Owner: SYSTEM

Transport_Tablespaces

Use the TRANSPORT_TABLESPACES  parameter to specify a list of tablespace names for 
which object metadata will be exported from the source database into the 
target database.

Keep INDIA Tablespace in Read only mode.

SQL> alter tablespace INDIA read only;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/india01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf

[oracle@oracle]$ scp /u01/app/oracle/oradata/proddb/india01.dbf 
[email protected]/u01/app/oracle/oradata/TEST/datafile

[oracle@oracle export]$ expdp DUMPFILE=india.dmp  LOGFILE=india.logDIRECTORY=dumpdir 
TRANSPORT_TABLESPACES=india 

Export: Release 19.0.0.0.0 - Production on Thu Aug 6 23:07:59 2018
Version 19.3.0.0.0

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

Username: system
Password:

******************************************************************************
Datafiles required for transportable tablespace INDIA:
/u01/app/oracle/oradata/proddb/india01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu 
Aug 6 23:08:48 2021

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Export and Import Utilities

Export(exp) and import(imp) are Logical backup and Recovery. When exporting the database objects are dumped to a binary file which can then be imported into another Oracle database.

The Export and Import utilities provide a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.

When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file. First, let us see export in detail.

Difference between exp/imp and expdp/impdp

Exp/imp is a Traditional  export import,expdp/impdp is a Datapump export    
  import.

Traditional export import utility is starting with exp/imp,Datapump utility   
is starting with expdp/impdp. 

Datapump access files in the server (using ORACLE directories). Traditional export can 
access files in client and server both (not using ORACLE directories).

exp/imp (Traditional)  is using conventional path , expdp /impdb (Datapump) is using 
Direct path.

Exp (Traditional) is byte mode ,Datapump is block mode.

Data Pump will recreate the user, whereas the old imp utility required the DBA to
create the user ID before importing.

Datapump utility we can stop and Restart the Jobs.

Features of Datapump utility

Job Estimation can be Done in Datapump.

Data Remapping can be done using REMAPDATA parameter.

EXCLUDE and INCLUDE parameter allows the fine-grained object selection.

Failed export/import Jobs can be Restarted.

Export and import can be taken over the network using database links even 
without Generating the dump file using NETWORK_LINK parameter.

CONTENT parameter gives the freedom for what to export with options METADATA ONLY,
DATA, BOTH.

You don’t need to specify the BUFFER size in datapump

Job estimated completion time can be monitored from v$session_longops view.

Dump file can be compressed with COMPRESSION parameter. In conventional exp/imp you
have to compress the dumps using OS utilities.

Data encryption can be done in datapump.

DATAPUMP has interactive options like ADD_FILE, START_JOB, KILL_JOB, STOP_JOB.

REUSE_DUMPFILES parameter asks the confirmation/rewrite the existing dumpfile.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

SRVCTL command in RAC in 19c Database

Oracle RAC Administration and Monitoring Scripts -2

There are lots of options used with SRVCTL command, i mentioned them below.

-d Database Name
-i Instance Name
-s Service Name
-n Node Name
-r Preferred list
-a Available list
-p TAF (Transparent application failover policy)
-v Verbose

Status and Config Database

We can check all instance status of database as follows.

srvctl status database -d DB_NAME

[oracle@RAMdbrac01 ~]$ srvctl status database -d RAM
Instance RAM1 is running on node RAMdbrac01
Instance RAM2 is running on node RAMdbadm02
Instance RAM3 is running on node RAMdbadm03
Instance RAM4 is running on node RAMdbadm04
[oracle@RAMdbrac01 ~]$

We can display the configurations (instances name, nodes and oracle home) of any RAC Database as follows.

srvctl config database -d DB_NAME

srvctl config database -d RAM

Shutdown RAC Database

We can shutdown all instances of any database as follows.

srvctl stop database -d db_name [-o stop_options]
srvctl stop database -d DB_NAME
srvctl stop database -d DB_NAME -o normal
srvctl stop database -d DB_NAME -o immediate
srvctl stop database -d DB_NAME -o transactional
srvctl stop database -d DB_NAME -o abort

[oracle@RAMdbrac01 ~]$ srvctl stop database -d RAM

Startup RAC Database

We can start all instances of any database as follows.

srvctl start database -d db_name [-o start_options]

srvctl start database -d DB_NAME

srvctl start database -d DB_NAME -o nomount
srvctl start database -d DB_NAME -o mount
srvctl start database -d DB_NAME -o open

[oracle@RAMdbrac01 ~]$ srvctl start database -d RAM

Shutdown An Instance

We can shutdown only specific Instance of RAC database as follows.

srvctl stop instance -d db_unique_name [-i "instance_name_list"]} [-o stop_options] [-f]

srvctl stop instance -d DB_NAME -i INSTANCE_NAME

[oracle@RAMdbrac01 ~]$ srvctl stop instance -d RAM -i RAM1

Startup and Status An Instance

We can startup only specific Instance(s) of RAC database as follows.

srvctl start instance -d db_unique_name [-i "instance_name_list"} [-o start_options]

srvctl start instance -d DB_NAME -i INSTANCE_NAME

[oracle@RAMdbrac01 ~]$ srvctl start instance -d RAM -i RAM1

We can display any instance of RAC database as follows.

srvctl status instance -d DB_NAME -i INSTANCE_NAME

[oracle@RAMdbrac01 ~]$ srvctl status instance -d RAM -i RAM1

Add and Remove Database from CRS

We can add new DB to CRS as follows.

srvctl add database -d db_unique_name -o ORACLE_HOME [-p spfile]

srvctl add database -d RAM -o /u01/app/product/12.1.0.2/dbhome_1 -p +DATAC1/RAM/parameterfile/spfileRAM.ora

We can remove existing DB from CRS as follows.

srvctl remove database -d db_unique_name [-f] [-y] [-v]

srvctl remove database -d DB_NAME -f -y

Stop, Start and Check ASM via SRVCTL

We can shutdown an ASM instance as follows.

srvctl stop asm -n NODE_NAME -o immediate

srvctl stop asm -n RAMdbrac01 -o immediate

We can startup an ASM instance as follows.

srvctl start asm -n NODE_NAME

srvctl start asm -n RAMdbrac01

We can display configuration and status an ASM instance as follows.

srvctl status asm -n NODE_NAME
srvctl config asm -n NODE_NAME

[oracle@RAMdbrac01 ~]$ srvctl status asm -n RAMdbrac01
ASM is running on RAMdbrac01
[oracle@RAMdbrac01 ~]$
[oracle@RAMdbrac01 ~]$
[oracle@RAMdbrac01 ~]$ srvctl config asm -n RAMdbrac01
PRKO-2207 : Warning:-node option has been deprecated and will be ignored.
ASM home: <CRS home>
Password file: +DBFS_DG/orapwASM
ASM listener: LISTENER
[oracle@RAMdbrac01 ~]$

Stop, Start and Check Service via SRVCTL

We can start, stop and check any service on Database as follows.

srvctl status service -d db_name
srvctl config service -d db_name
srvctl start service -d dbname -s service_name
srvctl stop service -d dbname -s service_name

srvctl add service -d {DB_NAME} -s {SERVICE_NAME} -r {"preferred_list"} -a {"available_list"} [-P {BASIC | NONE | PRECONNECT}]

srvctl relocate service -d db_name -s service_name -i instance_name -t new_instance_name [-f]

We can remove any service as follows.

srvctl remove service -d {DB_NAME} -s {SERVICE_NAME}

srvctl remove service -d RAM -s RAM_SALES

Enable/disable Autorestart of Instance and Database

We can enable and disable auto restart of the database as follows.

srvctl enable database -d DB_UNIQUE_NAME
srvctl disable database -d DB_UNIQUE_NAME

We can enable and disable auto restart of the instance

srvctl enable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME
srvctl disable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME

Start, Stop and Check status,configurations of NodeApps

srvctl status nodeapps -n nodename
srvctl config nodeapps -n nodename
srvctl start nodeapps -n nodename
srvctl stop nodeapps -n nodename

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Oracle RAC Administration and Monitoring Scripts

Oracle RAC Administration and Monitoring Scripts -1

To check clusterware status, execute following command.

[root@racdb1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1

[root@racdb ~]$ crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

To check cluster status, execute following command.

[root@racdb ~]$ crsctl check cluster 
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@racdb ~]$

To check all cluster resource status, execute following command.

[root@Prodecidbrac01 ~]# crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details 
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATAC1.dg
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.DBFS_DG.dg
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.RECOC1.dg
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.asm
ONLINE ONLINE Prodecidbrac01 Started,STABLE
ONLINE ONLINE Prodecidbrac02 Started,STABLE
ora.net1.network
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.ons
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE Prodecidbrac02 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE Prodecidbrac01 169.254.94.159,STABL
E
ora.cvu
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.dbm01.db
1 ONLINE OFFLINE STABLE
2 ONLINE OFFLINE Instance Shutdown,ST
ABLE
ora.ebys.db
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
2 ONLINE ONLINE Prodecidbrac02 Open,STABLE
ora.ebytest.db
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.mgmtdb
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
ora.oc4j
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.repdb.db
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
2 ONLINE ONLINE Prodecidbrac02 Open,STABLE
ora.scan1.vip
1 ONLINE ONLINE Prodecidbrac02 STABLE
ora.scan2.vip
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.scan3.vip
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.test.db
1 OFFLINE OFFLINE STABLE
2 ONLINE OFFLINE Instance Shutdown,ST
ABLE
ora.test.db
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.Prodeci.db
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
2 ONLINE ONLINE Prodecidbrac02 Open,STABLE
ora.Prodecidbrac01.vip
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.Prodecidbrac02.vip
1 ONLINE ONLINE Prodecidbrac02 STABLE
--------------------------------------------------------------------------------
[root@Prodecidbrac01 ~]#

RAC Cluster Command ( Scripts )

To stop Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

[root@racdb1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1

[root@racdb ~]$ crsctl stop crs

To start Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

[root@racdb1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1

[root@racdb ~]$ crsctl start crs

To disable Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

[root@racdb ~]$ crsctl disable crs

To enable Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

[root@racdb ~]$ crsctl enable crs

To Query Voting disk location, execute following command. Set ASM profile before executing crsctl command.

[oracle@racdb ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE bd81ab8e27a64fbebf7b6b326c5f614d (/Prod/mapper/HDD_E0_S03_1418288932p1) [DATA]
2. ONLINE 450684e276874ff9bff52d481befa809 (/Prod/mapper/HDD_E0_S15_1420185788p1) [DATA]
3. ONLINE fd1efd6de5204f6bbfe9793a480c517f (/Prod/mapper/HDD_E0_S06_1423843312p1) [DATA]
Located 3 voting disk(s).

To find OCR files location, execute following command.

[root@racdb ~]# cat /etc/oracle/ocr.loc
#Prodice/file +DATA getting replaced by Prodice +DATA/Prodeci-c/OCRFILE/registry.255.923150247
ocrconfig_loc=+DATA/Prodeci-c/OCRFILE/registry.255.923150247
local_only=false
[root@racdb ~]#

To check the status of the Oracle Cluster registry , execute following command.

[root@racdb ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 2412
Available space (kbytes) : 407156
ID : 2022573354
Prodice/File Name : +DATA
Prodice/File integrity check succeeded
Prodice/File not configured
Prodice/File not configured
Prodice/File not configured
Prodice/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
[root@racdb ~]#

To add voting disk, execute following command. Set ASM profile before executing crsctl command.

crsctl add css votedisk new_vote_disk_path

To Delete voting disk, execute following command. Set ASM profile before executing crsctl command.

crsctl delete css votedisk new_vote_disk_path

To Add voting disk to ASM Disk, execute following command.

crsctl add votedisk asm_disk_group

To Replace or Migrate voting disk, execute following command.

crsctl replace votedisk asm_diskgroup / vote_disk_path

To find OCR backup location, execute following command. Set ASM profile before executing crsctl command.

ocrconfig –showbackup auto

To change OCR Backup location, execute following command.

ocrconfig –backuploc Shared_PATH

To Add OCR files location, execute following command.

ocrconfig –add Location_PATH

To Replace or Change OCR files location, execute following command.

ocrconfig –replace New_PATH

To Repair OCR files location, execute following command. Cluster or Oracle RAC Services should be power off.

ocrconfig –repair –add +DATAC1

There are lots of options used with SRVCTL command, i described them below.

-d Database Name
-i Instance Name
-s Service Name
-n Node Name
-r Preferred list
-a Available list
-p TAF (Transparent application failover policy)
-v Verbose

Click this link for srvctl commands here.

Tag Archives: cluster

How does it work?

Step 1 –

Step 2 –

Step 3 –

Step 4 –

Step 5 –

Step 6 –

Step 7 –

Step 8 –

Step 9 –

Step 10 –

Step 11 –

Wait Event:

log file parallel write

DB file scattered reads

DB file sequential reads

Actions:

Remarks:

Include and Exclude

Include

Exclude

Content {Meta_data_only / data_only}

Meta_data only

Data_only

Query

Flashback_Scn

Flashback_Time

PARFILE

TABLES

Tablespace

Attach

Remap Table

Remap_schema

Remap_Tablespace

expdp help =y

Full Database Export

Include and Exclude

Include

Exclude

Content {Meta_data_only / data_only}

Meta_data only

Data_only

Query

Flashback_Scn

Flashback_Time

PARFILE

TABLES

Tablespace

Attach

Directory

Transport_Tablespaces

Difference between exp/imp and expdp/impdp

Features of Datapump utility

Oracle RAC Administration and Monitoring Scripts -2

Status and Config Database

Shutdown RAC Database

Startup RAC Database

Shutdown An Instance

Startup and Status An Instance

Add and Remove Database from CRS

Stop, Start and Check ASM via SRVCTL

Stop, Start and Check Service via SRVCTL

Enable/disable Autorestart of Instance and Database

Start, Stop and Check status,configurations of NodeApps

Oracle RAC Administration and Monitoring Scripts -1

To check clusterware status, execute following command.

To check cluster status, execute following command.

To check all cluster resource status, execute following command.

To stop Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

To start Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

To disable Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

To enable Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.

To Query Voting disk location, execute following command. Set ASM profile before executing crsctl command.

To find OCR files location, execute following command.

To check the status of the Oracle Cluster registry , execute following command.

To add voting disk, execute following command. Set ASM profile before executing crsctl command.

To Delete voting disk, execute following command. Set ASM profile before executing crsctl command.

To Add voting disk to ASM Disk, execute following command.

To Replace or Migrate voting disk, execute following command.