Move datafiles

Sometimes, when all your datafiles are on the same volume, you can have a lack of space and risks fulling it. In that case, you should move some of your DB datafiles (*.dbf) to another volume.

To do so, you have a few steps to follow:

On the primary

1. tablespace with the files to move must be offline
2. move the datafile(s) with the OS command
3. rename the datafile with SQL
4. put the tablespace online

For the example, we'll move files from /volume_01 to /volume_02, which are within the DATA tablespace.

1. Put the tablespace offline:

SQL> ALTER TABLESPACE DATA OFFLINE NORMAL;

2. Move the file(s)

[oracle@lab01 ~]$ mv /volume_01/datafile/data_01.dbf /volume_02/datafile/data_01.dbf

[oracle@lab01 ~]$ mv /volume_01/datafile/data_02.dbf /volume_02/datafile/data_02.dbf

3. SQL rename

SQL>  ALTER TABLESPACE DATA_XX_REPORT RENAME DATAFILE

      '/volume_01/datafile/data_01.dbf', '/volume_01/datafile/data_02.dbf'

      TO

      '/volume_02/datafile/data_01.dbf', '/volume_02/datafile/data_02.dbf';

4. Tablespace back online

SQL> ALTER TABLESPACE DATA_XX_REPORT ONLINE;

On the standby

The datafiles are supposed to be renamed automatically thanks to the parameter STANDBY_FILE_MANAGEMENT='AUTO', but it doesn't... To fix this, it has to be done on the standby as well.
The steps are:

1. Switch off managed recovery
2. Shutdown immediate
3. move the affected datafiles
4. startup mount
5. alter the database standby file management to manual
6. alter database rename files to new location.
7. alter database standby file management to auto.
8. switch on managed recovery.

1. Switch off managed recovery

[oracle@lab01 ~]$ dgmgrl /

DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production



Copyright (c) 2000, 2005, Oracle. All rights reserved.



Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> disable configuration

Disable.

 stb file management

SQL> ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;

2. Shutdown immediate

SQL> shutdown immediate

ORA-01109: database not open



Database dismounted.

ORACLE instance shut down.

3.  move the files (OS)
See primary commands

4. Startup mount

SQL> startup mount

ORACLE instance started.



Total System Global Area 8.5899E+10 bytes

Fixed Size      2245688 bytes

Variable Size   1711279048 bytes

Database Buffers  8.4171E+10 bytes

Redo Buffers     14528512 bytes

Database mounted.

6. SQL rename files
As the standby is not open, the alter tablespace command does not work. To do it, use the below command

SQL> alter database rename file

     '/volume_01/datafile/data_01.dbf', '/volume_01/datafile/data_02.dbf'

     TO

     '/volume_02/datafile/data_01.dbf', '/volume_02/datafile/data_02.dbf';

7. AUTO standby file management

SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;

8. switch on managed recovery
On the primary:

[oracle@lab01 ~]$ dgmgrl /

DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production



Copyright (c) 2000, 2005, Oracle. All rights reserved.



Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> show configuration



Configuration

  Name:                db1

  Enabled:             NO

  Protection Mode:     MaxPerformance

  Fast-Start Failover: DISABLED

  Databases:

    db1_lab02 - Primary database

    db1_lab01 - Physical standby database



Current status for "db1":

DISABLED



DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration



Configuration

  Name:                db1

  Enabled:             YES

  Protection Mode:     MaxPerformance

  Fast-Start Failover: DISABLED

  Databases:

    db1_lab02 - Primary database

    db1_lab01 - Physical standby database



Current status for "db1":

SUCCESS

Oracle 10g dbconsole setup

How to see the status of dbconsole and the URL :

su - oracle
emctl status dbconsole

TZ set to Europe/Paris
Oracle Enterprise Manager 10g Database Control Release 10.2.0.3.0
Copyright (c) 1996, 2006 Oracle Corporation.  All rights reserved.
http://ora-stb:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db64/ora-stb_URCL2/sysman/log

How to start the dbconsole :

su - oracle
emctl start dbconsole

How to stop the dbconsole :

su - oracle
emctl stop dbconsole

Recreate DbConsole online

The database will be placed in quiecse mode if you are using emca to drop and recreate. For example, the following commands will quiesce database:

emca -deconfig dbcontrol db -repos drop emca -config dbcontrol db -repos recreate

But "emca -config dbcontrol db -repos create" will not place the database in quiecse state.
So being said this, I would suggest you to manually drop the dbcontrol and then create it using emca which does not require downtime.
So you can do the following steps to recreate the database control which does not place the database in quiesce state:
Make sure that the database and the listener is up and running.
Logon SQLPLUS as user SYS and drop the sysman account and mangement objects:

drop user sysman cascade;
drop role MGMT_USER;
drop user MGMT_VIEW cascade;
drop public synonym MGMT_TARGET_BLACKOUTS;
drop public synonym SETEMVIEWUSERCONTEXT;

Manually remove the following folders:

ORACLE_HOME/host_sid/ ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_host_sid

For example if ORACLE_HOME is /u01/app/oracle and hostname is test.oracle.com and sid is db1, then the above mentioned folders would be:

/u01/app/oracle/oracle/test.oracle.com_db1 /u01/app/oracle/oc4j/j2ee/OC4J_DBConsole_test.oracle.com_db1

Set the ORACLE_HOME and ORACLE_SID
From $ORACLE_HOME/bin issue the following command:

emca -deconfig dbcontrol db -repos drop

Attention ! Database in quiesce mode

emca -config dbcontrol db -repos create

Reconfigure the Port

emca -reconfig ports -DBCONTROL_HTTP_PORT 5500

For more information see Metalink Doc: https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=278100.1&blackframe=1

Install Oracle Grid Control 11g

To install Oracle Grid Control, you need to have a few components installed beforehand.
* Oracle database 11g
* Oracle Weblogic server

Once this is done, you can install it. The steps bellow will guide you from the beginning.


Step 1 : Install the DB
It will be detailed in another post. Link to come soon.

Step 2 : Install the weblogic server
Be sure you have the java 1.6 SDK installed:

[oracle@ora-gridr1 oracle]$ java -version

java version "1.6.0_21"
Java(TM) SE Runtime Environment (build 1.6.0_21-b06)
Java HotSpot(TM) 64-Bit Server VM (build 17.0-b16, mixed mode)

Then, download the version of the installer you need, then run it:

java -jar wls1032_generic.jar

When it is asked, choose the typical install

After that, next, next, next and finish.

Step 3 : Install Enterprise manager
Official installation doc: http://docs.oracle.com/cd/E11857_01/install.111/e15838/toc.htm

* stop the dbconsole created via dbca

SQL> drop user sysman cascade;
SQL> drop role MGMT_USER;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;

* Manually remove the following folders:
ORACLE_HOME/host_sid/ ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_host_sid

For example if ORACLE_HOME is /u01/app/oracle and hostname is test.oracle.com and sid is db1, then the above mentioned folders would be:
/u01/app/oracle/oracle/test.oracle.com_db1 /u01/app/oracle/oc4j/j2ee/OC4J_DBConsole_test.oracle.com_db1


* set some Oracle parameters:

SQL> alter system set processes = 500 scope = spfile;

System altered.

SQL>  alter system set  session_cached_cursors = 200 scope = spfile;

System altered.

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3423965184 bytes
Fixed Size      2218152 bytes
Variable Size   1879050072 bytes
Database Buffers  1526726656 bytes
Redo Buffers     15970304 bytes
Database mounted.
Database opened.

* add a datafile for the undo tablespace to be more than 200m:

alter tablespace UNDOTBS1 add datafile '/u01/app/oracle/oradata/GRID/undotbs02.dbf' size 200m autoextend on next 1m maxsize 500m;

* the install will fail trying to configure the repository. You still need to do it.
After that, you will have to clean the mess and start again, then it will work.

*Drop AQ related objects in the SYSMAN schema

SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'SYSMAN.MGMT_NOTIFY_QTABLE',force =>TRUE);

* Drop the DB Control Repository Objects
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> EXEC sysman.setEMUserContext('',5);
SQL> REVOKE dba FROM sysman;
SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/
SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Now, click on retry.
The install and configuration process will keep going, you can go for a coffee.

Your grid control is now operational, have fun

Restore datafile with RMAN

Process to restore datafile with rman

Situation
Typical ORACLE error

ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/BDDPROD/PROD/users01.dbf'

Check what happened to the datafile
Example:

ls -l /BDDPROD/PROD/users01.dbf

HOW TO
Switch the online logfiles to archive logs

rman target / catalog rman/rman@rman_cat
sql 'alter system switch logfile';

Restore the missing datafile

If you get an error at the end of the recovery process :

restore datafile 4;

You need to put all the others datafiles of the tablespace offline This command will show which datafiles need to become offline :

recover tablespace users;

After that, proceed to set offline datafiles :

sql 'alter datafile 7 offline';

Next you can recover your missing datafile and set your tablespace online :

recover datafile 4;
sql 'alter tablespace users online';

Migrate from MySQL to PostgreSQL

Enterprise DB provide a tool (Migration Toolkit) that allow you to migrates database from MySQL to PostgreSQL


Before running this script, you must have a config file filled with information regarding the source and destination DB:

/opt/PostgresPlus/9.0AS/etc/toolkit.properties

SRC_DB_URL=jdbc:mysql://orig-server.com:3306/orig_db
SRC_DB_USER=root
SRC_DB_PASSWORD=*****

TARGET_DB_URL=jdbc:postgresql://dest-server.com:5432/dest_db
TARGET_DB_USER=postgres
TARGET_DB_PASSWORD=********

Then, in the binary folder from your enterpriseDB install (/opt/PostgresPlus/9.0AS/bin), run the runMTK.sh script with the following parameter:

[root@test bin]# ./runMTK.sh -sourcedbtype mysql -targetdbtype postgres orig_db

Some information will be displayed :

Source database connectivity info...
conn =jdbc:mysql://orig-server.com:3306/orig_db
user =root
password=******
Target database connectivity info...
conn =jdbc:postgresql://dest-server.com:5432/dest_db
user =postgres
password=******
Connecting with source MySQL database server...
Connecting with target Postgres database server...
Importing mysql schema orig_db...
Creating Schema...orig_db
Creating Tables...
Creating Table: table1
Creating Table: table2
Creating Table: table3
Created 3 tables.
Loading Table Data in 8 MB batches...
Loading Table: table1 ...
[table1] Migrated 12 rows.
[table1] Table Data Load Summary: Total Time(s): 0.115 Total Rows: 12 

Loading Table: table2 ...
[table2] Migrated 4 rows.
[table2] Table Data Load Summary: Total Time(s): 0.075 Total Rows: 4 

Loading Table: table3 ...
[table3] Migrated 19 rows.
[table3] Table Data Load Summary: Total Time(s): 0.049 Total Rows: 19 

Total Size(MB): 9.765625E-4 

Data Load Summary: Total Time (sec): 0.414 Total Rows: 35 Total Size(MB): 0.0010 Creating Constraint: PRIMARY Creating Constraint: PRIMARY Creating Constraint: PRIMARY

Schema orig_db imported successfully.


Migration process completed successfully.

Migration logs have been saved to /root/.enterprisedb/migration-toolkit/logs

******************** Migration Summary ********************
Tables: 3 out of 3
Constraints: 3 out of 3

Total objects: 6
Successful count: 6
Failure count: 0

*************************************************************

DBMS_CRYPTO

synopsis:

select sys.DBMS_CRYPTO.HASH( utl_raw.cast_to_raw(v_string), v_type) from dual;

example:

select sys.DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW ('tata toto titi','AL32UTF8'), 3) from dual;
select sys.DBMS_CRYPTO.HASH( utl_raw.cast_to_raw('tata toto titi'), 3) from dual;

variables:
- v_type can be 1 (MD4), 2 (MD5), 3 (SH1)
- v_string has to be converted using utl_raw.cast_to_raw or UTL_I18N.STRING_TO_RAW.

references:
http://psoug.org/reference/dbms_crypto.html 

Encrypt in AES 128: The code bellow will encrypt tha string "tata titi toto":

DECLARE
enc_val   RAW(2000);
l_key     RAW(2000);
l_key_len NUMBER := 128/8; -- convert bits to bytes
l_mod     NUMBER := dbms_crypto.ENCRYPT_AES128
+ dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_ZERO;

BEGIN
  l_key := dbms_crypto.randombytes(l_key_len);

  enc_val := dbms_crypto.encrypt(
  utl_i18n.string_to_raw('tata titi toto', 'AL32UTF8'),
  l_mod, l_key);

  dbms_output.put_line(enc_val);
END;
/

Or just with a select:

select dbms_crypto.encrypt(
         utl_i18n.string_to_raw('tata titi toto', 'AL32UTF8'),
         6 + 256 + 4096,
         dbms_crypto.randombytes(128/8)
       )
from dual

The 6 + 256 + 4096 represents dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_ZERO

PHP with oci8 support on centos

Hi,

many people have trouble install and configuring php with oci8 support... below is a quick, easy and working way of doing it.Php and apache must be installed already.

All actions has to be done as root user.

First, install rpm for instantclient basic and SDK (previously downloaded from oracle website):

[root@test ~]# rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-instantclient11.########################################### [100%]
[root@test ~]# rpm -ivh oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-instantclient11.########################################### [100%]

next, just run pecl to install oci8 and configure php with it support:

[root@test ~]# pecl install oci8

It will ask you for the librairy folder:

Please provide the path to the ORACLE_HOME directory. Use 'instantclient,/path/to/instant/client/lib' if you're compiling with Oracle Instant Client [autodetect] : 

on a 64bits system, it should be 

instantclient,/usr/lib/oracle/11.2/client64/lib

on a 32bits system, it should be 

instantclient,/usr/lib/oracle/11.2/client/lib

if you have the following error, verify that you have the sdk installed:

checking Oracle Instant Client SDK header directory... configure: error: Oracle Instant Client SDK header files not found
ERROR: `/var/tmp/oci8/configure --with-oci8=instantclient,/usr/lib/oracle/11.2/client' failed

Then, locate your php.ini file 

[root@test ~]# find / -type f -name php.ini
/etc/php.ini

And add this line:

extension=oci8.so

The last operation is the apache service to restart.

check its status:

[root@test ~]# service httpd status
httpd (pid  3173) is running...

stop it

[root@test ~]# service httpd stop
Stopping httpd:                                            [  OK  ]

restart it:

[root@test ~]# service httpd start
Starting httpd:                                            [  OK  ]

You're done, time for you to enjoy a coffee :-)

Flashback archive (Oracle 11g)

In Oracle 11g, you have the possibility to use flashback archive. This feature allow you to keep track of data change on specific table (used by your application for example).

Here, we will use this feature to keep track of historical data on transaction on specific table.

Fisrt, we need to create a tablespace (TBS_FBARC) that will store these data:

CREATE TABLESPACE TBS_FBARC DATAFILE 
  '/u01/datafile/tbs_fbarc_01.dbf' SIZE 10m AUTOEXTEND ON NEXT 8K MAXSIZE 5120M,
  '/u01/datafile/tbs_fbarc_02.dbf' SIZE 10M AUTOEXTEND ON NEXT 8K MAXSIZE 5120M
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Then, we create the flashback archive, specify what tablespace to use, the retention time to keep historical data and grant rights on the application user (APPUSR) to use it.

CREATE FLASHBACK ARCHIVE DEFAULT FBARC
TABLESPACE TBS_FBARC
RETENTION 3 DAY;

alter flashback archive FBARC set default;
grant flashback archive on FBARC to APPUSR;

We can now add tables:

alter table APPUSR.TABLE1 flashback archive FBARC;
alter table APPUSR.TABLE2 flashback archive FBARC;

If you want (or need) to remove this feature for a table:

alter table APPUSR.TABLE2 NO flashback archive;

To check what table(s) are in the flashback archive, you need to query the table DBA_FLASHBACK_ARCHIVE_TABLES.

Attention, the UNDO tablespace has to be monitored closely, if the flashback archive tablespace is too small or the retention too big, it will be filled really quickly!!

You can monitor the space used by each tables using flashback archive with this query:

 SELECT T.Owner_name,
         T.Table_name,
         S.Tablespace_name,
         SUM (S.Bytes)/1024/1024 Total_space
    FROM Sys.Dba_flashback_archive_tables T, Sys.Dba_segments S
   WHERE     T.Owner_name = S.Owner
         AND T.Flashback_archive_name = 'FBARC'
         AND S.Segment_name LIKE 'SYS_FBA%' || SUBSTR (Archive_table_name,
                           INSTR (Archive_table_name, '_', -1, 1) + 1,
                           LENGTH (Archive_table_name))
GROUP BY T.Owner_name, T.Table_name, S.Tablespace_name
ORDER BY 4 desc;

Supplemental documentation can be found here:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm

I will resume this at a later stage and add the most important information on this post.

TNS-12535: TNS:operation timed out

Specific to Oracle 11G

If you see the errors in the alert logfile :

*********************************************************************** 

Fatal NI connect error 12170. 

  VERSION INFORMATION: 
        TNS for Linux: Version 11.2.0.2.0 - Production 
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production 
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production 
  Time: 11-DEC-2011 15:08:18 
  Tracing not turned on. 
  Tns error struct: 
    ns main err code: 12535 

TNS-12535: TNS:operation timed out 
    ns secondary err code: 12560 
    nt main err code: 505 

TNS-00505: Operation timed out 
    nt secondary err code: 110 
    nt OS err code: 0

It may be due to the usage of Oracle Grid Control. To fix the issue you have to add the 2 lines below in your sqlnet.ora

SQLNET.ENCRYPTION_TYPES_SERVER=(3DES168)
SQLNET.ENCRYPTION_SERVER=REQUIRED

SHUTDOWN: waiting for active calls to complete

1. connect to SYS user with new terminal window

SQL> conn / sysdba
Connected TO idle instance.

2. shutdown database with ABORT option

SQL> shutdown abort;
ORACLE instance shut down.

3. open database for normal shutdown with RESTRICT option

SQL> startup restrict
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 788496 bytes
Variable Size 149682160 bytes
DATABASE Buffers 16777216 bytes
Redo Buffers 524288 bytes
DATABASE mounted.
DATABASE opened. 

4. now shutdown database with NORMAL option

SQL> shutdown normal;
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.

5. You can now restart your instance properly.

YUM / RPM cheat

As root (or sudo):

YUM

To update your system:

yum update

To install a package:

yum install PACKAGE

To search a package:

yum search PACKAGE

To have more information for a specific package (version, architecture, etc):

yum info PACKAGE

To clean a "fucked up" yum conf:

yum clean all

RPM

What package "hold" a command

rpm -qf CMD

List packages

rpm -qa

Details of the package

rpm -qi PACKAGE

Erase a package. Give dependencies that block PACKAGE to be removed too)

rpm -e PACKAGE

Details of what provides a package

rpm -q --provides PACKAGE

Upgrade or install a package

rpm -Uvh PACKAGE

Upgrade a package (if already installed)

rpm -F PACKAGE

ORA-00059: maximum number of DB_FILES exceeded

If you have this error in your alertSID.log:

ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

You have to increase the number of file allowed for the database. The instance has to be restarted:

SQL> SHOW parameter db_files
--> 1500

SQL> ALTER system SET db_files = 3000 scope = BOTH;
SQL> shutdown immediate
SQL> startup
SQL> SHOW parameter db_files
--> 3000

Configure Oracle Enterprise Linux for an Oracle 11g install

Step by step Oracle database 11g install (Part 1)

Install Centos 6 (or any RHEL 6)

(for Unbreakable Enterprise Linux User)
as root:
If you don't have a ULN subscription you may prefer to remove up2date and access repos manually via yum instead:

yum remove up2date

add the repo in yum:

cd /etc/yum.repo.d 

wget http://public-yum.oracle.com/public-yum-ol6.repo

in /etc/yum.repos.d/public-yum-ol6.repo, the following must be with enabled=1 (the other at 0):
[ol6_latest]
[ol6_addons]
[ol6_UEK_latest]

Search for the latest Oracle software:

yum search oracle

Auto-configure your new system (users, group, pre-require packages, etc)

yum install oracle-rdbms-server-11gR2-preinstall.i686

if you have this error:

[Errno 14] Could not open/read file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

It's because the Oracle gpg key for their repo hasn't been installed yet.

Do it and restart the install process

wget https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

or

wget --no-check-certificate https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

Reboot, your system is ready to have Oracle 11g installed. Di it here :)