Oracle DBA - Blog

Coming to Primary Database (3)

[oracle@localhost ~]$ export ORACLE_SID=orcl

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 5 06:14:24 2013

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 608174080 bytes

Fixed Size 1220844 bytes

Variable Size 201330452 bytes

Database Buffers 402653184 bytes

Redo Buffers 2969600 bytes

Database mounted.

Database opened.

SQL> exit

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME

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

4 05-JUL-13 05-JUL-13

5 05-JUL-13 05-JUL-13

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

System altered.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME

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

4 05-JUL-13 05-JUL-13

5 05-JUL-13 05-JUL-13

6 05-JUL-13 05-JUL-13

7 05-JUL-13 05-JUL-13

8 05-JUL-13 05-JUL-13

9 05-JUL-13 05-JUL-13

10 05-JUL-13 05-JUL-13

11 05-JUL-13 05-JUL-13

12 05-JUL-13 05-JUL-13

13 05-JUL-13 05-JUL-13

14 05-JUL-13 05-JUL-13

SEQUENCE# FIRST_TIM NEXT_TIME

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

15 05-JUL-13 05-JUL-13

16 05-JUL-13 05-JUL-13

17 05-JUL-13 05-JUL-13

18 05-JUL-13 05-JUL-13

19 05-JUL-13 05-JUL-13

20 05-JUL-13 05-JUL-13

21 05-JUL-13 05-JUL-13

18 rows selected.

SQL> desc v$log

Name Null? Type

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

GROUP# NUMBER

THREAD# NUMBER

SEQUENCE# NUMBER

BYTES NUMBER

MEMBERS NUMBER

ARCHIVED VARCHAR2(3)

STATUS VARCHAR2(16)

FIRST_CHANGE# NUMBER

FIRST_TIME DATE

SQL> select status, MEMBERS from v$log;

STATUS MEMBERS

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

INACTIVE 1

ACTIVE 1

CURRENT 1

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

System altered.

SQL> select status,group# from v$log;

STATUS GROUP#

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

ACTIVE 1

CURRENT 2

ACTIVE 3

Standby Database Steps

oracle@192.168.0.138's password:

Last login: Fri Jul 5 01:52:04 2013 from 192.168.0.1

[oracle@localhost ~]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ pwd

/home/app/oracle/product/10.2.0/db_1/dbs

[oracle@localhost dbs]$ ls

hc_orcl.dat init.ora lkSTANDBY spfileorcl.ora standby.ctl

hc_standby.dat initorcl.ora orapworcl spfilestandby.ora

initdw.ora lkORCL orapwstandby STANBY.ctl

[oracle@localhost dbs]$ cd /home/app/oracle/admin/

[oracle@localhost admin]$ls

orcl

[oracle@localhost admin]$ mkdir standby

[oracle@localhost admin]$ ls

orcl standby

[oracle@localhost admin]$ cd /home/app/oracle/admin/standby

[oracle@localhost standby]$ mkdir adump bdump cdump dpdump udump

[oracle@localhost standby]$ ls

adump bdump cdump dpdump pfile udump

[oracle@localhost standby]$ cd /home/app/oracle/flash_recovery_area

[oracle@localhost flash_recovery_area]$ mkdir STANDBY

[oracle@localhost flash_recovery_area]$ ls

ORCL STANDBY

Configuring listner

[oracle@localhost flash_recovery_area]$ cd $ORACLE_HOME/network/admin

[oracle@localhost admin]$ ls

listener.ora samples shrept.lst sqlnet.ora tnsnames.ora

[oracle@localhost admin]$ vi listener.ora

[oracle@localhost admin]$ cat listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = orcl)

(ORACLE_HOME = /home/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = standby)

(ORACLE_HOME = /home/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.138)(PORT = 1521))

)

[oracle@localhost admin]$ ls

listener.ora samples shrept.lst sqlnet.ora tnsnames.ora

[oracle@localhost admin]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-JUL-2013 04:14:04

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

The command completed successfully

[oracle@localhost admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-JUL-2013 04:47:57

Starting /home/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production

System parameter file is /home/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Log messages written to /home/app/oracle/product/10.2.0/db_1/network/log/listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.138)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 05-JUL-2013 04:47:57

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /home/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File /home/app/oracle/product/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.138)(PORT=1521)))

Services Summary...

Service "orcl" has 1 instance(s).

Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

Service "standby" has 1 instance(s).

Instance "standby", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Configuration of tnsname

[oracle@localhost admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /home/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.138)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.oracle.com)

)

STANDBY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.138)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

[oracle@localhost admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 05-JUL-2013 04:49:05

Used parameter files:

/home/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.138)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.oracle.com)))

OK (0 msec)

[oracle@localhost admin]$ tnsping standby

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 05-JUL-2013 04:49:34

Used parameter files:

/home/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.138)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby)))

OK (0 msec)

[oracle@localhost admin]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ ls

hc_orcl.dat init.ora lkSTANDBY spfileorcl.ora standby.ctl

hc_standby.dat initorcl.ora orapworcl spfilestandby.ora

initdw.ora lkORCL orapwstandby STANBY.ctl

Password file creation

[oracle@localhost dbs]$ orapwd file=orapwstandby password=oracle force=y entries=10

[oracle@localhost dbs]$ ls

hc_orcl.dat init.ora lkSTANDBY orapwstandby STANBY.ctl

hc_standby.dat initorcl.ora orapworcl spfileorcl.ora standby.ctl

initdw.ora lkORCL orapwSTAN spfilestandby.ora

[oracle@localhost dbs]$ ls -ltr

total 13900

-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora

-rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora

-rw-r----- 1 oracle oinstall 1544 Jun 30 09:45 hc_orcl.dat

-rw-rw---- 1 oracle oinstall 24 Jun 30 10:18 lkORCL

-rw-r----- 1 oracle oinstall 1536 Jun 30 10:20 orapworcl

-rw-rw---- 1 oracle oinstall 1544 Jul 5 02:12 hc_standby.dat

-rw-rw---- 1 oracle oinstall 24 Jul 5 02:13 lkSTANDBY

-rw-r----- 1 oracle oinstall 3584 Jul 5 02:15 spfilestandby.ora

-rw-r----- 1 oracle oinstall 7061504 Jul 5 02:35 STANBY.ctl

-rw-r----- 1 oracle oinstall 4608 Jul 5 03:17 spfileorcl.ora

-rw-r----- 1 oracle oinstall 7061504 Jul 5 03:19 standby.ctl

-rw-r--r-- 1 oracle oinstall 1734 Jul 5 03:46 initorcl.ora

-rw-r----- 1 oracle oinstall 2560 Jul 5 05:22 orapwSTAN

-rw-r----- 1 oracle oinstall 2560 Jul 5 05:23 orapwstandby

Primary db location

[oracle@localhost dbs]$ cd /home/app/oracle/oradata/orcl

[oracle@localhost orcl]$ ls -ltr

total 1256076

-rw-r----- 1 oracle oinstall 20979712 Jun 30 10:19 temp01.dbf

-rw-r----- 1 oracle oinstall 52429312 Jul 5 02:23 standby01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 5 02:23 standby02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 5 02:24 standby03.log

-rw-r----- 1 oracle oinstall 52429312 Jul 5 03:21 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 5 03:21 redo03.log

-rw-r----- 1 oracle oinstall 5251072 Jul 5 03:22 users01.dbf

-rw-r----- 1 oracle oinstall 31465472 Jul 5 03:22 undotbs01.dbf

-rw-r----- 1 oracle oinstall 503324672 Jul 5 03:22 system01.dbf

-rw-r----- 1 oracle oinstall 251666432 Jul 5 03:22 sysaux01.dbf

-rw-r----- 1 oracle oinstall 52429312 Jul 5 03:22 redo01.log

-rw-r----- 1 oracle oinstall 104865792 Jul 5 03:22 example01.dbf

-rw-r----- 1 oracle oinstall 7061504 Jul 5 03:25 control03.ctl

-rw-r----- 1 oracle oinstall 7061504 Jul 5 03:25 control02.ctl

-rw-r----- 1 oracle oinstall 7061504 Jul 5 03:25 control01.ctl

[oracle@localhost orcl]$ ls

control01.ctl example01.dbf redo03.log standby03.log system01.dbf users01.dbf

control02.ctl redo01.log standby01.log standby04.log temp01.dbf

control03.ctl redo02.log standby02.log sysaux01.dbf undotbs01.dbf

[oracle@localhost orcl]$ cp *.dbf /home/app/oracle/oradata/standby/

[oracle@localhost orcl]$ cp *.log /home/app/oracle/oradata/standby/

[oracle@localhost orcl]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ ls

hc_orcl.dat init.ora lkSTANDBY orapwstandby standby.ctl

hc_standby.dat initorcl.ora orapworcl spfileorcl.ora

initdw.ora lkORCL orapwSTAN spfilestandby.ora

[oracle@localhost dbs]$ cp initorcl.ora initstandby.ora

Edit the standby init file

[oracle@localhost dbs]$ vi initstandby.ora

[oracle@localhost dbs]$ cat initstandby.ora

standby.__db_cache_size=402653185

standby.__java_pool_size=4194304

standby.__large_pool_size=4194304

standby.__shared_pool_size=192937984

standby.__streams_pool_size=0

*.audit_file_dest='/home/app/oracle/admin/standby/adump'

*.background_dump_dest='/home/app/oracle/admin/standby/bdump'

*.compatible='10.2.0.1.0'

*.control_files='$ORACLE_HOME/dbs/standby.ctl'

*.core_dump_dest='/home/app/oracle/admin/standby/cdump'

db_name='orcl'

db_unique_name=standby

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,standby)'

LOG_ARCHIVE_DEST_1=

'LOCATION=/home/app/oracle/flash_recovery_area/standby/ARCHIVELOG

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=standby'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

remote_login_passwordfile='EXCLUSIVE'

FAL_SERVER=orcl

FAL_CLIENT=standby

remote_login_passwordfile='EXCLUSIVE'

DB_FILE_NAME_CONVERT='/home/app/oracle/oradata/orcl','/home/app/oracle/oradata/standby'

LOG_FILE_NAME_CONVERT=./home/app/oracle/oradata/orcl','/home/app/oracle/oradata/standby'

STANDBY_FILE_MANAGEMENT=AUTO

*.db_recovery_file_dest='/home/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=201326592

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=605028352

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/app/oracle/admin/standby/udump'

Oracle DBA - Blog

Wednesday, July 10, 2013

Creation of Standby Database - 3

Creation of Standby Database - 2

Blogroll

Translate