Oracle DB

• 履歴一覧
• ソース を表示
• Oracle DB は削除されています。
  • 1 (2006-07-28 (金) 00:49:02)
  • 2 (2006-07-28 (金) 00:56:17)

---

†

solarisx86_DB_10_1_0_3_Disk1.cpio.gz (634,888,056 bytes) (cksum - 3520147492)

1. Run "gunzip <filename>" on all the files.
   Eg.: gunzip solarisx86_DB_10_1_0_3_Disk1.cpio.gz
2. Extract the cpio archives with the command "cpio -idcmv < <filename>"
   Eg.: cpio -idcmv < solarisx86_DB_10_1_0_3_Disk1.cpio
3. Installation guides and general Oracle Database 10g documentation can be found here.

SysPreReqs

% ./runInstaller -ignoreSysPreReqs

†

http://supportforum.sun.com/jive/thread.jspa?threadID=74218

Sun Tools & Products Forums - Solaris 10, Oracle 10g and ORA-27102 out of memory

After much STFW'ing, RTFM'ing and experimenting I finally got Oracle 10g to start a 
database on a SunBlade 100 with 512MB of memory and Solaris 10. As most of the replies 
on the web about error 27102 "out of memory" address solutions in Solaris 8 and 9, I thought 
I should post a clear description of the solution here.&br;&br;
Unlike earlier releases of Solaris, most of the system parameters needed to run Oracle 
are already set properly, so the only one you need is the maximum shared memory parameter. 
In earlier versions this was called SHMMAX and was set by editing the /etc/system file and 
rebooting. With Solaris 10 you set this by modifying a "Resource Control Value". 
You can do this temporarily by using prctl, but that is lost at reboot so you will need to 
add the command to the oracle user's .profile. The other option is to create a default project 
for the oracle user: 

projadd -U oracle -K "project.max-shm-memory=(priv,2048MB,deny)" user.oracle 

what this does:
- makes a project named "user.oracle" with the user oracle as it's only member.
- because the name was of the form "user.username" it becomes the oracle user's default project.
- the value of the maximum shared memory is set to 2gb, you might want to use a larger value here 
 if you have more memory and swap.
- no reboot is needed, the user will get the new value at their next login. 

†

$ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/em/em/cabo/images/cache/ja

http://otn.oracle.co.jp/cgi-bin/non/msgview_r.cgi?communityid=otn-901233&bbsid=1&no=2009&view=9

†

execute dbms_fga.add_policy (      -
object_schema   => 'john',         -
object_name     => 'table3',       -
policy_name     => 'audit_table3', -
audit_condition => 'id = 5',       -
audit_column    => 'name',         -
enable          => true,           -
statement_types => 'select');

†

• Windows

  create tablespace tablespace1 datafile 'C:\oracle\product\10.2.0\oradata\orcl\datafile1.dbf' size 1m autoextend on;
  create tablespace tablespace2 datafile 'C:\oracle\product\10.2.0\oradata\orcl\datafile2.dbf' size 1m autoextend on;
  create tablespace tablespace3 datafile 'C:\oracle\product\10.2.0\oradata\orcl\datafile3.dbf' size 1m autoextend on;

• Solaris

  create tablespace tablespace1 datafile '/home/oracle/OraHome_1/oradata/orcl/datafile1.dbf' 
  size 1M autoextend on next 1m segment space management auto;
  create tablespace tablespace2 datafile '/home/oracle/OraHome_1/oradata/orcl/datafile2.dbf'
  size 1M autoextend on next 1m segment space management auto;
  create tablespace tablespace3 datafile '/home/oracle/OraHome_1/oradata/orcl/datafile3.dbf'
  size 1M autoextend on next 1m segment space management auto;

†

create bigfile tablespace tablespace4
datafile 'C:\oracle\product\10.2.0\oradata\orcl\datafile4.dbf' size 1 M reuse 
autoextend on next 1 M
maxsize unlimited
blocksize 8 K
extent management local uniform size 40 K
online
segment space management auto
logging;

†

create tablespace tablespace5
datafile 'C:\oracle\product\10.2.0\oradata\orcl\datafile5.dbf' size 1 M reuse 
autoextend on next 1 M
maxsize unlimited
blocksize 8 K
extent management local autoallocate
online
segment space management auto
logging;

†

create table table1 (id number, name char(10)) tablespace tablespace1;
create table table2 (id number, name char(10)) tablespace tablespace2;
create table table3 (id number, name char(10)) tablespace tablespace3;
create table table4 (id number, name char(10)) tablespace tablespace4;

†

insert into table1 values(1, 'hoge1');
insert into table1 values(2, 'hoge2');
insert into table1 values(3, 'hoge3');
insert into table1 values(4, 'hoge4');
insert into table1 values(5, 'hoge5');

insert into table2 values(1, 'hoge1');
insert into table2 values(2, 'hoge2');
insert into table2 values(3, 'hoge3');
insert into table2 values(4, 'hoge4');
insert into table2 values(5, 'hoge5');

insert into table3 values(1, 'hoge1');
insert into table3 values(2, 'hoge2');
insert into table3 values(3, 'hoge3');
insert into table3 values(4, 'hoge4');
insert into table3 values(5, 'hoge5');

insert into table4 values(1, 'hoge1');
insert into table4 values(2, 'hoge2');
insert into table4 values(3, 'hoge3');
insert into table4 values(4, 'hoge4');
insert into table4 values(5, 'hoge5');

†

drop tablespace tablespace4 including contents and datafiles;

†

alter database orcl datafile 'C:\oracle\product\10.2.0\oradata\orcl\datafile4' autoextend on;

†

alter tablespace tablespace4 offline immediate;
recover tablespace tablespace4;
alter tablespace tablespace4 online;

†

select * from dict where upper(comments) like '%PROFILE%';

†

SQL> startup


Total System Global Area  209715200 bytes
Fixed Size                  1248164 bytes
Variable Size             134218844 bytes
Database Buffers           71303168 bytes
Redo Buffers                2945024 bytes




'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE4.DBF'

C:\>rman target /



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

RMAN> restore datafile 9;

RMAN> recover datafile 9;

C:\>sqlplus / as sysdba



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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database open;



SQL> select * from john.table4;

       ID NAME
--------- ----------
        1 hoge1
        2 hoge2
        2 hoge3
        4 hoge4
        5 hoge5

SQL>

†

select property_name, property_value from database_properties where lower(property_name) like '%default%';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------
DEFAULT_TEMP_TABLESPACE        TEMP
DEFAULT_PERMANENT_TABLESPACE   USERS
DEFAULT_TBS_TYPE               SMALLFILE

†

SQL> column name format a55
SQL> select file#, name, status from v$datafile;

    FILE# NAME                                                    STATUS
---------- ------------------------------------------------------- -------
         1 /home/oracle/OraHome_1/oradata/orcl/system01.dbf       SYSTEM
         2 /home/oracle/OraHome_1/oradata/orcl/undotbs01.dbf      ONLINE
         3 /home/oracle/OraHome_1/oradata/orcl/sysaux01.dbf       ONLINE
         4 /home/oracle/OraHome_1/oradata/orcl/users01.dbf        ONLINE
         5 /home/oracle/OraHome_1/oradata/orcl/example01.dbf      ONLINE
         6 /home/oracle/OraHome_1/oradata/orcl/datafile1.dbf      ONLINE
         7 /home/oracle/OraHome_1/oradata/orcl/datafile2.dbf      ONLINE
         8 /home/oracle/OraHome_1/oradata/orcl/datafile3.dbf      ONLINE
         9 /home/oracle/OraHome_1/oradata/orcl/datafile4.dbf      ONLINE
        10 /home/oracle/OraHome_1/oradata/orcl/datafile4-1.dbf    ONLINE
        11 /home/oracle/OraHome_1/oradata/orcl/datafile4-2.dbf    ONLINE
        12 /home/oracle/OraHome_1/oradata/orcl/index.dbf          ONLINE

†

ANALYZE INDEX SYS_C005409 VALIDATE STRUCTURE;

SELECT HEIGHT,BLOCKS, LF_BLKS, BR_BLKS, LF_ROWS, DEL_LF_ROWS, DEL_LF_ROWS/LF_ROWS*100 
AS "DEL%" FROM INDEX_STATS;

†

†

CONFIGURE CONTROL FILE AUTOBACKUP ON;

†

• CONFIGURE BACKUP OPTIMIZATION ON;

†

• CONFIGURE DEFAULT DEVICE TYPE TO DISK;

• CONFIGURE DEFAULT DEVICE TYPE TO SBT;

†

• CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

• CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;

†

• BACKUP [AS BACKUPSET] DATABASE

†

BACKUP ARCHIVELOG ALL [DELETE INPUT];

†

• BACKUP AS COMPRESSED BACKUPSET DATABASE;

• CONFIGURE DEVICE TYPE DISK BACKUP TYEP TO COMPRESSED BACKUPSET;

†

• BACKUP INCREMENTAL LEVEL 0 DATABASE;

• BACKUP INCREMENTAL LEVEL 1 DATABASE;

†

• ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

†

• BACKUP [AS COPY] DATABASE;

†

• RECOVER COPY OF DATABASE;

†

• REPORT SCHEMA;

†

• ALTER DATABASE BEGIN BACKUP;

• ALTER DATABASE END BACKUP;

†

• ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

†

†

†

1. CREATE TEMPORARY TABLESPACE TEMP02
   TEMPFILE '/PATH_TO_DIR/oradata/orcl/temp02.dbf'
   SIZE 10M AUTOEXTEND ON NEXT 1M;

2. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp02;

3. DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

†

• RECOVER [AUTOMATIC] DATABASE;

†

• RESTORE[AUTOMATIC] DATABASE;

†

• RECOVER [AUTOMATIC] DATABASE UNTIL TIME 'YYYY-MM-DD:HH24:MM:SS';

• RECOVER [AUTOMATIC] DATABASE UNTIL CANCEL;

†

• RUN {
  SET UNTIL TIME="TO_DATAE('2006-07-20:10:30:00', 'YYYY-MM-DD:HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
  }

• RUN {
  SET UNTIL TIME= '06-07-20';
  RESTORE DATABASE;
  RECOVER DATABASE;
  }

†

†

†

recover database until time '2006-07-21:15:05:30' using backup controlfile;

RESTORE DATABASE UNTIL TIME "TO_CHAR('2006-07-21:15:58:11', 'YYYY-MM-DD:HH24:MI:SS')" USING BACKUP CONTROLFILE;

†

ALTER SYSTEM SWITCH LOGFILE;

†

†

• SELECT current_scn scn , SCN_TO_TIMESTAMP(current_scn) timestamp FROM v$database;

• SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) scn, SYSTIMESTAMP timestamp from dual;

†

†

• VERSIONS_STARTSCN
• VERSIONS_ENDSCN
• VERSIONS_STARTTIME
• VERSIONS_ENDTIME
• VERSIONS_XID
• VERSIONS_OPERATION

†

†

ALTER TABLE ɽ̾ ENABLE ROW MOVEMENT;

†

†

†

SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'TABLE3';

INDEX_NAME
------------------------------
BIN$GRUkInc6XW7gRAAMKa8uJw==$0

SQL> C/ENAME/RENAME
  1* ALTER INDEX "BIN$GRUkInc6XW7gRAAMKa8uJw==$0" RENAME TO TABLE3_INDEX
SQL> /

†

SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TABLE3';

CONSTRAINT_NAME
------------------------------
BIN$GRUkInc5XW7gRAAMKa8uJw==$0

SQL> ALTER TABLE TABLE3 RENAME CONSTRAINT "BIN$GRUkInc5XW7gRAAMKa8uJw==$0" TO TABLE3_CONSTRAINT;

†

• PURGE DBA_RECYCLEBIN;

• PURGE USER_RECYCLEBIN;

†

1. ALTER DATABASE ARCHIVELOG ON;

ALTER DATABASE FLASHBACK ON;

ALTER DATABASE FLASHBACK OFF;

†

†

• ANALYZE TABLE ɽ̾ VALIDATA STRUCTURE [CASCADE];

†

• BACKUP VALIDATE DATAFILE 6;

• BACKUP VALIDATE CHECK LOGICAL DATAFILE 6;

†

1. BEGIN
   DBMS_REPAIR.ADMIN_TABLES ( 
      table_type => DBMS_REPAIR.REPAIR_TABLE,
      action     => DBMS_REPAIR.CREATE_ACTION,
      tablespace => 'USERS');
   END;
   /

2. SET SERVEROUTPUT ON
   DECLARE
      v_corrupt_count NUMBER;
   BEGIN
      DBMS_REPAIR.CHECK_OBJECT (
         schema_name    => 'SCOTT',
         object_name    => 'TABLE11',
         corrupt_count  => v_corrupt_count);
      DBMS_OUTPUT.PUT_LINE ('Currupt:'|| v_corrupt_count);
   END;
   /

SELECT relative_file_id, block_id, marked_corrupt, repair_description
FROM sys.repair_table;

1. SET SERVEROUTPUT ON
   DECLARE
      v_fix_count NUMBER;
   BEGIN
     DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
        schema_name => 'SCOTT',
        object_name => 'TABLE1', 
        object_type => DBMS_REPAIR.TABLE_OBJECT,
        fix_count   => v_fix_count);
     DBMS_OUTPUT.PUT_LINE('Marked: '|| v_fix_count);
   END;
   /

2. BEGIN
     DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
       schema_name => 'SCOTT',
       object_name => 'TABLE11',
       object_type => DBMS_REPAIR.TABLE_OBJECT,
       flags       => DBMS_REPAIR.SKIP_FLAG);
   END;
   /

†

• BLOCKRECOVER CORRUPTION LIST [RESTORE UNTIL {TIME|SEQUENCE|SCN}];

†

• ALTER TABLE ɽ̾ SHRINK SPACE;

• ALTER TABLE ɽ̾ SHRINK SPACE COMPACT;

• ALTER TABLE ɽ̾ SHRINK SPACE CASCADE;

†

===============================================

†

†

SELECT INDEX_NAME, TABLE_NAME, START_MONITORING, USED FROM V$OBJECT_USAGE;

†

†

†

†

†

†

†

†

†

†

†