Oracle - DBA

Enabling Remote HTTP Connection to the Database

1.  Database Home Page > Administration > Manage Http Access
2.  SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

Starting Up the Database

    $ net start OracleXETNSListener
    $ net start OracleServiceXE
    $ cd %ORACLE_INSTALL_DIR%\app\oracle\product\10.2.0\server\BIN\
    $ SQLPLUS / AS SYSDBA
    $ STARTUP

Stop the Database

    $ net start OracleXETNSListener
    $ net start OracleServiceXE
    $ cd %ORACLE_INSTALL_DIR%\app\oracle\product\10.2.0\server\BIN\
    $ SQLPLUS / AS SYSDBA
    $ SHUTDOWN IMMEDIATE

 

Recovery the database if shutdown fails

    $ SHUTDOWN ABORT

Connect the Databae Remotely

To connect remotely, you must supply not just a user name and password, but a complete Oracle Net connect string.
    Connect Strings
    username/password@[//]host[:port][/service_name]

 

Manage Oracle Net Listener

    $ LSNRCTL STATUS | START | STOP

 

Managing Database Memory

System global area (SGA)
Program global area (PGA)

 

 

Managing Database Storage

• Logical structures
• Physical structures
• Recovery-related structures

 

 

Tablespaces
   

• Permanent tablespaces
          store system and user data
• Temporary tablespaces
          improve the concurrency of multiple sort operations, and reduce their overhead
• Undo tablespaces
  • undo any uncommited changes made to the database in the event that a rollback is necessary
  • provide read consistency
  • support the Flashback Query feature

Recovery-Related Structures in the Flash Recovery Area

• Backups of the physical files that make up the database (datafiles, the control file, and the server parameter file (SPFILE))

Note:
Oracle database backup and recovery is based on protecting the physical files of the database, rather than individual database objects such as tables.

• Online redo logs
• Archived redo logs
  If you enable log archiving, filled redo log files are archived (copied) in the flash recovery area before being reused. The online and archived logs together constitute a record of all changes committed to the database since the last backup was taken.

Flash Recovery Area Default Locations

Linux	/usr/lib/oracle/xe/app/oracle/flash_recovery_area/
Windows	%ORACLE_INSTALL_DIR%\app\oracle\flash_recovery_area\

 
Datafile Locations

Linux	/usr/lib/oracle/xe/oradata/XE/
Windows	C:\oraclexe\oradata\XE\

 

Online Redo Log Files

The database writes to the redo log files in a circular fashion. When the current redo log file fills, the database begins writing to the next available redo log file. (The redo log files that are not current are called inactive.) When the last available redo log file is filled, the database returns to the first redo log file and writes to it (overwriting previous redo entries), starting the cycle again.

Multiplexed Redo Log

Two or more identical copies of the redo log can be automatically maintained in separate locations.

Archived Redo Log Files

Oracle Database XE can be configured so that a background archiving process makes copies of filled, inactive redo log files in the flash recovery area before they are reused. Redo log files copied in this way are called archived redo log files.
A database configured to archive redo logs is said to be in ARCHIVELOG mode. (A database not configured to archive redo logs is said to be in NOARCHIVELOG mode.)

     

When you drop (delete) a user, you must either first drop all the user's schema objects, or use the cascade feature of the drop operation, which simultaneously drops a user and all of his schema objects.

Role:
    CONNECT
    RESOURCE
    DBA
Internal User Acounts
    SYSTEM
        all administrative orther than startup/shutdown the database
    SYS
        If you want to log in as SYS with SQL Command Line (SQL*Plus), you must connect to the database "AS SYSDBA".
        SQL > connect sys/password as sysdba
Operating System Authentication
    Group Name
        Linux
            dba
                User Name
                    oracle
        Windows
            ORA_DBA

 

 

 

 

 

 

 

 

 

 

 

export ORACLE_BASE=/home/oracle/app #export ORACLE_SID=orcl export ORACLE_HOME=/home/oracle/app/oracle/product/10.2.0/db_1 export ORACLE_SID=orcl # start the listener /home/oracle/app/oracle/product/10.2.0/db_1/bin/lsnrctl start #start the db /home/oracle/app/oracle/product/10.2.0/db_1/bin/dbstart #start em /home/oracle/app/oracle/product/10.2.0/db_1/bin/emctl start dbconsole