Category Archives: Oracle 12c

Flashback table to before / Restore table

How can I restore a table to a before state in a Oracle pluggable database (PDB)?

  • If Flashback is enabled, you will use this amazing feature and flaskback the table.
  • From Oracle 12c you can also restore a table with RMAN

 

Flashback table

If the timeframe allows it and flashback enabled.

Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system.

PRE-REQUISITE: You must enable row movement for the table to carry out flashback operation on it.

ALTER TABLE my-table-to-flaskback ENABLE ROW MOVEMENT;

Now, let’s Flaskback the table:

sqlplus / as sysdba
alter session set container=my-pdb;
create table my-schema.my-table-08312018 as select * from my-schema.my-table as of timestamp to_timestamp('31-AUG-2018 10:45:52','DD-Mon-YYYY hh24:MI:SS');

FLASHBACK TABLE my-table
[TO BEFORE DROP] |
[TO TIMESTAMP time_stamp] |
[TO SCN scn_number] |
[ENABLE TRIGGERS | DISABLE TRIGGERS];

With flashback table you can retrieve removed tables from the database, dropped using DROP and TRUNCATE commands.

 

Restore table from RMAN backup (until Oracle 12c)

RMAN enables you to recover one or more tables or table partitions to a specified point in time without affecting the remaining database objects. You can use previously-created RMAN backups to recover tables and table partitions to a specified point in time.

-- create directory /u01/vincent. You will need space for the tablespace of the table and also for system, sysaux
recover table my-schema.my-table OF PLUGGABLE DATABASE my-pdb until time "to_date('08/31/2018 10:45:00','mm/dd/yyyy hh24:mi:ss')"
auxiliary destination '/u01/vincent' REMAP TABLE my-schema.my-table:my-table_08312018;

With this command we restore the SYSTEM, SYSAUX, and UNDO tablespaces and the tablespace that contain the data for this table. Having restored the database to the appropriate point in time, you can use Oracle Data Pump expdp to export the table.  And you import them into the original database, again using Oracle Data Pump impdp.

Have a nice day!

Author: Vincent Fenoll – Oracle DBA Montreal

Basic Multitenant CDB / PDB Operations

Here is a FAQ on Multitenant basic CDB / PDB Operations.

 

How do I know if my database is Multitenant or not?

select NAME, DECODE(CDB, ‘YES’, ‘Multitenant Option enabled’, ‘Regular 12c Database: ‘) “Multitenant Option ?” , OPEN_MODE, CON_ID from V$DATABASE;

 

What Pluggable databases do I have in this container database?

select CON_ID, NAME, OPEN_MODE from V$PDBS;

or

show pdbs

 

How do I connect to my Pluggable Database PDB1?

Connected as sysdba in the CDB:

alter session set container = pdb1;

With a tnsnames alias:

pdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = my-host)
(Port = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1)
)
)

Using easy connect

Format: CONNECT username/password@host[:port][/service_name][:server][/instance_name]
$ sqlplus scott/tiger@//localhost:1521/pdb1
$ sqlplus scott/tiger@//my-server-name:1525/pdb1

To switch back to the main container:
ALTER SESSION SET CONTAINER = CDB$ROOT;

Where am I connected?

SQL> show con_name
CON_NAME
———
PDB1

 

How to stop a PDB in Oracle RAC?

On the current node [or on all the nodes]:
(connected on the CDB) ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE [Instances=all];
(connected on the PDB) ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE [Instances=all];

This will stop the associated service too.
Manually stopping the associated service will not close the PDB. You have to use the SQL command.

 

How to start and stop a PDB in Oracle RAC?

On the current node [or on all the nodes]:
(connected on the CDB) ALTER PLUGGABLE DATABASE PDB1 OPEN [Instances=all;]
(connected on the PDB) ALTER PLUGGABLE DATABASE OPEN [Instances=all;]

You can also start the PDB with the associated service (srvctl start service -d my-db_unique_name -s pdb1)
This will NOT start the other service(s) associated with this PDB.

 

Pluggable Database not open automatically

From 12.1.0.2 you can save the state of a PDB once it’s open: next time the database starts, it will automatically start the pdbs opened previously

— 1 pdb save
alter pluggable database pdb_name save state;

— All pdbs
alter pluggable database all save state;

— All except
alter pluggable database all except pdb1, pdb2 save state;

Author: Vincent Fenoll – Oracle DBA Montreal

Create a pluggable database

dba oracle montreal create pdb oracle12c
 

How to create a pluggable database?

One of my clients in Montreal wants to create an empty pluggable database in a multitenant Oracle database. How can I do that?

In this example, I will not use the plug/unplug/clone features.

tnsnames.ora:

# PDB:
MY-PDB-NAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = my-hostname.ca)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = my-pdb-name.ca)
(SERVER = DEDICATED)
)
)

create pluggable database MY-PDB-NAME admin user PDBA identified by xxxxx
file_name_convert = ('/u02/oradata/CDB-NAME/seed','/u02/oradata/CDB-NAME/MY-PDB-NAME');

alter pluggable database MY-PDB-NAME open;

alter session set container=MY-PDB-NAME;

alter system set listener_networks = '((NAME=net1)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my-hostname.ca)(PORT=1525)))))';

-- Restart the listener on 1525 port 


-- Create the user and the index tablespace

CREATE BIGFILE TABLESPACE MY-PDB-NAME_DATA DATAFILE
 '/u02/oradata/CDB-NAME/MY-PDB-NAME/my-pdb-name_data.dbf' SIZE 256M
AUTOEXTEND ON NEXT 256M MAXSIZE 10G
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
--BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE BIGFILE TABLESPACE MY-PDB-NAME_INDEX DATAFILE
 '/u02/oradata/CDB-NAME/MY-PDB-NAME/my-pdb-name_index.dbf' SIZE 256M
AUTOEXTEND ON NEXT 256M MAXSIZE 10G
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
--BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


-- Create the application user
CREATE USER MY-PDB-NAME
 IDENTIFIED BY xxxxx
 DEFAULT TABLESPACE MY-PDB-NAME_DATA
 TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK;
 
 -- 2 Roles for MY-PDB-NAME
 GRANT CONNECT TO MY-PDB-NAME;
 GRANT RESOURCE TO MY-PDB-NAME;
 GRANT SELECT_CATALOG_ROLE TO MY-PDB-NAME;
 ALTER USER MY-PDB-NAME DEFAULT ROLE ALL;
 
 -- 1 System Privilege for MY-PDB-NAME
 GRANT UNLIMITED TABLESPACE TO MY-PDB-NAME;
 
 -- 2 Tablespace Quotas for MY-PDB-NAME
 ALTER USER MY-PDB-NAME QUOTA UNLIMITED ON MY-PDB-NAME_DATA;
 ALTER USER MY-PDB-NAME QUOTA UNLIMITED ON MY-PDB-NAME_INDEX;

GRANT CREATE ANY VIEW TO MY-PDB-NAME;


-- Test the connection to the pluggable database:
sqlplus MY-PDB-NAME/xxxxx@MY-PDB-NAME
Connected.
SQL>REM THE END

Author: Vincent Fenoll
Compatible: Oracle 12c with the multitenant option.