How to connect and start Oracle 7 database:

I started my job as DBA from oracle 9i, when I had a situation to start a version 7 DB it took me few minutes to find and resolve. Today after few years there was a need to check db status of version 7 database and my friend asked how? so I thought put this steps here though now there are very less version 7,8 databases.

1) Connect to  SVRMGRL

apu82$ svrmgrl

Oracle Server Manager Release 2.3.3.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.3.0.0 - Production Release

With the distributed and parallel query options

PL/SQL Release 2.3.3.0.0 – Production

2) Connect to database as internal

SVRMGR> connect internal

Connected to an idle instance.

3) Startup the database

SVRMGR> startup

ORACLE instance started.

Total System Global Area     109157168 bytes

Fixed Size                       39808 bytes

Variable Size                 23003056 bytes

Database Buffers              81920000 bytes

Redo Buffers                   4194304 bytes

Database mounted.

Database opened.

SVRMGR> exit

SQL> desc v$instance

 Name                            Null?    Type

 ------------------------------- -------- ----

 KEY                                      VARCHAR2(64)

 VALUE                                    NUMBER

SQL> select * from v$instance;

KEY                                                                   VALUE

---------------------------------------------------------------- ----------

RESTRICTED MODE                                                           0

SHUTDOWN PENDING                                                          0

STARTUP TIME - JULIAN                                               2455414

STARTUP TIME - SECONDS                                                66687

Hi,

Another  benefit of SQL environment variable, Today I would like to tell our experience about this variable. few days back for a deployment one of my friend faced this issue,  though it is a simple matter it took more than an hour to find and fix.

The issue here was before deploying the scripts we set our environment variable scripts in which it is SCAN=Off which suppresses the processing of substitution variables. SCAN=on is default one.

From Doc
SET SCAN {ON|OFF} (obsolete)

Controls scanning for the presence of substitution variables and parameters. OFF suppresses processing of substitution variables and parameters; ON enables normal processing.

ON functions in the same manner as SET DEFINE ON.

Here is our experiment

SQL> sho scan
scan ON
SQL> conn a/a
Connected.
SQL> define schema_name=a
SQL> update &schema_name..t1 set rno=3;
old   1: update &schema_name..t1 set rno=3
new   1: update a.t1 set rno=3

700 rows updated.

SQL> set scan off
SQL> update &schema_name..t1 set rno=4;

SP2-0552: Bind variable "SCHEMA_NAME" not declared.

SQL> set scan on
SQL> update &schema_name..t1 set rno=4;
old   1: update &schema_name..t1 set rno=4
new   1: update a.t1 set rno=4

700 rows updated.

So whenever you get this error first we can check this variable and proceed with other things any..,

Happy Reading :) ..

Archive log Destination change

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\APP\ATHIL\ADMIN\ORCL\arch
...

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=C:\Athil\Arch\ORCL' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=C:\Athil\Arch\ORCL' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or
LOG_ARCHIVE_DUPLEX_DEST


SQL> sho parameter arc

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string      C:\APP\ATHIL\ADMIN\ORCL\arch

SQL> alter system set LOG_ARCHIVE_DEST='LOCATION=C:\Athil\Arch\ORCL' scope=both
  2  ;
alter system set LOG_ARCHIVE_DEST='LOCATION=C:\Athil\Arch\ORCL' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16032: parameter LOG_ARCHIVE_DEST destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory

After checking in Doc 

LOG_ARCHIVE_DEST

Property	Description
Parameter type	String
Syntax	LOG_ARCHIVE_DEST = filespec

ALTER SYSTEM SET LOG_ARCHIVE_DEST = filespec, where filespec is the new archive destination.

NO "LOCATION " for LOG_ARCHIVE_DEST

SQL> alter system set LOG_ARCHIVE_DEST='C:\Athil\Arch\ORCL' scope=both;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\Athil\Arch\ORCL
...

SQL>  alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> host dir C:\Athil\Arch\ORCL

06/08/2012  10:45 AM        38,752,256 ORCL_1_137_780346113.ARC
06/08/2012  10:45 AM             1,024 ORCL_1_138_780346113.ARC
06/08/2012  10:45 AM             3,072 ORCL_1_139_780346113.ARC