Daily Archives: March 19, 2017

12c, flashback

Oracle 12c – Flashback Technology Overview

Leave a comment

Flashback table – by default when a table is dropped oracle does not immediately remove the space, but renames it and places the table and associated objects in recycle bin.   This allows it to be recovered at a later time.

 

The recycle bin is a data dictionary table that contains info needed to recover dropped tables.  Original table stays in place and occupies same space until purged or database needs space for storage.

 

tablespaces are not placed in recycle bin and once dropped are gone.   All tables in that tablespace are removed from recycle bin if they have previously been dropped.

 

Recycle bin can be disabled with recyclebin initialization parameter.

 

FLASHBACK TABLE ….  TO BEFORE DROP;    statement is used to recover tables from recyclebin.  You must use the system generated recyclebin name.  You can rename the table with RENAME clause during recovery.  The USER_RECYCLEBIN view can be used to find the system generated recyclebin name.

example:

 

FLASHBACK TABLE test2_bak TO BEFORE DROP RENAME test2;

 

Larry Catt

OCP

12c, tablespaces

Oracle 12c – Relocate SYSAUX occupants

Leave a comment

In Oracle the SYSAUX tablespace is an auxiliary storage location for the SYSTEM tablespace.  Many of the Oracle database components which are not critical to the core functionality reside in the SYSAUX tablespace.  If the SYSAUX tablespace becomes unavailable, core database will still function, though pieces of the database which rely on SYSAUX components will fail or have limited capabilities.  You can view the components of the SYSAUX tablespace through the view V$SYSAUX_OCCUPANTS  as described below:

 

SQL> desc v$SYSAUX_OCCUPANTS

 Name                                      Null?    Type

 —————————————– ——– —————————-

 OCCUPANT_NAME                                      VARCHAR2(64)

 OCCUPANT_DESC                                      VARCHAR2(64)

 SCHEMA_NAME                                        VARCHAR2(64)

 MOVE_PROCEDURE                                     VARCHAR2(64)

 MOVE_PROCEDURE_DESC                                VARCHAR2(64)

 SPACE_USAGE_KBYTES                                 NUMBER

 CON_ID                                             NUMBER

SQL>

 

Procedure shown in the MOVE_PROCEDURE column of V$SYSAUX_OCCUPANTS details procedure name to move the product. This article will detail the steps to move the LGNMR component from SYSAUX tablespaces to its own tablespace storage.

 

  1. Logon to your database server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Jan 11 15:04:41 EST 2017 on pts/2

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$

 

  1. Connect to SQLPLUS as sysdba.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 12 09:03:45 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL>

 

  1. Create a new tablespace called LOGMINER.

 

SQL> create tablespace LOGMINER datafile ‘/u01/oradata/orcl/orcl/LOGMNR_data01.dbf’ size 1g;

Tablespace created.

SQL>

 

  1. Extract the move_procedure from V$SYSAUX_OCCUPANTS view for LOGMNR.

 

SQL> select occupant_name, schema_name, move_procedure from v$sysaux_occupants where occupant_name=’LOGMNR’;

OCCUPANT_NAME

—————————————————————-

SCHEMA_NAME

—————————————————————-

MOVE_PROCEDURE

—————————————————————-

LOGMNR

SYSTEM

SYS.DBMS_LOGMNR_D.SET_TABLESPACE

SQL>

 

  1. Execute procedure passing the new tablespace name.

 

execute SYS.DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMINER’);

 

SQL> execute SYS.DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMINER’);

PL/SQL procedure successfully completed.

SQL>

 

  1. This completes movement of LOGMNR from sysaux tablespace.

 

 

 

Larry Catt

OCP