■ catupgrd.sql This is the script that performs the actual upgrading of the
database to the Oracle Database 11g release and it now supports parallel
upgrades of the database.
■ utlu111s.sql This is the Upgrade Status Utility script which lets you
check the status of the upgrade—that is, whether the upgraded database’s
components have a valid status.
■ catuppst.sql This is the script you run to perform post-upgrade actions. This
is new in Oracle Database 11g Release 1.
■ utlrp.sql This script recompiles and revalidates any remaining application
objects.
file from the $ORACLE_HOME/rdbms/admin
directory to a staging directory such as /u01/app/oracle/upgrade. Log in as the owner
of the Oracle home directory of the older release and run the utlu111.i sql script
(from the /u01/app/oracle/upgrade directory). Spool the results so you can review the
output. Here’s an example showing the output of an execution of the utlu111i.sql
script on my system:
SQL> spool upgrade.log
SQL> @utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool
01-30-2008 05:33:22
***********************************************************
How Is Oracle Managing My Memory?
You can monitor how Oracle is managing your memory by reviewing the
V$MEMORY_RESIZE_OPS view. This view contains a list of the last 800 SGA resize
requests handled by Oracle. Here is an example:
select parameter, initial_size, target_size, start_time
from v$memory_resize_ops
where initial_size > 0 and final_size > 0
order by parameter, start_time;
DDL WAIT Option Now DefaultIn Oracle Database 10g, by default DDL
commands would not wait if the object
was locked. Instead an error would be generated and the attempted change would
fail with an ORA-00054, indicating the resource was locked. Now, in Oracle
Database 11g, Oracle in many cases will not only not return an error, but will
execute the change without a wait being required. For example, you can now do
the following in Oracle Database 11g:
Session 1:
SQL>insert into test values (1);
1 row created.
Session 2:
SQL>alter table test add (id2 number);
Table altered.
approach but the only feasible one. In Oracle 11g, you do not need to do
much. In the session you want to issue a DDL statement, issue this SQL first:
alter session set ddl_lock_timeout = 10;
Duplicate
RMAN> duplicate database to dupdb
2> from active database
3> db_file_name_convert '/u01/app/oracle','/u05/app/oracle'
4> spfile
5> parameter_value_convert '/u01/app/oracle','/u05/app/oracle'
6> set log_file_name_convert '/u01/app/oracle',
'/u05/app/oracle'
7> set sga_max_size '3000m'
8> set sga_target '2000m';
Restoring an Archival Backup
You can issue the duplicate database command to restore an archival backup.
Here are the steps to restore and recover the database using an archival backup:
1. Create an auxiliary instance after creating the usual password file and the
parameter files. Connect to the auxiliary instance and start it.
2. Connect to the recovery catalog, the target, and the auxiliary instances, as
shown here:
RMAN> connect target sys/
RMAN> connect catalog rman/rman@catdb
RMAN> connect auxiliary /
3. Issue the list restore point all command to find out the name of
the restore points that are available.
RMAN> list restore point all;
SCN RSP Time Type Time Name
------- ------------- ---------- ------------
3074299 30-DEC-07 FIRSTQUART07
RMAN>
4. Issue the duplicate database command, making sure you specify the
correct restore point name to restore the database to the point in time the
restore point stands for.
RMAN> duplicate database
2> to newdb
3> until restore point firstquart07
4> db_file_name_convert='/u01/prod1/dbfiles/',
5>'/u01/newdb/dbfiles'
6> pfile = '/u01/newdb/admin/init.ora';
Using the Recover…Block Command
RMAN> recover datafile 2 block 24
datafile 4 block 10;
You can specify the exact backup from which you want RMAN to recover the
corrupt data blocks by specifying the backup tag with the recover . . . block
command, as shown here:
RMAN> recover datafile 2 block 24
datafile 4 block 10
The following example demonstrates how to create a flashback data archive in
the FLASH_TBS1 tablespace:
SQL> create flashback data archive flash1
tablespace flash_tbs1
retention 4 year;
Flashback Data Archive created.
SQL>
The clause retention 4 year specifies that the database must retain the
data in the new flashback data archive flash1 for four years before purging it. The
absence of the quota clause means the flash1 archive can occupy the entire
tablespace FLASH_TBS1. If you want to limit the archive to only a part of the
tablespace, specify the quota clause, as shown here:
SQL> create flashback data archive flash2
tablespace flash_tbs1
quota 2000m
retention 4 year;
Flashback Data Archive created.
SQL> drop flashback archive flash1;
SQL> alter flashback archive flash1
set default # makes flash1 the default archive
SQL> alter flashback archive flash1
add tablespace
flash_tbs1 # adds space to the flashback archive
SQL> alter flashback archive flash1
modify tablespace
flash_tbs1 quota 10G; # changes the quota for the archive
SQL> alter flashback archive flash1
modify retention
2 year; # changes the archive retention time
SQL> alter flashback tablespace flash1
add tablespace flash_tbs2; #adds a tablespace to an archive
SQL> alter flashback tablespace flash1
remove tablespace
flash_tbs2; #removes a tablespace from an archive
SQL> alter flashback archive flash1
purge all; # purges all archived data
SQL> alter flashback archive flash1
purge before
SQL> alter table employees
flashback archive;
SQL> alter table employees
flashback archive flash2;
SQL> alter table employees
no flashback archive;
Monitoring Flashback Data Archives
SQL> select flashback_archive_name,retention_in_days
from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS
---------------------- -----------------
FLASH1 365
Accessing Older Data
SQL> select transaction_number, doctor_name, count
from patient_info as of
timestamp to_timestamp ('2007-01-01 00:00:00',
'YYYY-MM-DD HH23:MI:SS');
Case Sensitive
DBA, if you want to avoid one of those upgrade-broke-my-system moments?
alter system set sec_case_sensitive_logon = false;
Default Password Use
DBA_USERS_WITH_DEFPWD, that you can query to
determine whether a given user account is using one of these default passwords
SQL> SELECT * FROM DBA_USERS_WITH_DEFPWD;
USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT
Preparing the Database for Tablespace Encryption
Configuring the Compatible Parameter Correctly
Here is an example of changing the compatible parameter (as you might do after
a database upgrade from Oracle Database 10g to Oracle Database 11g):
Alter system set compatible='11.1.0.0.0' scope=spfile;
Configure the Sqlnet.ora File (Optional) Oracle will, by default, create the wallet
in the directory $ORACLE_BASE/admin/$ORACLE_SID/wallet (you will need to
create this directory if it does not already exist). If you wish to use a location other than the default location, then configure the encryption_wallet_location parameter in the sqlnet.ora file of our database. Here is an example of such an
entry:
Encryption_wallet_location=
(source=(method=file)
(method_data=(directory=/mywallet)))
Open the Wallet and Create the Master Encryption Key
Alter system set encryption key authenticated by "robert";
The wallet is closed each time the database is shut down. You will need to
reopen the wallet each time you cycle the database, and the database will not open
until you open the wallet (which implies you have to nomount or mount the
database, open the wallet, and then open the database). The command is slightly
different to open an existing wallet:
Alter system set encryption wallet open authenticated by "robert";
While it might be an unusual operation, you can also close the wallet with the
alter system command as seen here:
Alter system set encryption wallet close;
Creating Encrypted Tablespaces
Create tablespace my_secure_tbs
datafile '/oracle01/oradata/orcl/my_secure_tbs_01.dbf' size 100m
encryption using '3DES168' default storage (encrypt);
You can also encrypt a tablespace using the default encryption algorithm by just
using the encryption keyword as seen here:
Create tablespace my_second_secure_tbs
datafile '/oracle01/oradata/orcl/my_second_secure_tbs_01.dbf' size 100m
encryption default storage (encrypt);
SECUREFILE LOBS must be created in tablespaces using Automatic Segment Space
Management (ASSM), and you must have your wallet open in order to use SECUREFILE.
Here is an example of the creation of a table with a SECUREFILE LOB:
Example:
create table notes (note_doc clob encrypt using 'AES128')
lob(note_doc) store as securefile
(cache nologging);
example of using the alter table command to compress the contents of a table. Also all future contents will be compressed:
Alter table emp move compress;
select table_name, partition_name, compression
from dba_tab_partitions
where table_name='COMPRESS_DEMO'
order by 1,2;
TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
COMPRESS_DEMO CLOSE_BUT_NOT_YET DISABLED
COMPRESS_DEMO LONG_AGO ENABLED
COMPRESS_DEMO NOT_SO_LONG_AGO DISABLED
COMPRESS_DEMO NOW_OR_FUTURE DISABLED
New lock table Parameter
Lock table my_tab in exclusive mode wait 60;
Lock table my_tab in share mode nowait;
You can make an index invisible when you create it with the create index
command by using the invisible keyword as seen in this example:
Create index ix_test on test(id) invisible;
Alter index ix_test visible;
alter index ix_test invisible;
Read-Only Tables
The alter table command can now be used to make a table read-only. This allows
the DBA to make a table read-only across the database, including the owner of the
table. The following examples demonstrate the use of the alter table command along
with the read only keywords to make a table read-only, and then the use of the read
write keywords to make the table read-write:
Alter table my_table read only;
SQL> delete from my_table;
delete from my_table
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."MY_TABLE"
alter table my_table read write;
Using the Result Cache
You can tell if you are using the result cache by looking at the execution plan of
your SQL statement. Here is an example of a SQL statement that uses the result_
cache hint, along with its associated execution plan (the output is cleaned up for the
benefit of the size of this page):
select /*+ result_cache */ sum(sal) sum from emp;
You can generate a report on the result cache using the Oracle PL/SQL
procedure dbms_result_cache.memory_report as seen in this example:
SQL> set serveroutput on
SQL> dbms_result_cache.memory_report
SQL> exec dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 864K bytes (864 blocks)
Maximum Result Size = 43K bytes (43 blocks)
[Memory]
Total Memory = 103528 bytes [0.077% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.073% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
Create Triggers as Enabled or DisabledOracle Database 11g allows you to create a trigger either as enabled (the default) or
disabled using the new enabled or disable clause. If you want to create a trigger as
disabled, you would use the disable clause as seen in this example:
Create or replace trigger trigger_two
before insert on test
disable
begin
null;
end;
/
Performance Tuning
For example, I could run the following query to find all cumulative waits of more than one second that occurred on foreground/background processes:
select event, total_waits_fg twg, total_timeouts_fg ttf,
time_waited_fg twf, average_wait_fg awf,
time_waited_micro_fg twmf
from v$system_event
where time_waited_micro_fg > 1000000
and wait_class !='Idle';
No hay comentarios:
Publicar un comentario