Oracle Database 12c : New Features for Administrators Book.
Oracle Database 12c: New Features for Administrators vol :- 1
Oracle Database 12c: New Features for Administrators vol :-2

In-Memory Column (Oracle 12c)

In-Memory Column Store enables objects  (tables and partitions) be stored in memory using columnar format. This format enables scans, joins and aggregates faster than

traditional format (row format). This feature does not replace the buffer cache, it only maintains additional and consistent copy of the object.


The In-Memory Column Store is a new SGA component called In-Memory Area.
1st check is inmemory size has been defined ornot by default it will be 0.
SQL> show parameter inmemory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 0===================>>it's 0 mince you have to defaine the size as per your requirement
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE
SQL>
SQL> select name,value from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size              3011888
Variable Size         415238864
Database Buffers      125829120
Redo Buffers            5373952
==============================================================>>here no component it allocated to inmemory area mince IM is nt enable for this instance.
SQL>

SQL>ALTER SYSTEM SET inmemory_size = 500M scope=spfile; //set the memory parameter and bounce your db to get effect IM parameter.
SQL> show parameter inmemory                            //check the value of inmemory related parameter and inmemory is showing now 500M

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 500M====================>>now it's showing
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE
SQL>
SQL> select name,value from v$sga;  //after enabling this parameter new sga componant will be create as below it's showing.

NAME                      VALUE
-------------------- ----------
Fixed Size              3011888
Variable Size         415238864
Database Buffers      125829120
Redo Buffers            5373952
In-Memory Area        524288000  ===========================>>new componant has been allocated named as ...in-memory area with 500mb

SQL>

SQL>select v.owner,v.segment_name name,v.populate_status status from v$im_segments v;  //Verifying memory segments 0 row mince no segment in IM

no rows selected

SQL>
SQL> ALTER TABLE prd.emp INMEMORY;  //Changing a table to use in-memory

Table altered.

SQL> select v.owner,v.segment_name name,v.populate_status status from v$im_segments v;  //Verifying memory segments ..again no row mince you have enable the IM for emp

table but you have to populate into memory by running the select query as example given below,

no rows selected

SQL>
SQL>  SELECT id, Count(*)         //Populating memory and you also can run select * from emp;
FROM prd.emp
GROUP BY id;  2    3

        ID   COUNT(*)
---------- ----------
         1          1
         2      10000

SQL> select v.owner,v.segment_name name,v.populate_status status from v$im_segments v;  //Verifying memory segments.....now emp table has been populated into IM.

OWNER      NAME       STATUS
---------- ---------- ---------
PRD        EMP        COMPLETED

SQL>

Steps to switch Primary to Standby

Step 1   Verify that the primary database can be switched to the standby role.

Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database.For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
 -----------------
 TO STANDBY
 1 row selected

A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either misconfigured or is not functioning properly.

Step 2   Initiate the switchover on the primary database.

Issue the following SQL statement on the primary database to switch it to the standby role:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH

 SESSION SHUTDOWN;

This statement converts the primary database into a physical standby database. The current control file is backed up to the current SQL session trace file before the switchover. This makes it possible to reconstruct a current control file, if necessary.

Note:
The WITH SESSION SHUTDOWN clause can be omitted from the switchover statement if the query performed in the previous step returned a value of TO STANDBY.

Step 3   Shut down and then mount the former primary database.

SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;

At this point in the switchover process, the original primary database is a physical standby database

Step 4   Verify that the switchover target is ready to be switched to the primary role.

Query the SWITCHOVER_STATUS column of the V$DATABASE view on the standby database. For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
-----------------
TO_PRIMARY
1 row selected

A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that Redo Apply is active and that redo transport is configured and working properly. Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.

Step 5   Switch the target physical standby database role to the primary role.

Issue the following SQL statement on the target physical standby database:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY 

WITH SESSION SHUTDOWN;

Note:The WITH SESSION SHUTDOWN clause can be omitted from the switchover statement if the query performed in the previous step returned a value of TO PRIMARY.

Step 6   Open the new primary database.

SQL> ALTER DATABASE OPEN;

Step 7   Start Redo Apply on the new physical standby database.

For example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY 

DATABASE DISCONNECT FROM SESSION;

Now enjoy with New Primary database.... 

Standby Manual Failover (open the standby database when primary lost).

 Open Standby in Read-write Mode When Primary is Lost

1.)    Open standby database in mount state :

2.)     SQL> select name,open_mode from v$database;

NAME OPEN_MODE
 ------ -------------
RAJNISH MOUNTED

3.)   
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 230689012 bytes
Database Buffers 25165824
bytes Redo Buffers 6410240 bytes
Database mounted.

SQL> select open_mode ,protection_mode , database_role from v$database ; OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------------------ ----------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY

2.) Recover if there is any archive logs:
SQL>recover standby database;

ORA-01153: an incompatible media recovery is active

To solve this issue, we cancel the media recovery by using the below command .

SQL> alter database recover managed standby database cancel;
Database altered.
SQL> recover standby database
ORA-00279: change 2698969 generated at 10/05/2011 16:46:58 needed for thread
ORA-00289: suggestion : D:\ARCHIVE\ARC0000000133_0761068614.0001
ORA-00280: change 2698969 for thread 1 is in sequence #133 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel
Media recovery cancelled.

3.) Finish the Recovery process :

The below command will perform the role transition as quickly as possible with little or no data loss and without rendering other standby databases unusable and to open the database in read-write mode we fire the below command :

SQL>alter database recover managed standby database finish;
Database altered.

4.) Activate the Standby Database :

SQL> alter database activate physical standby database ; Database altered.

5.) Check the new status

SQL> select open_mode ,protection_mode , database_role from v$database ;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE
 --------- ------------------------ ---------------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY

6.) Open the Database
SQL> alter database open ;
Database altered.
SQL> select open_mode ,protection_mode , database_role from v$database ;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ------------------------ --------------------
READ WRITE MAXIMUM PERFORMANCE PHYSICAL STANDBY



Now database is open in Read Write mode.

External Table

When you have to upload the data from os file (csv,txt..etc) into oracle db thn u can use external table use the below mentioned step.

1.Make the directry with name "raj" into c drive if having unix so make the directory.

2.copy the your file into raj folder as i have file with name "lory.txt" and below mentioned contents is written in file.

1, This is oracle.
2, this is sql server.

3.Create the database dir....

SYS@orcl>>create directory dir as 'c:\raj';

Directory created.

4. Create the table using below mentioned command.

Wrote file rajnish.txt

    create table sweet
    (employee_id number,
     empl_comment varchar2(250))
     organization external
     (type oracle_loader
     default directory dir
     access parameters
     (records delimited by newline
     fields terminated by ','
    (employee_id char,
    empl_comment char)
    )
    location('lory.txt')
   )
SYS@orcl>>/

Table created.

5. Check now you data has been uploaded into database.

SYS@orcl>>desc sweet
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 EMPLOYEE_ID                                        NUMBER
 EMPL_COMMENT                                       VARCHAR2(250)

SYS@orcl>>select * from sweet;

EMPLOYEE_ID
-----------
EMPL_COMMENT
--------------------------------------------------------------------------------

        1
 This is oracle.

        2
 this is sql server.


SYS@orcl>>



===Now Enjoy with oracle==

ENCRYPTION WALLET IN ORACLE

Set the below config in sqlnet.ora

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\app\Administrator\admin\orcl\wallet\))


set wallet password.......

11:37:51 Scott@std>>ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "hello";


11:42:34 SCOTT@std>>CREATE TABLE tde_test (    === creating the table with encrypt option.
11:42:36   2    id    NUMBER(10),
11:42:36   3    data  VARCHAR2(50) ENCRYPT
11:42:36   4  );

Table created.

11:42:39 SCOTT@std>>desc tde_test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER(10)
 DATA                                               VARCHAR2(50) ENCRYPT   ==> Here it showing encrypt config for that column
11:42:44 SCOTT@std>>SELECT * FROM dba_encrypted_columns; ==> Same is showing in data dict also.

OWNER                          TABLE_NAME
============================== ==============================
COLUMN_NAME                    ENCRYPTION_ALG                SAL INTEGRITY_AL
============================== ============================= === ============
SCOTT                          TDE_TEST
DATA                           AES 192 bits key              YES SHA-1




Disabling the encrypt.....
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

Kill the oracle session from os(windows).

Here std is instance name in user case pls put ur instance name ...lie..orakill orcl or orakill ora...etc.....
1st i have get the user name from db with spid.....
13:56:06 SYS@std>>ed
Wrote file rajnish.txt

  1  select 'orakill std '|| b.spid,a.username
  2     from v$session a, v$process b
  3     where a.paddr = b.addr
  4*      and a.sid in  (select sid from v$session c where username is not null
and c.serial#=a.serial# )
13:56:37 SYS@std>>/

'ORAKILLSTD'||B.SPID                 USERNAME
==================================== ==============================
orakill std 2372                     SCOTT
orakill std 6744                     SYS

13:56:38 SYS@std>>

from other window i have logon on data with scort user....

no go on cmd execute below mentioned command....

C:\Users\Administrator>orakill std 2372

Kill of thread id 2372 in instance std successfully signalled.

C:\Users\Administrator>

and now when i execute desc emp it's diplaying not connect to oracle ....mince session has ben kill from os......:)

13:55:16 SCOTT@std>>conn scott/tiger_12
Connected.
13:56:14 SCOTT@std>>desc emp
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 2372
Session ID: 91 Serial number: 13


13:56:53 SCOTT@std>>




=========Enjoy with Oracle=======

How to Know Dumpfile is created by exp or expdp

C:\Users\INSYS-09>exp tables=scott.EMP file=c:\jjjj.dmp  ===>executing the backup from exp command

Export: Release 11.2.0.1.0 - Production on Mon Jan 27 16:11:59 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sys as sysdba
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table                            EMP         14 rows exported
Export terminated successfully without warnings.

C:\Users\INSYS-09>expdp tables=scott.EMP file=c:\jjddsjj.dmp directory=dir     ===>>executing the backup from expdp command

Export: Release 11.2.0.1.0 - Production on Mon Jan 27 16:13:38 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=c:\jjddsjj.dmp" Location: Command Line, Replaced wi
th: "dumpfile=DIR:jjddsjj.dmp"
Database Directory Object "DIR" has been added to file specification: "c:\jjddsjj.dmp".
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYS"."SYS_EXPORT_TABLE_02":  sys/******** AS SYSDBA tables=scott.EMP d
umpfile=DIR:jjddsjj.dmp directory=dir reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SYS"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_02 is:
  C:\JJDDSJJ.DMP
Job "SYS"."SYS_EXPORT_TABLE_02" successfully completed at 16:14:00

C:\Users\INSYS-09>imp show=y tables=EMP fromuser=scott touser=scott file=c:\jjjj.dmp   ==> Using the show=y option for checking the file is this created by exp

Import: Release 11.2.0.1.0 - Production on Mon Jan 27 16:14:48 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path                         ===>there is meaage export file is created by export:v11.02.00 it mice this file  is creted by exp command. import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
 "CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH"
 "AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM"
 "BER(7, 2), "DEPTNO" NUMBER(2, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
 "S 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST "
 "GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . skipping table "EMP"

 "GRANT SELECT ON "EMP" TO "SCOTT""
 "CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" )  PCTFREE 10 INITRANS 2 MAX"
 "TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL"
 "IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING"
 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
 "ALTER TABLE "EMP" ADD  CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE"
 "X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MIN"
 "EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "US"
 "ERS" LOGGING ENABLE "
 "ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN"
 "CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE"
 "ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""
Import terminated successfully without warnings.

C:\Users\INSYS-09>expdp tables=EMP file=c:\jjddsjj.dmp directory=dir   ==> Using the show=y option for checking the file is this created by expdp

Export: Release 11.2.0.1.0 - Production on Mon Jan 27 16:16:34 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=c:\jjddsjj.dmp" Location: Command Line, Replaced wi
th: "dumpfile=DIR:jjddsjj.dmp"
Database Directory Object "DIR" has been added to file specification: "c:\jjddsjj.dmp".
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYS"."SYS_EXPORT_TABLE_02":  sys/******** AS SYSDBA tables=EMP dumpfil   =====There is mesage "SYS.SYS_EXPORT_TABLE_N" it mince it's created by expdp
e=DIR:jjddsjj.dmp directory=dir reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object SYS.EMP was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_02" completed with 2 error(s) at 16:16:39


                                    ===Now Enjoy with oracle===