Basics Part 1 - Create Datafiles, Redologs, Tablespaces, Datafiles.

Redologs:

1. Add Online redolog file group:

SQL> alter database DB01 add logfile group 4 ('/u01/app/oracle/oradata/DB01/DB01/redo4a.rdo'), ('/u01/app/oracle/oradata/DB01/DB01/redo4b.rdo') size 10M;

2. Add Onilne redo log members to Groups:

SQL>alter database DB01 add logfile member '/u01/app/oracle/oradata/DB01/DB01/redo4c.rdo' to GROUP 4;

3. Drop Redo log group:

SQL>alter database drop logfile group 4;
SQL> Database altered.

4. Drop Redo log member:

SQL>alter database drop logfile member
'/u01/app/oracle/oradata/DB01/DB01/redo01c.log';


5. Renaming and Relocationg a Redolog file:

SYS@DB01> shutdown immediate;
.mailto:.SYS@DB01> startup mount;
ORACLE instance started.
SYS@DB01> alter database rename file '/u01/app/oracle/oradata/DB01/DB01/redo01c.log' to '/u01/app/oracle/oradata/redo01cc.log';
Database altered.
mailto:SYS@DB01 database open;

6. Clearing Logfile group in case of corruption:
SQL> alter database clear logfile group 2;

Tablespaces:

1. Create tablespace options:
SQL>create tablespace ts1 datafile '/u01/app/oracle/DB01/data/df1.dbf' size 100M;
Options:
DATAFILE: Name and location of datafiles.
MINIMUM EXTENT: This ensures every used extent size in tablespace is a multiple of integer.
LOGGING: By default, all the tables, indexes in the tablespaces have all the changes recorded in the redo logs.
NOLOGGING: All the changes aren't recorded.
EXTENT MANAGEMENT: LOCAL/DICTIONARY


Archivelog mode:




Filled online redo log files are backed up using archiver(automatic or manual).

Put the database in archivelog mode:

1. SYS@DB01> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled
Archive destination /u01/app/oracle/product/9.2.0.8/dbs/arch
Oldest online log sequence 16
Current log sequence 18


2. SYS@DB01> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

3. SYS@DB01> startup mount

ORACLE instance started.

Total System Global Area 235999648
bytesFixed Size 450976
bytesVariable Size 201326592
bytesDatabase Buffers 33554432
bytesRedo Buffers 667648
bytes
Database mounted.

4.


SYS@DB01> alter database archivelog;

Database altered.


SYS@DB01> alter database open;

Database altered.

Smailto:SYS@DB01>



5. Backup your previous database as this will not be valid in future(This is because the database was in NoArchivelog mode previously and that backup is invalid)



6 Enable Archiver.
At this point we can decide if the redo log files needs are copied manually or automatically.


Automatic: In automatic archiving, Archivern background process are enables and they copy filled online redo files.

Manual: Done manually by OEM or SQL Plus.

Enabling ARCHIVER at instance startup:

LOG_ARCHIVE_START=TRUE;

Enabling ARCHIVER after the instance is started:

SYS@DB01> archive log list;

Database log mode Archive Mode

Automatic archival DisabledArchive destination /u01/app/oracle/product/9.2.0.8/dbs/arch

Oldest online log sequence 16

Next log sequence to archive 18

Current log sequence 18

SYS@DB01> alter system archive log start to '/u01/app/oracle/product/9.2.0.8/dbs/arch';

System altered.


SYS@DB01> archive log list;

Database log mode Archive Mode

Automatic archival EnabledArchive destination /u01/app/oracle/product/9.2.0.8/dbs/arch

Oldest online log sequence 16

Next log sequence to archive 18

Current log sequence 18


SYS@DB01>


Manually Archive:


SQL> alter system archive log current