Rewinding a Table Using Oracle Flashback Table

On Oracle Database 11g (10gR2...), we can rewind one or more tables back to their contents at a previous time without affecting other database objects.

Before we use Flashback Table, We must enable row movement on the table. because rowids will change after the flashback.

Example: Flashback the table back to previous time using SCN

SQL> select count(*) from scott.test;

COUNT(*)
----------
68781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1584494

SQL> delete from scott.test where rownum <= 50000; 50000 rows deleted. SQL> commit;

Commit complete.

SQL> select count(*) from scott.test;

COUNT(*)
----------
18781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1587106

Enable row movement:

SQL> alter table scott.test enable row movement;

Table altered.

SQL> FLASHBACK TABLE scott.test to scn 1584494;

Flashback complete.

SQL> select count(*) from scott.test;

COUNT(*)
----------
68781

SQL> alter table scott.test disable row movement;

Table altered.

We can rewind the table back to previous time using timestamp:

SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:01:09

SQL> delete from scott.test ;

68781 rows deleted.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:03:18

SQL> select count(*) from scott.test;

COUNT(*)
----------
0

SQL> alter table scott.test enable row movement;

Table altered.

SQL> flashback table scott.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS');

Flashback complete.

SQL> select count(*) from scott.test;

COUNT(*)
----------
68781

Nice feature...

(Thank You Bradd Piontek commend, After check 10gR2, we can rewind the table back to previous time as well)

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

SQL> create table test tablespace users as select * from all_objects;

Table created.

SQL> select count(*) from test;

COUNT(*)

----------

42981

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

---------------------

8361904200781

SQL> delete from test ;

42981 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

COUNT(*)

----------

0

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

---------------------

8361904200441

SQL> alter table test enable row movement;

Table altered.

SQL> FLASHBACK TABLE test to scn 8361904200781;

Flashback complete.

SQL> select count(*) from test;

COUNT(*)

----------

42981

Query Disk Device (ASMLib) by Disk Label "oracleasm"

On old ASMLib version , that's not easy to get Disk Device Name from Disk Label(sample script).

On asmlib-support since 2.1.0... we can use "oracleasm" command-line to query!

# for x in `rpm -qa | grep oracleasm`; do rpm --query --queryformat "%{NAME}-%{VERSION}.%{RELEASE} (%{ARCH})\n" $x ; done

oracleasm-2.6.18-128.el5-2.0.5.1.el5 (x86_64)
oracleasm-support-2.1.3.1.el5 (x86_64)
oracleasmlib-2.0.4.1.el5 (x86_64)

# oracleasm
Usage: oracleasm [--exec-path=<exec_path>] <command> [ <args> ]
oracleasm --exec-path
oracleasm -h
oracleasm -V

The basic oracleasm commands are:
configure Configure the Oracle Linux ASMLib driver
init Load and initialize the ASMLib driver
exit Stop the ASMLib driver
scandisks Scan the system for Oracle ASMLib disks
status Display the status of the Oracle ASMLib driver
listdisks List known Oracle ASMLib disks
querydisk Determine if a disk belongs to Oracle ASMlib
createdisk Allocate a device for Oracle ASMLib use
deletedisk Return a device to the operating system
renamedisk Change the label of an Oracle ASMlib disk
update-driver Download the latest ASMLib driver

Example: use "oracleasm" query disk device name

# oracleasm listdisks
VOL1

# oracleasm querydisk
Usage: oracleasm-querydisk [-l <manager>] [-v] [-d|-p] <label>|<device> ...

# oracleasm querydisk VOL1
Disk "VOL1" is a valid ASM disk

# oracleasm querydisk -d VOL1
Disk "VOL1" is a valid ASM disk on device [7, 1]

# oracleasm querydisk -p VOL1
Disk "VOL1" is a valid ASM disk
/dev/loop1: LABEL="VOL1" TYPE="oracleasm"

Switch SELinux "Enforcing" mode to "Permissive" mode

Security-Enhanced Linux (SELinux) is a security architecture integrated into the 2.6.x kernel using the Linux Security Modules (LSM). It is a project of the United States National Security Agency (NSA) and the SELinux community. SELinux integration into Red Hat Enterprise Linux was a joint effort between the NSA and Red Hat.

And???

On EL5/RHEL5+ 11g, Someone might find some error. example:

error while loading shared libraries: $ORACLE_HOME/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

Example on ASM on Single Node (OLE 5)

That's a bug(FAILS TO LOAD LIBNNZ11.SO WITH SELINUX ENABLED ON EL5/RHEL5)... we need to change SELinux mode. How?

If we need to change "Enforcing" mode to the "Permissive" mode, we can use "setenforce" command.

# setenforce

usage: setenforce [ Enforcing | Permissive | 1 | 0 ]

setenforce 1 — SELinux runs in enforcing mode.

setenforce 0 — SELinux runs in permissive mode.

Example:

# getenforce

Enforcing

# setenforce 0

# getenforce

Permissive

# sestatus -v

SELinux status: enabled

SELinuxfs mount: /selinux

Current mode: permissive

Mode from config file: enforcing

Policy version: 18

Policy from config file:targeted

.

.

A "setenforce" command,that change mode immediate, and effect until the next reboot.

If we need to use "Permissive" mode at the next reboot, we have to modify /etc/selinux/config file as well.

SELINUX=permissive

SELINUX=enforcing|permissive|disabled

enforcing — The SELinux security policy is enforced.

permissive — The SELinux system prints warnings but does not enforce policy.

This is useful for debugging and troubleshooting purposes. In permissive mode, more denials are logged because subjects can continue with actions that would otherwise be denied in enforcing mode. For example, traversing a directory tree in permissive mode produces avc: denied messages for every directory level read. In enforcing mode, SELinux would have stopped the initial traversal and kept further denial messages from occurring.

disabled — SELinux is fully disabled. SELinux hooks are disengaged from the kernel and the pseudo-file system is unregistered.

reference SELinux:

http://www.nsa.gov/research/selinux/index.shtml

http://selinux.sourceforge.net

http://docs.fedoraproject.org

ORA-06550: line 1, column 91: PLS-00201: identifier 'NameFromLastDDL' must be declared

read Oracle® Database Java Developer's Guide and tested program with "loadjava" command-line.

$ loadjava -user java_user/password -oci8 Oscar.class

Error while creating class Oscar

ORA-06550: line 1, column 91:

PLS-00201: identifier 'NameFromLastDDL' must be declared

ORA-06550: line 1, column 85:

PL/SQL: Statement ignored

The following operations failed

class Oscar: creation (createFailed)

exiting : Failures occurred during processing

So, Check Java Enabled on Database.

SQL> select owner, object_name, object_type from all_objects where object_name like '%NameFromLastDDL%' ;

no rows selected

SQL> select comp_name, status from DBA_REGISTRY where upper(comp_name) like '%JAVA%' ;

no rows selected

and then install java on exist database by initjvm.sql script.

http://download.oracle.com/docs/cd/B19306_01/install.102/e10319/java.htm#DFSIG276

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/scripts005.htm#sthref2935

run $ORACLE_HOME/javavm/install/initjvm.sql script.

SQL>@?/javavm/install/initjvm.sql
.
.
.

SQL> select owner, object_name, object_type from all_objects where object_name like '%NameFromLastDDL%' ;

OWNER OBJECT_NAME OBJECT_TYPE

--------------- ------------------------------ -------------------

SYS NameFromLastDDL FUNCTION

PUBLIC NameFromLastDDL SYNONYM

SQL> select comp_name, status from DBA_REGISTRY where upper(comp_name) like '%JAVA%' ;

COMP_NAME STATUS

------------------------------------------------

JServer JAVA Virtual Machine VALID

After make sure a java enabled on database, So run "loadjava" again.

$ loadjava -user java_user/password -oci8 Oscar.class

"No Error"

and it's test program:

SQL> CREATE FUNCTION oscar_quote RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'Oscar.quote() return java.lang.String';

SQL>VARIABLE theQuote VARCHAR2(50);

SQL>CALL oscar_quote() INTO :theQuote;

SQL>PRINT theQuote;

THEQUOTE

---------------------------------------------------

I can resist everything except temptation

and then read and test...

ORA-12709: error while loading create database character set

On Test database error:

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 1326064 bytes

Variable Size 266060816 bytes

Database Buffers 805306368 bytes

Redo Buffers 1048576 bytes

ORA-12709: error while loading create database character set

What wrong?

SQL*Plus uses NLS files at $ORACLE_HOME/ocommon/nls/admin directory (pre 10g)

or $ORACLE_HOME/nls/ (10g and up), So have to verify:

- Verifify the ownership/permissions NLS files

- Verify the number of NLS files

Verify:

$ id

uid=200(oracle) gid=200(oinstall) groups=200(oinstall),201(dba)

$ cd $ORACLE_HOME/nls/

bash: cd: $ORACLE_HOME/nls/ : Permission denied

So, changed permmision in $ORACLE_HOME/nls path and verified every directories + files in $ORACLE_HOME/nls/*, that "oracle" user can access...

startup database again:

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 1326064 bytes

Variable Size 266060816 bytes

Database Buffers 805306368 bytes

Redo Buffers 1048576 bytes

Database mounted.

Database opened.

;)

Check enable/disable the startup of the Oracle Clusterware daemons

We can use crsctl commands as follows to enable and disable the startup of the Oracle Clusterware daemons. Run the following command to enable startup for all of the Oracle Clusterware daemons:

crsctl enable crs

Run the following command to disable the startup of all of the Oracle Clusterware daemons:

crsctl disable crs

Indeed... Oracle Document told me like that. But How can I know enable/disable startup status for all of the Oracle Clusterware daemons now?

I know... someone don't need to know because they can run "crsctl enable/disable crs" again and again.

Oracle has scls_scr directory at /etc/oracle path. We can check about enable/disable startup status of the Oracle Clusterware daemons at crsstart file in /etc/oracle/scls_scr/<hostname>/root/ path.

really? example:

root@rac1# cat /etc/oracle/scls_scr/rac1/root/crsstart

enable

root@rac1# cd CRS_HOME/bin

root@rac1# ./crsctl disable crs

root@rac1# cat /etc/oracle/scls_scr/rac1/root/crsstart

disable

after disabled by "crsctl disable crs", crsstart file was changed be "disable"

root@rac1# ./crsctl enable crs

root@rac1# cat /etc/oracle/scls_scr/rac1/root/crsstart

enable

after enabled by "crsctl enable crs", crsstart file was changed be "enable"

that justs idea ;)

OMS 10.2.0.5 _/console/rac/racSitemap] WARN pref.SubtabPref getFolders.710 - Unknown folder id: VirtualServers retrieved from repository

After upgraded OMS to use 10.2.0.5

Found error:
==> emoms.log <==
2009-08-10 08:50:53,450 [EMUI_08_50_52_/console/rac/racSitemap] WARN pref.SubtabPref getFolders.710 - Unknown folder id: VirtualServers retrieved from repository

==> emoms.trc <==
2009-08-10 08:50:53,450 [EMUI_08_50_52_/console/rac/racSitemap] WARN pref.SubtabPref getFolders.710 - Unknown folder id: VirtualServers retrieved from repository

check with oracle support, that is bug 8612381

So, applied Patch 8244731 to the OMS for Oracle VM monitoring.

Hope this'll only one bug, after upgraded to use oms 10.2.0.5.

Query Disk Device (ASMLib) by Disk Label

On ASMLib, If need to querydisk.
Used oracleasm script to query disk LABEL from disk device:

# /etc/init.d/oracleasm querydisk /dev/sdc5
Disk "/dev/sdc5" is marked an ASM disk with the label "VOL1"

But If used disk Label to query, that get major,minor numbers.
# /etc/init.d/oracleasm querydisk VOL1
Disk "VOL1" is a valid ASM disk on device [8, 37]

On asmlib-support since 2.1.0
# /etc/init.d/oracleasm querydisk -d VOL1
Disk "VOL1" is a valid ASM disk on device [8, 37]

After that, used major,minor numbers to find disk device.

# ls -l /dev/* | grep 8, | grep 37
brw-rw---- 1 root disk 8, 37 Jul 31 17:48 /dev/sdc5

If written sample shell code:

#!/bin/sh
# Script: getasmlib

if [ $# -ne 1 ]
then

echo "Please Check arguments."
echo
echo "./getasmlib [label]"
echo
echo " ./getasmlib VOL01"
exit 0
fi

LABEL=$1
C=`/etc/init.d/oracleasm querydisk -d ${LABEL} |grep 'a valid ASM disk' |wc -l`

if [ ${C} -eq 0 ]
then
echo "Don't Found ${LABEL} Disk in ASMLIB"
exit 0
fi

M=`/etc/init.d/oracleasm querydisk -d ${LABEL} | grep 'a valid ASM disk' |awk -F\[ '{print $2 }' | awk '{print $1}'`
N=`/etc/init.d/oracleasm querydisk -d ${LABEL} | grep 'a valid ASM disk' |awk -F\, '{print $2 }' | awk -F\] '{print $1}'`

for x in ` ls -l --time-style=long-iso /dev/* | grep ${M} | grep ${N}' ' | awk '{print $9}' `
do
/etc/init.d/oracleasm querydisk $x |grep -i ${LABEL}
done

Example:
# ./getasmlib VOL1
Disk "/dev/sdc5" is marked an ASM disk with the label "VOL1"

# ./getasmlib VOL2
Disk "/dev/sdc6" is marked an ASM disk with the label "VOL2"

Just sample shell code ;)