Archive

Archive for June, 2011

Oracle – Change user password


There are two different methods for changing a users password. The user is able to do it themselves using the following:

SQL> password
Changing password for <username>
Old password:
New password:
Retype new password:
Password changed
SQL>

Or as sysdba you can do the following:

SQL> alter user <usernamer> identified by <password>;
User altered.

Oracle – PL/SQL show errors


To check if a package or procedure is producing any errors, within SQLPLUS run the following command:

SQL> show error

If there are any errors then these will be listed along with line numbers. If there are no errors then the following will be displayed:

SQL> show error
No errors.

Linux – GZip decompress files


GZIP can compress a file reducing it to 70 or 80 percent. Unlike tar it replaces the original file with a compressed version. The compressed file has the same ownership modes, access time, and modification time as the original.

Decompress Files

To decompress files use

gzip –d, gunzip,

or

gzcat

Options

gzip filename.txt

This will produce a compressed file called filename.txt.gz, and filename.txt will be deleted.
To decompress filename.txt.gz run the following:

gunzip filename.txt.gz

This will produce filename.txt and delete filename.txt.gz

Using the -n

Sets the speed at which files are compressed. The faster a file is compressed, the lower the amount of compression performed. With the -# option, # can be any number between 1 and 9 where 1 is the fastest and 9 is the slowest. The –fast option is the equivalent to -1 whereas –best is the equivalent to -9. By default gzip performs compression as thought the -6 option was specified.

Oracle – New DBID Post-clone Task


After a clone it is sometimes necessary to change the dbid (particularly when not using RMAN). Therefore you need to perform the following:

$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 29 11:36:36 2011
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

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

SQL> startup mount
ORACLE instance started.
Total System Global Area  264241152 bytes
Fixed Size                  2072056 bytes
Variable Size              88080904 bytes
Database Buffers          167772160 bytes
Redo Buffers                6316032 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64
With the Partitioning and Data Mining options

$ nid TARGET=/
DBNEWID: Release 10.2.0.3.0 - Production on Wed Jun 29 11:37:11 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to database SAWTST1 (DBID=1067814800)
Connected to server version 10.2.0
Control Files in database:
/sawtst1/ctrl1/sawtst1_01.ctl
/sawtst1/ctrl2/sawtst1_02.ctl
Change database ID of database SAWTST1? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1067814800 to 2196809383
Control File /sawtst1/ctrl1/sawtst1_01.ctl - modified
Control File /sawtst1/ctrl2/sawtst1_02.ctl - modified
Datafile /sawtst1/system/system_01.dbf - dbid changed
Datafile /sawtst1/undo/undotbs_01.dbf - dbid changed
Datafile /sawtst1/system/sysaux_01.dbf - dbid changed
Datafile /sawtst1/oradata/users_01.dbf - dbid changed
Datafile /sawtst1/oradata/data_01.dbf - dbid changed
Datafile /sawtst1/oradata/data_02.dbf - dbid changed
Datafile /sawtst1/oradata/data_03.dbf - dbid changed
Datafile /sawtst1/oradata/indexes_01.dbf - dbid changed
Datafile /sawtst1/oradata/indexes_02.dbf - dbid changed
Datafile /sawtst1/oradata/csmig_01.dbf - dbid changed
Datafile /sawtst1/oradata/cm_rem_ts_01.dbf - dbid changed
Datafile /sawtst1/temp/temp_01.dbf - dbid changed
Control File /sawtst1/ctrl1/sawtst1_01.ctl - dbid changed
Control File /sawtst1/ctrl2/sawtst1_02.ctl - dbid changed
Instance shut down
Database ID for database SAWTST1 changed to 2196809383.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 29 11:37:40 2011
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area  264241152 bytes
Fixed Size                  2072056 bytes
Variable Size              88080904 bytes
Database Buffers          167772160 bytes
Redo Buffers                6316032 bytes
Database mounted.

SQL> alter database open resetlogs;
Database altered.

SQL>

Oracle – Drop User/schema


To drop a user/schema and all associated tables etc, issue the following command:

SQL> drop user <username> cascade;
User dropped.

Oracle – Clone Database without RMAN

June 27, 2011 3 comments

If you need to do a clone of a database, say from production to test, then you can do this by backing up the production control file to trace, copying the datafiles and pfile across to the new test environment, then running the script created by the control file backup.

Prereqs:

The copy of the datafiles must be done with the source database shutdown, or in ARCHIVELOG mode.

Copy parameter file:

Copy the init.ora or spfile from the source database $ORACLE_HOME/dbs to the $ORACLE_HOME/dbs directory of the target database.

Vi the parameter file and change any directory names that need amending. I.e. logfile locations, control file locations, etc…

EITHER

A…………….>

A. Source database in archivelog mode

If the source database is open then you need to put the tablespaces into backup mode before starting the copy:

SQL> ALTER TABLESPACE <tablespace_name> BEGIN BACKUP;

Copy all datafiles

SQL> ALTER TABLESPACE <tablespace_name> END BACKUP;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

<……………A

OR

B…………….>

B. Source database not in archivelog mode

On source database:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
SQL> SHUTDOWN IMMEDIATE;

<…………….B

Copy control files, datafiles, all archivelogs to target destination

It may be necessary to create the directory structure for these files if it does not already exist. However, it is best to SCP -rp the entire $ORACLE_HOME/dbs directory to ensure permissions and ownership are correctly assigned and no files are missed.

Set Oracle environment

$ set ORACLE_SID = <target_oracle_sid>
$ set ORACLE_HOME = <target_oracle_home>

Setup password file

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=
$ sqlplus '/as sysdba'

EITHER

A…………….>

A. Clone to same name database

If you are cloning to a database of the same name, then after checking the control file has the correct location for the datafiles, you should be able to startup the database with the startup command:

Check datafile location:

SQL> STARTUP MOUNT
SQL> SELECT file#, name FROM v$datafile;

Rename any incorrect datafile locations:

SQL> ALTER DATABASE RENAME FILE '/old_full_directory_path/datafile_name.dbf' TO '/new_full_directory_path/datafile_name.dbf';

Check logfile location:

SQL> SELECT member FROM v$logfile;

Rename any incorrect logfile locations:

SQL> ALTER DATABASE RENAME FILE '/old_full_directory_path/logfile_name.log' TO '/new_full_directory_path/logfile_name.log';

EITHER
A…………….>

A. Database without archive logs

Open database

SQL> ALTER DATABASE OPEN
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 2072056 bytes
Variable Size 88080904 bytes
Database Buffers 167772160 bytes
Redo Buffers 6316032 bytes
Database mounted.
Database opened.
SQL>

<……………A
OR
B…………….>

B. Database with archive logs

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Wait for all archivelogs to apply then:

SQL> ALTER DATABASE CANCEL;

Open database with resetlogs if necessary

SQL> ALTER DATABASE OPEN RESETLOGS;

<…………….B

<……………A

OR

B…………….>

B. Clone to new database name

  • Rename clone pfile to new db name
  • Change directory names within pfile if necessary
  • Find the trace file containing the backup controlfile script (normally in $ORACLE_ADMIN/udump)
  • Edit the create script from the trace file such as below, remember to change directory names from source to target, save as “create_db.sql”:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "TST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 (
'/tst/redologs1/redo01_1.log',
'/tst/redologs2/redo01_2.log'
) SIZE 25M,
GROUP 2 (
'/tst/redologs1/redo02_1.log',
'/tst/redologs2/redo02_2.2og'
) SIZE 25M,
GROUP 3 (
'/tst/redologs1/redo03_1.log',
'/tst/redologs2/redo03_2.log'
) SIZE 25M
DATAFILE
'/tst/system/system_01.dbf',
'/tst/undo/undotbs_01.dbf',
'/tst/system/sysaux_01.dbf',
'/tst/oradata/users_01.dbf',
'/tst/oradata/data_01.dbf',
'/tst/oradata/data_02.dbf',
'/tst/oradata/data_03.dbf',
'/tst/oradata/indexes_01.dbf',
'/tst/oradata/indexes_02.dbf',
'/tst/oradata/csmig_01.dbf',
'/tst/oradata/cm_rem_ts_01.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER TABLESPACE TEMP ADD TEMPFILE '/tst/temp/temp_01.dbf'
SIZE 1048576000 REUSE AUTOEXTEND OFF;

  • create udump, adump, bdump, cdump directories
  • run create script

$ sqlplus '/as sysdba'
SQL> @create_db.sql

  • check database is open

<…………….B

Change DBID if required using NID

$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 29 11:36:36 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 2072056 bytes
Variable Size 88080904 bytes
Database Buffers 167772160 bytes
Redo Buffers 6316032 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64
With the Partitioning and Data Mining options

$ nid TARGET=/
DBNEWID: Release 10.2.0.3.0 - Production on Wed Jun 29 11:37:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database SAWTST1 (DBID=1067814800)
Connected to server version 10.2.0
Control Files in database:
/sawtst1/ctrl1/sawtst1_01.ctl
/sawtst1/ctrl2/sawtst1_02.ctl
Change database ID of database SAWTST1? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1067814800 to 2196809383
Control File /sawtst1/ctrl1/sawtst1_01.ctl - modified
Control File /sawtst1/ctrl2/sawtst1_02.ctl - modified
Datafile /sawtst1/system/system_01.dbf - dbid changed
Datafile /sawtst1/undo/undotbs_01.dbf - dbid changed
Datafile /sawtst1/system/sysaux_01.dbf - dbid changed
Datafile /sawtst1/oradata/users_01.dbf - dbid changed
Datafile /sawtst1/oradata/data_01.dbf - dbid changed
Datafile /sawtst1/oradata/data_02.dbf - dbid changed
Datafile /sawtst1/oradata/data_03.dbf - dbid changed
Datafile /sawtst1/oradata/indexes_01.dbf - dbid changed
Datafile /sawtst1/oradata/indexes_02.dbf - dbid changed
Datafile /sawtst1/oradata/csmig_01.dbf - dbid changed
Datafile /sawtst1/oradata/cm_rem_ts_01.dbf - dbid changed
Datafile /sawtst1/temp/temp_01.dbf - dbid changed
Control File /sawtst1/ctrl1/sawtst1_01.ctl - dbid changed
Control File /sawtst1/ctrl2/sawtst1_02.ctl - dbid changed
Instance shut down
Database ID for database SAWTST1 changed to 2196809383.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 29 11:37:40 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 2072056 bytes
Variable Size 88080904 bytes
Database Buffers 167772160 bytes
Redo Buffers 6316032 bytes
Database mounted.

SQL> alter database open resetlogs;
Database altered.

SQL>

Linux – extract cpio file


If you have a compressed .cpio file that you need to extract to a directory you can perform the following command:

$ cpio -icvdu < {filename}

Categories: Commands, Linux Tags: