Ramin Hossaini (blog)

Update statistics on all objects in a database

This helps Oracle choose the best execution plan for queries:

BEGIN
dbms_stats.gather_database_stats(options=> 'GATHER AUTO');
END;
/

Turning Archive-log mode on and off

You can check what mode the database is in with:

1

SELECT LOG_MODE FROM SYS.V$DATABASE;

If you do not specify an archive-log location, it will end up going to a directory like $ORACLE_HOME/dbs - which can be quite a mess.
Check where it's going:

1

SHOW PARAMETER log_archive_dest;

It would be good practice to set the location if it doesn’t show one.
To turn archive-log-mode on or off:

shutdown database:

1
2
3

SHUTDOWN IMMEDIATE
STARTUP RESTRICT
SHUTDOWN

Mount the database:

1

ALTER DATABASE MOUNT;

Then issue one of the following:

1
2

ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE ARCHIVELOG;

If the location hasn’t been set yet:

1

ALTER SYSTEM SET LOG_ARCHIVE_DEST = "/ARC_LOCATION";

Then open the database:

1

ALTER DATABASE OPEN;

There might also be archivelog related entries in your PFILE, which you should also make changes to.