oracle 11g RAC : Starting and Stopping Instances and RAC databases with srvctl and sql

oracle 11g RAC : Starting and Stopping Instances and RAC databases with srvctl and sql

==================
A. srvctl
==================
1. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2

2. Stopping Instances and RAC databases with srvctl
[oracle@node1 ~]$ srvctl stop database -d node

3. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is not running on node node1
Instance node2 is not running on node node2

4. Starting Instances and RAC databases with srvctl
[oracle@node1 ~]$ srvctl start database -d node

5. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2

6. Stopping Instances and RAC databases with srvctl (not all instance)
[oracle@node1 ~]$ srvctl stop instance -d node -i node2

7. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is not running on node node2

8. Starting Instances and RAC databases with srvctl (not all instance)
[oracle@node1 ~]$ srvctl start instance -d node -i node2

9. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2

==================
B. sql command
==================
10. status database with sql
[oracle@node1 ~]$ sqlplus system/oracle0@node1
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node1 node1 STARTED 1 OPEN
node2 node2 STARTED 2 OPEN
sql> exit;
11. Stopping Instances and RAC databases with sql (not all instance, in ex : node1)
[oracle@node1 ~]$ sqlplus sys/oracle0@node1 as sysdba
sql> shutdown;
sql> exit;
[oracle@node1 ~]$

12. status database with srvctl
[oracle@node1 ~]$ sqlplus system/oracle0@node2
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node2 node2 STARTED 2 OPEN
sql> exit;
[oracle@node1 ~]$

13. Starting Instances and RAC databases with sql (not all instance, in ex : node1)
[oracle@node1 ~]$ sqlplus sys/oracle0@node1 as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Sep 23 13:46:08 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12521: TNS:listener does not currently know of instance requested in
connect descriptor

Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 853716992 bytes
Fixed Size 1303244 bytes
Variable Size 557845812 bytes
Database Buffers 289406976 bytes
Redo Buffers 5160960 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@node1 ~]$

=========================================
Note use command : sqlplus / as sysdba
=========================================
ERROR:
ORA-12521: TNS:listener does not currently know of instance requested in
connect descriptor

14. status database with sql
[oracle@node1 ~]$ sqlplus system/oracle0@node2
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node2 node2 STARTED 2 OPEN
node1 node1 STARTED 1 OPEN

Pemrograman PostgreSQL

1. start, shutdown postgresql
[root@bb bb]# service postgresql start
Initializing database: [ OK ]
Starting postgresql service: [ OK ]
[root@bb bb]# chkconfig postgresql on
[root@bb bb]# su potsgres
su: user potsgres does not exist
[root@bb bb]# su - postgres

2. createdb, dropdb
-bash-3.1$ createdb test
CREATE DATABASE
-bash-3.1$ psql test
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=# \q

-bash-3.1$ dropdb test
DROP DATABASE

3. createuser, dropuser
-bash-3.1$ createuser bb
Shall the new role be a superuser? (y/n) y
CREATE ROLE

-bash-3.1$ dropuser bb
DROP ROLE

4. createuser & createdb
-bash-3.1$ createuser bb -P
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
CREATE ROLE

-bash-3.1$ createdb bb
CREATE DATABASE
-bash-3.1$ exit
logout

[root@bb ~]# su - bb
[bb@bb ~]$ psql
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

-bash-3.1$ createuser admin -P
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
CREATE ROLE

5. edit configuration
error : postgresql fatal: ident authentication failed for user

[bb@bb ~]$ su - root
Password:
[root@bb ~]# vi /var/lib/pgsql/data/pg_hba.conf
# “local” is for Unix domain socket connections only
local all all password
host all all 127.0.0.1/32 password
host all all 172.20.0.0/16 password
host all all ::1/128 trust

[root@bb ~]# vi /var/lib/pgsql/data/postgresql.conf
listen_addresses = ‘*’

[root@bb ~]# service postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]
[root@bb ~]# exit

6. general command in PostgreSQL
\l :List databases
\c database-name :List databases
\d :List tables in database
\d table-name escribe table
select * from table-name :List table contents

[bb@bb ~]$ psql -U bb -d bb -W
Password for user bb:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
bb=# \l
List of databases
Name | Owner | Encoding
———-+———-+———-
bb | postgres | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
test2 | bb | UTF8
(5 rows)

bb=# \c
Password:
You are now connected to database “bb” as user “bb”.
bb=# \d
No relations found.

7. select database
bb=# \c test2
Password for user bb:
You are now connected to database “test2″.
test2=# \d
List of relations
Schema | Name | Type | Owner
——–+——————-+———-+——-
public | barang | table | bb
public | barang_id_seq | sequence | bb

test2=# \q
[bb@bb ~]$

8. test connection
[admin@bb ~]$ psql -U admin -d test2 -W
Password for user admin:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
test2=# \q

9. test connection from ip
[admin@bb ~]$ psql -U admin -d test2 -h 172.20.88.88 -W
Password for user admin:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
test2=# \q

clone database oracle

===============================================================
source
===============================================================
1. Konfigurasi RMAN pada database harus dalam mode archive log.
[oracle@iscdd ~]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

2. Konfigurasi RMAN agar archive log disimpan pada folder tertentu sesuai dengan DB_RECOVERY_FILE_DEST.
SQL> host;
[oracle@iscdd ~]$ mkdir /u02/archive/iscdd
[oracle@iscdd ~]$ exit
SQL> alter system set log_archive_dest_1=’location=/u02/archive/iscdd/archive01′;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/archive/iscdd/archive01
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> exit;

3. Setting RMAN
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 12 21:51:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2058697900)
using target database control file instead of recovery catalog
RMAN> configure controlfile autobackup on;
RMAN> configure backup optimization on;
RMAN> host;
[oracle@iscdd bin]$ mkdir /home/oracle/backup/iscdd/ -p
[oracle@iscdd bin]$ exit
RMAN> configure channel device type disk format ‘/home/oracle/backup/iscdd/%d-id-%I-%s.bkp’;
RMAN> exit;

4. Test
a. tambahan tablespace
[oracle@iscdd ~]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> create tablespace senin_ts
datafile ‘/u02/oradata/iscdd/iscdd/senin_ts01.dbf’
size 2M
segment space management auto;

b. create pfile
SQL> create pfile=’$ORACLE_HOME/dbs/initclone.ora’ from spfile;

c. tambahkan record.
SQL> conn scott/tiger;
SQL> insert into dept values(61,’IT’,’surabaya’);
SQL> insert into dept values(62,’IT’,'madiun’);
SQL> commit;
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6 rows selected.
SQL> exit;

5. Pastikan RMAN
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
RMAN> delete noprompt obsolete;
RMAN> backup database plus archivelog delete input TAG=clone_backup;
RMAN> exit;

===============================================================
target
===============================================================
6. Menyiapkan directory admin oracle, datafile oracle dan oratab.
Misalkan diinginkan SID bernama clone
[oracle@iscdd ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin)

a. menyiapkan folder admin oracle clone
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/adump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/bdump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/cdump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/dpdump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/pfile
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/scripts
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/udump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/flash_recovery_area

b. menyiapkan folder datafile dan archive oracle clone
[oracle@iscdd ~]$ mkdir -p /u02/oradata/clone/iscdd
[oracle@iscdd ~]$ mkdir -p /u02/archive/clone

c. menyiapkan oratab
[oracle@iscdd ~]$ echo “clone:/u01/app/oracle/product/10.2.0/db_1:N” >> /etc/oratab
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ oraenv
ORACLE_SID = [clone] ?

7. Menyiapkan Oracle Password File oracle clone
[oracle@iscdd ~]$ orapwd file=$ORACLE_HOME/dbs/orapwclone password=ora

8. Menyiapkan file init untuk clone
[oracle@iscdd ~]$ vi $ORACLE_HOME/dbs/initclone.ora

tambahkan dan edit, jika folder berbeda dengan perintah :
*.db_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’
*.log_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’

======================
selengkapnya :
====================

clone.__db_cache_size=717225984
clone.__java_pool_size=4194304
clone.__large_pool_size=4194304
clone.__shared_pool_size=268435456
clone.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/clone/adump’
*.background_dump_dest=’/u01/app/oracle/admin/clone/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u02/oradata/clone/clone/control01.ctl’,'/u02/oradata/clone/clone/control02.ctl’,'/u02/oradata/clone/clone/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/clone/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’
*.log_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’
*.db_name=’clone’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=cloneXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’location=/u02/archive/clone/archive01′
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=333447168
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1002438656
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/clone/udump’

9. Create SPFILE clone
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 12 14:21:20 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile=’$ORACLE_HOME/dbs/initclone.ora’;

10. Startup nomount clone
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 6758400 bytes
SQL> exit

11. RMAN Clone
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ rman TARGET sys/ora@iscdd AUXILIARY /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Sep 15 22:30:11 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2058697900)
connected to auxiliary database: CLONE (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO “CLONE”;
RMAN> exit;

12. test apakah data terakhir sudah dipindahkan ?
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> conn scott/tiger
Connected.

SQL> select * from dept;
DEPTNO DNAME LOC
—— ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

6 rows selected.
SQL> exit

13. Configure tnsnames.ora and listner.ora instance clone
[oracle@iscdd ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
clone =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = iscdd)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clone)
)
)

[oracle@iscdd ~]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = iscdd)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = clone)(PORT = 1521))
)
)

[oracle@iscdd iscdd]$ lsnrctl reload

14. test apakah listener ok ?
[oracle@iscdd iscdd]$ sqlplus scott/tiger@clone
SQL> select * from dept;
DEPTNO DNAME LOC
—— ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

6 rows selected.
SQL> exit