Archiving

How to put the database in archivelog mode ?

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                  1218412 bytes
Variable Size              67111060 bytes
Database Buffers          113246208 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


[oracle@devuser bdump]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 22 15:26:49 2013

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

Enter user-name: / as sysdba

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

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/home/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     0
Current log sequence           1

To put the database in archive log mode use below command.
SQL> alter database archivelog;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/home/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1

Also you can check with below commands.

SQL> select archiver from v$instance;

ARCHIVE
-------
STARTED

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> alter database open;

Database altered.

SQL>

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

How to check whether your database startup with the pfile or spfile in 10g ?

SQL> startup
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                  1218412 bytes
Variable Size              67111060 bytes
Database Buffers          113246208 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/home/oracle/product/10.2.
                                                 0/db_1/dbs/spfilePROD.ora

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/home/oracle/product/10.2.
                                                 0/db_1/dbs/spfilePROD.ora
SQL>

You can also check by usin below command

SQL> select name,value from v$parameter2 where name like '%spfile%';

NAME                                                                             VALUE
-------------------------------------------------------------------------------- --------------------------------------------------
spfile                                                                                      /u01/home/oracle/product/10.2.0/db_1/dbs/spfilePROD.ora

==========


SQL> startup nomount pfile=initPROD.ora;
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                  1218412 bytes
Variable Size              67111060 bytes
Database Buffers          113246208 bytes
Redo Buffers                7168000 bytes

SQL> alter database open;

Database altered.

No path in value column and thats mean your database startup with the pfile.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>
SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>

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