Enable remote SYSDBA login

UNIX OS user who is in the same Group as the user who installed Oracle on the server can login as SYS from the server locally. The key to log onto Oracle as SYS from a remote client is to set init.ora parameter REMOTE_LOGIN_PASSWORDFILE not equal to NONE (the default?).

The steps to make it work:

Step 1: Set the init parameter, and then bounce the database:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

EXCLUSIVE forces the password file to be tied exclusively to a single instance. In 10gR2 release, the value EXCLUSIVE is supported for backward compatibility and now has the same behavior as the value SHARED (Reference B14237-04).

Step 2: Create a passwor file:
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=xxxxx

ORAPWD has an option "entries=", which is not mandatory. I do not know what is the default number. In most cases, just do not use this option.

Step 3: Verify below view returns at least one row (username SYS):
SQL> select * from v$pwfile_users;

If yes, then everything works and the new passwordfile is in use by the username on the list. Now, SYSDBA can logon to the database from a remote client.

Additional notes:
1. If "grant sysdba to user_A;" is run by SYS, the user_A will show up in view v$pwfile_users. And user_A can login as SYSDBA useing its own password (not the one in passwordfile).
2. Without Step 1, Step 2 really does not do anything.
3. The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect as SYSDBA or SYSOPER from a remote machine. This parameter was deprecated in 11g and should not be used, it is for 'normal' users that use OS authentication (Note 50507.1).
4. If REMOTE_OS_AUTHENT is set to FALSE, OS_AUTHENT_PREFIX does not take any effect.

datapump export / import tables

Scripts use datapump to export and import data on table level.

~~~~~~~~~~~~~~~~~ export data from source db ~~~~~~~~~~~~~~~~
-- exp_init.par to export data with filters on tables (for Golden Gate initial load)
DIRECTORY=EXP_DIR
DUMPFILE=exp_init.dmp
LOGFILE=exp_init.log
PARALLEL=8
EXCLUDE=INDEX
EXCLUDE=CONSTRAINT
EXCLUDE=GRANT
EXCLUDE=TRIGGER
EXCLUDE=SYNONYM
TABLES=BUS_UNIT,BUS_UNIT_COST,CLAIM
QUERY=BUS_UNIT_COST:"WHERE BE_ID <> 250681 and BE_ID <> 251013",
   CLAIM:"WHERE BE_ID <> 250681 and BE_ID <> 251013"

-- Shell script to call the .par file. Use "nohup" to run it.
export ORACLE_SID=SOURCEDB
PWD=`/path/XXXX`
$ORACLE_HOME/bin/expdp userID/$PWD parfile=exp_init.par

~~~~~~~~~~~~~~~~~~~~ import ~~~~~~~~~~~~~~~~~~~~~
-- optional: drop indexes first (and disable triggers if any)
SQL> alter table userid.CLAIM drop PRIMARY KEY drop INDEX;
SQL> drop INDEX userid.clm_INDX1;

-- imp_init.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp_init.dmp
LOGFILE=imp_init.log
PARALLEL=8
TABLE_EXISTS_ACTION=TRUNCATE
TABLES=BUS_UNIT,BUS_UNIT_COST,CLAIM

-- Shell script for importing
export ORACLE_SID=TARGETDB
echo $ORACLE_SID
PWD=`/path/XXXX`
$ORACLE_HOME/bin/impdp userID/${PWD} parfile=imp_init.par

-- after import, re-create indexes, such as
SQL> CREATE UNIQUE INDEX userid.CLM_PK ON userid.CLAIM
  (CLM_ID, BE_ID)
  parallel 4 nologging
  TABLESPACE CLM_INDEX01;
SQL> ALTER TABLE userid.CLAIM ADD (
  CONSTRAINT CLM_PK
  PRIMARY KEY (CLM_ID, BE_ID)
  USING INDEX userid.CLM_PK
  ENABLE VALIDATE);

SQL> grant select on userid.CLAIM to READONLY_RO;
... ... ...