Backing up an Oracle database without RMAN
If you aren’t using RMAN for your backups (and are using something like Netapp’s snapshots, or the simple copy-command) (in Oracle 9i this can only be done when the Database is in mounted-mode, not opened), you can do this:
Note: Archivelog must be enabled for this to work.
ALTER DATABASE BEGIN BACKUP;
At this point, you can begin copying/taking snapshots. When you’re done:
ALTER DATABASE END BACKUP;
In older databases, you’ll have to use (run for every tablespace):
ALTER TABLESPACE tablespace_name BEGIN BACKUP; ALTER TABLESPACE tablespace_name END BACKUP;
Quote from ‘Kafka on the Shore’ – Murakami
I love how random Haruki Murakami gets:
"Are you really Colonel Sanders?"
Colonel Sanders cleared his throat. "Not really. I'm just taking on his appearance for a time."
"That's what I thought," Hoshino said. "So what are you really?"
"I don't have a name."
"How do you get along without one?"
"No problem. Basically I don't have a name or a shape"
"So you're kind of like a fart."
"You could say that."
Enabling Trace
The TRACE_ENABLED parameter allows you to trace the execution history of Oracle. It should be set to TRUE by default.
The information gathered is stored in the following locations:
#User: /usr/oracle/admin/sid/udump #Background: /usr/oracle/admin/sid/bdump #Core: /usr/oracle/admin/sid/cdump
These locations could be different depending on what’s set in your PFILE.
Check if trace is enabled:
SHOW parameter trace_enabled;
Sample output:
SQL> show parameter trace_enabled; NAME TYPE VALUE ------------------------- -------- -------------- trace_enabled BOOLEAN TRUE
And if it isn’t enabled, enable it using:
ALTER SYSTEM SET trace_enabled = TRUE;
Note: Prior to version Oracle 9i, this parameter was: _trace_enabled
Starting, stopping, and Status of Oracle Enterprise Manager (EM)
To start the Oracle Enterprise Manager Database Control (from the command-line):
emctl start dbconsole
To get the status:
emctl status dbconsole
To stop the dbconsole
emctl stop dbconsole
To access the EM (with default settings):
http://hostname:5500/em
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.