It’s easy to find average number but how to use avg() function with time information?

create table test_table ( my_time date );

insert into test_table values ( to_date(‘05.10.2011 08:00:00′,’dd.mm.yyyy hh24:mi:ss’) );
insert into test_table values ( to_date(‘05.10.2011 12:00:00′,’dd.mm.yyyy hh24:mi:ss’) );
insert into test_table values ( to_date(‘05.10.2011 16:00:00′,’dd.mm.yyyy hh24:mi:ss’) );

 

SELECT TO_CHAR( TO_DATE( TO_CHAR( AVG( TO_NUMBER( TO_CHAR( MY_TIME, ‘SSSSS’ ) ) ),
                                ‘9999999’
                                ),
                        ‘SSSSS’
                        )
              ,’HH24:MI:SS’) AVG_TIME
FROM TEST_TABLE;

 

The result of above sql:

AVG_TIME
——–
12:00:00
1 row selected.

 

Hi again. After a long long long time I have just come back to blogosfer. One year ago I started a datawarehouse project and have to spare my all time on it.

After today, I want to write regularly. Especially I plan to share subjects that I learned in that dw project. I learned and used the SAP Business Warehouse as datawarehouse and ETL tool.

Further I am planning to compare Oracle ETL tools and SAP Business Warehouse.

See you soon.

If you set the initialization parameter log_checkpoints_to_alert to TRUE, Oracle will write the SCN of each checkpoint and a log switch to alert.log file. With this method you can save history of checkpoint SCNs.

SQL> alter system set log_checkpoints_to_alert=true scope=both;

System altered.

After “alter system checkpoint”, contents of the alert.log file:

Fri Nov 19 15:47:32 2010
Beginning global checkpoint up to RBA [0x3.b3.10], SCN: 1410260
Completed checkpoint up to RBA [0x3.b3.10], SCN: 1410260

After “alter system switch logfile”, contents of the alert.log file:

Fri Nov 19 15:52:50 2010
Beginning log switch checkpoint up to RBA [0x4.2.10], SCN: 1410411

To simulate a media recovery, at first I have taken an RMAN backup of the database. And later I have done several DML operations on test tables. To generate archived logs I run “alter system switch logfile” command as well. After these, my system was ready for simulation.

First shutdown database cleanly and remove one of the datafiles with operating system command.

(more…)

If you duplicate a database you should change the DBID of it. And after changing you should take a full backup because of earlier backups are not usable.

You can use the DBNEWID utility, using the nid command at the command line prompt.

1. First look at the current value of the DBID.

SQL> select dbid, name from v$database;

      DBID NAME
———- ———
1240564264 DB1

SQL>

2. Shut down the database cleanly and restarted in mount mode.

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

Total System Global Area  314572800 bytes
Fixed Size                  1261540 bytes
Variable Size             117440540 bytes
Database Buffers          192937984 bytes
Redo Buffers                2932736 bytes
Database mounted.
SQL>

 

3. Run the nid command at the command line prompt.

[oracle@localhost ~]$ nid target=sys/syspassword@db1

DBNEWID: Release 10.2.0.3.0 – Production on Sun Nov 14 15:18:49 2010

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

Connected to database DB1 (DBID=1240564264)

Connected to server version 10.2.0

Control Files in database:
    /u02/oracle/oradata/DB1/control01.ctl
    /u02/oracle/oradata/DB1/control02.ctl
    /u02/oracle/oradata/DB1/control03.ctl

Change database ID of database DB1? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1240564264 to 1328930970
    Control File /u02/oracle/oradata/DB1/control01.ctl – modified
    Control File /u02/oracle/oradata/DB1/control02.ctl – modified
    Control File /u02/oracle/oradata/DB1/control03.ctl – modified
    Datafile /u02/oracle/oradata/DB1/system01.dbf – dbid changed
    Datafile /u02/oracle/oradata/DB1/undotbs01.dbf – dbid changed
    Datafile /u02/oracle/oradata/DB1/sysaux01.dbf – dbid changed
    Datafile /u02/oracle/oradata/DB1/users01.dbf – dbid changed
    Datafile /u02/oracle/oradata/DB1/example01.dbf – dbid changed
    Datafile /u02/oracle/oradata/DB1/temp01.dbf – dbid changed
    Control File /u02/oracle/oradata/DB1/control01.ctl – dbid changed
    Control File /u02/oracle/oradata/DB1/control02.ctl – dbid changed
    Control File /u02/oracle/oradata/DB1/control03.ctl – dbid changed
    Instance shut down

Database ID for database DB1 changed to 1328930970.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID – Completed succesfully.

[oracle@localhost ~]$

4. Finally, open the database with RESETLOGS.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1261540 bytes
Variable Size             130023452 bytes
Database Buffers          180355072 bytes
Redo Buffers                2932736 bytes
Database mounted.
SQL>              
SQL> alter database open resetlogs;

Database altered.

SQL>

 

And don’t forget backup your database.

You need to know DBID of your database. How to know it? Answer is simple and easy: I query the DBID column of the V$DATABASE view. So, if all control files is lost and instance don’t mount, what do you do?

Here are the methods:

1. Connect RMAN (Please read comments about this)

RMAN> connect target sys/password@mynewdb
connected to target database: MYNEWDB (DBID=1259749044)

2. Write to a dump file. To create a dump file, you can use any of the datafiles, online redo logs or archived redo logs.

[oracle@oel ~]$ sqlplus / as sysdba
SQL> startup nomount;
SQL> alter system dump datafile ‘/u02/oradata/orcl/testtbs01.dbf’ block min 1 block max 10;

SQL> alter system dump logfile ‘/u02/oradata/orcl/redo01.log';

The trace file can be found user_dump_dest and you should search for “Db ID” expression in it.

[oracle@oel trace]$  find . -name "DB11G_ora_5037.trc" | xargs  grep -i "Db ID"

Db ID=1259749044=0x4b163eb4, Db Name=’ORCL’

When connect to RMAN session using command line, anyone can see the database password with ps –ef command.

First method:

[oracle@oel ~]$ rman target sys/password@mynewdb
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Aug 13 00:40:02 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: MYNEWDB (DBID=175983730)
RMAN>

 

[root@oel ~]# ps -ef | grep rman | grep -v grep
oracle    5069  4744  5 00:40 pts/1    00:00:00 rman target
sys/password@mynewdb
[root@oel ~]#

 

Second method:

[oracle@oel ~]$ rman
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Aug 13 00:41:23 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

RMAN> connect target sys/password@mynewdb
connected to target database: MYNEWDB (DBID=175983730)
RMAN>

 

[root@oel ~]# ps -ef | grep rman | grep -v grep
oracle    5123  4926  1 00:41 pts/4    00:00:00 rman
[root@oel ~]#

 

The second method should be used on multi-user environment.

Follow

Get every new post delivered to your Inbox.