Ulfet`s Oracle Blog: 2013

Friday, December 20, 2013

Changing archivelog destination in STANBY side

If you did not set log_archive_dest_* or db_recovery_file_dest in stanby database`s parameter file during creation of stanbdy, primary`s archivelogs will be transferred to default location of standby database.
This is $ORACLE_HOME/dbs (in linux/unix).

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN STND PHYSICAL STANDBY

Disconnect recovery managed

SQL> alter database recover managed standby database cancel;

Database altered.

Check current location of archivelogs

[oracle@fcdbdb_stby trace]$ ls -lrt /u01/app/oracle/product/11.2.0.3/db_1/dbs/
total 133444
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 24 Dec 19 14:43 lkSTND
-rw-r--r--. 1 oracle oinstall 1520 Dec 19 14:55 initSTND.ora
-rw-r-----. 1 oracle oinstall 1536 Dec 19 14:56 orapwSTND
-rw-r-----. 1 oracle oinstall 1141248 Dec 19 15:02 arch1_11_834576721.dbf
-rw-r-----. 1 oracle oinstall 1459200 Dec 19 15:02 arch1_12_834576721.dbf
-rw-r-----. 1 oracle oinstall 93696 Dec 19 15:04 arch1_13_834576721.dbf
-rw-r-----. 1 oracle oinstall 15872 Dec 19 15:04 arch1_14_834576721.dbf
-rw-r-----. 1 oracle oinstall 4966912 Dec 19 16:15 arch1_15_834576721.dbf
-rw-r-----. 1 oracle oinstall 83456 Dec 19 16:17 arch1_16_834576721.dbf
-rw-r-----. 1 oracle oinstall 40787968 Dec 19 22:00 arch1_17_834576721.dbf
-rw-r-----. 1 oracle oinstall 37803008 Dec 20 02:26 arch1_18_834576721.dbf
-rw-r-----. 1 oracle oinstall 38546432 Dec 20 11:01 arch1_19_834576721.dbf
-rw-r-----. 1 oracle oinstall 1888768 Dec 20 11:37 arch1_20_834576721.dbf
-rw-r-----. 1 oracle oinstall 66560 Dec 20 11:38 arch1_21_834576721.dbf

Check db_recovery_file_desc`s value

SQL> show parameters db_rec

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 4122M
db_recycle_cache_size big integer 0

Set db_recovery_file_dest

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' scope=both;

System altered.

Connect recovery managed

SQL> alter database recover managed standby database disconnect from session;

Database altered.

On Primary side enforce archivelog

SQL> alter system switch logfile;

System altered.

And check in Standby side :

[oracle@fcdbdb_stby trace]$ ls -lrt /u01/app/oracle/fast_recovery_area/STND/archivelog/2013_12_20/
total 12240
-rw-r-----. 1 oracle oinstall 51200 Dec 20 11:40 o1_mf_1_22_9c7x32kp_.arc

As you see, staring 22th archivelog file transferred to new destination.

You can do it using dgmgrl utiliy.

Wednesday, December 18, 2013

What is ID: oracle.install.commons.util.exception.DefaultErrorAdvisor:37 and how to fix it

Today while installing Oracle 11g r2 (11.2.0.3) on OEL 6 I faced below error.

Looking up something in the OraInstall Log.

[oracle@fcdbdb database]$ cd /tmp/OraInstall2013-12-18_04-47-35PM
[oracle@fcdbdb OraInstall2013-12-18_04-47-35PM]$ ls -ltr
total 36
-rwxr-x---. 1 oracle oinstall 5578 Dec 18 16:47 command_output_15591
drwxr-x---. 2 oracle oinstall 4096 Dec 18 16:47 images
drwxr-x---. 6 oracle oinstall 4096 Dec 18 16:47 jdk
drwxr-x---. 3 oracle oinstall 4096 Dec 18 16:47 diagnostics
drwxr-x---. 3 oracle oinstall 4096 Dec 18 16:47 srvm
drwxr-x---. 8 oracle oinstall 4096 Dec 18 16:47 oui
drwxrwx---. 5 oracle oinstall 4096 Dec 18 16:47 ext
-rw-------. 1 oracle oinstall 0 Dec 18 16:47 oraInstall2013-12-18_04-47-35PM.out
-rw-------. 1 oracle oinstall 1269 Dec 18 16:47 oraInstall2013-12-18_04-47-35PM.err

Here it is oraInstall2013-12-18_04-47-35PM.err

[oracle@fcdbdb OraInstall2013-12-18_04-47-35PM]$ more oraInstall2013-12-18_04-47-35PM.err
---# Begin Stacktrace #---------------------------
ID: oracle.install.commons.util.exception.DefaultErrorAdvisor:37
oracle.cluster.verification.VerificationException: fcdbdb: fcdbdb
at oracle.cluster.verification.ClusterVerification.<init>(ClusterVerification.java:231)
at oracle.cluster.verification.ClusterVerification.getInstance(ClusterVerification.java:333)
at oracle.install.driver.oui.OUISetupDriver.load(OUISetupDriver.java:419)
at oracle.install.ivw.db.driver.DBSetupDriver.load(DBSetupDriver.java:190)
at oracle.install.commons.base.driver.common.Installer.run(Installer.java:299)
at oracle.install.ivw.common.util.OracleInstaller.run(OracleInstaller.java:106)
at oracle.install.ivw.db.driver.DBInstaller.run(DBInstaller.java:136)
at oracle.install.commons.util.Application.startup(Application.java:891)
at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:165)
at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:182)
at oracle.install.commons.base.driver.common.Installer.startup(Installer.java:348)
at oracle.install.ivw.db.driver.DBInstaller.startup(DBInstaller.java:124)
at oracle.install.ivw.db.driver.DBInstaller.main(DBInstaller.java:155)

---# End Stacktrace #-----------------------------

The cause of error most likely in that that dns does not return ip the server for the name.
Let`s check.

more /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

edit hostname

vi /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
XXXXXXXXX fcdbdb

now, re-run runInstaller again.

Tuesday, December 17, 2013

Moving & renaming datafile

Sometimes we need to rename or move data files.

My db version: 11.2.0.3

One of the easy way is below:

1. Identify which data file you will move. In my case I will move datafile which belong FCATDEV121 tablespace.

select a.tablespace_name, a.status, file_name from dba_tablespaces a, dba_data_files b
where a.tablespace_name=b.tablespace_name
order by 1;

TABLESPACE_NAME STATUS FILE_NAME
==========================================================================================================================
SYSAUX ONLINE /u01/app/oracle/oradata/FCDBT/sysaux01.dbf
SYSTEM ONLINE /u01/app/oracle/oradata/FCDBT/system01.dbf
UNDOTBS1 ONLINE /u01/app/oracle/oradata/FCDBT/undotbs01.dbf
USERS ONLINE /u01/app/oracle/oradata/FCDBT/users01.dbf
FCATDEV121 ONLINE /home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8

2. Take tablespace to offline mode, copy using OS command (cp, mv), rename datafile and take tablespace to online mode

SQL> ALTER TABLESPACE FCAT_DEV21 offline;

Tablespace altered.

SQL> host
[oracle@fc-db-tst1 FCDBT_EXPTPS]$ mv /home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf /u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf
[oracle@fc-db-tst1 FCDBT_EXPTPS]$ exit
exit

SQL> ALTER TABLESPACE FCAT_DEV21 RENAME DATAFILE '/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf' TO '/u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf';

Tablespace altered.

SQL> ALTER TABLESPACE FCAT_DEV21 online;

3. Check

SQL> select a.tablespace_name, a.status, file_name from dba_tablespaces a, dba_data_files b
where a.tablespace_name=b.tablespace_name
order by 1; 2 3

TABLESPACE_NAME STATUS FILE_NAME
------------------------------ --------- --------------------------------------------------------------FCAT_DEV21 ONLINE /u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf
SYSAUX ONLINE /u01/app/oracle/oradata/FCDBT/sysaux01.dbf
SYSTEM ONLINE /u01/app/oracle/oradata/FCDBT/system01.dbf
UNDOTBS1 ONLINE /u01/app/oracle/oradata/FCDBT/undotbs01.dbf
USERS ONLINE /u01/app/oracle/oradata/FCDBT/users01.dbf

Starting 12.c we can move datafiles in online mode!

ALTER DATABASE MOVE DATAFILE
'/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf'
TO '/u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf';

Moving production databases across platforms

Today I requested moving 3 databases under Aix to Linux. I am going to demonstrate only one database in this tutorial.

So, let`s start. Selected SID name is FCDBT.

Source

-bash-4.2$ uname -a
AIX localhost 1 6 00F697214C00

Target

[oracle@fc-db-tst1 FCDBT_EXPTPS]$ uname -a
Linux fc-db-tst1 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux

1. create same name DB on target.

ex: dbca

Note: nls_language should be same as source

SQL> select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.3.0

20 rows selected.

AL32UTF8

Source:

SQL> set linesize 300
SQL> SELECT A.platform_id, A.platform_name, B.endian_format
FROM v$database A, v$transportable_platform B
WHERE B.platform_id (+) = A.platform_id; 2 3

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
6 AIX-Based Systems (64-bit) Big

SQL>

Target:

SQL> set linesize 300
SQL> SELECT A.platform_id, A.platform_name, B.endian_format
FROM v$database A, v$transportable_platform B
WHERE B.platform_id (+) = A.platform_id; 2 3

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
13 Linux x86 64-bit Little

SQL>

2. select users and identify tablespaces to be transported

select username, account_status, default_tablespace from dba_users
--where .....
order by created;

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE
------------------------------ -------------------------------- ------------------------------
FCDBAPPDEV121 OPEN FCATDEV121
FCDBADMINDEV121 OPEN FCATDEV121
TMPDBA OPEN SYSTEM
FCDBAPP_DEV21 OPEN FCAT_DEV21
FCDBADMIN_DEV21 OPEN FCAT_DEV21
FCDBB001_DEV21 OPEN FCAT_DEV21

SQL> SELECT tablespace_name, segment_type, COUNT(*),

SUM (bytes) / 1024 / 1024 mb

FROM dba_segments

WHERE owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21')

GROUP BY tablespace_name, segment_type

ORDER BY 1, 2 DESC;

TABLESPACE_NAME SEGMENT_TYPE COUNT(*) MB

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

FCATDEV121 TABLE 209 57.375

FCATDEV121 LOBSEGMENT 24 44.375

FCATDEV121 LOBINDEX 24 1.5

FCATDEV121 INDEX 253 37.4375

FCAT_DEV21 TABLE 230 80.5625

FCAT_DEV21 LOBSEGMENT 33 131.375

FCAT_DEV21 LOBINDEX 33 2.4375

FCAT_DEV21 INDEX 266 40.25

TBS_OFFLINE_DEV21 TABLE 9 16.75

TBS_OFFLINE_DEV21 LOBSEGMENT 12 88.6875

TBS_OFFLINE_DEV21 LOBINDEX 12 .75

11 rows selected.

3.create OS dir (in this case I will create same dircetories on both host)

-bash-4.2$ pwd

/home/oracle

-bash-4.2$ mkdir FCDBT_EXPTPS

-bash-4.2$ cd FCDBT_EXPTPS

Ran the following on the source database to verify there were no self-containment problems:

SQL> BEGIN

SYS.dbms_tts.transport_set_check('FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21', incl_constraints=>TRUE, full_check=>FALSE);

END;

/ 2 3 4

PL/SQL procedure successfully completed.

SQL> SELECT * FROM SYS.transport_set_violations;

no rows selected

SQL> SELECT B.data_type, COUNT(*)

FROM dba_tables A, dba_tab_columns B

WHERE A.owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21')

AND B.owner = A.owner

AND B.table_name = A.table_name

GR 2 3 4 5 6 OUP BY B.data_type

ORDER BY B.data_type; 7

DATA_TYPE COUNT(*)

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

BLOB 135

CHAR 1240

CLOB 4

DATE 774

FLOAT 2

LONG 8

LONG RAW 10

NUMBER 1505

TIMESTAMP(1) 19

TIMESTAMP(6) 34

VARCHAR2 6691

DATA_TYPE COUNT(*)

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

XMLTYPE 4

12 rows selected.

SQL>

SQL> SELECT B.owner, B.table_name

FROM dba_xml_tables A, all_all_tables B

WHERE B.owner = A.owner

AND B.table_name = A.table_name

AND B.tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21');SQL> 2 3 4 5

no rows selected

SQL>

SQL> SELECT owner, COUNT(*)

FROM dba_segments

WHERE tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21')

GROUP BY owner; 2 3 4

OWNER COUNT(*)

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

FCDBADMIN_DEV21 585

FCDBADMINDEV121 510

FCDBB001_DEV21 10

SQL>

Next we verify that this schema did not yet exist on the target database:

[oracle@fc-db-tst1 admin]$ export ORACLE_SID=FCDBT

[oracle@fc-db-tst1 admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 17 16:29:19 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> select name from v$database;

NAME

---------

FCDBT

SQL> SELECT username

FROM dba_users

WHERE username in ( 'FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21');

no rows selected

SQL>

Now we have to create missing schemas on the target database and grant necessary system privileges:

SQL> CREATE USER FCDBAPPDEV121 IDENTIFIED BY FCDBAPPDEV121;

SQL> CREATE USER FCDBADMINDEV121 IDENTIFIED BY FCDBADMINDEV121;

SQL> CREATE USER TMPDBA IDENTIFIED BY TMPDBA;

SQL> CREATE USER FCDBAPP_DEV21 IDENTIFIED BY FCDBAPP_DEV21;

SQL> CREATE USER FCDBADMIN_DEV21 IDENTIFIED BY FCDBADMIN_DEV21;

SQL> CREATE USER FCDBB001_DEV21 IDENTIFIED BY FCDBB001_DEV21;

SQL> GRANT connect, resource, create library TO FCDBAPPDEV121;

SQL> GRANT connect, resource, create library TO FCDBADMINDEV121;

SQL> GRANT connect, resource, create library TO TMPDBA;

SQL> GRANT connect, resource, create library TO FCDBAPP_DEV21;

SQL> GRANT connect, resource, create library TO FCDBADMIN_DEV21;

SQL> GRANT connect, resource, create library TO FCDBB001_DEV21;

Check exist tablespaces on TARGET

SQL> SELECT tablespace_name

FROM dba_tablespaces

WHERE tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21');

2 3

no rows selected

ON SOURCE db make tablespace READ ONLY

Make Tablespaces Read-only in Source Database

SQL> select name from v$database;

NAME

---------

FCDBT

SQL>

SQL> ALTER TABLESPACE FCATDEV121 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE FCAT_DEV21 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE TBS_OFFLINE_DEV21 READ ONLY;

Tablespace altered.

SQL>

Extract Metadata from Source Database

exp "'/ as sysdba'" file=FCDBT_tabsp.dmp transport_tablespace=y tablespaces=FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21

Copy Files to Target Server and Convert if Necessary

-bash-4.2$ echo $ORACLE_SID

FCDBT

-bash-4.2$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 17 16:46:04 2013

connected to target database: FCDBT (DBID=1987414639)

RMAN> CONVERT TABLESPACE FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21

TO PLATFORM 'Linux x86 64-bit'

FORMAT='/home/oracle/FCDBT_EXPTPS/%U';2> 3>

Starting conversion at source at 17-DEC-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=154 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00005 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_fcatdev1_94vkgg0m_.dbf

converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00011 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_fcat_dev_94vkl66p_.dbf

converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00014 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_tbs_offl_94vkn2n1_.dbf

converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03

Finished conversion at source at 17-DEC-13

RMAN>

now copy

-bash-4.2$ scp -r /home/oracle/FCDBT_EXPTPS/* 192.168.10.168:/home/oracle/FCDBT_EXPTPS

oracle@192.168.10.168's password:

FCDBT_tabsp.dmp 100% 7852KB 7.7MB/s 00:00

data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8 100% 500MB 45.5MB/s 00:11

data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf 100% 500MB 45.5MB/s 00:11

data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm 100% 160MB 53.3MB/s 00:03

-bash-4.2$

change back read write source db`s tablespace

ALTER TABLESPACE FCATDEV121 READ WRITE;

ALTER TABLESPACE FCAT_DEV21 READ WRITE;

ALTER TABLESPACE TBS_OFFLINE_DEV21 READ WRITE;

Import Metadata into Target Database

imp "'/ as sysdba'" file=FCDBT_tabsp.dmp transport_tablespace=y \

datafiles=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8, \

/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf, \

/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm

Checking on target database

SELECT owner, COUNT(*)

FROM dba_segments

WHERE tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21')

GROUP BY owner;

SELECT B.data_type, COUNT(*)

FROM dba_tables A, dba_tab_columns B

WHERE A.owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21')

AND B.owner = A.owner

AND B.table_name = A.table_name

GROUP BY B.data_type

ORDER BY B.data_type;

SELECT tablespace_name, segment_type, COUNT(*),

SUM (bytes) / 1024 / 1024 mb

FROM dba_segments

WHERE owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21')

GROUP BY tablespace_name, segment_type

ORDER BY 1, 2 DESC;

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

FCATDEV121 READ ONLY

FCAT_DEV21 READ ONLY

TBS_OFFLINE_DEV21 READ ONLY

8 rows selected.

SQL>

Alter status to Read Write

SQL> ALTER TABLESPACE FCATDEV121 READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE FCAT_DEV21 READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE TBS_OFFLINE_DEV21 READ WRITE;

Tablespace altered.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

FCATDEV121 ONLINE

FCAT_DEV21 ONLINE

TBS_OFFLINE_DEV21 ONLINE

8 rows selected.

SQL>

That is all. :)

Thursday, November 21, 2013

How to reset sga_max_size

Today I decided to increase memory but before that I wanted to change sga_max_size`s value.

Because in my production database configured sga_max_size to 12Gb.

It means independing on busy or idle of instance state, SGA could not be more than 12GB (it is worse).
The SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.

Our instance configured AMM:

select name, value from v$parameter where name like 'memory%'

memory_target 32212254720
memory_max_target 32212254720

--Checking init file

-bash-3.2$ more initPROD.ora| grep -i sga
PROD.__sga_target=12616466432
*.sga_max_size=25769803776
*.sga_target=0
-bash-3.2$

SGA_MAX_SIZE is not dynamic value, after changing those value, you should restart instance.

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 12G
sga_target big integer 0

SQL> alter system set sga_max_size=0 scope=spfile;

System altered.

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

SQL> startup
ORACLE instance started.

Total System Global Area 1,2828E+10 bytes
Fixed Size 2272944 bytes
Variable Size 6710886736 bytes
Database Buffers 6106906624 bytes
Redo Buffers 7602176 bytes
Database mounted.
Database opened.

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 12G
sga_target big integer 0

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.0.1/db_1/dbs/spfilePROD.ora

Uppps! Nothing is changed!

-bash-3.2$ cd /u01/app/oracle/product/11.0.1/db_1/dbs/
-bash-3.2$ more spfilePROD.ora | grep sga
PROD.__sga_target=12276727808
*.sga_max_size=0
*
-bash-3.2$

As you see spfile changed but Oracle set this parameter itself.

After that I investigated from the internet and found interesting post from Tom Kyte.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516

"You are using automatic memory management, we set things - the caches, everything, to whatever we feel like.
If you set memory_target, you need set nothing else. Just don't set it at all. Unset it."

If you want to set sga_max_size turn of memory parameters then set sga_max_size.

Thursday, October 24, 2013

Dropping user hanging

Today I requested that while dropping user request hanged after some time.

Let`s to check:

-bash-3.00$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Oct 23 15:52:36 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user fcjlive cascade;

Checking alert log

--checking alertlog

-bash-3.00$ tail -50 alert_PROD.log
ORA-12012: error on auto execute of job 3149
ORA-01435: user does not exist
Wed Oct 23 16:02:19 2013
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_j000_20447444.trc:
ORA-12012: error on auto execute of job 3149
ORA-01435: user does not exist
Wed Oct 23 16:04:19 2013
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_j000_20447450.trc:
ORA-12012: error on auto execute of job 3149
ORA-01435: user does not exist
Wed Oct 23 16:06:20 2013
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_j000_14614654.trc:
ORA-12012: error on auto execute of job 3149
ORA-01435: user does not exist

Opening another session and checking object counts.

SQL> select count(1) from dba_objects where owner='FCJLIVE';

COUNT(1)
----------
40504

SQL> /

COUNT(1)
----------
40465

SQL>

As you see count is decrease. But after some period it hanged.

drop user fcjlive cascade

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

I checking queue tables of mentioned user.

Select OWNER, QUEUE_TABLE from dba_queue_tables where owner = 'FCJLIVE'

no rows.

But there was not any queue table. Suddenly I remember there was error related jobs.

ORA-12012: error on auto execute of job 3149

select * from dba_jobs where job=3149;

Drop job:

exec dbms_job.remove(3149);

commit;

Now execute above statement again.

Friday, September 20, 2013

Interview with Lucia Hustatyova

Hi, today I will post interview with young beautiful woman, Oracle 10g and 11g OCM certified - Lucia Hustatyova.

She is one of the seldom DBA woman which have had OCM 10g and 11g certified.

After graduating from high school in Bardejov Stockel Leonard continued his studies at the University of Pavol Jozef Safarik in Kosice, the first scientific maths, later to study maths and science.
Thanks opportunities in Kosice as a student she worked at various companies for part-time positions.
The last six years working as a database consultant at famous company in Slovakia .

Q: Lucia, when did you pass that certified exam and where did you do it?
A: Oracle 10g OCM in Slovakia 2011, 11g also Slovakia but in 2012.

Q: How many years have you had experience with Oracle?
A: 6 years.

Q: What is your job responsibility and where currently are you in charge?
A: I am database admin at leader IT company - mainly practical stuff but sometimes my colleagues need consultations during performance problems or during creating architecture, and so on, therefore consultant as well
in our company it's also the name of the expert level.

Q: How long did you need for preparation?
A: Almost 4 months.

Q: Can you tell about the most recent oracle book you have read?
A: Last time it was about the exadata administratiion, because I am preparing for the certification.

Q: What are your interests, hobbies?
A: As every woman cleaning (does anyone have) and cooking :-).
I would rather it was divided into winter and summer activities - travel, skiing, bike, skate or just take a walk and forget about the daily grind.
Sometimes very happy exchange of technical documentation for fiction and excellent relaxation for me piano.

Q: In the end I would like to ask your recommendations to the people who are preparing the certificate exam?
A: Don`t be afraid and just go for OCM exam, it`s really worth for it. I can give you only one hint-
be familiar with oracle documentation.

Wednesday, July 10, 2013

Recovering database until time

Example of recovery database until time.

For checking after recovery I am going to create simple table with one row

 SQL> create user ulfet identified by ulfet;  
 User created.  
 SQL> grant dba to ulfet;  
 Grant succeeded.  
 SQL> conn ulfet/ulfet  
 Connected.  
 SSQL> create table test1 (id number);  
 Table created.  
 SQL> insert into test1 values(1);  
 1 row created.  
 SQL> commit;  
 Commit complete.

Now taking backup.

RMAN> backup database plus archivelog;

--note date

 SQL> select systimestamp from dual;  
 SYSTIMESTAMP  
 ---------------------------------------------------------------------------  
 13-JAN-16 11.03.34.646076 AM +04:00  
 --dropping table  
 SQL> drop table ulfet.test1;  
 Table dropped.  
 SQL> select systimestamp from dual;  
 SYSTIMESTAMP  
 ---------------------------------------------------------------------------  
 13-JAN-16 11.04.40.428868 AM +04:00  
 SQL>

--start recovery process

--firstly start database in nomount mode for restoring controlfile

RMAN> startup nomount  
 connected to target database (not started)  
 Oracle instance started  
 Total System Global Area   523108352 bytes  
 Fixed Size           1337632 bytes  
 Variable Size        314574560 bytes  
 Database Buffers       201326592 bytes  
 Redo Buffers          5869568 bytes  
 RMAN>

--drop control files

[oracle@dbserver oradata]$ cd mydb/  
 [oracle@dbserver mydb]$ ls  
 control01.ctl redo01.log redo03.log  system01.dbf undotbs01.dbf  
 example01.dbf redo02.log sysaux01.dbf temp01.dbf  users01.dbf  
 [oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ rm control01.ctl   
 [oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ cd ../../flash_recovery_area/  
 mydb/ MYDB/   
 [oracle@dbserver mydb]$ cd ../../flash_recovery_area/mydb/  
 [oracle@dbserver mydb]$ rm control02.ctl   
 [oracle@dbserver mydb]$

--Restoring controlfile from autoback;

RMAN> restore controlfile from autobackup;  
 Starting restore at 13-JAN-16  
 allocated channel: ORA_DISK_1  
 channel ORA_DISK_1: SID=20 device type=DISK  
 recovery area destination: /u01/app/oracle/flash_recovery_area  
 database name (or database unique name) used for search: MYDB  
 channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp found in the recovery area  
 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160113  
 channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp  
 channel ORA_DISK_1: control file restore from AUTOBACKUP complete  
 output file name=/u01/app/oracle/oradata/mydb/control01.ctl  
 output file name=/u01/app/oracle/flash_recovery_area/mydb/control02.ctl  
 Finished restore at 13-JAN-16

check if you want :)

[oracle@dbserver mydb]$ pwd  
 /u01/app/oracle/oradata/mydb  
 [oracle@dbserver mydb]$ ls control*  
 control01.ctl   
 [oracle@dbserver mydb]$ ls ../../flash_recovery_area/mydb/  
 control02.ctl

alter database in mount mode for restore and recovery process

 RMAN> alter database mount;  
 database mounted  
 released channel: ORA_DISK_1  
 RMAN>

--write commands for recovery process

 RMAN> run  
 {  
 set newname for database to '/u01/app/oracle/oradata/mydb/%b';  
 set until time "to_date('2016-01-13 11:03:34', 'yyyy-mm-dd hh24:mi:ss')";  
 restore database;  
 switch datafile all;  
 switch tempfile all;  
 recover database;  
 }2> 3> 4> 5> 6> 7> 8> 9>   
 executing command: SET NEWNAME  
 executing command: SET until clause  
 Starting restore at 13-JAN-16  
 Starting implicit crosscheck backup at 13-JAN-16  
 allocated channel: ORA_DISK_1  
 channel ORA_DISK_1: SID=20 device type=DISK  
 Crosschecked 9 objects  
 Finished implicit crosscheck backup at 13-JAN-16  
 Starting implicit crosscheck copy at 13-JAN-16  
 using channel ORA_DISK_1  
 Finished implicit crosscheck copy at 13-JAN-16  
 searching for all files in the recovery area  
 cataloging files...  
 cataloging done  
 List of Cataloged Files  
 =======================  
 File Name: /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901017408_c9cw425g_.bkp  
 File Name: /u01/app/oracle/flash_recovery_area/MYDB/autobackup/2016_01_13/o1_mf_s_901018889_c9cxlcbt_.bkp  
 using channel ORA_DISK_1  
 channel ORA_DISK_1: starting datafile backup set restore  
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set  
 channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/mydb/system01.dbf  
 channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/mydb/sysaux01.dbf  
 channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/mydb/undotbs01.dbf  
 channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/mydb/users01.dbf  
 channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/mydb/example01.dbf  
 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/MYDB/backupset/2016_01_13/o1_mf_nnndf_TAG20160113T110002_c9cxhmmo_.bkp  
 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/MYDB/backupset/2016_01_13/o1_mf_nnndf_TAG20160113T110002_c9cxhmmo_.bkp tag=TAG20160113T110002  
 channel ORA_DISK_1: restored backup piece 1  
 channel ORA_DISK_1: restore complete, elapsed time: 00:01:56  
 Finished restore at 13-JAN-16  
 Starting recover at 13-JAN-16  
 using channel ORA_DISK_1  
 starting media recovery  
 archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/MYDB/archivelog/2016_01_13/o1_mf_1_2_c9cxl882_.arc  
 archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/mydb/redo03.log  
 archived log file name=/u01/app/oracle/flash_recovery_area/MYDB/archivelog/2016_01_13/o1_mf_1_2_c9cxl882_.arc thread=1 sequence=2  
 archived log file name=/u01/app/oracle/oradata/mydb/redo03.log thread=1 sequence=3  
 media recovery complete, elapsed time: 00:00:02  
 Finished recover at 13-JAN-16

I have forgotten writing open table in resetlogs in end of command.
Now you can see, out database`s state is mounted, open it with resetlogs

SQL> conn /as sysdba  
 Connected.  
 SQL> select open_mode from v$database;  
 OPEN_MODE  
 --------------------  
 MOUNTED  
 SQL> alter database open resetlogs;  
 Database altered.

--Now try to check

SQL> conn ulfet/ulfet  
 Connected.  
 SQL> select * from tab;  
 TNAME             TABTYPE CLUSTERID  
 ------------------------------ ------- ----------  
 TEST1             TABLE  
 SQL> select * from test1;  
     ID  
 ----------  
      1  
 SQL>

That`s all

Tuesday, July 9, 2013

How to fix ORA-01130: database file version 11.1.0.0.0 incompatible with ORACLE version 11.0.0.0.0

Today while duplication database I faced strange error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/09/2013 16:02:16
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-01130: database file version 11.1.0.0.0 incompatible with ORACLE version 11.0.0.0.0
ORA-01110: data file 1: '/u01/app/oracle/oradata/DUPLPROD/system01.dbf'

I checked all actions and tried again, but result was same. Error happened creation control file. Let me note that I used to duplication on the same host and Oracle version 11.1.0.7.0

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
SQL>

SQL> show parameter compatible

NAME TYPE VALUE
----------- ------ ----------
compatible string 11.1.0.0.0
SQL>

I just added below line on duplication DB`s init file:

compatible='11.1.0'

Here is step by step creation duplication:

My production db is PROD and planning to create duplication from action database without backup and name will be DUPLPROD.

So, firstly
create listener, tns for duplication.

listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = PROD)
)
(SID_DESC =
(GLOBAL_DBNAME = DUPLPROD)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = DUPLPROD)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxxx)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

tnsname.ora

PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)

DUPLPROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DUPLPROD)
)
)

create folders : ../oradata/DUPLPROD ../flashback/DUPLPROD etc

create init file (I created it under dbs)

create password file:

cd $ORACLE_HOME/dbs

orapwd password=oracle file=orapwDUPLPROD

file_name : initDUPLPROD.ora

DB_NAME=DUPLPROD
diagnostic_dest='/u01/app/oracle/admin/DUPLPROD'
DB_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/PREPROD','/u01/app/oracle/oradata/DUPLPROD')
LOG_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/PREPROD','/u01/app/oracle/oradata/DUPLPROD')
compatible='11.1.0'

Now, start DUPLPROD in nomount mode using pfile.

export ORACLE_SID=DUPLPROD
sqlplus "/as sysdba"

SQL> startup nomount pfile=/u01/app/oracle/product/11.1.0/db_1/dbs/initDUPLPROD.ora
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2152328 bytes
Variable Size 159385720 bytes
Database Buffers 50331648 bytes
Redo Buffers 5287936 bytes
SQL>

Test connectivity to auxiliary and target instance from the Both Sides.

sqlplus "sys/oracle@PREPROD" as sysdba

sqlplus sys/oracle@DUPLPROD as sysdba

Now connect to RMAN and make duplication:

-bash-3.00$ rman target sys/oracle@PREPROD auxiliary sys/oracle@DUPLPROD

or

-bash-3.00$ rman target sys/oracle@PREPROD

Recovery Manager: Release 11.1.0.7.0 - Production on Tue Jul 9 15:07:49 2013

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

connected to target database: PREPROD (DBID=628957311)

RMAN> connect auxiliary sys/oracle@DUPLPROD

connected to auxiliary database: DUPLPROD (not mounted)

RMAN>

run
{
DUPLICATE TARGET DATABASE TO 'DUPLPROD' FROM ACTIVE DATABASE
NOFILENAMECHECK;
}

Thursday, June 27, 2013

Oracle database 12c now available!

So, finally Oracle introduced Oracle database 12c (12.1.0.1). You can easily download it from oracle`s official site : http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
For now only for Linux and Solaris platform`s download is available, soon for windows platform will have.

Thursday, June 13, 2013

Kill Session vs Disconnect Session

Today my friend asked me what is the different between kill session and disconnect session. After researching I found useful information and here I`m going to share briefly.

Using Disconnect Session clause to disconnect the current session by destroying the dedicated server process.
Using Kill Session clause let you mark a session as terminated, release locks.

Example:
SQL>ALTER SYSTEM DISCONNECT SESSION 'sid,serial#';
SQL>ALTER SYSTEM KILL SESSION 'sid,serial#';

For more details please refer to : http://oracletempspace.wordpress.com/2011/04/20/example-alter-sytem-disconnect-vs-alter-system-kill-for-currently-connected-sessions/

Monday, March 25, 2013

How to fix ORA-26040: Data block was loaded using the NOLOGGING option

Today I faced with new ORA error. After solving I want to share this experience with yours.

So, today 5`th datafile of my database was corrupted (/u01/app/oracle/oradata/ulfet_db/example01.dbf).
After recover via RMAN I saw strange error.

RMAN> recover datafile 5 block 443;
Starting recover at 24-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp tag=TAG20130324T223233
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03

starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-MAR-13
RMAN>

SQL> select count(1) from block_corrupt_test;
select count(1) from block_corrupt_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 443)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ulfet_db/example01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

col segment_name format a20
col tablespace_name format a15

SQL> select owner, segment_name, tablespace_name from dba_extents where FILE_ID=5 and 443 between BLOCK_ID and BLOCK_ID+BLOCKS;

OWNER SEGMENT_NAME TABLESPACE_NAME
------------------------ -------------------- ---------------
SYS BLOCK_CORRUPT_TEST EXAMPLE

Using dbv utility to clarify datafile corruption

[oracle@localhost ~]$ dbv file=/u01/app/oracle/oradata/ulfet_db/example01.dbf

DBVERIFY: Release 11.2.0.1.0 - Production on Sun Mar 24 22:57:06 2013

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ulfet_db/example01.dbf

DBV-00201: Block, DBA 20971963, marked corrupt for invalid redo application

DBVERIFY - Verification complete

Total Pages Examined : 12800

Total Pages Processed (Data) : 6586

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 1261

Total Pages Failing (Index): 0

Total Pages Processed (Other): 3251

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 1702

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 1377097 (0.1377097)

[oracle@localhost ~]$

Use below query to get block error and file_name:

SQL> select dbms_utility.data_block_address_block('20971963') block_no,

dbms_utility.data_block_address_file('20971963') file_no

from dual;

BLOCK_NO FILE_NO

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

443 5

SQL>

Now follow up below statements:

Create the repair table (in tablespace where corrupted table resides)

SQL> BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'REPAIR_TABLE',

TABLE_TYPE => dbms_repair.repair_table,

ACTION => dbms_repair.create_action,

TABLESPACE => 'example');

END;

/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

SQL>

Identify corrupted blocks:

SQL> set serveroutput on

DECLARE num_corrupt INT;

BEGIN

num_corrupt := 0;

DBMS_REPAIR.CHECK_OBJECT (

SCHEMA_NAME => 'SYS',

OBJECT_NAME => 'BLOCK_CORRUPT_TEST',

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

corrupt_count => num_corrupt);

DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;

/SQL> 2 3 4 5 6 7 8 9 10 11

number corrupt: 1

PL/SQL procedure successfully completed.

SQL>

Show corrupted blocks:

SQL> select BLOCK_ID, CORRUPT_TYPE from REPAIR_TABLE;

BLOCK_ID CORRUPT_TYPE

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

443 6148

SQL>

Mark the identified blocks as corrupted:

SQL> DECLARE num_fix INT;

BEGIN

num_fix := 0;

DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

SCHEMA_NAME => 'SYS',

OBJECT_NAME=> 'BLOCK_CORRUPT_TEST',

OBJECT_TYPE => dbms_repair.table_object,

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

FIX_COUNT=> num_fix);

DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));

END;

/ 2 3 4 5 6 7 8 9 10 11 12

PL/SQL procedure successfully completed.

SQL>

Allow in the future DML statements to skip the corrupted blocks:

SQL> BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME => 'SYS',

OBJECT_NAME => 'BLOCK_CORRUPT_TEST',

OBJECT_TYPE => dbms_repair.table_object,

FLAGS => dbms_repair.SKIP_FLAG);

END;

/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

SQL>

Now, check table again:

SQL> select count(1) from block_corrupt_test;

COUNT(1)

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

Wednesday, March 13, 2013

Change production database name

Today I will show you how to rename existing database name. Before it take backup of your database and also please note that this step will change only database name not dbid.

My database name is prod2 and oracle version is 11g R2. I will change it to prod name.

SQL> select name from v$database;

NAME
---------
PROD2

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

[oracle@localhost ~]$ uname -n

localhost.localdomain

[oracle@localhost ~]$ more /etc/redhat-release

Red Hat Enterprise Linux Server release 5.5 (Tikanga)

Firstly shutdown database and open it on mount mode.

[oracle@localhost ~]$ sql

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 13 19:29:16 2013

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

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 318046208 bytes

Fixed Size 1336260 bytes

Variable Size 109055036 bytes

Database Buffers 201326592 bytes

Redo Buffers 6328320 bytes

Database mounted.

SQL>

Now execute nid utility (dbnewid) and specify new database name.

[oracle@localhost ~]$ nid target =/ dbname=prod setname=YES

DBNEWID: Release 11.2.0.1.0 - Production on Wed Mar 13 19:33:07 2013

Connected to database PROD2 (DBID=1461820883)

Connected to server version 11.2.0

Control Files in database:

/u01/app/oracle/oradata/prod2/control01.ctl

/u01/app/oracle/flash_recovery_area/prod2/control02.ctl

Change database name of database PROD2 to PROD? (Y/[N]) => Y

Proceeding with operation

Changing database name from PROD2 to PROD

Control File /u01/app/oracle/oradata/prod2/control01.ctl - modified

Control File /u01/app/oracle/flash_recovery_area/prod2/control02.ctl - modified

Datafile /u01/app/oracle/oradata/prod2/system01.db - wrote new name

Datafile /u01/app/oracle/oradata/prod2/sysaux01.db - wrote new name

Datafile /u01/app/oracle/oradata/prod2/undotbs01.db - wrote new name

Datafile /u01/app/oracle/oradata/prod2/users01.db - wrote new name

Datafile /u01/app/oracle/oradata/prod2/temp01.db - wrote new name

Control File /u01/app/oracle/oradata/prod2/control01.ctl - wrote new name

Control File /u01/app/oracle/flash_recovery_area/prod2/control02.ctl - wrote new name

Instance shut down

Database name changed to PROD.

Modify parameter file and generate a new password file before restarting.

Succesfully changed database name.

DBNEWID - Completed succesfully.

[oracle@localhost ~]$

Create new name directories in admin folder

[oracle@localhost admin]$ mkdir prod

[oracle@localhost admin]$ cd prod

[oracle@localhost prod]$ mkdir adump dpdump pfile

Last action is change pfile and password file and open database with resetlogs option.

[oracle@localhost pfile]$ pwd

/u01/app/oracle/admin/prod2/pfile

[oracle@localhost pfile]$ ls

init.ora.2132013192312

[oracle@localhost pfile]$ cat init.ora.2132013192312 | grep db_name

db_name=prod2

#change db_name to new db_name
#change audit_file_dest to new path : /u01/app/oracle/admin/prod/adump

[oracle@localhost pfile]$ vi init.ora.2132013192312

[oracle@localhost pfile]$ cat init.ora.2132013192312 | grep db_name

db_name=prod

[oracle@localhost pfile]$ cat init.ora.2132013192312 | grep audit_file
audit_file_dest=/u01/app/oracle/admin/prod/adump

[oracle@localhost dbs]$ pwd
/u01/oracle/product/11.2.0/db_1/dbs

[oracle@localhost dbs]$ orapwd file=orapwprod entries=5 ignorecase=y password=oracle

[oracle@localhost dbs]$ export | grep ORACLE_SID

declare -x ORACLE_SID="prod"

[oracle@localhost dbs]$

mv init file from ../prod2/pfile/ to ../prod/pfile/

and recreate spfile

[oracle@localhost dbs]$ export | grep ORACLE_SID

declare -x ORACLE_SID="prod"

[oracle@localhost dbs]$ sql

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 13 19:52:23 2013

Connected to an idle instance.

SQL> shut immediate

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux Error: 2: No such file or directory

SQL> shut abort;

ORACLE instance shut down.

SQL> create spfile from pfile='/u01/app/oracle/admin/prod/pfile/init.ora.2132013192312';

File created.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 318046208 bytes

Fixed Size 1336260 bytes

Variable Size 109055036 bytes

Database Buffers 201326592 bytes

Redo Buffers 6328320 bytes

Database mounted.

SQL> alter database open;

Database altered.

SQL> select name from v$database;

NAME

---------

PROD

SQL>

Friday, February 1, 2013

Archive and purge aud$ table

If in your production database configured audit, DBA should maintain audit tables.
Because audit records may grows up to undesired size. Today I will show you one of the easy way to care aud$ table.

Scenario:

Create new tablespace
Create new archive table
Create procedure
Create Scheduler or Cron Job
Execute and check result

Firstly I will create new tablespace for my achived aud table.

 SQL> create tablespace arch_tbs   
 datafile '/u01/app/oracle/oradata/ulfet_db/arch_tbs01.dbf'   
 size 500M;  
 Tablespace created.

-- you can set more space
SQL> alter tablespace arch_tbs add datafile '/u01/app/oracle/oradata/ulfet_db/arch_tbs02.dbf' size 500M;

Tablespace altered.

Now I will create new table with aud$ table structure but new table will be range partitioned.
--You can create daily/monthly/yearly partition also subpartition too.

To get table structure I use dbms_metadata.get_ddl package`s procedure.

SQL> set pagesize 1000  
 SQL> select   
 to_char(dbms_metadata.get_ddl('TABLE', 'AUD$'))   
 from dual;  
 TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','AUD$'))  
 --------------------------------------------------------------------------------  
  CREATE TABLE "SYS"."AUD$"  
   (  "SESSIONID" NUMBER NOT NULL ENABLE,  
     "ENTRYID" NUMBER NOT NULL ENABLE,  
     "STATEMENT" NUMBER NOT NULL ENABLE,  
     "TIMESTAMP#" DATE,  
     "USERID" VARCHAR2(30),  
     "USERHOST" VARCHAR2(128),  
     "TERMINAL" VARCHAR2(255),  
     "ACTION#" NUMBER NOT NULL ENABLE,  
     "RETURNCODE" NUMBER NOT NULL ENABLE,  
     "OBJ$CREATOR" VARCHAR2(30),  
     "OBJ$NAME" VARCHAR2(128),  
     "AUTH$PRIVILEGES" VARCHAR2(16),  
     "AUTH$GRANTEE" VARCHAR2(30),  
     "NEW$OWNER" VARCHAR2(30),  
     "NEW$NAME" VARCHAR2(128),  
     "SES$ACTIONS" VARCHAR2(19),  
     "SES$TID" NUMBER,  
     "LOGOFF$LREAD" NUMBER,  
     "LOGOFF$PREAD" NUMBER,  
     "LOGOFF$LWRITE" NUMBER,  
     "LOGOFF$DEAD" NUMBER,  
     "LOGOFF$TIME" DATE,  
     "COMMENT$TEXT" VARCHAR2(4000),  
     "CLIENTID" VARCHAR2(64),  
     "SPARE1" VARCHAR2(255),  
     "SPARE2" NUMBER,  
     "OBJ$LABEL" RAW(255),  
     "SES$LABEL" RAW(255),  
     "PRIV$USED" NUMBER,  
     "SESSIONCPU" NUMBER,  
     "NTIMESTAMP#" TIMESTAMP (6),  
     "PROXY$SID" NUMBER,  
     "USER$GUID" VARCHAR2(32),  
     "INSTANCE#" NUMBER,  
     "PROCESS#" VARCHAR2(16),  
     "XID" RAW(8),  
     "AUDITID" VARCHAR2(64),  
     "SCN" NUMBER,  
     "DBID" NUMBER,  
     "SQLBIND" CLOB,  
     "SQLTEXT" CLOB,  
     "OBJ$EDITION" VARCHAR2(30)  
   ) PCTFREE 10 PCTUSED 40 INITRANS 1   
 MAXTRANS 255 NOCOMPRESS LOGGING  
 STORAGE(INITIAL 65536 NEXT 1048576   
 MINEXTENTS 1 MAXEXTENTS 2147483645  
 PCTINCREASE 0 FREELISTS 1 FREELIST   
 GROUPS 1 BUFFER_POOL DEFAULT   
 FLASH_CACHE DE FAULT   
 CELL_FLASH_CACHE DEFAULT)  
 TABLESPACE "SYSTEM"  
 LOB ("SQLBIND") STORE AS BASICFILE (  
 TABLESPACE "SYSTEM"   
 ENABLE STORAGE IN ROW CHUNK   
 8192 RETENTION NOCACHE LOGGING  
 STORAGE(INITIAL 65536 NEXT 1048576   
 MINEXTENTS 1 MAXEXTENTS 2147483645  
 PCTINCREASE 0 FREELISTS 1   
 FREELIST GROUPS 1   
 BUFFER_POOL DEFAULT FLASH_CACHE DE  
 FAULT CELL_FLASH_CACHE DEFAULT))  
 LOB ("SQLTEXT") STORE AS BASICFILE (  
 TABLESPACE "SYSTEM" ENABLE   
 STORAGE IN ROW CHUNK 8192 RETENTION  
 NOCACHE LOGGING  
 STORAGE(INITIAL 65536 NEXT 1048576   
 MINEXTENTS 1 MAXEXTENTS 2147483645  
 PCTINCREASE 0 FREELISTS 1   
 FREELIST GROUPS 1 BUFFER_POOL DEFAULT   
 FLASH_CACHE DE FAULT   
 CELL_FLASH_CACHE DEFAULT))  
 SQL>

--a little bit change and creation script is ready

SQL> CREATE TABLE SYS.AUD$_ARCH  
 (  
  SESSIONID    NUMBER NOT NULL ENABLE,  
  ENTRYID     NUMBER NOT NULL ENABLE,  
  STATEMENT    NUMBER NOT NULL ENABLE,  
  TIMESTAMP#    DATE,  
  USERID      VARCHAR2(30 BYTE),  
  USERHOST     VARCHAR2(128 BYTE),  
  TERMINAL     VARCHAR2(255 BYTE),  
  ACTION#     NUMBER,  
  RETURNCODE    NUMBER,  
  OBJ$CREATOR   VARCHAR2(30 BYTE),  
  OBJ$NAME     VARCHAR2(128 BYTE),  
  AUTH$PRIVILEGES VARCHAR2(16 BYTE),  
  AUTH$GRANTEE   VARCHAR2(30 BYTE),  
  NEW$OWNER    VARCHAR2(30 BYTE),  
  NEW$NAME     VARCHAR2(128 BYTE),  
  SES$ACTIONS   VARCHAR2(19 BYTE),  
  SES$TID     NUMBER,  
  LOGOFF$LREAD   NUMBER,  
  LOGOFF$PREAD   NUMBER,  
  LOGOFF$LWRITE  NUMBER,  
  LOGOFF$DEAD   NUMBER,  
  LOGOFF$TIME   DATE,  
  COMMENT$TEXT   VARCHAR2(4000 BYTE),  
  CLIENTID     VARCHAR2(64 BYTE),  
  SPARE1      VARCHAR2(255 BYTE),  
  SPARE2      NUMBER,  
  OBJ$LABEL    RAW(255),  
  SES$LABEL    RAW(255),  
  PRIV$USED    NUMBER,  
  SESSIONCPU    NUMBER,  
  NTIMESTAMP#   TIMESTAMP(6),  
  PROXY$SID    NUMBER,  
  USER$GUID    VARCHAR2(32 BYTE),  
  INSTANCE#    NUMBER,  
  PROCESS#     VARCHAR2(16 BYTE),  
  XID       RAW(8),  
  AUDITID     VARCHAR2(64 BYTE),  
  SCN       NUMBER,  
  DBID       NUMBER,  
  SQLBIND     CLOB,  
  SQLTEXT     CLOB,

  OBJ$EDITION VARCHAR2(30)

 )  
 TABLESPACE ARCH_TBS  
 NOLOGGING  
 PARTITION BY RANGE (NTIMESTAMP#)  
 (  
 PARTITION P201211 VALUES   
 LESS THAN (TO_DATE('2012-12-01', 'YYYY-MM-DD')),  
 PARTITION P201212 VALUES   
 LESS THAN (TO_DATE('2013-01-01', 'YYYY-MM-DD')),  
 PARTITION P201301 VALUES   
 LESS THAN (TO_DATE('2013-02-01', 'YYYY-MM-DD')),  
 PARTITION P201302 VALUES   
 LESS THAN (TO_DATE('2013-03-01', 'YYYY-MM-DD')),  
 PARTITION P201303 VALUES   
 LESS THAN (TO_DATE('2013-04-01', 'YYYY-MM-DD')),  
 PARTITION P201304 VALUES   
 LESS THAN (TO_DATE('2013-05-01', 'YYYY-MM-DD'))  
 )              
 NOCOMPRESS  
 NOCACHE  
 NOPARALLEL  
 MONITORING;             
 Table created.  
 SQL>

Now time to create procedure. Procedure will 1 input parameter (date).

 CREATE OR REPLACE PROCEDURE SYS.purge_audit_trail2 (input_days IN NUMBER) AS  
 purge_date  DATE;  
 i         NUMBER := 0;  
 rcount     NUMBER := 0;  
 BEGIN  
   purge_date := TRUNC (SYSDATE - input_days);  
   DBMS_SYSTEM.  
   ksdwrt (2,'AUDIT: Purging Audit Trail until '|| purge_date|| ' Start datetime : '|| TO_CHAR (SYSDATE, 'dd/mm/yyyy, HH24:MI:SS')); -- writing message to alert log  
   FOR c IN (SELECT ROWID AS rwid,  
           sessionid,  
           entryid,  
           STATEMENT,  
           timestamp#,  
           userid,  
           userhost,  
           terminal,  
           action#,  
           returncode,  
           obj$creator,  
           obj$name,  
           auth$privileges,  
           auth$grantee,  
           new$owner,  
           new$name,  
           ses$actions,  
           ses$tid,  
           logoff$lread,  
           logoff$pread,  
           logoff$lwrite,  
           logoff$dead,  
           logoff$time,  
           comment$text,  
           clientid,  
           spare1,  
           spare2,  
           obj$label,  
           ses$label,  
           priv$used,  
           sessioncpu,  
           ntimestamp#,  
           proxy$sid,  
           user$guid,  
           instance#,  
           process#,  
           xid,  
           auditid,  
           SCN,  
           dbid,  
           sqlbind,  
           sqltext,

           obj$edition
         FROM SYS.aud$  
        WHERE ntimestamp# < purge_date)  
   LOOP  
    INSERT INTO SYS.AUD$_ARCH  
       VALUES (c.sessionid,  
           c.entryid,  
           c.STATEMENT,  
           c.timestamp#,  
           c.userid,  
           c.userhost,  
           c.terminal,  
           c.action#,  
           c.returncode,  
           c.obj$creator,  
           c.obj$name,  
           c.auth$privileges,  
           c.auth$grantee,  
           c.new$owner,  
           c.new$name,  
           c.ses$actions,  
           c.ses$tid,  
           c.logoff$lread,  
           c.logoff$pread,  
           c.logoff$lwrite,  
           c.logoff$dead,  
           c.logoff$time,  
           c.comment$text,  
           c.clientid,  
           c.spare1,  
           c.spare2,  
           c.obj$label,  
           c.ses$label,  
           c.priv$used,  
           c.sessioncpu,  
           c.ntimestamp#,  
           c.proxy$sid,  
           c.user$guid,  
           c.instance#,  
           c.process#,  
           c.xid,  
           c.auditid,  
           c.SCN,  
           c.dbid,  
           c.sqlbind,  
           c.sqltext,

           c.obj$edition);

    DELETE FROM SYS.aud$  
       WHERE ROWID = c.rwid;  
    i := i + 1;  
    IF MOD (i, 1000) = 0  
    THEN  
      rcount := rcount + i;  
      i := 0;  
      COMMIT;  
    END IF;  
   END LOOP;  
   rcount := rcount + i;  
   COMMIT;  
   DBMS_SYSTEM.  
   ksdwrt (3,'AUDIT: Purging Audit Trail until '|| purge_date|| ' has completed. Processed : '|| rcount|| ' rows. End datetime: '|| TO_CHAR (SYSDATE, 'dd/mm/yyyy, HH24:MI:SS')); -- writing message to alert log and trace file  
 END;  
 /

You can create scheduler and set:

 SQL> BEGIN    
 sys.dbms_scheduler.create_job(  
 job_name => 'PURGE_AUDIT_TABLE',   
 job_type => 'PLSQL_BLOCK',   
 job_action => 'begin purge_audit_trail(10); end;',   
 schedule_name => 'MAINTENANCE_WINDOW_GROUP',     
 job_class => '"DEFAULT_JOB_CLASS"',     
 comments => 'Audit Trail Purge',     
 auto_drop => FALSE,     
 enabled => TRUE  
 );   
 END;   
 /    
 PL/SQL procedure successfully completed.  
 SQL>

Check

 SQL> select owner, job_name, job_name, program_owner,   
 program_name, enabled, last_start_date, next_run_date  
 from dba_scheduler_jobs where job_name like '%AUD%'

But I will create cron job and this script will work every day of months :

5,15,25 (3 times per months) at 01:00 a.m.

[oracle@localhost ~]$ crontab -l

0 01 5,15,25 * * /home/oracle/audit_purge_table.sh

Script is audit_purge_table.sh

[oracle@localhost ~]$ vi audit_purge_table.sh   
 #! /bin/sh  
 . /home/oracle/.bashrc  
 export ORACLE_SID=ulfetdb  
 sqlplus -S '/as sysdba' <<NEOF>>/home/audit_ptable.log  
 SET SERVEROUTPUT ON;  
 begin  
 SYS.purge_audit_trail2(3); --purge older 3 days  
 end;  
 /  
 exit;  
 NEOF

Now checking script`s output log:

[oracle@localhost ~]$ tail -10 audit_purge_table.log

PL/SQL procedure successfully completed.

Checking alert log:

[oracle@localhost ~]$ tail -5 /u01/app/oracle/diag/rdbms/ulfet_db/ulfetdb/trace/alert_ulfetdb.log

Fri Feb 01 01:11:30 2013

SMCO started with pid=29, OS id=17010

Fri Feb 01 01:20:01 2013

AUDIT: Purging Audit Trail until 31-01-2013 Start datetime : 01/02/2013, 01:20:01

AUDIT: Purging Audit Trail until 31-01-2013 Processed : 391 rows. End datetime: 01/02/2013, 01:20:02

[oracle@localhost ~]$

 SQL> select count(1) from AUD$_ARCH partition(p201301);  
  COUNT(1)  
 ----------  
     391  
 SQL>

Source: http://blog.abigold.fr/joomla/index.php?option=com_content&view=article&id=122:purge-audit-table-aud&catid=66:oracle-securite&Itemid=53

That`s all.

Ulfet`s Oracle Blog

Friday, December 20, 2013

Changing archivelog destination in STANBY side

Wednesday, December 18, 2013

What is ID: oracle.install.commons.util.exception.DefaultErrorAdvisor:37 and how to fix it

Tuesday, December 17, 2013

Moving & renaming datafile

Moving production databases across platforms

Thursday, November 21, 2013

How to reset sga_max_size

Thursday, October 24, 2013

Dropping user hanging

Friday, September 20, 2013

Interview with Lucia Hustatyova

Wednesday, July 10, 2013

Recovering database until time

Tuesday, July 9, 2013

How to fix ORA-01130: database file version 11.1.0.0.0 incompatible with ORACLE version 11.0.0.0.0

Thursday, June 27, 2013

Oracle database 12c now available!

Thursday, June 13, 2013

Kill Session vs Disconnect Session

Monday, March 25, 2013

How to fix ORA-26040: Data block was loaded using the NOLOGGING option

Wednesday, March 13, 2013

Change production database name

Friday, February 1, 2013

Archive and purge aud$ table

Cannot access dba_directories inside procedure

Total Pageviews

My Blog List