Oracle DBA Queries: 2009

mercredi 23 décembre 2009

Oracle 10g - Manually Create a Physical Standby Database Using Data Guard

Step-by-step instructions on how to create a Physical Standby Database on Windows and UNIX servers, and maintenance tips on the databases in a Data Guard Environment.

Oracle 10g Data Guard is a great tool to ensure high availability, data protection and disaster recovery for enterprise data. I have been working on Data Guard/Standby databases using both Grid control and SQL command line for a couple of years, and my latest experience with Data Guard was manually creating a Physical Standby Database for a Laboratory Information Management System (LIMS) half a year ago. I maintain it daily and it works well. I would like to share my experience with the other DBAs.

In this example the database version is 10.2.0.3.. The Primary database and Standby database are located on different machines at different sites. The Primary database is called PRIM and the Standby database is called STAN. I use Flash Recovery Area, and OMF.

I. Before you get started:

1. Make sure the operating system and platform architecture on the primary and standby systems are the same;

2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.

3. Test the Standby Database creation on a test environment first before working on the Production database.

II. On the Primary Database Side:

1. Enable forced logging on your primary database:
SQL> ALTER DATABASE FORCE LOGGING;

2. Create a password file if it doesn’t exist.
1) To check if a password file already exists, run the following command:
SQL> select * from v$pwfile_users;

2) If it doesn’t exist, use the following command to create one:
- On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)

- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)

3. Configure a Standby Redo log.
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;

BYTES
----------
52428800
52428800
52428800

2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;

3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;

4. Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;

5. Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.

1) Create pfile from spfile for the primary database:
- On Windows:
SQL>create pfile=’\database\pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).

- On UNIX:
SQL>create pfile=’/dbs/pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).

2) Edit pfilePRIM.ora to add the new primary and standby role parameters: (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)

db_name=PRIM
db_unique_name=PRIM
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
LOG_ARCHIVE_DEST_1=
'LOCATION=F:\Oracle\flash_recovery_area\PRIM\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_2=
'SERVICE=STAN LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STAN'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=STAN
FAL_CLIENT=PRIM
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT='E:\oracle\product\10.2.0\oradata\STAN\DATAFILE','E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’

6. Create spfile from pfile, and restart primary database using the new spfile.
Data Guard must use SPFILE. Create the SPFILE and restart database.
- On windows:
SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).

- On UNIX:
SQL> shutdown immediate;
SQL> startup nomount pfile=’/dbs/pfilePRIM.ora’;
SQL>create spfile from pfile=’/dbs/pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).

III. On the Standby Database Site:

1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:
SQL>shutdown immediate;

On Standby Server (While the Primary database is shut down):
1) Create directory for data files, for example, on windows, E:\oracle\product\10.2.0\oradata\STAN\DATAFILE.
On UNIX, create the directory accordingly.

2) Copy the data files and temp files over.

3) Create directory (multiplexing) for online logs, for example, on Windows, E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG and F:\Oracle\flash_recovery_area\STAN\ONLINELOG.
On UNIX, create the directories accordingly.

4) Copy the online logs over.

2. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘STAN.ctl;
SQL>ALTER DATABASE OPEN;

3. Copy the Primary DB pfile to Standby server and rename/edit the file.

1) Copy pfilePRIM.ora from Primary server to Standby server, to database folder on Windows or dbs folder on UNIX under the Oracle home path.

2) Rename it to pfileSTAN.ora, and modify the file as follows. : (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)

*.audit_file_dest='E:\oracle\product\10.2.0\admin\STAN\adump'
*.background_dump_dest='E:\oracle\product\10.2.0\admin\STAN\bdump'
*.core_dump_dest='E:\oracle\product\10.2.0\admin\STAN\cdump'
*.user_dump_dest='E:\oracle\product\10.2.0\admin\STAN\udump'
*.compatible='10.2.0.3.0'
control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\STAN\CONTROLFILE\STAN.CTL','F:\ORACLE\FLASH_RECOVERY_AREA\STAN\CONTROLFILE\STAN.CTL'
db_name='PRIM'
db_unique_name=STAN
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM,STAN)’
LOG_ARCHIVE_DEST_1=
‘LOCATION=F:\Oracle\flash_recovery_area\STAN\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STAN’
LOG_ARCHIVE_DEST_2=
‘SERVICE=PRIM LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIM’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRIM
FAL_CLIENT=STAN
remote_login_passwordfile='EXCLUSIVE'
# Specify the location of the primary DB datafiles followed by the standby location
DB_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE’,’E:\oracle\product\10.2.0\oradata\STAN\DATAFILE’
# Specify the location of the primary DB online redo log files followed by the standby location
LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’
STANDBY_FILE_MANAGEMENT=AUTO

(Note: Not all the parameter entries are listed here.)

4. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.

5. Copy the standby control file ‘STAN.ctl’ from primary to standby destinations ;

6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.
On Windows copy it to \database folder, and on UNIX copy it to /dbs directory. And then rename the password file.

7. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID STAN –STARTMODE manual

8. Configure listeners for the primary and standby databases.

1) On Primary system: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start

2) On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start

9. Create Oracle Net service names.
1) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN

2) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN

10. On Standby server, setup the environment variables to point to the Standby database.

Set up ORACLE_HOME and ORACLE_SID.

11. Start up nomount the standby database and generate a spfile.
- On Windows:
SQL>startup nomount pfile=’\database\pfileSTAN.ora’;
SQL>create spfile from pfile=’\database\pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;

- On UNIX:
SQL>startup nomount pfile=’/dbs/pfileSTAN.ora’;
SQL>create spfile from pfile=’/dbs/pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
(Note- specify your Oracle home path to replace ‘’).

12. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;

If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;

13. Verify the standby database is performing properly:
1) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;

2) On Primary, force a logfile switch:
SQL>alter system switch logfile;

3) On Standby, verify the archived redo log files were applied:
SQL>select sequence#, applied from v$archived_log order by sequence#;

14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.

To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;

15. To create multiple standby databases, repeat this procedure.

IV. Maintenance:

1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.

2. Cleanup the archive logs on Primary and Standby servers.

I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.

For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target /@STAN;
RMAN>backup archivelog all delete input;

To delete the archivelog backup files on the standby server, I run the following once a month:
RMAN>delete backupset;

3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server.

Refer to section II.2, step 2 to update/recreate password file for the Standby database.

Reference:
Oracle Data Guard Concepts and Administration 10g Release 2 (10.2), B14239-04

Author :Haili Jiao (orafaq)

mercredi 2 décembre 2009

How do I find used/free space in a TEMPORARY tablespace?

Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view:

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM   V$temp_space_header
GROUP  BY tablespace_name;

To report true free space within the used portion of the TEMPFILE:

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

samedi 21 novembre 2009

How do I Use the Linux Top Command?

The Unix top command is designed to help users determine which processes are running and which applications are using more memory or processing power than they should be.

The top command is very easy to use but you should know the things in details. The output of to is :

top output:

top - 22:09:08 up 14 min, 1 user, load average: 0.21, 0.23, 0.30
Tasks: 81 total, 1 running, 80 sleeping, 0 stopped, 0 zombie
Cpu(s): 9.5%us, 31.2%sy, 0.0%ni, 27.0%id, 7.6%wa, 1.0%hi, 23.7%si, 0.0%st
Mem: 255592k total, 167568k used, 88024k free, 25068k buffers
Swap: 524280k total, 0k used, 524280k free, 85724k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3166 apache 15 0 29444 6112 1524 S 6.6 2.4 0:00.79 httpd
3161 apache 15 0 29444 6112 1524 S 5.9 2.4 0:00.79 httpd
3164 apache 15 0 29444 6112 1524 S 5.9 2.4 0:00.75 httpd
3169 apache 15 0 29444 6112 1524 S 5.9 2.4 0:00.74 httpd
3163 apache 15 0 29444 6112 1524 S 5.6 2.4 0:00.76 httpd
3165 apache 15 0 29444 6112 1524 S 5.6 2.4 0:00.77 httpd
3167 apache 15 0 29444 6112 1524 S 5.3 2.4 0:00.73 httpd
3162 apache 15 0 29444 6112 1524 S 5.0 2.4 0:00.77 httpd
3407 root 16 0 2188 1012 816 R 1.7 0.4 0:00.51 top
240 root 15 0 0 0 0 S 0.3 0.0 0:00.08 pdflush
501 root 10 -5 0 0 0 S 0.3 0.0 0:01.20 kjournald
2794 root 18 0 12720 1268 560 S 0.3 0.5 0:00.73 pcscd
1 root 15 0 2060 636 544 S 0.0 0.2 0:03.81 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root 10 -5 0 0 0 S 0.0 0.0 0:00.07 events/0

The first line in top:

top - 22:09:08 up 14 min, 1 user, load average: 0.21, 0.23, 0.30

“22:09:08″ is the current time; “up 14 min” shows how long the system has been up for; “1 user” how many users are logged in; “load average: 0.21, 0.23, 0.30″ the load average of the system (1minute, 5 minutes, 15 minutes).

Load average is an extensive topic and to understand its inner workings can be daunting. The simplest of definitions states that load average is the cpu utilization over a period of time. A load average of 1 means your cpu is being fully utilized and processes are not having to wait to use a CPU. A load average above 1 indicates that processes need to wait and your system will be less responsive. If your load average is consistently above 3 and your system is running slow you may want to upgrade to more CPU’s or a faster CPU.

The second line in top:

Tasks: 82 total, 1 running, 81 sleeping, 0 stopped, 0 zombie

Shows the number of processes and their current state.

The third lin in top:

Cpu(s): 9.5%us, 31.2%sy, 0.0%ni, 27.0%id, 7.6%wa, 1.0%hi, 23.7%si, 0.0%st

Shows CPU utilization details. “9.5%us” user processes are using 9.5%; “31.2%sy” system processes are using 31.2%; “27.0%id” percentage of available cpu; “7.6%wa” time CPU is waiting for IO.

When first analyzing the Cpu(s) line in top look at the %id to see how much cpu is available. If %id is low then focus on %us, %sy, and %wa to determine what is using the CPU.

The fourth and fifth lines in top:

Mem: 255592k total, 167568k used, 88024k free, 25068k buffers
Swap: 524280k total, 0k used, 524280k free, 85724k cached

Describes the memory usage. These numbers can be misleading. “255592k total” is total memory in the system; “167568K used” is the part of the RAM that currently contains information; “88024k free” is the part of RAM that contains no information; “25068K buffers and 85724k cached” is the buffered and cached data for IO.

So what is the actual amount of free RAM available for programs to use ?

The answer is: free + (buffers + cached)

88024k + (25068k + 85724k) = 198816k

How much RAM is being used by progams ?

The answer is: used - (buffers + cached)

167568k - (25068k + 85724k) = 56776k

The processes information:

Top will display the process using the most CPU usage in descending order. Lets describe each column that represents a process.

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3166 apache 15 0 29444 6112 1524 S 6.6 2.4 0:00.79 httpd

PID - process ID of the process

USER - User who is running the process

PR - The priority of the process

NI - Nice value of the process (higher value indicates lower priority)

VIRT - The total amount of virtual memory used

RES - Resident task size

SHR - Amount of shared memory used

S - State of the task. Values are S (sleeping), D (uninterruptible sleep), R (running), Z (zombies), or T (stopped or traced)

%CPU - Percentage of CPU used

%MEM - Percentage of Memory used

TIME+ - Total CPU time used

COMMAND - Command issued
Interacting with TOP

Now that we are able to understand the output from TOP lets learn how to change the way the output is displayed.

Just press the following key while running top and the output will be sorted in real time.

M - Sort by memory usage

P - Sort by CPU usage

T - Sort by cumulative time

z - Color display

k - Kill a process

q - quit

If we want to kill the process with PID 3161, then press “k” and a prompt will ask you for the PID number, and enter 3161.
Command Line Parameters with TOP

You can control what top displays by issuing parameters when you run top.

- d - Controls the delay between refreshes

- p - Specify the process by PID that you want to monitor

-n - Update the display this number of times and then exit

If we want to only monitor the http process with a PID of 3166

$ top -p 3166

If we want to change the delay between refreshes to 5 seconds

$ top -d 5

jeudi 12 novembre 2009

oracle 10g RAC administration, two official books

10g RAC for Administrators
hxxp://rapidshare.com/files/61294558/10gRACforAdministratorsR2_Vol1_pwd_-_racr2.pdf
hxxp://rapidshare.com/files/61295444/10gRACforAdministratorsR2_Vol2_pwd_-_racr2.pdf
Password: racr2

Oracle Database Administration Fundamentals I & II VTC Training CD

This is the second in a series of courses, aimed at preparing the user for Oracle Certification Exam # 1Z0-031. This course will teach Oracle database administration fundamentals; installing Oracle, creating databases, managing objects in the database and more. This course expands on the first course offered by VTC (Introduction to Oracle SQL and PL/SQL). Following courses will include: Oracle Database Administration Fundamentals II and Oracle Database Performance Tuning. To get started, click one of the movie topics below.

http://rapidshare.com/files/46014180/VTC.C...ntals.part1.rar
http://rapidshare.com/files/46014024/VTC.C...ntals.part2.rar
http://rapidshare.com/files/46014101/VTC.C...ntals.part3.rar
http://rapidshare.com/files/46014050/VTC.C...ntals.part4.rar

Fundamental II

http://rapidshare.com/files/46664910/___OS..._NADU.part1.rar
http://rapidshare.com/files/46664896/___OS..._NADU.part2.rar
http://rapidshare.com/files/46664904/___OS..._NADU.part3.rar
http://rapidshare.com/files/46664898/___OS..._NADU.part4.rar
http://rapidshare.com/files/46664900/___OS..._NADU.part5.rar

Oracle Training Certification - Complete Collection

1Z0-001 Introduction to Oracle: SQL and PL/SQL 130 Questions 2/24/2009
1Z0-007 Introduction to Oracle9i: SQL 203 Questions 2/10/2009
1Z0-020 Oracle8i: New Features for Administrators 83 Questions 10/31/2008
1Z0-023 Architecture and Administration 151 Questions 3/18/2009
1Z0-024 Performance Tuning 92 Questions 3/18/2009
1Z0-025 Backup and Recovery 127 Questions 3/18/2009
1Z0-026 Network Administration 126 Questions 3/19/2009
1Z0-030 Oracle9i: New Features for Administrators 134 Questions 10/30/2008
1Z0-031 Oracle9i: Database Fundamentals I 389 Questions 10/31/2008
1Z0-032 Oracle9i: Database Fundamentals II 348 Questions 10/31/2008
1Z0-033 Oracle9i: Performance Tunning 510 Questions 11/4/2008
1Z0-035 Oracle 7.3 & 8 to Oracle9i DBA OCP Upgrade 905 Questions 11/4/2008
1Z0-036 Managing Oracle 9i on Linux 144 Questions 11/3/2008
1Z0-040 Oracle Database 10g: New Features for Administrators 200 Questions 11/3/2008
1Z0-041 Oracle Database 10g: DBA Assessment 65 Questions 3/19/2009
1Z0-042 Oracle Database 10g: Administration I 286 Questions 2/10/2009
1Z0-043 Oracle Database 10g: Administration II 185 Questions 11/3/2008
1Z0-045 Oracle Database 10g: New Features for Oracle8i OCPs 200 Questions 11/4/2008
1Z0-048 Oracle Database 10g R2: Administering RAC 130 Questions 4/8/2009
1Z0-050 Oracle Database 11g: New Features for Administrators 183 Questions 1/29/2009
1Z0-101 Develop PL/SQL Program Units 92 Questions 11/7/2008
1Z0-131 Oracle9i, Build Internet Applications I 153 Questions 11/10/2008
1Z0-132 Oracle9i, Build Internet Applications II 157 Questions 11/7/2008
1Z0-140 Oracle9i Forms Developer: New Features 120 Questions 11/21/2008
1Z0-141 Oracle9i Forms Developer: Build Internet Applications 186 Questions 11/10/2008
1Z0-147 Oracle 9i: Program with PL/SQL 111 Questions 11/10/2008
1Z0-200 Oracle 11i E-Business Essentials 64 Questions 11/7/2008
1Z0-211 Oracle 11i General Ledger 152 Questions 11/10/2008
1Z0-212 Oracle Payables 11i Fudamentals 70 Questions 1/27/2009
1Z0-213 Oracle Receivables 11i Fundamentals 75 Questions 11/7/2008
1Z0-221 Oracle 11i Inventory Management Fundamentals 150 Questions 1/29/2009
1Z0-222 Oracle Purchasing 11i Fundamentals 85 Questions 11/7/2008
1Z0-223 Oracle Order Management 11i Fundamentals 81 Questions 11/7/2008
1Z0-231 Oracle 11i/2.6 Implementing Workflow 76 Questions 11/6/2008
1Z0-232 Oracle 11i System Administration 164 Questions 11/6/2008
1Z0-233 11i Install Patch and Maintain Oracle Applications 130 Questions 11/6/2008
1Z0-301 Oracle9iAS: Basic Administrations 90 Questions 11/6/2008
1Z0-311 Oracle Application Server 10g: Administration I 152 Questions 11/6/2008

http://rapidshare.com/files/236264299/Oracle_Training_Certification_Complete_Collection.rar

Oracle certification free prep materials

1Z0-042 Oracle Database 10g: Administration I
1Z0-043 Oracle Database 10g: Administration II
1Z0-052 Oracle Database 11g: Administration I
1Z0-255 Hyperion Essbase 7.1.2 Cnsultant
1Z0-108 Oracle WebLogic Server 10g System Administration

ITS Oracle Education Official Material

It covers only 10g and 11g technologies and they are mainly powerpoint slides and e-books on PL/SQL, development and database administration :

D17171GC20 - 10g Develop Applications Using HTML Db 2nd Edition
http://rapidshare.com/files/102856123/D17171GC20_-_10g_Develop_Applications_Using_HTML_Db_Ed_2.rar

D50325GC11 - 11g Change Management Overview Seminar
http://rapidshare.com/files/102854889/D50325GC11_ppt.rar

D47045GC10 - 10g QuickStart for DBAs
http://rapidshare.com/files/102853797/D47045GC10_ppt.rar

D46590GC11 - 10g Managing Oracle on Linux for DBAs
http://rapidshare.com/files/102852659/D46590GC11_ppt.rar

D44810GC10 - 10g Warehouse Builder - Administrations
http://rapidshare.com/files/101720578/D44810GC10_ppt.rar

D22057GC10 - 10g Backup and Recovery
http://rapidshare.com/files/101719973/D22057GC10_ppt.rar

D18957GC10 - 10g Implement and Administer a Data Warehouse
http://rapidshare.com/files/101719479/D18957GC10_ppt.rar

D18422GC10 - 10g 2-Day DBA for Windows
http://rapidshare.com/files/101717770/D18422GC10_ppt.rar

D17333GC20 - 10g Implement Streams
http://rapidshare.com/files/101717123/D17333GC20_ppt.rar

D17316GC20 - 10g Dataguard Administration
http://rapidshare.com/files/101716105/D17316GC20_ppt.rar

D17276GC20 - 10g RAC for Administrators
http://rapidshare.com/files/101715902/D17276GC20_ppt.rar

D17265GC20 - 10g SQL Tuning
http://rapidshare.com/files/101714827/D17265GC20_ppt.rar

D17169GC21 - 10g Develop PL/SQL Program Units
http://rapidshare.com/files/101714449/d17169gc21_ppt.rar

D17108GC21 - 10g SQL Fundamentals I
http://rapidshare.com/files/101713435/D17108GC21_ppt.rar

D17111GC21 - 10g SQL Fundamentals II
http://rapidshare.com/files/101713775/D17111GC21_ppt.rar

D17092GC30 - 10g Administration Workshop II
http://rapidshare.com/files/101712763/D17092GC30-Oracle_Database_10g-Administration_Workshop_II.Jan.2006.rar

D17090GC30 - 10g Administration Workshop I
http://rapidshare.com/files/101711813/D17090GC30-Oracle_Database_10g-Administration_Workshop_I.Nov.2005.rar

D50000GC10 - 11g SQL and PL/SQL New Features
http://rapidshare.com/files/101605951/D50000GC10_ppt.rar

D46592GC11 - 10g Managing Oracle on Linux for System Administrators
http://rapidshare.com/files/102853129/D46592GC11_ppt.rar

D46590GC11 - 10g Managing Oracle on Linux for DBAs
http://rapidshare.com/files/101605359/10gManagingOracleonLinuxforDBA.pdf

D50081GC10 - 11g New Features for Administrators
http://rapidshare.com/files/101603860/d50081gc10_ppt.rar

D44422GC10 - 10g RAC Basic Concepts & Architecture Seminar
http://rapidshare.com/files/101576907/coursepptd44422gc10.rar

D50079GC10 - 11g Administration Workshop II
http://rapidshare.com/files/100322117/d50079gc10_ppt1.rar

D50102GC10 - 11g Administration Worshop I
http://rapidshare.com/files/94588473/D50102GC10_ppt.rar

D49990GC10 - 11g PL/SQL Fundamentals I
http://rapidshare.com/files/94580385/D49990GC10_ppt.rar

D49996GC10 - 11g SQL Fundamentals I
http://rapidshare.com/files/92312629/D49996GC10_ppt.rar

D49994GC10 - 11g SQL Fundamentals II
http://rapidshare.com/files/94579314/D49994GC10_ppt.rar

mercredi 11 novembre 2009

Initialization Parameter files: PFILEs vs. SPFILEs

When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or SPFILE. SPFILEs are available in Oracle 9i and above. All prior releases of Oracle are using PFILEs.

SPFILEs provide the following advantages over PFILEs:

An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)

Reduce human errors. The SPFILE is maintained by the server. Parameters are checked before changes are accepted.

Eliminate configuration problems (no need to have a local PFILE if you want to start Oracle from a remote machine)

Easy to find - stored in a central location

What is the difference between a PFILE and SPFILE:

A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file.

An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.

How will I know if my database is using a PFILE or SPFILE:

Execute the following query to see if your database was started with a PFILE or SPFILE:

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 
       FROM sys.v_$parameter WHERE name = 'spfile';

You can also use the V$SPPARAMETER view to check if you are using a PFILE or not: if the "value" column is NULL for all parameters, you are using a PFILE.

Viewing Parameters Settings:

One can view parameter values using one of the following methods (regardless if they were set via PFILE or SPFILE):

The "SHOW PARAMETERS" command from SQL*Plus (i.e.: SHOW PARAMETERS timed_statistics)

V$PARAMETER view - display the currently in effect parameter values

V$PARAMETER2 view - display the currently in effect parameter values, but "List Values" are shown in multiple rows

V$SPPARAMETER view - display the current contents of the server parameter file.

Starting a database with a PFILE or SPFILE:

Oracle searches for a suitable initialization parameter file in the following order:

Try to use the spfile${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)

Try to use the spfile.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)

Try to use the init${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)

One can override the default location by specifying the PFILE parameter at database startup:

SQL> STARTUP PFILE='/oradata/spfileORCL.ora'

Note that there is not an equivalent "STARTUP SPFILE=" command. One can only use the above option with SPFILE's if the PFILE you point to (in the example above), contains a single 'SPFILE=' parameter pointing to the SPFILE that should be used. Example:

SPFILE=/path/to/spfile

Changing SPFILE parameter values:

While a PFILE can be edited with any text editor, the SPFILE is a binary file. The "ALTER SYSTEM SET" and "ALTER SYSTEM RESET" commands can be used to change parameter values in an SPFILE. Look at these examples:

SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

SQL> ALTER SYSTEM SET timed_statistics=TRUE
	COMMENT='Changed by Frank on 1 June 2003'
	SCOPE=BOTH
 	SID='*';

The SCOPE parameter can be set to SPFILE, MEMORY or BOTH:

- MEMORY: Set for the current instance only. This is the default behaviour if a PFILE was used at STARTUP.

- SPFILE: update the SPFILE, the parameter will take effect with next database startup

- BOTH: affect the current instance and persist to the SPFILE. This is the default behaviour if an SPFILE was used at STARTUP.
The COMMENT parameter (optional) specifies a user remark.

The SID parameter (optional; only used with RAC) indicates the instance for which the parameter applies (Default is *: all Instances).

Use the following syntax to set parameters that take multiple (a list of) values:

SQL> ALTER SYSTEM SET utl_file_dir='/tmp/','/oradata','/home/' SCOPE=SPFILE;

Use this syntax to set unsupported initialization parameters (obviously only when Oracle Support instructs you to set it):

SQL> ALTER SYSTEM SET "_allow_read_only_corruption"=TRUE SCOPE=SPFILE;

Execute one of the following command to remove a parameter from the SPFILE:

SQL> ALTER SYSTEM RESET timed_statistics SCOPE=SPFILE SID=‘*’;
SQL> ALTER SYSTEM SET timed_statistics = '' SCOPE=SPFILE;

Converting between PFILES and SPFILES:

One can easily migrate from a PFILE to SPFILE or vice versa. Execute the following commands from a user with SYSDBA or SYSOPER privileges:

SQL> CREATE PFILE FROM SPFILE; 
SQL> CREATE SPFILE FROM PFILE;

One can also specify a non-default location for either (or both) the PFILE and SPFILE parameters. Look at this example:

SQL> CREATE SPFILE='/oradata/spfileORCL.ora' from PFILE='/oradata/initORCL.ora';

Here is an alternative procedure for changing SPFILE parameter values using the above method:

Export the SPFILE with: CREATE PFILE=‘pfilename’ FROM SPFILE = ‘spfilename’;

Edit the resulting PFILE with a text editor

Shutdown and startup the database with the PFILE option: STARTUP PFILE=filename

Recreate the SPFILE with: CREATE SPFILE=‘spfilename’ FROM PFILE=‘pfilename’;

On the next startup, use STARTUP without the PFILE parameter and the new SPFILE will be used.

Parameter File Backups:

RMAN (Oracle's Recovery Manager) will backup the SPFILE with the database control file if setting "CONFIGURE CONTROLFILE AUTOBACKUP" is ON (the default is OFF). PFILEs cannot be backed-up with RMAN. Look at this example:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Use the following RMAN command to restore an SPFILE:

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

References:

Oracle9i Database Administrator's Guide Release 2 (9.2)
Chapter 2: Creating an Oracle Database

Oracle9i Recovery Manager User's Guide Release 2 (9.2)
Chapter 5: "RMAN Concepts I: Channels, Backups, and Copies"

Oracle9i SQL Reference Release 2 (9.2)

mardi 10 novembre 2009

Find all large files on a Linux machine

Finds all files over 20,000KB (roughly 20MB) in size and presents their names and size in a human readable format:

find / -type f -size +20000k -exec ls -lh {} \; 2>/dev/null|awk '{print $NF ": " $5}'|sort -nrk 2,2

find / -type f -size +20000k -exec ls -lh {} \; 2>/dev/null|awk '{print $NF ": " $5}'|sort -nk 2,2

dimanche 8 novembre 2009

Killing Oracle Sessions

Sessions can be killed from within oracle using the ALTER SYSTEM KILL SESSION syntax.

First identify the offending session as follows:

SELECT s.sid,
       s.serial#,
       s.osuser,
       s.program
FROM   v$session s;

       SID    SERIAL# OSUSER                         PROGRAM
---------- ---------- ------------------------------ ---------------
         1          1 SYSTEM                         ORACLE.EXE
         2          1 SYSTEM                         ORACLE.EXE
         3          1 SYSTEM                         ORACLE.EXE
         4          1 SYSTEM                         ORACLE.EXE
         5          1 SYSTEM                         ORACLE.EXE
         6          1 SYSTEM                         ORACLE.EXE
        20         60 SYSTEM                         DBSNMP.EXE
        43      11215 USER1                          SQLPLUSW.EXE
        33       5337 USER2                          SQLPLUSW.EXE

The SID and SERIAL# values of the relevant session can then be substituted into the following statement:

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

In some situations the Oracle.exe is not able to kill the session immediately. In these cases the session will be "marked for kill". It will then be killed as soon as possible.

Issuing the ALTER SYSTEM KILL SESSION command is the only safe way to kill an Oracle session. If the marked session persists for some time you may consider killing the process at the operating system level, as explained below. Killing OS processes is dangerous and can lead to instance failures, so do this at your own peril.

It is possible to force the kill by adding the IMMEDIATE keyword:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

This should prevent you ever needing to use the orakill.exe in Windows, or the kill command in UNIX/Linux.

The NT Approach

To kill the session via the NT operating system, first identify the session as follows:

SELECT s.sid,
       p.spid,
       s.osuser,
       s.program
FROM   v$process p,
       v$session s
WHERE  p.addr = s.paddr;

       SID SPID      OSUSER                         PROGRAM
---------- --------- ------------------------------ ---------------
         1 310       SYSTEM                         ORACLE.EXE
         2 300       SYSTEM                         ORACLE.EXE
         3 309       SYSTEM                         ORACLE.EXE
         4 299       SYSTEM                         ORACLE.EXE
         5 302       SYSTEM                         ORACLE.EXE
         6 350       SYSTEM                         ORACLE.EXE
        20 412       SYSTEM                         DBSNMP.EXE
        43 410       USER1                          SQLPLUSW.EXE
        33 364       USER2                          SQLPLUSW.EXE

The SID and SPID values of the relevant session can then be substituted into the following command issued from the command line:

C:> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources released.

The UNIX Approach

To kill the session via the UNIX operating system, first identify the session in the same way as the NT approach, then substitute the relevant SPID into the following command:

% kill spid

If after a few minutes the process hasn't stopped, terminate the session using:

% kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using:

% ps -ef | grep ora

The session thread should be killed immediately and all resources released.

mercredi 4 novembre 2009

Top memory consuming processes

You can check for top memory consuming processes by issuing the following commands:

we use the following ps commands in order to check for performance probelms

1) Displaying top CPU_consuming processes:

ps aux | head -1; ps aux | sort -rn +2 | head -10

2) Displaying top 10 memory-consuming processes:

ps aux | head -1; ps aux | sort -rn +3 | head

3) Displaying process in order of being penalized:

ps -eakl | head -1; ps -eakl | sort -rn +5

4) Displaying process in order of priority:

ps -eakl | sort -n +6 | head

5) Displaying process in order of nice value

ps -eakl | sort -n +7

6) Displaying the process in order of time

ps vx | head -1;ps vx | grep -v PID | sort -rn +3 | head -10

7) Displaying the process in order of real memory use

ps vx | head -1; ps vx | grep -v PID | sort -rn +6 | head -10

8) Displaying the process in order of I/O

ps vx | head -1; ps vx | grep -v PID | sort -rn +4 | head -10

9) Displaying WLM classes

ps -a -o pid, user, class, pcpu, pmem, args

10) Determinimg process ID of wait processes:

ps vg | head -1; ps vg | grep -w wait

11) Wait process bound to CPU

ps -mo THREAD -p <PID>

samedi 31 octobre 2009

Oracle rename data file

Oracle is the worlds most flexible database and we have many methods for renaming tablespaces and the underlying data files.

Tablespace data file rename

We can use the alter tablespace rename datafile command, but the tablespace most be offline and you must re-name the data file while the tablespace is offline:

sqlplus: ALTER TABLESPACE cust_ts OFFLINE;

linux> mv 'OLDFILE.DBF ' 'NEWFILE.DBF'

sqlplus: ALTER TABLESPACE
cust_ts
RENAME datafile
'/u01/app/oracle/mysid/oldname.dbf'
TO
'/u01/app/oracle/mysid/newname.dbf'

Database data file rename

We can also use the alter database rename datafile command, but the data file must be renamed in the OS (using the mv linux command) while the database is down and the rename data file must be done while the database is un-opened (in the mount stage):

sqlplus: shutdown;

linux> mv 'OLDFILE.DBF ' 'NEWFILE.DBF'

sqlplus: startup mount;

sqlplus: ALTER DATABASE

RENAME file
'/u01/app/oracle/mysid/oldname.dbf'
TO
'/u01/app/oracle/mysid/newname.dbf'

RMAN data file rename

In RMAN, you can rename a data file like this:

SET NEWNAME – This command can be used to rename the data files to be restored to a new location. It is equivalent to the db_file_name_convert parameter of the server initialization parameter file. Combination of the SET NEWNAME and SWITCH command is the equivalent of the ALTER DATABASE RENAME FILE statement.

CONFIGURE AUXNAME – CONFIGURE AUXNAME is equivalent to the SET NEWNAME command, except that the CONFIGURE AUXNAME is persistent, whereas, the SET NEWNAME command must be used every time the DBA wants to rename a data file. It is necessary to connect to the recovery catalog in order to use the CONFIGURE AUXNAME command.

mercredi 28 octobre 2009

Tablespace Information

Here are some scripts related to Tablespace Information .

Information

TABLESPACE INFORMATION NOTES:

Tablespace Name - Name of the tablespace

Initial Extent - Default initial extent size

Next Extent - Default incremental extent size

Min Extents - Default minimum number of extents

Max Extents - Default maximum number of extents

PCT Increase - Default percent increase for extent size

Status - Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)

Contents - Type of tablespace. This column will have 'TEMPORARY' (v7.3+) for dedicated temporary tablespaces, and 'PERMANENT' for tablespaces that can store both temporary sort segments and permanent objects.

select	TABLESPACE_NAME,
	INITIAL_EXTENT,
	NEXT_EXTENT,
	MIN_EXTENTS,
	MAX_EXTENTS,
	PCT_INCREASE,
	STATUS,
	CONTENTS
from 	dba_tablespaces
order 	by TABLESPACE_NAME

Coalesced Exts

WAIT STATISTIC NOTES:

Tablespace Name - Name of tablespace

Total Extents - Total number of free extents in tablespace

Extents Coalesced - Total number of coalesced free extents in tablespace

% Extents Coalesced - Percentage of coalesced free extents in tablespace

Total Bytes - Total number of free bytes in tablespace

Bytes Coalesced - Total number of coalesced free bytes in tablespace

Total Blocks - Total number of free oracle blocks in tablespace

Blocks Coalesced - Total number of coalesced free Oracle blocks in tablespace

% Blocks Coalesced - Percentage of coalesced free Oracle blocks in tablespace

select	TABLESPACE_NAME,
	TOTAL_EXTENTS,
	EXTENTS_COALESCED,
	PERCENT_EXTENTS_COALESCED,
	TOTAL_BYTES,
	BYTES_COALESCED,
	TOTAL_BLOCKS,
	BLOCKS_COALESCED,
	PERCENT_BLOCKS_COALESCED
from 	dba_free_space_coalesced
order 	by TABLESPACE_NAME

Usage

TABLESPACE USAGE NOTES:

Tablespace Name - Name of the tablespace

Bytes Used - Size of the file in bytes

Bytes Free - Size of free space in bytes

Largest - Largest free space in bytes

Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%

select	a.TABLESPACE_NAME,
	a.BYTES bytes_used,
	b.BYTES bytes_free,
	b.largest,
	round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
	(
		select 	TABLESPACE_NAME,
			sum(BYTES) BYTES
		from 	dba_data_files
		group 	by TABLESPACE_NAME
	)
	a,
	(
		select 	TABLESPACE_NAME,
			sum(BYTES) BYTES ,
			max(BYTES) largest
		from 	dba_free_space
		group 	by TABLESPACE_NAME
	)
	b
where 	a.TABLESPACE_NAME=b.TABLESPACE_NAME
order 	by ((a.BYTES-b.BYTES)/a.BYTES) desc

Users Default (SYSTEM)

SYSTEM TABLESPACE USAGE NOTES:

Username - Name of the user

Created - User creation date

Profile - Name of resource profile assigned to the user

Default Tablespace - Default tablespace for data objects

Temporary Tablespace - Default tablespace for temporary objects

Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.

select 	USERNAME,
	CREATED,
	PROFILE,
	DEFAULT_TABLESPACE,
	TEMPORARY_TABLESPACE
from 	dba_users
order 	by USERNAME

Objects in SYSTEM TS

OBJECTS IN SYSTEM TABLESPACE NOTES:

Owner - Owner of the object

Object Name - Name of object

Object Type - Type of object

Tablespace - Tablespace name

Size - Size (bytes) of object

Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace

select	OWNER,
	SEGMENT_NAME,
	SEGMENT_TYPE,
	TABLESPACE_NAME,
	BYTES
from 	dba_segments
where	TABLESPACE_NAME = 'SYSTEM'
and	OWNER not in ('SYS','SYSTEM')
order 	by OWNER, SEGMENT_NAME

Freespace/Largest Ext

FREE, LARGEST, & INITIAL NOTES:

Tablespace - Name of the tablespace

Total Free Space - Total amount (bytes) of freespace in the tablespace

Largest Free Extent - Largest free extent (bytes) in the tablespace

select 	TABLESPACE_NAME,
	sum(BYTES) Total_free_space,
   	max(BYTES) largest_free_extent
from 	dba_free_space
group 	by TABLESPACE_NAME

Hit/Miss Ratios

Here are some scripts related to Hit/Miss Ratios .

Buffer Hit Ratio

BUFFER HIT RATIO NOTES:

Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.

DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).

Physical Reads - The cumulative number of blocks read from disk.

Logical reads are the sum of consistent gets and db block gets.

The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.

Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select 	sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
	sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
	sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
	round((sum(decode(name, 'consistent gets',value, 0)) +
	       sum(decode(name, 'db block gets',value, 0)) -
	       sum(decode(name, 'physical reads',value, 0))) /
	      (sum(decode(name, 'consistent gets',value, 0)) +
	       sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from   v$sysstat

Data Dict Hit Ratio

DATA DICTIONARY HIT RATIO NOTES:

Gets - Total number of requests for information on the data object.

Cache Misses - Number of data requests resulting in cache misses

Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select 	sum(GETS),
	sum(GETMISSES),
	round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from 	v$rowcache

SQL Cache Hit Ratio

SQL CACHE HIT RATIO NOTES:

Pins - The number of times a pin was requested for objects of this namespace.

Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be > 85%

select 	sum(PINS) Pins,
	sum(RELOADS) Reloads,
	round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from 	v$librarycache

Library Cache Miss Ratio

LIBRARY CACHE MISS RATIO NOTES:

Executions - The number of times a pin was requested for objects of this namespace.

Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select 	sum(PINS) Executions,
	sum(RELOADS) cache_misses,
	sum(RELOADS) / sum(PINS) miss_ratio
from 	v$librarycache

mardi 27 octobre 2009

Using Oracle's recycle bin

One of the many new features that Oracle 10g introduced is the recyclebin. When enabled, this feature works a little bit like the familiar Windows recycle bin or Mac Trash. Dropped tables go "into" the recyclebin, and can be restored from the recyclebin. OraFAQ has already published an article covering the basics; in this article, I'll cover some of the more subtle aspects of the recyclebin.

THE BASICS

First, a quick review of the basics. There are two recyclebin views: USER_RECYCLEBIN and DBA_RECYCLEBIN. For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN. The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.

When the recyclebin is enabled, any tables that you drop do not actually get deleted. Instead, when you drop a table, Oracle just renames the table and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.

For example, consider this simple table:

SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values ('Version1', sysdate);

1 row created.

SQL> select * from tst ;

COL        ROW_CHNG
---------- --------
Version1   16:10:03

If the RECYCLEBIN initialization parameter is set to ON (the default in 10g), then dropping this table will place it in the recyclebin:

SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin 
SQL> /

OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME
------------------------------ ------------- ----- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:10:12

All that happened to the table when we dropped it was that it got renamed. The table data is still there and can be queried just like a normal table:

SQL> alter session set nls_date_format='HH24:MI:SS' ;

Session altered.

SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

COL        ROW_CHNG
---------- --------
Version1   16:10:03

Since the table data is still there, it's very easy to "undrop" the table. This operation is known as a "flashback drop". The command is FLASHBACK TABLE... TO BEFORE DROP, and it simply renames the BIN$... table to its original name:

SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

COL        ROW_CHNG
---------- --------
Version1   16:10:03

SQL> select * from recyclebin ;

no rows selected

It's important to know that after you've dropped a table, it has only been renamed; the table segments are still sitting there in your tablespace, unchanged, taking up space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin
SQL> /

OBJECT_NAME                    ORIGINAL_NAME TYPE                      UND PUR DROPTIME
------------------------------ ------------- ------------------------- --- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE                     YES YES 2006-09-01:16:10:12

SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

Table purged.

SQL> select * from recyclebin ;

no rows selected

You have several purge options. You can also purge everything from the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges can purge everything from all recyclebins using DBA_RECYCLEBIN; and finally, you can purge recyclebin objects by schema and user with PURGE TABLESPACE USER .

Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space, or until you hit your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.

DROPPED TABLE VERSIONS

Just as you can wind up with several versions of a file with the same name in the Windows recycle bin, you can wind up with several versions of a table in the Oracle recyclebin. For example, if we create and drop the TST table twice, we'll have two versions in the recyclebin:

SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values ('Version1', sysdate);

1 row created.

SQL> drop table tst;

Table dropped.

SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values ('Version2', sysdate);

1 row created.

SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME
------------------------------ ------------- ----- --- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:10:12
BIN$HGnc55/8rRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:19:53

Query the two dropped tables to verify that they are different:

SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0";

COL        ROW_CHNG
---------- --------
Version1   16:10:03

SQL> select * from "BIN$HGnc55/8rRPgQPeM/qQoRw==$0" ;

COL        ROW_CHNG
---------- --------
Version2   16:19:45

If we issue a FLASHBACK DROP command for TST, which version will Oracle restore?

SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst;

COL        ROW_CHNG
---------- --------
Version2   16:19:45

Oracle always restores the most recent version of the dropped object. To restore the earlier version of the table, instead of the later one, we can either keep flashing back until we hit the version we want, or we can simply refer to the correct version of the table by using its new BIN$... name. For example, dropping TST once more gives us two versions in the recyclebin again:

SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME TYPE   UND PUR DROPTIME
------------------------------ ------------- ------ --- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE  YES YES 2006-09-01:16:10:12
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST           TABLE  YES YES 2006-09-01:16:21:00

To flashback to the first version, refer to the BIN$... name of the first version of TST:

SQL> flashback table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" to before drop;

Flashback complete.

SQL> select * from tst;

COL        ROW_CHNG
---------- --------
Version1   16:10:03

The second version is still hanging out in the recyclebin:

SQL> select object_name, original_name, operation, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME  OPERATION UND PUR DROPTIME
------------------------------ -------------- --------- --- --- -------------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST            DROP      YES YES 2006-09-01:16:21:00

DEPENDENT OBJECTS

In a modern relational database, few tables stand alone. Most will have indexes, constraints, and/or triggers. Dropping a table also drops these dependent objects. When you drop a table with the recyclebin enabled, the table and its dependent objects get renamed, but still have the same structure as before. The triggers and indexes get modified to point to the new BIN$ table name. (Any stored procedures that referenced the original object, though, are invalidated.) For example:

SQL> truncate table tst;

Table truncated.

SQL> insert into tst values ('Version3', sysdate);

1 row created.

SQL> create index ind_tst_col on tst(col);

Index created.

SQL> select * from tst;

COL        ROW_CHNG
---------- --------
Version3   16:26:10

SQL> drop table tst ;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime
  2  from recyclebin
  3  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME  TYPE   UND PUR DROPTIME
------------------------------ -------------- ------ --- --- -------------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST            TABLE  YES YES 2006-09-01:16:21:00
BIN$HGnc55//rRPgQPeM/qQoRw==$0 TST            TABLE  YES YES 2006-09-01:16:27:36
BIN$HGnc55/+rRPgQPeM/qQoRw==$0 IND_TST_COL    INDEX  NO  YES 2006-09-01:16:27:36

The RECYCLEBIN views have a few other columns that make the relationship between TST and IND_TST_COL clear:

SQL> select object_name, original_name, type, can_undrop as "UND", 
  2  can_purge as "PUR", droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME   TYPE  UND PUR DROPTIME            BASE_OBJECT   PURGE_OBJECT
------------------------------ --------------- ----- --- --- ------------------- -----------   ------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST             TABLE  YES YES 2006-09-01:16:21:00 233032        233032
BIN$HGnc55//rRPgQPeM/qQoRw==$0 TST             TABLE  YES YES 2006-09-01:16:27:36 233031        233031
BIN$HGnc55/+rRPgQPeM/qQoRw==$0 IND_TST_COL     INDEX  NO  YES 2006-09-01:16:27:36 233031        233434

The PURGE_OBJECT column is the object number of the object itself; eg. the object number of IND_TST_COL is 233434. Note the value of the BASE_OBJECT column for IND_TST_COL: 233031, the object number of the associated version of the TST table.

If we FLASHBACK DROP the TST table, its index will be restored - but Oracle will not rename it to its original name. It will retain its BIN$.. name:

SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

COL        ROW_CHNG
---------- --------
Version3   16:26:10

SQL> select index_name from user_indexes where table_name='TST' ;

INDEX_NAME
------------------------------
BIN$HGnc55/+rRPgQPeM/qQoRw==$0

I'm not sure why Oracle bothers storing the index's original name, since it doesn't seem to be used for anything. If we now drop this copy of the TST table, Oracle doesn't "remember" that the original name of the index "BIN$HGnc55/+rRPgQPeM/qQoRw==$0"was IND_TST_COL - the ORIGINAL_NAME column in RECYCLEBIN holds the ugly string "BIN$HGnc55/+rRPgQPeM/qQoRw==$0" :

SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", 
  2  droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME   TYPE  UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT
------------------------------ --------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST             TABLE YES YES 2006-09-01:16:21:00      233032       233032
BIN$HGnc56ABrRPgQPeM/qQoRw==$0 TST             TABLE YES YES 2006-09-01:16:31:43      233031       233031
BIN$HGnc56AArRPgQPeM/qQoRw==$1 BIN$HGnc55/+rRP INDEX NO  YES 2006-09-01:16:31:43      233031       233434
                               gQPeM/qQoRw==$0

Note the values in the CAN_UNDROP and CAN_PURGE columns for the index (displayed as "UND" and "PUR" above). An index cannot be undropped without the table - so CAN_UNDROP is set to NO. It can, however, be purged without purging the table:

SQL> purge index "BIN$HGnc56AArRPgQPeM/qQoRw==$1" ;

Index purged.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", 
  2  droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME  TYPE  UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT
------------------------------ -------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST            TABLE YES YES 2006-09-01:16:21:00      233032       233032
BIN$HGnc56ABrRPgQPeM/qQoRw==$0 TST            TABLE YES YES 2006-09-01:16:31:43      233031       233031

Now, if we restore the table, it will be restored without the index:

SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

COL        ROW_CHNG
---------- --------
Version3   16:26:10

SQL> select index_name from user_indexes where table_name='TST' ;

no rows selected

If you drop a table with associated LOB segments, they are handled in a similar way, except that they cannot be independently purged: CAN_UNDROP and CAN_PURGE are set to NO, and they are purged if you purge the table from the recyclebin, restored with the table if you restore it.

LIMITATIONS

A few types of dependent objects are not handled like the simple index above.

Bitmap join indexes are not put in the recyclebin when their base table is DROPped, and not retrieved when the table is restored with FLASHBACK DROP.

The same goes for materialized view logs; when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.

Referential integrity constraints that reference another table are lost when the table is put in the recyclebin and then restored.

If space limitations force Oracle to start purging objects from the recyclebin, it purges indexes first. If you FLASHBACK DROP a table whose associated indexes have already been purged, it will be restored without the indexes.

DISABLING THE RECYCLEBIN

In Windows, you can choose to permanently delete a file instead of sending it to the recycle bin. Similarly, you can choose to drop a table permanently, bypassing the Oracle recyclebin, by using the PURGE clause in your DROP TABLE statement.

SQL> purge recyclebin;

Recyclebin purged.

SQL> select * from recyclebin;

no rows selected

SQL> create table my_new_table (dummy varchar2(1));

Table created.

SQL> drop table my_new_table purge;

Table dropped.

SQL> select * from recyclebin;

no rows selected

If you disable the recyclebin at the session level, with ALTER SESSION SET RECYCLEBIN=OFF, it has the same effect as putting PURGE at the end of all your drop statements. Note, however, that you can still use FLASHBACK DROP to restore objects that were put in the recyclebin before you set RECYCLEBIN=OFF. For example:

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",
  2   droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT
------------------------------ ------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc56ACrRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:34:12      233031       233031

SQL> alter session set recyclebin=off ;

Session altered.

SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values ('Version5', sysdate);

1 row created.

SQL> drop table tst ;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",
  2   droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME   TYPE UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT
------------------------------ -------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc56ACrRPgQPeM/qQoRw==$0 TST            TABLE YES YES 2006-09-01:16:34:12      233031       233031

SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

COL        ROW_CHNG
---------- --------
Version3   16:26:10

CONCLUSION

This article has explored some of the subtler ramifications of the recyclebin. To sum up:

-The recyclebin may contain several versions of a dropped object. Oracle restores them in LIFO order; you can restore older versions by repeatedly restoring until you get the version you want, or by using the correct version's BIN$... name directly.
- Oracle drops most dependent objects along with the table, and restores them when the table is restored with FLASHBACK DROP, but does not restore their names. You can purge dependent objects separately to restore the table without them.
- Even after turning RECYCLEBIN OFF, you can FLASHBACK DROP objects that were already in the RECYCLEBIN.

10.2.0.4 Upgrade with DataGuard

Upgrade was done from 10.2.0.3 to 10.2.0.4. Dataguard has physical standby configuration. Steps and commands may vary depending on your environment.

...... primary database commands

...... standby database commands

1. On both the primary and standby host uncompress the downloaded patchset file into a new directory.

2. On the active primary database instance, identify and record the current log thread and sequence number. Then, archive the current log:

SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT';

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

3. Shut down the existing Oracle Database instance on the primary host with normal or immediate priority. Stop all listeners, agents and other processes running against the ORACLE_HOME

%lsnrctl stop

% emctl stop dbconsole

SQL> SHUTDOWN IMMEDIATE;

4. On the active standby instance that is running Redo Apply, query the V$LOG_HISTORY view to verify that each log file archived in Step 2 has been received and applied to the standby database. For example:

SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

5. Once the last log has been applied stop Redo Apply cancel managed recovery on the standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

6. Shutdown the standby instance on the standby host. Stop all listeners, agents and other processes running against the ORACLE_HOME.

% lsnrctl stop

% emctl stop dbconsole

SQL> SHUTDOWN IMMEDIATE;

7. Use 'runInstaller' to install the patchset. Do this on both the primary and standby ORACLE_HOME.

%./runInstaller

8. Once the patchset has been installed on on all hosts / nodes, startup the standby listener on the standby host first.

% lsnrctl start

9. Startup mount the standby database.

% sqlplus / as sysdba

SQL> startup mount

10. Place the standby database in managed recovery mode.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE parallel 8 DISCONNECT FROM SESSION;

11. Startup the primary instance on the primary host.

% sqlplus / as sysdba

SQL> STARTUP UPGRADE

12. Ensure that remote archiving to the standby database is functioning correctly by switching logfiles on the primary and verifying that v$archive_dest.status is valid.

SQL> select dest_id, status from v$archive_dest;

SQL> alter system archive log current;

13. Check if the archived log is applied on the standby database

SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

14. Start the Database Upgrade Assistant and upgrade the primary database.

% cd /oracle/app/oracle/product/10.2.0/bin

% ./dbua

There should be a line in the /etc/oratab file for the database that will be upgraded. Otherwise you cannot see the database when you open Database Upgrade Assistant.

DBUA may give a warning about the invalid objects onthe database and you should note these invalid object before the upgrade operation. The script below can be used to see a list of invalid objects in the database.

break on c1 skip 2

set pages 999

col c1 heading 'owner' format a15

col c2 heading 'name' format a40

col c3 heading 'type' format a10

ttitle 'Invalid|Objects'

select

owner c1,

object_type c3,

object_name c2

from

dba_objects

where

status != 'VALID'

order by

owner,

object_type

;

15. Once DBUA completes make note of the current log sequence and archive the current log:

SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT';

SQL> alter system archive log current;

16. Restart the primary database:

SQL> SHUTDOWN

SQL> STARTUP

17. Once all actions have been completed verify the standby database has been recovered to the last archive log produced by the primary. (Step 15)

On the primary:

SQL> select max(sequence#) from v$archived_log;

On the standby:

SQL> select max(sequence#) from v$log_history;

18. Run the script above again to get the list of invalid objects after database upgrade.

19. Check the success of upgrade with the following query

SQL>select comp_name, status, version from dba_registry;

20. Upgrade RMAN catalog

$ rman catalog username/password@alias

RMAN> UPGRADE CATALOG;

Note: After executing the query in step 19, i saw that Oracle Database Packages and Types component has the status INVALID, here is a work around to solve this issue:

SQL>sqlplus / as sysdba

SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan

SQL>@?/rdbms/admin/prvtspao.plb

SQL>@?/rdbms/admin/utlrp.sql

http://forums.oracle.com/forums/thread.jspa?threadID=669838

La technique du FlashBack en 10g

Introduction

En version 9i, oracle a introduit la notion de FlashBack query. En français, la possibilité d’interroger une table à un e époque révolue. Mécanisme bien sympathique pour prévenir les erreurs utilisateurs.

En 10g, ce mécanisme a été profondément perfectionné et permet de nombreuses opérations permettant de revenir en arrière sur des erreurs et ce, sur 3 niveaux :

au niveau d’une ligne

au niveau d’une table

au niveau d’une base de données complète.

Principe de fonctionnement

Le principe est différent selon l’opération de flashback à réaliser.

Si l’opération se situe au niveau ligne ou table (hors DROP) alors ce sont les informations d’annulation (UNDO) qui seront utilisées. Il est donc capital de bien régler le paramètre UNDO_RETENTION et la taille du tablespace d’annulation pour pouvoir revenir assez loin en arrière. Si le terme d’annulation n’évoque rien pour vous, une petite lecture s’impose ici : les annulations en 9i et 10g.

Si l’opération concerne un flashback avant DROP d’une table. Alors l’opération utilise le fait qu’une table n’est pas réellement supprimée. Utilisation de la "Corbeille" oracle.

Si l’opération concerne un flashback de la base de données. Alors l’opération utilise une nouvelle forme de journaux appelés journaux de FlashBack. Ces journaux sont stockés dans la flashback Recovery Area (nouveau en 10g), et dispose de l’extension FLB. Ils sont générés périodiquement et contiennent la liste des blocs modifiés durant cette période. Ils sont généralement moins volumineux que des ARCHIVE Logs. Cela dit, si on désire, réaliser un Flashback au niveau de la base de données. Celle ci doit être en mode ARCHIVELOG. En effet, les journaux de FLASHBACK ne contiennent que les blocs modifiés. Lors d’une opération de FLASHBACK DATABASE, ces blocs seront remis en place à l’instant demandé. Cela dit, il se peut qu’il y ait un décalage entre le temps demandé et le temps d’enregistrement du blocs. Pour se garder de cette différence et donc pour maintenir la cohérence de la base, on aura besoin de jouer les ARCHIVELOG.

Configuration

Niveau Ligne et tables (hors DROP)

Nous venons de voir que pour un flashback sur ligne ou sur table (hors DROP). Le tout se faisait dans le tablespace d’annulations. Il faut donc configurer les paramètres UNDO_RETENTION et la taille du tablespace d’annulations de la sorte.

Plus on désire revenir longtemps en arrière par une telle opération et plus il faudra augmenter le paramètre UNDO_RETENTION et également la taille du tablespace. Il y a donc une décision à prendre sur ce sujet dans le design de la base.

Niveau table après Suppression : DROP

Si on désire réaliser des flashback de table après les avoir supprimées. Il faut activer la "Corbeille" Oracle. Cette opération se fait en activant le paramètre RECYCLEBIN. C’est un paramètre dynamique pouvant être modifié pour la session ou pour le système. Par défaut, il est actif. Si le paramètres est inactif (OFF), il sera impossible d’effectuer un FLASHBACK après suppression sur les tables supprimées.

Ex :


ALTER SYSTEM SET RECYCLEBIN = ON SCOPE=SPFILE;
ALTER SESSION SET RECYCLEBIN = OFF;

Niveau Base de données

Pour pouvoir réaliser un FLASHBACK complet sur la base de données. Il faut :

Configurer la zone de flash ou Flash Recovery Area

Configurer le temps de retention pour effectuer une telle opération de Flashback

Activer le mode FLASHBACK pour la base de données.

Etre en mode ARCHIVELOG

- Configuration de la Flash Recovery Area. Cette zone de récupération va nous permettre de stocker les journaux de FlashBack. Elle pourra également servir de stockage des ARCHIVE LOGS, des Backup Sets RMAN. Il font donc lui donner une localisation et une taille suffisante pour contenir tous ces objets.

Cette zone se configure avec les paramètres : DB_RECOVERY_FILE_DEST : pour spécifier la localisation de la Flash Recovery Area. On peut lui spécifier une localisation physique ou un groupe de disques ASM. De plus, en environnement RAC (Real Application Cluster), toutes les instances doivent avoir la même déstination.

DB_RECOVERY_FILE_DEST_SIZE : pour spécifier la taille de la Flash Recovery Area.

Par exemple : Si on désire configurer une Flash Recovery Area dans /u02/orcl/flash de 5 GigaOctets :


ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u02/orcl/flash' SCOPE=SPFILE;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 5G SCOPE=SPFILE;

Puis redémarrer l’instance.

- Configuration du temps de rétention des informations de logs FlashBack.

Nous avons vu, plus haut, que le flashback database était rendu possible par des journaux spécifiques stockés dans la Flash Recovery Area. Ces fichiers vont contenir les informations d’une certaine période. Cette période est définie par le paramètre : DB_FLASHBACK_RETENTION_TARGET. Ce paramètre correpond au temps (en minutes) de conservation de ces journaux.

Si, par exemple, ce temps est réglé à 1440 (par défaut), alors il sera possible de revenir en arrière, au plus loin, une journée (24*60) avant l’opération.

- Activation du mode FLASHBACK

L’activation du mode FLASHBACK se réalise lorsque la base est en mode MOUNT. Je vous conseille donc de régler les paramètres précédents dans le SPFILE puis de redémarrer l’instance en lui donnant le bon mode de fonctionnement :


SHUTDOWN IMMEDIATE;
...
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

La vérification se fera dans la vue V$DATABASE (d’ou l’intérêt de configurer toutes les instances RAC à l’identique : c’est la base de données qui est activée dans le mode FLASHBACK) :


SQL> select name,flashback_on from v$database;

NAME FLASHBACK_ON
--------- ------------------
ORCL YES

Retour en Arrière !

Niveau Ligne

La commande de fonctionnement est la suivante :


SELECT colonne1[,colonne2,...,colonneN]
FROM table AS OF {TIMESTAMP | SCN} expression;

Mais plutôt que de grands discours, je vous propose un exemple :


SQL>  connect / as sysdba
Connected.
SQL> grant execute on dbms_flashback to hr;

Grant succeeded.

SQL> connect hr/hr
Connected.

SQL> select * from regions where region_id=4;

REGION_ID REGION_NAME
---------- -------------------------
4 Middle East and Africa

SQL> select to_char(SYSDATE,'DD/MM/YYYY HH24:MI:SS') as MAINTENANT,
2 dbms_flashback.get_system_change_number as SCN_COURANT
3 from dual;

MAINTENANT SCN_COURANT
------------------- -----------
26/11/2005 16:11:39 700821

SQL> -- On va prendre un café et on revient ;)
SQL> select to_char(SYSDATE,'DD/MM/YYYY HH24:MI:SS') as MAINTENANT,
2 dbms_flashback.get_system_change_number as SCN_COURANT
3 from dual;

MAINTENANT SCN_COURANT
------------------- -----------
26/11/2005 16:13:59 701157

SQL> update regions set region_name='Groenland' where region_id=4;

1 row updated.

SQL> commit;

Commit complete.

SQL> -- On va reprendre un café
SQL> -- histoire de passer un peu de temps ;)
SQL> select to_char(SYSDATE,'DD/MM/YYYY HH24:MI:SS') as MAINTENANT,
2 dbms_flashback.get_system_change_number as SCN_COURANT
3 from dual;

MAINTENANT SCN_COURANT
------------------- -----------
26/11/2005 16:18:48 702192

SQL> -- Et maintenant, on revient en arriere.
SQL> -- En jouant avec la date ...
SQL> select *
2 from regions AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE
3 where region_id=4;

REGION_ID REGION_NAME
---------- -------------------------
4 Middle East and Africa

SQL> ... ou avec le SCN
SQL> select *
2 from regions AS OF SCN 700821
3 where region_id=4;

REGION_ID REGION_NAME
---------- -------------------------
4 Middle East and Africa

SQL> Pour remettre le tout en ordre, il suffit de réaliser un update
SQL> SQL> update regions set region_name=
2 (select region_name
3 from regions AS OF SCN 700821
4 where region_id=4)
5 where region_id=4;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from regions where region_id=4;

REGION_ID REGION_NAME
---------- -------------------------
4 Middle East and Africa

Niveau Table

Données supprimées Pour revenir en arrière lorsqu’on a tout supprimé dans une table (hors structure). On réalise la séquence suivante :


FLASHBACK TABLE Table
AS OF {TIMESTAMP | SCN} expression
[ENABLE TRIGGERS];

Pour réaliser une telle opération il faut :

Avoir le droit FLASHBACK sur la table

Disposer des autorisation SELECT, INSERT et ALTER sur la table à restaurer

Activer le déplacement de lignes sur la table (ALTER TABLE table ENABLE ROW MOVEMENT) sous peine de ORA-08189.


SQL> select count(*) from regions_bis;

COUNT(*)
----------
4

SQL> delete from regions_bis;

4 rows deleted.

SQL> commit;

Commit complete.

SQL> -- Oups ....
SQL> -- 10 minutes plus tard, j'envisage le FLASHBACK;
SQL> ALTER TABLE regions_bis ENABLE ROW MOVEMENT;

Table altered.

SQL> FLASHBACK TABLE regions_bis TO TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE;

Flashback complete.

SQL> select count(*) from regions_bis;

COUNT(*)
----------
4

Table supprimée Nous avons vu plus haut, qu’en 10g, Oracle s’est doté d’une "Corbeille" ou "Recycle bin". Cette corbeille va nous permettre, si elle est activée, de récupérer une table ayant subit un DROP.

En terme de fonctionnement, tout objet supprimé, est marqué comme supprimé et placé dans la vue DBA_RECYCLEBIN (au niveau DBA) ; le niveau USER sera représenté par la vue USER_RECYCLEBIN. Une fois ces objets marqués comme supprimés. Ils pourront être récupérés. Cela dit, si le système demande des extensions suite à un manque d’espace dans le tablespace. Les extensions des segments placés dans la corbeille seront recyclés dans de nouvelles extensions.

La encore, je vais agrémenter d’un exemple, qui sera plus parlant que des longs discours.


SQL> conn / as sysdba
Connected.
SQL> show parameter recyclebin

NAME TYPE VALUE
--------------------- ----------- ------
recyclebin string on

SQL> conn hr/hr
Connected.
Name Null? Type
----------------------- -------- -------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER

SQL> select object_name,original_name,type from user_recyclebin;

no rows selected

SQL> drop table regions_bis;

Table dropped.

SQL> select object_name,original_name,type from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ --------------- -----------------
BIN$M958iDMLSwiRr1va7ePQ0A==$0 REGIONS_BIS TABLE

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
REGIONS_BIS BIN$M958iDMLSwiRr1va7ePQ0A==$0 TABLE 2005-11-27:16:11:53

SQL> desc regions_bis;
ERROR:
ORA-04043: object regions_bis does not exist

SQL> -- maintenant on restaure la table avant le DROP dans son nom original
SQL> FLASHBACK TABLE regions_bis TO BEFORE DROP;

Flashback complete.
SQL> desc regions_bis;
Name Null? Type
--------------------- -------- -------------
REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)

SQL> select object_name,original_name,type from user_recyclebin;

no rows selected

SQL> drop table regions_bis;

Table dropped.

SQL> -- On restaure la table et on en profite pour la renommer
SQL> FLASHBACK TABLE regions_bis TO BEFORE DROP RENAME TO regions_bis_repetita;

Flashback complete.

SQL> desc regions_bis;
ERROR:
ORA-04043: object regions_bis does not exist

SQL> desc regions_bis_repetita;
Name Null? Type
--------------------- -------- ---------------------------
REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)

SQL> -- Maintenant on supprime définitivement la table regions_bis_repetita:
SQL> drop table regions_bis_repetita;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
REGIONS_BIS_REPE BIN$LwfJR8+sTp2bksjHK5b3AQ==$0 TABLE 2005-11-27:16:21:30
TITA
SQL> purge recyclebin;

Recyclebin purged.

SQL> show recycle_bin;
SQL>

NB : Si on désire purger un seul objet de la Corbeille, on peut utiliser la commande :


PURGE {TABLE|INDEX} nom_de_l_objet;

Niveau base de données

Tout d’abord, il nous faut savoir vers quelle point maximum il nous est possible de revenir en arrière. Pour cela, la vue système V$FLASHBACK_DATABASE_LOG nous permettra d’obtenir la réponse :


SQL> select to_char(oldest_flashback_time,'DD/MM/YYYY HH24:MI')
2  from v$flashback_database_log
3  /

TO_CHAR(OLDEST_F
----------------
23/11/2005 22:28

Ensuite, on peut établir un FLASHBACK de la base de données. Le FLASHBACK d’une base de données implique deux points :

la base de donneés doit être dans l’état MONTEE en ARCHIVELOG

l’opération nécessite une remise à zéro des journaux de transaction (option OPEN RESETLOGS) et donc une nouvelle incarnation de la base.

Pour revenir une heure en arrière :


SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1/24
SQL> ALTER DATABASE OPEN RESETLOGS;