How to export/import schema/table with Oracle Data Pump?

Define new user:
SQL> CREATE USER NEWHR
  IDENTIFIED BY newhr
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

SQL>
GRANT CREATE SESSION TO NEWHR;
GRANT ALTER SESSION TO NEWHR;
GRANT CONNECT TO NEWHR;
GRANT RESOURCE TO NEWHR;
GRANT CREATE DATABASE LINK TO NEWHR;
GRANT CREATE SEQUENCE TO NEWHR;
GRANT CREATE SESSION TO NEWHR;
GRANT CREATE SYNONYM TO NEWHR;
GRANT CREATE VIEW TO NEWHR;
GRANT CREATE TYPE TO NEWHR;
GRANT CREATE TABLE TO NEWHR;
GRANT CREATE TRIGGER TO NEWHR;

Check current Oracle directories
SQL> select * from dba_directories;

Define new/replace new Oracle Directory
SQL> create or replace directory export_dir AS 'C:\Alper_ORACLE';

Give grants to user
SQL> grant read, write on directory export_dir to hr;

Export the schema
expdp hr/hr schemas=hr directory=export_dir dumpfile=hr.dmp logfile=hr.log

Export tables
expdp hr/hr tables=employees,employees_new directory=export_dir dumpfile=hr_table.dmp logfile=hr.log

Give grants to new user
SQL> grant read, write on directory export_dir to newhr;

Import schema to new machine
impdp newhr/newhr schemas=hr remap_schema=hr:newhr directory=export_dir dumpfile=hr.dmp logfile=hr.log

Import tables to new machine
impdp newhr/newhr tables=employees,employees_new remap_schema=hr:newhr directory=export_dir dumpfile=hr_table.dmp logfile=hr_table.log

If local storage is not enough to locate export file, use database link
e.g.
HR located in A machine 
NEWHR located in B machine
"XE" is A's machine DB link connection 

On B machine:
SQL> grant create database link to newhr;

SQL> create database link old_hr connect to hr identified by "HR" using 'XE';

To check db link;
SQL> select * from v$instance@XE;

impdp newhr/newhr DIRECTORY=export_dir NETWORK_LINK=old_hr remap_schema=hr:newhr logfile=newhr.log

What are database startup options?

SQL> STARTUP
--> starts normally

SQL> STARTUP nomount
--> starts as nomount mode

SQL> STARTUP restrict
--> start as restrict mode

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION
--> unlock restrict mode

SQL> STARTUP force
--> tries to ignore startup issues

SQL> STARTUP open recover
--> starts as recover mode

SQL> ALTER DATABASE OPEN READ ONLY
--> starts as read only mode

SQL> ALTER DATABASE OPEN READ WRITE
--> starts unrestricted mode

In extra:
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION
--> enable restrict mode 

How to enable debug mode?

SQL> GRANT DEBUG CONNECT SESSION TO hr;
SQL> GRANT DEBUG ANY PROCEDURE TO hr;

Lastly, reconnect to the session and try again :)

PLS-00201 Solution

Error
PLS-00201: identifier 'TABLE_ALPER' must be declared

Solution
Define public synonym for identifier
e.g.
SQL>
CREATE PUBLIC SYNONYM TABLE_ALPER
FOR HR.TABLE_ALPER;

How to truncate a folder in Outlook?

Right mouse click on desired folder -> Select "Delete All" -> Click "Yes" -> Done! :)

ORA-01578 solution (if object is INDEX)

Error

ORA-01578: ORACLE data block corrupted (file # 100, block # 50000)

Solution

Find the object that was corrupted:

SQL> 

SELECT SEGMENT_TYPE, OWNER || '.' || SEGMENT_NAME
  FROM DBA_EXTENTS
 WHERE FILE_ID = 100 AND 50000 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

Then re-create index or indexes:

SQL> drop index alper_index_pk;

SQL> 

CREATE UNIQUE INDEX HR.ALPER_INDEX_PK ON HR.EMPLOYEES

(EMPLOYEE_ID)

LOGGING

TABLESPACE INDEX_TS

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MAXSIZE          UNLIMITED

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

            FLASH_CACHE      DEFAULT

            CELL_FLASH_CACHE DEFAULT

           )

NOPARALLEL;

How to get AWR report as html file?

Generate Snapshots
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
-> Run this SQL as twice (at the begin and at the end)

Get Automatic Workload Repository (AWR) as HTML format
Login -> Targets -> Databases -> Click Database -> Performance -> AWR -> AWR Report -> Select By Snapshot -> Click Generate Report -> Click Save to File -> Done :)

How to add target manually in 12c EM ?

Prepare the 12c EM server
# vi /etc/hosts
10.10.10.10 alperdb.com
-> remote machine (agent) IP and hostname

Prepare the environment (at new host)
# useradd grid12 -g dba
# vi /etc/hosts
10.10.10.2 grid12.com 
-> Current grid12 server machine IP and hostname

# vi /etc/sudoers
grid12 ALL=NOPASSWD: ALL
grid12 ALL=(ALL) ALL

# vi /etc/oraInst.loc
inventory_loc=/home/grid12/oraInventory
inst_group=dba

# passwd grid12
-> password: grid12

# sudo su - grid12
$ mkdir /home/grid12/oraInventory

Adding Host
Login -> Setup from top-right corner -> Add Target -> Add Target Manually -> Add Host -> Installation Base Directory: /home/grid12/agent -> Instance Directory field will be filled automatically -> 

Configure Agent
Login -> Setup from top-right corner -> Add Target -> Add Target Manually -> Select Add Targets Declaratively by Specifying Target Monitoring Properties -> Target Type: Database Instance, Monitoring Agent: 10.10.10.10:3872 -> Target Name: ALPERDB, Database System: ALPERDB, Oracle Home Path: echo $ORACLE_HOME, Port: 1521 -> Click Test Connection -> Submit -> Done! :)

ORA-27102 solution

Error
SQL> startup;
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device 

Solution
$ vi /etc/sysctl.conf  
edit kernel.shmall, kernel.shmmax, kernel.shmmni parameter
e.g.
$ getconf PAGE_SIZE 
$ cat /proc/sys/kernel/shmall
(4096) * (524288) = 2147483648 (2 GB)
kernel.shmall = 2147483648 
kernel.shmmax = 21474836480
kernel.shmmni = 4096

# sysctl -p
apply the settings

Alternative link: http://ozsoyler.blogspot.com/2014/10/how-to-set-shmmax-value-and-what-is-that.html

How to import old Toad profile for new one?

• Export old User Files from %USERPROFILE%\AppData\Roaming\Quest Software
• Import them to %USERPROFILE%\AppData\Roaming\Quest Software under User Files folder

How to Clone Database Manually without any tool such as RMAN?

Collect live database paths before start
$ ps -ef | grep pmon
$ echo $ORACLE_SID
$ echo $ORACLE_BASE
$ echo $ORACLE_HOME
$ . oraenv

SQL> select name from v$datafile; --db files
SQL> select member from v$logfile; --redo log files
SQL> select name from v$controlfile; --control files
SQL> select name from v$tempfile; --temp files

Copy parameter file (pfile) from live database
SQL> create pfile='/tmp/Alper-init.ora' from spfile;
or
* Copy livedb pfile under $ORACLE_HOME/dbs directory as desired file name like 'Alper-init.ora'

Edit pfile
* Change related parameter such as db_name and control files path (Alper-init.ora)

Copy control file from live database
SQL> alter database backup controlfile to trace as '/tmp/Alper-ctl';
or
SQL> alter database backup controlfile to trace; 
-- Run SQL> show parameter user_dump_dest; to find it

Edit control file
* Prepare a new script with this script
SQL> CREATE CONTROLFILE REUSE SET DATABASE "ALPERDB" RESETLOGS  NOARCHIVELOG
-- Use this if you want to re-use livedb controlfiles in new clone db (if you already moved the controlfiles to new clone db)
or
SQL> CREATE CONTROLFILE SET DATABASE "ALPERDB" RESETLOGS  NOARCHIVELOG
-- Use this if you want to use fresh control file in new clone db
-- SQL> show parameter control; -- to find control file

Copy all database files to remote machine
$ scp -r /oradata/ALPERDB/ oracle@10.10.10.10:/oradata/NEW_ALPERDB

Open database
SQL> alter database open resetlogs;

Define temp tablespace for new database
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/NEW_ALPERDB/temp_01.dbf'
     SIZE 10240M REUSE AUTOEXTEND OFF;
For detail information please click this

That's all :)

How to configure listener port when it changes (like 1522) ?

After listener.ora and tnsnames.ora file changes, only run this command
SQL> ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1522))' SCOPE=BOTH;

Check current setting
SQL> SHOW PARAMETER local_listener;

Check listener
lsnrctl status

That's all.