2008/05/25

Change Database into Archvielog Mode

If you create your Oracle9i database through DBCA, it is created in NOARCHIVELOG mode by default unless you select archivelog option during the creating process.

You can change Archive Mode when database is mounted but now opened.

SQL> alter database archivelog;
SQL> alter database noarchivelog;
After archivelog mode is enabled and archive log destination is properly defined, the database is ready for archiving log files, either manually or automatically.
You can enable/disable automatic archiving by changing parameter archive_log_start. This parameter is static.

SQL> alter system set log_archive_start=TRUE scope=spfile;
SQL> alter system set log_archive_start=FALSE scope=spfile;
As log as automatic archival is enabled, you can see ARCn background processes running.
The following command can show you database archive settings.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\BACKUP
Oldest online log sequence     34
Current log sequence           37

2008/05/23

Open Database in Restricted Mode

Restricted mode will only allow users with RESTRICTED SESSION privileges to access the database (typically DBA’s).

We use the startup restrict command to open the database in restricted mode as seen in this example.

SQL> startup restrict;
After an instance is started, we can use alter system enable/disable restricted session to enable or disable restricted mode.

-- Put the database in restricted mode.

SQL> alter system enable restricted session;
-- Take the database out of restricted mode.

SQL> alter system disable restricted session;
We can check if the database is in restricted mode by the logins column from v$instance.

SQL> select logins from v$instance;
Any users connected to the Oracle instance when going into restricted mode will remain connected; they must be manually disconnected from the database by exiting gracefully or by the DBA with the “alter system kill session” command.

SQL> alter system kill session 'session-id, session-serial';
The session-id and session-serial parameters are found in the v$session view (columns sid and serial#).