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

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


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

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

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)
------------
10


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

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


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

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

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

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

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.

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...